Post image for subquery workaround in MySQL 4

subquery workaround in MySQL 4

by Carey on December 10, 2010

  • Share
  • CevherShare
  • Share

One of the most useful 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 is lacking this feature and is still prevalent in many web hosts. If you have a need to use a subquery, try this following method (example is 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 even save you time and code if you are writing for MySQL 4. Plus the added benefit is that it is quite easy to upgrade the code if/when you move to mysql 4.

{ 3 comments }

rudi November 29, 2007 at 8:56 am

Thx for this post man, it help me a lot
cause my server still using Mysql 4.x :)

Great Job

Polswa December 16, 2007 at 3:32 am

Well, you can also use purely MySQL solution: rewriting subselect with JOIN.

Dom January 3, 2008 at 7:17 am

Subqueries are available in MySQL 4.1, so if your webserver is running this version (or a higher version of course) then you don’t need this workaround. See http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html

Comments on this entry are closed.

Previous post:

Next post: