CODEWITHSUNDEEP
mysqlcountrowsmin
Tianbo84
Tianbo84

Reputation: 321

Get at least x number of rows in MySQL

Hi I am trying to get a mysql query to work properly but to be honest Im not sure where to start, I think count function would work but not sure.

What I am trying to do is do a select but if there are less than 5 rows counted than union with another query/where clause until 5 is reached.

So basically go at least 5 rows and keep joining rows until 5 or more is reached...

    $myquery_mysql = mysql_query("(SELECT title, city, ad_image, rent, uniqid FROM rentals WHERE front_page_ad = '1' AND paid = '1' AND city = '".mysql_real_escape_string($GLOBALS['user_city'])."')
    UNION ALL
    (SELECT title, city, ad_image, rent, uniqid FROM rentals WHERE front_page_ad = '1' AND paid = '1' AND province = '".mysql_real_escape_string($GLOBALS['user_province'])."' )
    UNION ALL
    (SELECT title, city, ad_image, rent, uniqid FROM rentals WHERE front_page_ad = '1' AND paid = '1' )" or die(mysql_error());

Would I do something like...

            $myquery_mysql = mysql_query("(SELECT title, city, ad_image, rent, uniqid count(if title > 5) THEN FROM rentals WHERE front_page_ad = '1' AND paid = '1' )"
    UNION ALL
    (SELECT title, city, ad_image, rent, uniqid FROM rentals WHERE front_page_ad = '1' AND paid = '1' AND province = '".mysql_real_escape_string($GLOBALS['user_province'])."' ) 
    ELSE FROM rentals WHERE front_page_ad = '1' AND paid = '1' )" END ;

or die(mysql_error());

I know this isn't right, but I am just trying to figure it out.

Upvotes: 3

Views: 590

Answers (2)

Bohemian
Bohemian

Reputation: 425053

I'd do:

  • left join on using all the comparisons
  • order by the comparison values desc so matching values order higher than non-matching (which will be null)
  • use limit 5

Upvotes: 0

Thariq Shihipar
Thariq Shihipar

Reputation: 1082

You could add a LIMIT 5 at the end, so it will do the entire query with all the unions and then select the top 5 results, as long as you're not giving it an ORDER BY it should list them in the order you do your unions.

So if the first query has 5 results it'll just take those, but if not, it'll take results from the next 2 unions. As long as your unions aren't very time consuming this seems like a good course of action.

Upvotes: 2

Related Questions