Ordering a field in mysql with PHP

ndounna

Morning !

I created a table with tre field:id,name,code.

The code have the default value set to one.

The thing I want to is to insert from a form article name into my table incrementing the code through php.
When I deleted a record, I have the succesion of code 1,2,3.

ex:id=1 name=Tennis code=1
id=3 name=Volley code=2
id=4 name= Basket code3

When I deleted record with id=3 I have in my table result like this:
id=1 name=Tennis code=1
id=4 name= Basket code=2

Help please ,Marcel

Anon

That's a pretty weird thing to have happen. Could we see the code responsible? I can't think why a simple "delete from sport where id=3" would cause the code field of another row entirely to change.

ndounna

Hello Grumbler, The thing I want is that when I deleted a record from my table, I want to have the record ordered in field codice from value 1,2..and so on.That what i want to do.

<?
$cnx=mysql_connect("localhost","root","mypasswd");
mysql_select_db("Compito",$cnx);
$sql="Delete from articolo where id=$id";
$resp=mysql_query($sql);
if($resp)
{
echo"<p>Delete successfull!!</p>";
}
else
{
echo"Error deleting Article".mysql_error();

}
//*********************Updating field codice

$sql = "SELECT codice FROM articolo";

$result = mysql_query($sql) ;

$num=mysql_numrows($result);
echo"il num righe:$num<br>";

$i=1;
while($riga=mysql_fetch_array($result))
{
$sql1="UPDATE articolo SET codice='$i'";
mysql_query($sql1);
$i=$i + 1;
}
//**************************

Thanks for your answer,marcel

Anon

Okay, I think I'm with you now. If you have

id=1 name=Tennis code=1
id=3 name=Volley code=2
id=4 name= Basket code=3

And you delete id=3, you want to end up with:

id=1 name=Tennis code=1
id=4 name= Basket code=2

With the code field adjusted.

Here's the idea: before deleting the record, find out what its code is:

SELECT code FROM articolo WHERE id=3;

and capture the result in $code - we'll be using later. Then do the delete:

DELETE FROM articolo WHERE id=3.

Now comes the code updating. We want every record with a code greater than the one just deleted to have its code value decreased by one (to fill the gap):

UPDATE articolo SET code=code-1 where code>$code

does this.

Is this what you wanted?

ndounna

Thanks Indeed Grumbler!!! for the idea.

It's working.

Marcel