[RESOLVED Temporarily] Filtering

[RobDog888]

Is it possible to filter a resultset of a db query? I dont want to hit the db with two calls and my query is joined to bring the related resultset back but upon iterating it may be best to loop through the one condition and then reloop through the other. There will be no duplication of items in the loop so its not a resource drain or waste.

For ex. if you have returned items that can be visible and invisible then it would be a waste to call the db twice just to loop through the visible ones and then another call and loop through the invisible ones. So if I have 5 visible and 5 invisible items then I only want to loop a total of 10 times; filter for visible, loop 5 times, unfilter, refilter for invisible, loop 5 more times and release resources.


Thanks

[dclamp]

you can use the WHERE clause.

SELECT * FROM `mytable` WHERE visibility='visible'

SELECT * FROM `mytable` WHERE visibility='invisible'

are you talking about a while loop?

while ($res = mysql_fetch_array($query)) {
     print_r($res);
} 

[RobDog888]

Yes, I know the where clause but I dont want to make 2 db calls.

Yes, in the loop fetch stuff

[RobDog888]

In ADO recordsets you can filter the recordset to prevent multiple db calls and allow efficient looping etc.

In php I am not sure if there is anything similar to this?

[dclamp]

I looked around, and i cant find anything. why dont you want to make 2 queries?

[RobDog888]

Trying to keep overhead to a minimum. Db calls are resource expenses.

The best I can get it to do a complex ORDER BY clause to create them in a logical order for looping where visible are ordered first and then the invisible ones. Its still going to require 20 iterations as I can not count on the data being 100% accuarate based unpon the complex ordering. Otherwise I would just process the first results as visible and the later ones as not but its not reliable programming logic.

[RobDog888]

Can I loop again after the first loop is done?

while ($res = fetch_array($results))
{
    //Blah
}

while ($res = fetch_array($results))
{
    // double Blah
} 

[dclamp]

you can, but that will just spit out the same info from the one above.

I think you are going to need 2 queries:

$sql1 = "SELECT * FROM `mytable` WHERE blah='blah'";
$sql2 = "SELECT * FROM `mytable` WHERE blah='noblah'";

$query1 = mysql_query($sql1);
$query2 = mysql_query($sql2);

while ($res1 = mysql_fetch_array($query1)) {

}
while ($res2 = mysql_fetch_array($query2)) {

} 

do you want it to echo like:

visible
visible
visible
visible
invisible
invisible
invisible

?

[RobDog888]

I already ended up putting something like that in place for now until I can find a more efficient way. This is what I didnt want to end up doing with a secondary db call.

In VB 6 you can reuse recordsets depending on how they are opened. I guess ph is still too wimpy.

I have a new issue that I have been working on for a bit now (see new thread).

[penagate]

Can I loop again after the first loop is done?

Yes. Or you could use an ORDER BY visible=1 clause. Or you could just enumerate the records once and branch.

I guess I'm not quite sure where the problem is...

[dclamp]

yeah i didnt quite understand what you were trying to do either... well good luck

[RobDog888]

Well I put in the dual db calls for now but I will change it later once its finished and we polish it up. I dont like depending on the order by clause to present the listing correctly as its not good logic of dependable.

So I cant do like #7 which is more reliable?

[penagate]

If an ORDER BY clause wasn't reliable it would be useless.

I think I know what you're doing.
How about this?:

$records = $db->query_read('SELECT blah.* FROM blah ORDER BY (blah.visible=TRUE) ASC');

while ($row = $db->fetch_array($records) && !$row['visible'])
{
  # non-visible items
}

while ($row = $db->fetch_array($records))
{
  # visible items
} 

Edit: Scupper that: that'll enumerate the non-visible items twice if there are one or more non-visible items and no visible items.

[RobDog888]

Yes, I just wasnt sure if the fetch_array would work twice as it may not have been scrollable, was my worry. The docs sstate it uses a pointer to read the recordset but fails to mention if its cached and is scrollable etc.

Yes, you are thinking correct.

Ps, check out the page its on as its FINISHED

[penagate]

fetch_array returns each item in order, until you pass the last item, at which point it returns NULL and resets the pointer to the start. The recordset is cached; fetch_array merely enumerates it. Using a server-side cache (cursor) is somewhat trickier, and unnecessary in this situation.

[RobDog888]

So then if you looped through the first 5 items as visible when you went to loop through the next 5 invisible items it should start at item 6.

I just dont like counting on counting as there may be instances where the data may be slightly inaccurate and this may lead to other issues.