problem with auto_increment - PHP and MySQL

kimrari

Hi,

I run a little classic gameing sports league and we have a very simple stats page.

the page is at:

what i had hoped, is that as u clicked on links [for exmple, sorting by rushing/pasing] that the auto increment would change to reflect the actual ranking.

instead, i have to change the value in the database manually.

is there something im missing?

is that field supposed to be empty in mysql [when i try to i encounter an error message] and the autosorting is performed in php?

thx for your assistance.

code is below:

<head>

          <style type="text/css"><!--

BODY {

 scrollbar-base-color: #000000;

 scrollbar-track-color: #ffffff;

 scrollbar-face-color: #0F35B2;

 scrollbar-highlight-color: #000000;

 scrollbar-3dlight-color: #ffffff;

 scrollbar-darkshadow-color: #ffffff;

 scrollbar-shadow-color: #000000;

 scrollbar-arrow-color: #ffffff;

 }

.right style type="text/css">{  }

a:hover { color: white; background-color: #ff0000} }

.left a:hover { color: white; background-color: #ff0000} }

-->



</style>

</head>

          <div align="center">

            <center>              

            <table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="591" id="AutoNumber1" height="231">

            <tr>

              <td width="116" height="22">

              <p align="left">

              &nbsp;</td>

              <td width="450" colspan="3" height="22">

              <p align="center">

              <font face="Verdana" size="4" color="#0033CC">Offensive Stats</font></td>

              <td width="33" height="22">

              <p align="center">

              <font face="Verdana" size="4" color="#0033CC">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></td>

              <td width="81" height="22">

              <p align="center">

              &nbsp;</td>

              <td width="1" height="22">

              </td>

            </tr>

            <tr>

              <td width="116" height="18"></td>

              <td width="203" height="18"></td>

              <td width="19" height="18"></td>

              <td width="228" height="18"></td>

              <td width="114" colspan="3" height="18">

              <p align="center"></td>

            </tr>

            <tr>

              <td width="680" colspan="7" height="81"><?php 



mysql_connect( 'localhost', '', '' ) or die ( 'Unable to 



connect.' ); 



mysql_select_db( 'tecmo_sbtl' ) or die ( 'Unable to select database.' ); 







// if $sortby isn't empty, then check to see which column we're sorting by 



if( $sortby != "" ){ 



switch( $sortby ){ 



case "col1": $sql = "SELECT * FROM totalO ORDER BY rank"; // sorts by column1 



break;



case "col2": $sql = "SELECT * FROM totalO ORDER BY  conference"; // sorts by column1 



break;



case "col3": $sql = "SELECT * FROM totalO ORDER BY team DESC";



break;



case "col4": $sql = "SELECT * FROM totalO ORDER BY total DESC";



break;



case "col5": $sql = "SELECT * FROM totalO ORDER BY average DESC";



break;



case "col6": $sql = "SELECT * FROM totalO ORDER BY passing DESC";



break;



case "col7": $sql = "SELECT * FROM totalO ORDER BY rushing DESC";



break;













// etc. 



default: $sql = "SELECT * FROM totalO"; // if $sortby isn't one of our values don't sort 



} 



} 



// otherwise, don't sort 



else { 



$sql = "SELECT * FROM totalO"; 



} 







$result = mysql_query( $sql ) or die ( 'Unable to execute query.' ); 



?> 







<table border="1" align="center" width="533"> 



<tr> 



<th width="8"><a href="offensivestats.phtml?sortby=col1">#</a></th>



<th width="1"><a href="offensivestats.phtml?sortby=col2">Div.</a></th>



<th width="143" align="left">

<a href="offensivestats.phtml?sortby=col3">Team</a></th> 



<th width="27"><a href="offensivestats.phtml?sortby=col4">Total</a></th> 



<th width="34"><a href="offensivestats.phtml?sortby=col5">YPG</a></th>



<th width="32"><a href="offensivestats.phtml?sortby=col6">Passing</a></th>



<th width="24"><a href="offensivestats.phtml?sortby=col7">Rushing</a></th>





</tr> 



<?php 



while($row = mysql_fetch_array($result)){ 



?> 



<tr> 



<td bgcolor="#A8BFFF" width="8"><?=$row[0]?>&nbsp;</td>



<td bgcolor="#A8BFFF" width="1"><?=$row[1]?>&nbsp;</td> 



<td bgcolor="#A8BFFF" align="left" width="143"><?=$row[2]?>&nbsp;</td> 



<td bgcolor="#A8BFFF" align="center" width="27"><?=$row[3]?>&nbsp;</td>



<td bgcolor="#A8BFFF" align="center" width="34"><?=$row[4]?>&nbsp;</td>



<td bgcolor="#A8BFFF" align="center" width="32"><?=$row[5]?>&nbsp;</td>



<td bgcolor="#A8BFFF" align="center" width="24"><?=$row[6]?>&nbsp;</td>







</tr> 



<?php 



} 



?> 



</table>



              <p>



<br>



              </td>

            </tr>

            </table></center>

          </div>

i serve it in via .phtml

eoghain

I'm going to assume that the auto_increment field that you are refering to comes out as $row[0] from your query.

First off what the auto_increment option of a MySQL field is used for is generating a unique value to index each row in the table off of. So if you have nothing in your table and add a new row and you leave the auto_increment field null then MySQL will place a 1 in that field, and every subsiquent row you add will get a new number.

There is no way for MySQL to know what you are using that field for and adjust the values to be what you think they should be. So if you want your code to display something in your # field and have that always be a 1 - x ranking based on the sorting that was done then you need to have PHP do that work for you.

Change this:

<?php  

while($row = mysql_fetch_array($result)){  

?>  

<tr> 
<td bgcolor="#A8BFFF" width="8"><?=$row[0]?>&nbsp;</td>

To this:

<?php  

$rowID = 0;
while($row = mysql_fetch_array($result)){  

$rowID++;
?>  

<tr> 
<td bgcolor="#A8BFFF" width="8"><?=$rowID?>&nbsp;</td>

kimrari

wow!.

you sir are Da Man.

Not only did you help me with the issue, but you taught me something so that I can resolve this myself in the future.

Thanks alot, I really appreciate that big time.