We help IT Professionals succeed at work.

Database Query Best Practice

Logifire
Logifire asked
on
417 Views
Last Modified: 2013-12-12
We have a PHP site and  MySQL database added with  many pictures and searche abilities. But we want to know how many rows a given query returns.

We use one query with a limit for every pageview, so when I use mysql_num_rows it returns the max of the limit.

(SELECT * FROM table LIMIT 0,25)

Should I make two queries, one with the limit and one without, or should I store all data in an array and handle the pageviews in PHP?
Comment
Watch Question

I would do 2 queries, one that counts like this

select count(*) as total FROM table

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for the response, I have a more complex search like this:

SELECT DISTINCT pictures.id,title,keywords,year,pixelw,pixelh,kbytes,pay.pic_id AS pay,area.name AS area,place,photographer.name AS photographer FROM (pictures LEFT JOIN pay ON pictures.id = pay.pic_id) LEFT JOIN area ON pictures.area=area.id LEFT JOIN photographer ON pictures.photographer = photographer.id RIGHT JOIN pictocat ON pictures.id = pictocat.ptc_pic_id WHERE title LIKE '%Water%' AND place LIKE '%Beach%' AND photographer = '6' AND area = '8' AND 2000 <= year AND year <= 2005 AND 3543 <= pixelw AND 2952 <= pixelh AND ( ptc_cat_id='21') LIMIT 0,25

Okay, so you kept the records id in the $_SESSION so you can make a simpler query for each page?
CERTIFIED EXPERT

Commented:
OK.
My first shot would be to run the query without limit, place all the 10 fields values for all the records into an array within $_SESSION. The data would be sorted with the require order either within the SQL query of with some processing in the php program.

NOTE: be careful that when you are changing $_SESSION variables it is guaranteed that it will be updated AT LEAST when you leave the page... but there is no guarantee that it will be done at once...
CONSEQUENCE: initially you need tu create the array within your php space THEN, when complete, transfer it into $_SESSION

For the memory problem:
- If your pages are not too heavy, you should not have too many problems here since your ten fields probably take less than 1K byte total, which for 2K pictures would create 2Mbytes.
- If you run into a memory problem, you might take a slightly different strategy: run your complex query just to get the DISTINCT picture.id with all the conditions met; place the picture.id in the $SESSION array; then when navigating thru this array, run the query for each picture.id, this time collecting all the other fields needed for the display (but you do not need any condition in the WHERE area, except for those needed for the joins)
- be careful if you are hosted on a 64bits system: memory space needed is roughly doubled.

Author

Commented:
Okay, maybe I could do this; the form post is transmittet to another page where to handle the query to the session and then sent the user back to the search page. I would then be absolute sure that the session is updated.

SESSIONS are like cookies, just stored on the server side, right?

<<CONSEQUENCE: initially you need tu create the array within your php space THEN, when complete, transfer it into $_SESSION>>

You mean like
<?PHP
array
while(query){
 put data in array
}
handle the data from array

at finished session = array
?>

When the user then navigate through the search, use the session.

Author

Commented:
Okay, i found another solution where I only need one query pr. page without getting the data in an array first.

First i get all the rows, afterwards do i run a MYSQL_NUM_ROWS to see how many results I get.  Then I use the MYSQL_DATA_SEEK to point to the specified row and run a fetch array x times. The memory usages will be at a minimum, i can not get under the amout of used memory for the resultset.

When I store the data in a SESSION this will have the same size in memory as the result from the query.

But as I see it, the SESSION solution will be more database friendly, this will only need one access pr. search.



CERTIFIED EXPERT

Commented:
<<First i get all the rows, afterwards do i run a MYSQL_NUM_ROWS to see how many results I get.  Then I use the MYSQL_DATA_SEEK to point to the specified row and run a fetch array x times.>>
B-)) You will need to run the query everytime the page is reloaded... php runs "on the server" which means that it runs, sends the html code (this when you get say the first 30 results),  the forgets everything that is not in $_SESSION or $_COOKIE. When you want to read the next 30 records, the php page reloads, knows from $GET or $POST that you need the 30 records starting at record #30: it issues the query again, this time with LIMIT 30,30.
You will run the complex query for each page of results.
If your query is complex enough, then this will use more database access than issuing 30 times the query for 1 record given by its id. [some testing might be needed to check that]

<<When I store the data in a SESSION this will have the same size in memory as the result from the query.>>
Yes, but then you fetch data only once.

<<But as I see it, the SESSION solution will be more database friendly, this will only need one access pr. search.>>
On access per page of results

<<You mean like
<?PHP
array
while(query){
 put data in array
}
handle the data from array

at finished session = array
?>

When the user then navigate through the search, use the session.>>
Yes.
<?php
session_start(); //make it the first executable statement in each php page
$my_results =array ();
...
while ($results = mysql_fetch_assoc($res)) {
   $my_results[]=$results;
}
....
$_SESSION['search_result'] = $my_result; //empty if no result to the query
....

Author

Commented:
Thank you fibo, I hope you do not get tired of me, but should I use mysql_fetch_assoc instead of mysql_fetch_array?

Author

Commented:
One last thing :)
"If your query is complex enough, then this will use more database access than issuing 30 times the query for 1 record given by its id."
When i do:
<?php
session_start(); //make it the first executable statement in each php page
$my_results =array ();
...
while ($results = mysql_fetch_assoc($res)) {
   $my_results[]=$results;
}
....
$_SESSION['search_result'] = $my_result; //empty if no result to the query
....

I do not need to make 30 small queries but can use it from the array as I understand

Just to be clear :)
CERTIFIED EXPERT

Commented:
<<should I use mysql_fetch_assoc instead of mysql_fetch_array?>>
http://www.php.net/manual/en/function.mysql-fetch-assoc.php
assoc will return just the associative array (ie 'named fields') while array will return these AND also the more usual [0], [1] etc. This would return a double amount of data, and in the precise case of << $my_results[]=$results;>> would use a double amount of memory.
As a routine, in most situations you should use 'assoc' rather than 'array'.

<<I do not need to make 30 small queries but can use it from the array as I understand>>
This is correct if memory size allows to keep the complete data in a $_SESSION array.
If this array takes too much space, there remains only 2 options
- re-issue the complex query with changing LIMIT parameter
- save in $SESSION just the productids, and the re-issue a query with just the productid as a parameter for the WHERE, to grab all the values of the record; this would then need 30 individual "simple SELECTs" for each page.
CERTIFIED EXPERT

Commented:
B-)) glad we could help. Thx for the grde and points
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.