[SOLVED] MySQL renumbering a row question/help!

[rubbertoad]

Hello - I assume this has been asked before, but I could not find it in the forum, so here it goes:

 

I have a table with an id column which is the Primary key and auto incremented and then I have another column named orderls that contains a unique number.  This number is used to define the order in which the row data is displayed on a page. 

 

When I delete a row, the orderls column now has a gap and I would like to renumber the subsequent rows to remove the gap.  I would think it's a simple task, but I can't figure out how to do it.  If I use the Update command with a $counter it renumbers all the rows to the same value.

 

Could someone help me with this?

 

Thanks,

 

Rob

[redarrow]

If your deleting the whole row, then the row shouldn't be there at all.

 

are you sure your using delete mysql function not update?

 

example

this will delete any row where the user_id matches in the database field.

if the user got more info in that field add a AND what_ever_field='what_ever'

<?php

$sql="DELETE FROM anythink where user_id='$user_id'";

[Mchl]

<facepalm>

 

Ok

 

How about this

 

UPDATE table SET orderls = orderls - 1 WHERE orderls > deleted_orderls 

[rubbertoad]

I must be dense, but I cannot find a way to implement it.  WHen I do I get all sorts of errors.  Still trying. 

 

<facepalm>

 

Ok

 

How about this

 

UPDATE table SET orderls = orderls - 1 WHERE orderls > deleted_orderls 

[Mchl]

Without any more details from your side, it'll be hard to help you.

[rubbertoad]

Sorry, here is what I'm doing:

 

I'm bringing the value of orderls via URL.  I then perform the deletion of the row.  Next I update the remaining rows that are after the deleted row using the following statement:

 

$deleteSQL = "UPDATE {&tablename} SET orderls = orderls - 1 WHERE orderls > $ordls";

$query_up =mysql_query($deleteSQL, $connC) or die(mysql_error());

 

{&tablename} is brought via URL.

$ordls is brought in via URL.

 

When I add this statement to my code, I get the following error:

 

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&tablename} SET orderls = orderls - 1 WHERE orderls > 7' at line 1"

 

Thanks,

 

Rob

[rubbertoad]

If I hardcode the tablename it works... Why?

 

Rob

[Mchl]

You do not replace {&tablename} with actual table name.

[rubbertoad]

The problem is that the tablename comes via URL.  So I cannot hardcode it or it won't work.  Am I missing something?  It works for the SELECT and DELETE operations.  Why is it not working here?  I use it a few lines above to delete the row.

 

In this code, it works:

 

mysql_select_db($database_connC, $connC);

$delSQL = "DELETE FROM {$tablename} WHERE id=$rowid";

$Result1 = mysql_query($deleteSQL, $connC) or die(mysql_error());

 

 

You do not replace {&tablename} with actual table name.

[rubbertoad]

I fixed it by replacing {$tablename} with $_GET['variable']

 

In the code $tablename = $_GET['variable'];

 

So I'm  a bit confused...  But it works.

 

Next I have to figure out how to insert a row in the middle of a table.  Basically with orderls in the middle of a table and bumping the subsequent rows down one.

 

Rob

[Mchl]

YOu had & instead of $. That's why it didn't work.

And you should run all variables that go into query through mysql_real_escape_string to lower the risk of SQL injection.

 

Inserting isn't that difficult if you think about it. It's reverse of deleting actually. First you have to bump up all orderls above the place where you want to insert a row, then just do insert.

[rubbertoad]

Oh man, I feel like an idiot with that variable.  I can't believe I missed it...  Must be tired.

 

The issue with inserting is that because the field is unique, I have to bump them starting iwht the last one.  So I need to figure out the logic for that.

 

Thanks again I really appreciate the help.  I'm still learning.

 

Rob

[rubbertoad]

Solved!!!