0

Hi I'm just learning how to use php and I'm testing out how to use a SQL trigger. What I need to do is print out the fields value before the trigger and then again after the trigger has been activated and changed the value. When I try to do this by before and after the trigger print out the same thing when i want the before to print out the before value and the after to print out the after value. It also doesnt print out the new value from the trigger running it once but it does running it a second time. Could someone help me. My code is below.

<?php


$connect = mysql_connect("localhost","jfunchio","810479431") or die (mysql_error());
mysql_select_db("jfunchio") or die(mysql_error());


$query = "SELECT tot_cred, name
          FROM student
          WHERE ID = '45678'";

$result = mysql_query($query) or die("Query failed:" . mysql_error());

echo "Before trigger: ";

for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--)
{   if (!mysql_data_seek($result, $i))
    {
            echo "Cannot seek to row $i: " . mysql_error() . "\n";
            continue;
        }

        if(!($row = mysql_fetch_row($result)))
            continue;

        print_r($row);
    }



$trigger = "DELIMITER $$
CREATE TRIGGER grade_change AFTER UPDATE on takes
FOR EACH ROW BEGIN
IF (OLD.grade='F' OR OLD.grade IS NULL) AND NEW.grade != 'F' AND NEW.grade IS NOT NULL THEN
  BEGIN
    set @c=(SELECT credits FROM course WHERE course.course_id=NEW.course_id);
    UPDATE student SET tot_cred=tot_cred+@c WHERE student.id=NEW.id;
  END;
END IF;

DELIMITER $$";

mysql_query($trigger);

$update = "update takes
          set grade = 'B'
          where grade = 'F' or null";

mysql_query($update);

echo "After trigger: ";

for ($i = mysql_num_rows($result) - 1; $i >= 0; $i--)
{   if (!mysql_data_seek($result, $i))
    {
            echo "Cannot seek to row $i: " . mysql_error() . "\n";
            continue;
        }

        if(!($row = mysql_fetch_row($result)))
            continue;

        print_r($row);
    }

    mysql_free_result($result);


?>
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by smantscheff
0

Without looking to deeply into this, first fix the condition

grade = 'F' or null

It should read

grade = 'F' or grade is null

Edited by smantscheff: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.