Need to use LIMIT with subquery!!!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
seodevhead
Forum Regular
Posts: 705
Joined: Sat Oct 08, 2005 8:18 pm
Location: Windermere, FL

Need to use LIMIT with subquery!!!

Post by seodevhead »

Hey guys... I wrote a mysql query that uses a subquery but I am getting an error stating that I can't use LIMIT with a subquery. I desperately need to apply a LIMIT to this subquery as I cannot figure out any other way of writing this query using joins, alternative methods, etc. I am using 4.1.14. Any idea how I can go about getting around this? Thanks.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

the only way I would know of without any kind of joins is to perform the query on it's own (the subquery part) and the implode the list using a comma as a delim

so like this un-tested psuedo code for example:

Code: Select all

$result = mysql_query('subquery limit 10');
$subquery=array();
while($row=$result) {
$subquery[]=$row;
}
$subquery = implode(',', $subquery);
$result = mysql_query('do something where field in ('.$subquery.') ');
certainly not the cleanest way but it gets the job done
User avatar
Jenk
DevNet Master
Posts: 3587
Joined: Mon Sep 19, 2005 6:24 am
Location: London

Post by Jenk »

Code: Select all

<?php

$res = mysql_query("query with (subquery)");

for ($i = 0; ($i < $limit) && ($row = mysql_fetch_assoc($res)); $i++) {

//do stuff with $row

}

?>
Where $limit is your limit.
Post Reply