auto update another table field on add

[sunpat]

Hello all,

 

I have a database setup as following:

 

Table1:

 

ID

Student

phone

last_class_date

remarks

 

Table2:

 

ID

class_Date

Student_ID

Time

billable_amount

remarks

 

So this is what i am trying to do from couple days now. When i add a record into Table 2, I want to automatically update Table1:Last_class_date, so that when i view tabe1, i can sort out student who have not attented class for few weeks or etc.

 

Any help is much appreciated.

 

Thanks,

[gizmola]

Seems pretty simple to me.

 

First you could always use a trigger. You didn't state what database you were using but I don't know of any that wouldn't give you a simple way of updating the last_class_date for the appropriate ID in table1 using an "insert or after insert" trigger.

 

You could also pretty easily do this in your php code with a 2nd query after the INSERT query.

 

$sth->prepare('INSERT INTO Table2 (Student_ID, ....) VALUES (?, ....)');
$sth->bindParam(1, $student_id, PDO::PARAM_INT);
if ($sth->execute()) {
   $sth->prepare('UPDATE Table1 SET last_class_date = CURDATE() WHERE ID = ?');
   $sth->bindParam(1, $student_id, PDO::PARAM_INT);
   $sth->execute();
}

 

[Barand]

Why update table? MAX(class_date) from table2 will give it for a student

[sunpat]

I am using mysql.

 

I did try the code from gizmola but its giving me error 256. Dont know much on this still a beginner to programming.