PHP Query Database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
marquischan
Forum Newbie
Posts: 4
Joined: Thu Jul 20, 2006 2:52 am

PHP Query Database

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Search for 'Pagination' in this community. 'PHP Pagination' in google.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

What code have you got thus far?
marquischan
Forum Newbie
Posts: 4
Joined: Thu Jul 20, 2006 2:52 am

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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).
marquischan
Forum Newbie
Posts: 4
Joined: Thu Jul 20, 2006 2:52 am

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply