Forcing a set number of results

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
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Forcing a set number of results

Post by GeXus »

I'm trying to do a look up where keyword = keyword.. but I would like to force at least 2 results to always be returned.. so there may be only be 1 or maybe 0 matches, if that is the case, it should just return any 2...

Is this posible on the sql end?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

It is possible, but I think you will either need a user defined function or something like stored procedures. I'm not sure a straight query will be able to identify if there are two or more of something and pull them, else pull any two if less than two.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Code: Select all

$result = mysql_query("SELECT count(*) FROM `table` WHERE `foo` = '$bar'") or die(mysql_error());

if(mysql_result($result,0) < 2){
   $result = mysql_query("SELECT `something` FROM `table` ORDER BY RAND() LIMIT 2");
} else {
   $result = mysql_query("SELECT `something` FROM `table` WHERE `foo` = '$bar' LIMIT 2");
}
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

scottayy wrote:

Code: Select all

$result = mysql_query("SELECT count(*) FROM `table` WHERE `foo` = '$bar'") or die(mysql_error());

if(mysql_result($result,0) < 2){
   $result = mysql_query("SELECT `something` FROM `table` ORDER BY RAND() LIMIT 2");
} else {
   $result = mysql_query("SELECT `something` FROM `table` WHERE `foo` = '$bar' LIMIT 2");
}
THANK YOU!
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Ok im stumped!

So, now I'm able to determine if not enough results are returned, I can return random results.. But what if say 1 result was returned.. how would I display that result AND a random result, to make up for the limit 2.. and order it by the non-random result..

:)
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Post by GeXus »

Ok i figured this one out.. by doing a UNION it worked perfect..

thanks again!
Post Reply