Work around for sub query issue in older version of MySql

We had to work with previous/older version (4.x) of MySql in one of our Projects.

We faced some difficulties with sql queries. Actually the required sql query was to delete some child table records based on few parent ids.

By searching Internet, we came to know that older version of MySql won't support subquery.

And also, we came to know that everything that can be done with subquery can also be done with Joins (inner join and/or outer joins).

But in our case, Joins didn't help to resolve the issue. We got syntax error in our set up, whereas the same query was working fine in some other environments.

So finally we came up this below solution.
-just we joined/concatenated all the required parent ids with comma.
-Used this concatenated string in the IN clause of another query.

Find below the sample

 $sql="select id from parent_table WHERE `ref_id` = $inputid";
 
 $parent_id="NULL";
 $result=mysql_query($sql);
 while($resultrow=mysql_fetch_assoc($result))
 {
$parent_id.=$resultrow['id'] .",";
 }
 $parent_id=substr($parent_id,0,strlen($parent_id)-1); //for removing last comma
 
mysql_query("DELETE FROM `child_table` WHERE `parent_id` in(".$parent_id.")");

In the above code we had to use "$parent_id="NULL";", as we faced issue if the query didn't return any value. (i-e $parent_id is empty)


More Articles...