Can i do a 1 query insted on 2 queries?

malhayek · August 19, 2012, 12:52am

Hello

I have these 2 queries and i was wondering if any one can help me with coming up with one query that does the job?

first one is (Where $device_pro= “1,4,3”; )

[php] $sub_query = mysql_query(“SELECT cat_id, cat_name FROM providers WHERE cat_removed=0 AND cat_id NOT IN ($device_pro) ORDER BY cat_name ASC”);[/php]

second one is

[php] $sub_query = mysql_query(“SELECT cat_id, cat_name FROM providers WHERE cat_removed=0 AND cat_id IN ($device_pro) ORDER BY cat_name ASC”);[/php]

is there a way to get the same results by running one query???

Thanks for the help

smokeys_13 · August 19, 2012, 10:46am

So basically you want all of the results, regardless of whether the cat_id is part of that list?

In that case, just remove that clause from the where statement. Once you’ve got the results you can filter out the ones that match those IDs as you save each row into a variable (or however you deal with the query).

malhayek · August 19, 2012, 3:48pm

Yes, what you are saying is correct! But I have been reading about optimizing my code to reduse memory usage and to reduce the queries and loops. So I was not sure if we can run one query that will give me those results separated!

Is there a better way that your suggestion?
If I have 10000 rows in the database then this will take 20000 times to check if the results in the list or not! I am just trying to save time!

Thanks

smokeys_13 · August 19, 2012, 4:36pm

Yes, that’s what I meant - one query which is exactly what you’re using in those examples, but take out the ‘cat_id in…’ clause.

malhayek · August 19, 2012, 5:19pm

[code]$sub_query = mysql_query(“SELECT cat_id, cat_name FROM providers WHERE cat_removed=0 ORDER BY cat_name ASC”);

$id_list = explode(’,’ , $device_pro);

while($row=mysql_fetch_array($sub_query)){

if(in_array($row[‘cat_id’] , $id_list)){
//Handel the found results
} else {
//Handel the rest of the results
}
}

unset($row);
unset($sub_query);[/code]

I think this is what you mean. But I still think there is got to be a better way that this!!! this will take a long time! in_array will be called 10000 time, the if and else condition will be called 1000 time also!!!

can you think of a more efficient way?
Thanks