0

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
Mike Brant
  • 70,514
  • 10
  • 99
  • 103
user2301506
  • 290
  • 5
  • 11
  • the join `join traceability t2 on (t1.parent in (select t2.child))` could be done as `join traceability t2 on t1.parent = t2.child` – Abhik Chakraborty Oct 17 '14 at 14:39
  • are you sure the databse you executed that first statement on is the same as the one you are trying your php code on? I.e. did you ensure they both have the same schema as the error indicates the db you are connecting to via php is missing `child`-column on `traceability` – DrCopyPaste Oct 17 '14 at 14:39
  • Hello DrCopyPaste - Yes 100% sure it's the same db. – user2301506 Oct 17 '14 at 14:39
  • I think your join clause is wrong, try ... on (t1.parent = t2.child) ... – Anas Oct 17 '14 at 14:45
  • @Abhik Chakraborty - I changed the query to the following as per your suggestion but still get the same error -> update traceability t1 inner join traceability t2 on t1.parent = t2.child set t1.root=false; – user2301506 Oct 17 '14 at 14:49
  • can you post the "traceability" table fields ? – Anas Oct 17 '14 at 14:53
  • @Anas I posted an answer to myself so the output of the describe is formatted for easier reading. – user2301506 Oct 17 '14 at 15:07

2 Answers2

0

Try 

UPDATE traceability AS t1 set t1.root=false WHERE t1.parent IN (SELECT child FROM traceability)

Anas

Anas
  • 366
  • 1
  • 6
0

It looks like you are trying to do a self-join in order to set any child nodes to have a value of root = 'false'. That should look like this:

UPDATE traceability AS t1
INNER JOIN traceability AS t2
  ON t1.parent = t2.child
SET t1.root='false'

It also seems unusual to have a two-way child-parent reference in your table. Not a common table schema at all. Are you saying each parent can only have a single child?

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • I already tried this as suggested by Abhik - this also works in SQL but not in my PHP script. Other SQL statements are working fine in the script but statements with a table self join are all working correctly in SQL script but not in PHP script. – user2301506 Oct 17 '14 at 15:27
  • @user2301506 With the brief code snippet you posted, it doesn't seem as if you are doing any error handling at all. Have you verified that you get a valid prepared statement object back from your `query()` call? You should always check this before trying to call `execute()` as it gives you a chance to catch and log any errors. With what you have shown in your question so far, there is nothing we can do to help further. You need to debug to see what error(s) you are getting. The error in your initial post was due to bad SQL statement as noted in my answer. – Mike Brant Oct 17 '14 at 15:31
  • "You can't reference a table alias in outer select within a subselect" - This is not true. – Dávid Horváth Oct 17 '14 at 15:47
  • @MikeBrant Is doing a try/catch going to give me any more info than the error caught in my /var/log/httpd/php_errors.log I posted earlier? I don't think the error is bad coding - the statement has been running fine as part of an SQL batch script for more than 1 year - just trying to tidy up and make things more consistent in our batch environment and move this batch processing into php script. Most of the commands continue to work fine but there are 2 that are using a self join and both fail - using my original SQL, Abhik's suggestion and your suggestion. The error thrown by PHP is same. – user2301506 Oct 17 '14 at 15:56
  • @MikeBrant The table contains sets of tuples that build relationship trees. A parent in one row can be a child in another. – user2301506 Oct 17 '14 at 16:02
  • @user2301506 Understood, but you really only need a single parent field to describe the parent of the current node. With your approach how would you plan on entering a relationship between 1 parent with 5 children. In a typical adjacency list tree representation (which seems to be what you are attempting), you would have 6 records (one for parent and one for each child node with parent's row ID in `parent` column). In your approach, you would assumedly have 5 different records for the parent node alone (one for each parent-child tuple) and then 5 records for the children. – Mike Brant Oct 17 '14 at 19:49
  • @user2301506 If you were then to say add a third generation with each second generation child having 5 children each (25 grandchildren of first node), you would then need to add mode records for the second generation (to total 25), which would cause you to then have to have more records on the parent node (to 25 as well) to match the 25 leaf nodes in the 3rd generation. You table would grow exponentially with each generation! This seems like a bad design unless of course each parent can have one and only one child (making the structure a degenerative tree - a doubly linked list). – Mike Brant Oct 17 '14 at 19:52
  • @user2301506 I would highly recommend looking through this post - http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462 where you have the excellent answer by Bill Karwin on talking about various models for tree in relational databases. Perhaps you were aiming for more of a closure table model like he presents? There are other good answers as well on nested sets model. – Mike Brant Oct 17 '14 at 19:54
  • @Mike Brant - Back to your question about the number of rows for 1 parent with 5 children -> yes, indeed my table would have 6 rows for this. If each of the children had 5 children then there would be an additional 25 rows (5 parents x 5 children each) in the table. Basically the table has 1 row for each tree node (includes root and leaf nodes as well as nodes at branches). For tree-grouping reasons, there is an additional 1 row for each root node, where the parent of the root is a group id. – user2301506 Oct 19 '14 at 18:23