Am i doing the right thing?

hi,

i am asking some php and mysql questions here. I have a table called, articles. And in that articles table, i have 10000 rows, increasing day by day.

I am displaying 10 of those article in one php page with pagination. (10 per age) (something like search engine). Currently, i am using php select * to display all articles and them i apply pagination to them. That mean's whenever i load the page, sql queries are run to display all the 10000 rows.

Currently, the php page still load fast. But i am wondering, if my database gets bigger, will it cause serverload and lagging time when lagging the page? Cos i am running a sql query to display all the results. Do you people use this method when displaying data? Am i doing the right thing?

 

you are doing wrong thing

Just fetch only records which u want to show on page not all 10000

Select * from articles Limit 0,99 <--- first page

Select * from articles Limit 100,199 <--- first page

Select * from articles Limit 200,299 <--- first page

Just need a for loop

I hopy u get the basic point

Regards

Alex

 

hi, but i need to display all the 10000 rows.., so that will be the problem on my pagination coding? but in order to count the number of pages and seperate them into different pages, first i need to run a select * to count the number of rows in the database b4 i can continue doing the sql limi

 

If you want to do a high/low limit you can use this:

$page = (int)$_GET['page']; //page 1, 2, 3, 4 ....
$result_num = 10; //number of rows you want displayed

$low_limit = $page*$result_num;
$high_limit = (($page+1)*$result_num)-1;

$query = "Select * from articles Limit $low_limit,$high_limit";

PHP:

 

yes u will need to count

counting does not take time

Just devide total rows number by 10

and u will get number of pages

Jastep gave the actual code and i just want that you write the code yourself

Regards

Alex

 

hi, if i use select * FROM articles and i count it.. will it takes load? like say i have a million articles.. the dividing and getting the pages i undertstand.. just wondering if it takes load.. cos that means everytime a person load a page, i have to count all the rows first.. what happens if i had one million articles?

 

Database Indexing

the best you index, the fast you get the data

 

Limit was implement in sql for paging. If you have 1 million record to select. You can use LIMIT to break apart your result into pieces.

For example:You have 1 million records.

SELECT * FROM articles LIMIT 0,20;

This query doesn't select all your 1 million record, its only select a result row 0 to 20 which is take a second to load.

 

hi, but inorder to determine how many pages i have, i need to count all the rows, divided by number of rows per page.. so in the end i still need to count all the rows..

 

Hello,

To count the rows you can use:

SELECT COUNT(*) FROM articles;

Which will return the number of rows in the table.

Sky22

 

In generall, querying by using Select * is much slower than selecting by specific column.

Ideally you should select only the columns that you are need in your results.

IE(Select count(id))

or:

Select id, name, url

 

will using this statement alot of serverload if i have 1 million rows?

 

Yes you right, you need to count all the rows.

If you test count your rows in mysql command line,

its takes 0.00 sec