Page 1 of 1
PHP Query Database
Posted: Thu Jul 20, 2006 2:57 am
by marquischan
I am working on a program which will query the database and receive a list of data.
about 100 to 1000 results.
I know that I can use Session to store the arraylist of results in Java (Tomcat)
So that I can process the data without query the database again.
Like i want to show 10 results per page. I just get back the data from session and print it out.
If under PHP, how would you do?
Posted: Thu Jul 20, 2006 2:59 am
by RobertGonzalez
Search for 'Pagination' in this community. 'PHP Pagination' in google.
Posted: Thu Jul 20, 2006 2:59 am
by JayBird
What code have you got thus far?
Posted: Thu Jul 20, 2006 12:48 pm
by marquischan
Let me point out more detail.
The way i do before in PHP is LIMIT the results by using SQL Query
such as SELECT * FROM ABC LIMIT 0,50
The 0 can be changed !!
However, is there any different between the example below?
SELECT * FROM ABC
under PHP code, i will do
for ($i = 0; $i < 50; $i++)
{
echo($sql_fetched_row['id']);
}
Is there any different? Of course, i will do paging with the two example above
Posted: Thu Jul 20, 2006 10:28 pm
by RobertGonzalez
I may have said this before, but I grab the entire array, then check for paging and run the loops from $start (which page we are on) to $page_limit (how many per page).
Posted: Fri Jul 21, 2006 4:15 am
by marquischan
The thing i want to point out here is
When i put ALL rows into the ARRAY.
If i just use START and LIMIT under SQL_query to contraint the number of returned rows, the array size will be decreased much.
Method 1:
SELECT * FROM ABC;
return 1000000 rows, put all into array. and use LOOP , $START and $END to control the print out
I can use count($row) to retrieve the total number of row.
Method 2:SELECT * FROM ABC LIMIT 0,50
Put all into array and print all out.
I need to use SELECT COUNT(*) FROM ABC again to count the total number of row.
Another PROBLEM.
If I show 50 records per page, I need to QUERY the database AGAIN every time i change page.
Is it time consuming and memory consuming?
Course I am doing repeated job. And is there any way to SAVE the query result so that I am no need to query the database again?
Which method will u use?
Posted: Fri Jul 21, 2006 4:37 am
by onion2k
marquischan wrote:And is there any way to SAVE the query result so that I am no need to query the database again?
What if the database changes? Someone adds or deletes a record for example. Then your saved query results would be wrong. Just query the database again, it's a lot easier and it'll be the correct data.
Posted: Fri Jul 21, 2006 9:03 am
by RobertGonzalez
The Db server can handle it. Test the timing on a complete query and on a paged query and see what the time difference are. If they are significant, then use your limits in your query. If not, use them in your loop.