Hi I'm trying to query a MySQL d

[luckyblackcat]

Hi

I'm trying to query a MySQL db and dynamically create webpages from the result.

Up to say 5 result a page with |<< < > >>| next/prev style navigation or better.

I've seen this on lots of sites but I'm a PHP novice with limited MySQL experience, so a ready made script that I can easily alter would be wonderful.

Thanks for reading this

luckyblackcat

I've put similar posts on the Dreamweaver and MySQL forums and received unhelpful one liners from timewasters with ego problems. Any genuine HELP please post.
Anyone else ... you just make it less likely for me to get genuine HELP as it looks like I've had replies.

 

[sleipnir214]

The trick with pagination is making sure that your script knows which set of records it should return at any given time.

Assume that your script will return 10 records. The first time it runs, it will have no input as to which record set it should return, so it returns the first 10. This is accomplished by using MySQL's &quot;Limit&quot; clause in your select statement:

SELECT * from foo LIMIT 10.

When it produces the output, it will also produce a link back to the same script, but with the page number listed:

<a href=&quot;/yourscript.php?page=2&quot;>Next 10</a>

Your script can then check $_GET['page'] to know which page to return. It can then use the second form of MySQL's LIMIT clause:

SELECT * from foo LIMIT 10, 10

The first number is where to start (starting counting at 0), the second is the number of rows to return. The above query will return records 11 through 20 (if you count from 1).

You can generalize the query, understanding that you can calculate the first number as

$start_number = ($_GET['page'] - 1) * 10;

Then creating your query to hand to MySQL is:

$query = &quot;SELECT * FROM foo LIMIT &quot; . $start_number . &quot;, 10&quot;;

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[luckyblackcat]

Wow Well slap my face with a wet kipper.

This is a good and useful reply ... not quite sure about the link back to the script
<a href=&quot;/yourscript.php?page=2&quot;>Next 10</a> I didn't realise it did this? I need to think this through as I am still not sure how to implement this.

Thanks

luckyblackcat

 

[sleipnir214]

You have a script that can output the xth set of y rows of your table. Why not reuse it, while passing back to it the next or previous set to display?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[luckyblackcat]

Hi

I need to work out (visualise) what is happening with the query so that I can make use of this feature, I am using Dreamweaver and another post in another forum said I could get Dreamweaver to do this without coding.
It's been a while since I used MySQL and I have forgoten more than I remember.
This is a feature I have never used before and I am not sure how ['page'] can be used .. yet.

Thanks for your help.

luckyblackcat

 

[sleipnir214]

$_GET['page'] simply tells the script where to start in the table fetching and displaying records.

The script should display from your table N records (let's say 10). Any set of 10 records can then be considered a page of records.

The value of $_GET['page'] will tell the script where in the table to begin fetching records. So, given a value of $_GET['page'], the script should fetch:

$_GET['page']   Fetch records
1 or no value   1 through 10
     2          11 through 20
     3          21 through 30
etc.

You can calculate the value of the starting record from $_GET['page'] by the formula:

<starting record> = ($_GET['page'] - 1) * <number of records in a page>


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[sleipnir214]

You don't have to pass around the number for a page of records. You can also pass around the absolute record in the table where the script should begin fetching records.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!