I want to populate a table from database, but don't know how to give it auto serial no from 1 to the last number starting before Staff ID. Here is the line of codes:

<?php
mysql_connect("localhost","root","") or 
die ("Unable to Establish connection".mysql_error());

mysql_select_db("demoDB") or
die ("unable to select database".mysql_error());

$sql="SELECT * FROM dec2013 WHERE PFA='Pension Alliance Ltd' ORDER BY StaffName ASC";
$queryN=mysql_query($sql)
    or die (mysql_error());
echo "<table border='1'>
<tr>
<td><h5>Staff ID</h5></td>
<td><h5>Staff Names</h5></td>
<td><h5>Pension Fund Administrators</h5></td>
<td><h5>Pension PIN Codes</h5></td>
<td><h5>Min. / Depts / Agency</h5></td>
<td><h5>Amount Deducted</h5></td>
</tr>";  
while($rows=mysql_fetch_array($queryN))
{
echo "<tr>";
echo "<td><h5>" .$rows['Staff_ID']. "</h5></td>";
echo "<td><h5>" .$rows['StaffName']. "</h5></td>";
echo "<td><h5>" .$rows['PFA']. "</h5></td>";
echo "<td><h5>" .$rows['Pension_Pin']. "</h5></td>";
echo "<td><h5>" .$rows['MDA']. "</h5></td>";
echo "<td align=right><h5>" .$rows['Amt_Ded']. "</h5></td>";
echo "</tr>";
}
echo "</table>";
?>

In you database for your table make field Staff_ID AUTO_INCREMENT.
In MySQL just go to the table structure, click "Change" on the field Staff_ID and check the A_I checkbox.
You dont need to put data in this field, when you add a new record this field will take the increment value of Staff_ID field of the last record from table.

Thanks, but the Staff ID is unique. I need another field that will give auto increment starting form number 1

Member Avatar for diafol

Ok well just go to phpmyadmin and insert a new field and make it the primary key / autoincrement.
Be careful with your keys though. You may end up confusing them if you use different ones for different relationships.

Why do you need an autoincrement for this table? I'm assuming that the staff_id is not numeric?

OR you can do this simple and beginner method

$i = 1;
while($rows=mysql_fetch_array($queryN))
{
echo "<tr>";
echo "<td><h5>" .$i. "</h5></td>";
echo "<td><h5>" .$rows['Staff_ID']. "</h5></td>";
echo "<td><h5>" .$rows['StaffName']. "</h5></td>";
echo "<td><h5>" .$rows['PFA']. "</h5></td>";
echo "<td><h5>" .$rows['Pension_Pin']. "</h5></td>";
echo "<td><h5>" .$rows['MDA']. "</h5></td>";
echo "<td align=right><h5>" .$rows['Amt_Ded']. "</h5></td>";
echo "</tr>";
$i++;
}

by adding variable $i to the loop :P.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.