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.