CODEWITHSUNDEEP
phpmysql
sg552
sg552

Reputation: 1543

Check mysql query result to run second query which is not available in first query result

I have this code:

$sql = "SELECT ns, id FROM `dns` WHERE `ns` = 1 LIMIT 0, 30 ";       
$result = mysql_query($sql);

// After this I need to run second query which is not available in first query. Help me with this please.
$sql2 = "SELECT ns, id FROM `dns` WHERE `ns` = 1 AND `id` = not available in first query, how to do that? LIMIT 0, 30 ";         
$result = mysql_query($sql);

// I can then echo my first query result here 
// I can then echo my second query result here

What I need to do is to check the first result to run the second query. I set the limit on purpose. For example if the id from 1 to 30 is available in first query. The second query will query id for example starting from 31 to 60.

I hope you can understand what I'm trying to say. Thanks in advance.

UPDATE: I need to make dig lookup with the query result. The query result is nameserver. Because dig response speed is random so I was thinking I want to split for example: 30 result from 90 nameserver and run 3 simultaneous dig lookup. So thats why the second query (nameserver) result should not available in the first query result and the third query result should not available in the second query result. I need to use all 3 result for later use. I realy appreciate your response. Thank you.

Upvotes: 0

Views: 1184

Answers (5)

Simon at The Access Group
Simon at The Access Group

Reputation: 7025

You can just change the LIMIT criteria. There's an "OFFSET" option (which you've specified as 0 in your first query). Using your examples the following would suffice.

$sql = "SELECT ns, id FROM `dns` WHERE `ns` = 1 LIMIT 0, 30 ";       
$result = mysql_query($sql);

// After this I need to run second query which is not available in first query. Help me with this please.
$sql2 = "SELECT ns, id FROM `dns` WHERE `ns` = 1 LIMIT 30, 30 ";         
$result = mysql_query($sql);

The second query will start at offset by 30 and return 30 more rows, so in this case rows 31-60

edit Based on your updated question, a more sensible approach would be to simply do 

$sql = "SELECT ns, id FROM `dns` WHERE `ns` = 1 LIMIT 0, 90 ";       
$result = mysql_query($sql);

And then have PHP split up the rows into requests to separate servers as required thus ensuring there are no duplicates and reducing back and forth between PHP and MySQL.

Upvotes: 3

ChrisPatrick
ChrisPatrick

Reputation: 984

Why not just do the first query without a limit and then extract the first 30 results with a for loop or something.

Saves doing two queries.

So

$query = SELECT ns, id FROM dns WHERE ns = 1
$result = mysql_query($query);

for ($i=0;$i<30;$i++) {
//Do Stuff with mysql_fetch_row...
}

for ($i=0;$i<30;$i++) {
//Do more stuff
}

Unless there is a reason you can't get all the results at once?

EDIT: Althought clearly you could put LIMIT 60 in the query if you definitely only wanted 60 results.

Upvotes: 0

Ben Lee
Ben Lee

Reputation: 53329

If you just need to grab the next 30 rows, why not just:

LIMIT 30, 30

On the second query?

See http://dev.mysql.com/doc/refman/5.1/en/select.html#id838627 regarding LIMIT:

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

Note that this can also be written as LIMIT 30 OFFSET 30 if you want to be more clear. So:

$sql = "SELECT ns, id FROM `dns` WHERE `ns` = 1 LIMIT 30 OFFSET 30";
$result = mysql_query($sql);

If your ids/queries were just examples, and what you really need is a query that selects from a set that excludes the result set from a different query, then you can use a sub-query like this:

SELECT ns, id
FROM `dns`
WHERE `ns` = 1
AND `id` NOT IN (
    SELECT `id`
    FROM `dns`
    WHERE `ns` = 1
    LIMIT 0, 30
)
LIMIT 0, 30

First the inner sub-query will run and return a list of ids. That set of ids will be explicitly excluded from the outer query because we specify NOT IN (...).

Upvotes: 1

paulsm4
paulsm4

Reputation: 121719

So what's your question?

And why don't you just do a subselect or join?

Upvotes: 0

Anthony Grist
Anthony Grist

Reputation: 38345

Use NOT IN with a subquery, like so:

SELECT ns, id FROM `dns` WHERE `ns` = 1 AND `id` NOT IN 
(SELECT ns, id FROM `dns` WHERE `ns` = 1 LIMIT 0, 30) 
LIMIT 0, 30

Upvotes: 0

Related Questions