php - Changing a field from a table depending on a mysql query -

the prototype software creating uses mvc framework.

i trying change allowed field in entrylog table either "y" (yes) or "n" (no) depending on whether card associated driver authorised or not.

the 4 tables involved card, driver, state , entrylog table. there card_driver table associates card driver.

create table if not exists `card` (     `id`           int(11)     not null auto_increment,     `startdate`    date        not null,     `enddate`      date        not null,     `state_id`     int(11)     not null,     `referred_as`  varchar(40) not null,     primary key  (`id`) );  create table if not exists `driver` (     `id`           int(11)     not null auto_increment,     `title`        varchar(40) default null,     `supplier_id`  int(11)     not null,     `referred_as`  varchar(40) not null,     primary key  (`id`) );  create table if not exists `card_driver` (     `card_id`      int(11)     not null,     `driver_id`    int(11)     not null );  create table if not exists `state` (     `id`           int(11)     not null auto_increment,     `referred_as`  varchar(40) not null,     primary key  (`id`) );  create table if not exists `entrylog` (     `id`           int(11)     not null auto_increment,     `vehicle_id`   int(11)     not null,     `driver_id`    int(11)     not null,     `venue_id`     int(11)     not null,     `supplier_id`  int(11)     not null,     `deliverydate` date        not null,     `allowed`      binary(1)   not null default 'n',     primary key  (`id`) ); 

in driver table have code checks authorisation of selected drivers associated card. apply similiar code entry log, when using form enter delivery determines 'allowed' field automatically. code check drivers authorisation in driver table follows:

if ($name == driver) { ?>     <? include('common.php') ?>  <?php if(isset($_post['check'])) { $conn = mysql_connect($server, $db_user, $db_pass); if(! $conn ) {  die('could not connect: ' . mysql_error()); }  $id = $_post['id'];    $sql ="select *   driver d  inner   join card_driver cd     on cd.driver_id = d.id inner   join card c     on c.id = cd.card_id       d.id = $id"; mysql_select_db('damp'); $result = mysql_query( $sql, $conn ); $row = mysql_fetch_assoc($result); switch($row['state_id']) { case "1":    echo "<strong><font color=\"green\">authorisation granted!</font></strong>";    break; case "2":    echo "<strong><font color=\"red\">your card has expired , authorisation denied</font></strong>";    break; case "3":    echo "<strong><font color=\"red\">your card has been cancelled , authorisation denied</font></strong>";    break; default:    echo "<strong><font color=\"red\">the card id not exist</font></strong>"; } 

thank or advise can give me.

edit : code controller_create.php

  if ($class_obj == "entrylog")   {     $id = $_post['id'];     $driverid = myactiverecord::findbyid('driver',$this_obj->driver_id);     $query = mysql_query("supdate entrylog el inner join driver d on d.driver_id = el.driver_id  inner join card_driver cd on cd.driver_id = d.id inner join card c on c.id = cd.card_id      set el.allowed = case when c.state_id = 1                        'y'                        else 'n'                   end el.id = $entrylogid");  mysql_select_db('damp');   } 

not entirely clear on you're asking following may help:

update entrylog el inner join driver d on d.id = el.driver_id  inner join card_driver cd on cd.driver_id = d.id inner join card c on c.id = cd.card_id      set el.allowed = case when c.state_id = 1                        'y'                        else 'n'                   end el.id = $entrylogid 

from recent comment sounds value should calculated part of insert though, rather update later.