more expensive to run multiple queries or select * and manipulate in PHP?

[alexweber15]

for filtering / re-ordering results for example. 

 

1) i started off by selecting all relevant fields (~800 rows) (and not necessarily * but a large subset of the columns) and then using fetch_assoc to store in an array and using array functions to filter the results and show them.

 

2) i then switched to executing a new query every time

 

the first option might be better for small result sets but in the end i'm pretty convinced that in pretty much all cases the second option is more intelligent and viable than having arrays with thousands of entries and manipulating them (which usually involves callbacks for more advanced stuff)

 

 

can someone please confirm this?

 

thanks

Alex

[corbin]

It would depend on the specific content/number of queries.

 

Can we see a table schema and an example extraction query?

[alexweber15]

It would depend on the specific content/number of queries.

 

Can we see a table schema and an example extraction query?

 

by all means:

 

for the sake of simplicity:

TABLE stats:
|-------------------------------------------------------
|  id  |  ref  |  form  |  variation  |  action  |  ip  |  time  |  
|-------------------------------------------------------

 

for some context:

 

i have 3 different forms that users can fill in and each form has 2 or 3 variations (more and less fields) and i logged 3 actions (view form, attempt submit, success submit), the ip, referral and time.

 

so a sample result set would look like this

TABLE stats:
|-----------------------------------------------------------------
|  id  |  ref          |  form        |  variation  |  action   |  ip           |  time  |  
|-----------------------------------------------------------------------|
|  1  |  site x       | finance     |     A         |    view    |     xxxx     |        |
|  2  |  site y       | insurance  |     B         |    view    |     xxx       |        |
|  3  |  site x       | mortgage  |     B         |    submit  |    xxxxx     |        |
|  4  |  site z       | finance     |     B         |    done    |    xxxxx     |         |
|  5  |  localhost  | insurance   |    C         |     view    | 127.0.0.1  |         |
|-------------------------------------------------------------------------

(wow there was nothing simple about drawing that by hand btw) 

 

 

a sample query would be:

SELECT form, variation, action COUNT(DISTINCT ip) FROM stats WHERE ref NOT LIKE "http://localhost%" GROUP BY form, variation, action

(off the top of my head, could have errors in the sql syntax)

 

so basically, select * returns ~800 (up to ~900 by now)

a typical select would return 3-15 rows because of the GROUP BY but there is one or another situation where i'd want to see the full set of results:

SELECT form, variation, action, time FROM stats WHERE WHERE ref NOT LIKE "http://localhost%" ORDER BY time

 

does that help at all?

[corbin]

So what exactly would you be filtering?  Not quite sure I see your problem/question.

[alexweber15]

So what exactly would you be filtering?  Not quite sure I see your problem/question.

 

that was just one example!

 

i'm gonna have to mostly re-order and hide subsets of the resultset (which as far as i know is ok to do with arrays) but for example there's a time column, remember that so far is not used. 

 

through the gui the user will be able to select filters and format the data the way he wants.  so he might for example decide to filter only the results from the last 7 days for example.

 

in that case (assuming 800 results), as far as i know its definitely more efficient to perform another query than to filter the entire array (also because you might have to sort it first to avoid iterating through all results)

 

know what I mean?

 

I've been adopting some practices recently and I'm kinda curious as to how they compare to proven methods of achieving the same thing. 

[corbin]

So you're second query would be the same query just with a different WHERE clause?

 

 

 

I think it would probably be easier to just refetch the data.  Also, if they changed the criteria to be looser, you would definitely have to refetch the rows.

[alexweber15]

So you're second query would be the same query just with a different WHERE clause?

 

Actually the SELECT clause might change too (from the example: stats.time wasn't even selected in the initial query and then as in the new query), in fact, the only thing that remains constant is the table name...

 

Maybe this would be a good scenario to play with prepared statements 

 

SELECT ? FROM stats WHERE ? LIMIT ?

???????) (gotta read up on this)

 

but either way corbin thanks for clearing it up about multiple queries, i had a feeling that might be the case but always good to get confirmation!

[corbin]

Well I guess you could always just fetch every column (unless it's a huge table row wise), and then you could manipulate client side.  Also, you could decide if the query is more or less specific, and if more you could filter client side, if not reissue a query.

 

 

But, unless you plan on this being a hugely used app, it's probably just simpler to reissue queries.