DevChunks

subquery workaround in MySQL 4

One of the most valuable additions to MySQL 5 is the subquery, which lets you execute SQL like this:

SELECT username FROM users WHERE user_id IN (SELECT user_id FROM admins);

Unfortunately, MySQL 4 lacks this feature and is still prevalent in many web hosts. If you need to use a subquery, try the following workaround in PHP;


# Get the admin user_ids into an array
$sql = "SELECT user_id FROM admins";
$rs = mysql_query($sql);
$adminuserids = array();

while ($ra = mysql_fetch_array($rs)) {
  $adminuserids[] = $ra['user_id'];
}

# Now the clever bit - implode your array into a comma seperated string
$adminuserids = implode(',',$adminuserids);

# Our sql with "sub query"
$sql = "SELECT username FROM users WHERE user_id IN ($adminuserids)";

Step by step:

  • Get an array of user_ids from the “admins” table
  • implode the array, so we get a string similar to “1,5,7,8,4,9”
  • append the string to the SQL, so we get something like: “SELECT username FROM users WHERE user_id IN (1,5,7,8,4,9);”

This is a bit more code to do, but depending on the SQL you are writing, this may save you time and code if you are writing for MySQL 4.

Plus, the added benefit is that it is pretty easy to upgrade the code if/when you move to MySQL 4.

Share:

3 Responses

Leave a Reply

Your email address will not be published.

More Posts

How to use PHP Type Declarations

PHP has developed into a more strictly typed language in recent years. Type Declarations help with that and are a fairly new addition to PHP,

Iteration

If you have come to JavaScript from a background in Java or C, then you will like the variation on the for loop. It is

Context Object Manipulation

One of the key concepts of JavaScript is the keyword this, which signifies the JavaScript context object. If you write an inner function, you can access

Polymorphic Functions

It is possible to define multiple functions with the same name in JavaScript, and the last one of those that you define will be the