I want to write a page that will show all the records from a database, and have it limited using the SQL LIMIT syntax.
My question is how would I use PHP to automatically generate the links? Would I have a variable that would automatically increase somehow, hardcode the links in etc, Is there a convention or best practise for this kind of thing?
This is standard pagination it sounds like. Basically you will use the LIMIT functionality in MySQL, set amount of records per page, and finally track a page number.
You'll run this off of a $_GET variable called "page". If Page is equal to 2, and # of results per page is 10, then your LIMIT should start on the 21st record. So your LIMIT clause becomes LIMIT 21, 10.
The most work will be done on building the links for Page 1, Page 2, Page 3, etc. You'll need to know how many records you have, divided by the limit of records per page. 100 records and 10 records per page? You'll have Page 1 - Page 10. Each link simply calls the same page, but with a different $_GET["page"] value.
This is a cursory description of pagination. There are tutorials available online.