The following SQL statement runs correctly against a MySQL database table in an SQL script or in phpmyadmin but errors when run from within a PHP script.
The SQL statement is:
update traceability t1 inner join traceability t2 on (t1.parent in (select t2.child)) set t1.root=false;
in the PHP script the code looks like:
$sqlupdatestmt = $db->query("update traceability t1 inner join traceability t2 on (t1.parent in (select t2.child)) set t1.root=false;");
$sqlupdatestmt->execute();
The error in the PHP log is:
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 't2.child' in 'field list'' in /<myfilepath> /traceability.php:50
Stack trace:
#0 /<myfilepath>/traceability.php(50): PDO->query('update traceabi...')
#1 {main}
What's going wrong in the PHP that it throws a PHP/SQL error?
Here is information on traceability table schema:
Field Type Null Key Default Extra
Variant varchar(20) YES MUL NULL
Parent varchar(50) YES NULL
Child varchar(50) YES NULL
Relation varchar(20) YES NULL
Root tinyint(1) YES NULL
Leaf tinyint(1) YES NULL