SQL LIMIT for multiple queries

Question

I have my paging script that gets the page variable and puts in in LIMIT values inside a sql query.

LIMIT $page, $limit2

it all works well,the problem is that i have 2 sql queries so LIMIT 0,10 displays 10 results from first query and then 10 more from second query =20 results per page,what i need is to display 10 from 1st QRY and on 2nd page the rest from first QRY and only then to print the results from 2nd query.

I tried if statement before whole query an a ++increment inside while loop to even the numbers with found results and then run 2nd QRY but it seems that it doesn't work that way.

also tried something like this with few matematical operations but it also doesn't do much good.

    if($page==$printf2)
    {$limit2=10;} 
    if($page==0)
    {$limit2=0;}

What would be the proper way of making the 2nd QRY list after 1st one ended,without more than 10 results per page?

could you please post also the queries? maybe it is possible to merge them and then use just one limit clause — Dalen
– Dalen, Commented Jun 29, 2011 at 19:15

fdaines · Accepted Answer · 2011-06-29 19:20:58Z

1

did you evaluate to use UNION statement?

SELECT * FROM xxxxx (first query) 
UNION ALL
SELECT * FROM YYYYYY (second query)

answered Jun 29, 2011 at 19:20

fdaines

1,2361 gold badge10 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mentales Over a year ago

union would save me a lot of trouble,im trying it for some time already,problem is that queries are a bit larger than basic example.I had to run few queries just to get enough data for query 1.

Sterex · Accepted Answer · 2011-06-29 19:21:03Z

0

$sql = "QUERY1 LIMIT $page, $limit";
$result = mysql_query($result);

/* process the result here! */

if (mysql_num_rows($result) < 10) {
    $sql2 = "QUERY2 LIMIT $page, $limit2";
    $result = mysql_query($result);

    /* process result of query 2 */
}

This way, the query two is executed only when the rows returned from query1 is the last set of rows in the table.

answered Jun 29, 2011 at 19:21

Sterex

1,0281 gold badge13 silver badges29 bronze badges

2 Comments

Mentales Over a year ago

It looked good but it acts same as other if statements i tried,on page 2 number of rows is always same since the query dosent change...so it prints only 1st Qry.

Sterex Over a year ago

@Arremer: Not really. The mysql_num_rows() would be less than 10 only on the last page. Only then, the second query will be executed and you can list the results from the second query. So until all the rows are returned from the first query, the second query is not executed.

Collectives™ on Stack Overflow

SQL LIMIT for multiple queries

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related