|
|
Posted by Rik on 07/16/06 17:21
Mike wrote:
> I'm writing a PHP script to only display 20 lines per page when doing
> a search, then have the next/prev/ page 1, page 2 etc...Parsing I
> think its called.
>
> Anyway, before looking on forums, I worked out that I could do it
> using...
>
> $conn = //database connection stuff
> $sql = "select f_name from TABLE where f_name = $_POST['firstname']";
> $result = mysql_query($sql,$conn) or die(mysql_error());
> $name = mysql_result($result, $num, 'f_name');
>
> and after working out how many on a page etc I can use $num as the row
> to retreive. Then just set up a counter and loop through until the
> counter = the starting number + 20. I can easily work out what row to
> start and end based on what page your on. I have to remember that the
> first row is 0 and not 1.
>
> Then I looked around and saw the LIMIT being used and storing the
> results to an array and looping through that way.
>
> What are the pro's and con's on both these methods? I'm guessing that
> the LIMIT array method is quicker??
Yes, it saves resources.
The total number of rows can be retrieved easily with COUNT(*) (of which the
manual states: " COUNT(*) is optimized to return very quickly if the SELECT
retrieves from one table, no other columns are retrieved, and there is no
WHERE clause."
Unfortunately, if you want a WHERE clause it will be slightly slower, but
still faster then retrieving all the rows.
LIMIT has other advantages, it will for instance stop sorting by the ORDER
BY clause as soon as it's determined the order of the
With a simple calculation of records per page, total records & requested
pagenumber you'll only have to retrieve the relative resultset from the
database, which will mean less resource use with both database & PHP. Your
mentioning of 'storing the results to an array and looping through that way'
get's me a bit confused. It'd be a waste to turn it into an array, and then
proceed. Why not mysql_fetch_array() the rows and perform your desired
actions immediately?
In the first option you query the entire resultset all the time. Also,
settings the pointer with mysql_data_seek() & fetching the desired number of
rows with mysql_fetch_array() are probably faster then mysql_result() in
this scenario.
Grtz,
--
Rik Wasmus
Navigation:
[Reply to this message]
|