Seperating Mysql Results on to tables.

[Baljeet]

Ok this tutorial will teach you how to retrieve a list of items from your database, print them in a neat table and seperate them into pages!!
I will also show how to link these items to a page which will be able to tell you more info using the GET method(look at storing your images in MySQL part II).

Establishing the table:

<table border="8" width="750">
<th>Title</th>
<th>Author</th>
<th>Pos Votes</th>
<th>Neg Votes</th>

This basicly makes a table with four columns with the titles:
Title | Author | Pos Votes | Neg Votes
Edit this to suit your needs.

<table border="8" width="750">

Makes a table with a border of eight (that can be ugly if you don't correct this with CSS).

<th>Title</th>
<th>Author</th>
<th>Pos Votes</th>
<th>Neg Votes</th>

This titles each column.
Right now your table will look bad don't worry will fix it.

<?php
$username="User";
$password="Pass";
$database="Database";
$page = $_GET['page'];
if ($page == "") {
$page = 1;
} else {
}
$start_from = ($page-1) * 2;

Firstly we establish the database's username, password and the database we are using.
Now we get the page variable from a GET call, this basicly means after .php their will be ?page=3 this is because we are cutting up are results into neat pages. If their isn't a ? page=1 ending we make page equal to one.
We then take $page take away 1 (as mysql starts from 0 instead of 1) and times it by twenty (each page will show twenty results).

$query="SELECT * FROM yourtable ORDER BY title DESC LIMIT $start_from, 10";

This make the $query variable (it will contain are query) "SELECT * FROM yourtable ORDER BY title DESC LIMIT $start_from, 10" This is saying get data from (change yourtable to your tables name) order it by (put a column which you would like to order your posts by (e.g.
the date).

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$result = mysql_query($query);
$num = mysql_numrows($result);

In this code we connect to MySQL,
Select are database,
Then we make $result into a handle for are query.
Then we make $num the number of rows in are query...
Normally this will be twenty but on the last page it may be less.

$i = 0;
while ($i < $num) {
$title = mysql_result($result,$i,"title");
$ratingup = mysql_result($result,$i,"plusrating");
$ratingdown = mysql_result($result,$i,"downrating");
$author = mysql_result($result,$i,"author");
echo "<tr>            <td width='45%'><a href='viewtut.php?id=$i'><b> $title </b></a></td>  

   <td width='25%'>$author</td>		<td width='15%'><font 
color='green'>$ratingup</font></td>		<td><font color='red'>$ratingdown</font></td>   </tr>";
$i = $i + 1;
}

In this part we establish $i as 0 then we make a loop that will continue while $i is less than $num (the number of rows we have taken from MySQL) at the end of each loop we add 1 on
to $i.


Then we set $title as row $i (it will count up each time) from $result and in the column title.
We do this for each variable.

Now we echo are data:
<tr> = We add a new row to are table,
<td width='45%'><a href='viewitem.php?id=$i'><b> $title </b></a></td> = We print the title and a link that will take us to viewitem.php?id=(the id of this record) in the first column which is 45% of the table.
NOTE: For rating up and down I chaanged the color of the text.

mysql_close();
?>
</table>

We close MySQL end the PHP script and end are table.

<?php
$page = $_GET['page'];
$nextpage = $page + 1;
echo "<a href='NAMEOFTHISPAGE.php?page=$nextpage'>Next Page</a>";
?>

This will create a link to the next page.
This is my first tutorial if it goes down well i will add another on creating a list of the

pages (eg 1,2,<b>3</b>,4).

If you find something wrong in her PM me as i might not see the post and be able to fix it.
IF YOU HAVE A QUESTION ASK IT BELOW...
As that way other people can assist you.

All Credits goes to one who really made this...