PHP Freaks

Very nice tutorial CV!.

Nice Tutorial, I'd like to see how this could be adapted to work with a system where rows can also be deleted. So;
1, 2, 3, 4, 5 could change to 1, 2, 3, 5, 4

then something could be removed 1, 3, 5, 4

then it could re-order without first doing this 1, 2, 5, 4

Okay, here you go. Add this code somewhere before the codeblock that selects the data to be displayed:

// delete from table if ($_GET['del'] == 'true') { // cast id as int for security $id = (int) $_GET['id']; // delete row from table $sql = "DELETE FROM info WHERE usort = '$id'"; $result = mysql_query($sql, $conn) or die(mysql_error()); // select the info, ordering by usort $sql = "SELECT usort, name FROM info ORDER BY usort"; $result = mysql_query($sql, $conn) or die(mysql_error()); // initialize a counter for rewriting usort $usort = 1; // while there is info to be fetched... while ($r = mysql_fetch_assoc($result)) { $name = $r['name']; // update the usort number to the one in the next number $sql = "UPDATE info SET usort = '$usort' WHERE name = '$name'"; $update = mysql_query($sql, $conn) or die(mysql_error()); // inc to next avail number $usort++; } // end while } // end if del

and then inside your while loop that lists the data, add another table cell next to the name or wherever:

echo "<td><a href='{$_SERVER['PHP_SELF']}?del=true&id={$r['usort']}'>delete</a></td>";

Basically what that does is first check to see if the delete link was clicked. If so, it takes the id and deletes the row that has the id.

Then, all the rows and columns are selected. Okay here's the thing about that: You need another unique column in order to do this. In that code ^ I use the name column because it's the only other column there is in this code, and the example data happens to be unique. Ideally, you would use a real unique column like the primary key or account_id type column or whatever.

Basically the idea is to then loop through each row and use a regular + 1 inc'ed var and update the usort column where name = current name.... and that's why you really need to use your primary key column instead of some column that can have duplicate data.

Again, this code example only has 2 columns to work with, and they both happen to be unique. Ideally, when you're getting into adding and deleting rows, you're going to have a separate column containing a unique id, as primary key, auto_ince'd or whatever. So in your real code, you would be passing that id and basing your delete off that, not the usort column. And ideally, you would be looping through the data and basing the update off that same unique id column in the where clause.

That's fantastic thank you. Will be implementing something similar in my CMS

Hi, great tutorial that you wrote.
I have a few questions.
How can i add another line to the table? Can you explain how to count the number of rows so the new one will be like total + 1?
In case of use a unique id as you mentioned how should it look on your code? What the variable $r['name'] means?
Thanks a lot for your work.

alapimba,

Since the numbers are supposed to be unique increments of 1 to x, there are several ways you can find out the highest number. For instance,

You can...

- SELECT usort FROM info ORDER BY usort DESC LIMIT 1
- SELECT MAX(usort) FROM info
- SELECT count(*) FROM info

Add 1 to the result and you're good to go.

re: $r['name']

$r is an associative array that holds the current row fetched from the result source. 'name' is the name of the element for $r. You can read my basic ]database handling tutorial for more info, particularly page 11

Crayon just finished my backend thanks for your tutorial!

Thanks a lot to make this tutorial.

Great Script CV, I have added it to one of my projects (had to make a few tweaks, but thats to be expected..) But it was better than the solution i had

Great Job *5xThumbs Up*