Deleting a row from MySQL

[Atomic-Design]

How do I delete a row of info from a MySQL database?

[Catweasel]

Well, the MySQL manual says :
"DELETE [LOW_PRIORITY | QUICK] FROM table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT rows]"

An example is :
"DELETE FROM my_table WHERE id = 10"

which would delete from the table named my_table all records where the id field equals 10. You did include a unique record identifier in your tables, didn't you? ;)

[sophiespo]

OR if youve got phpMyAdmin installed then click on the table name that has the row you want to delete, click on Browse in the main frame, and then when you see the row you want to get rid of, click on the delete link right next to it. Simple as that! It essentially does the same thing the other person suggested, but you dont have to worry about syntax or anything like that.

[Atomic-Design]

I have a small problem. My script loops to display all the rows in a table by using ID numbers. However, when I delete a row (like if a user wants to delete someone elses name), then I have Id's like this:

1
2
3
5
6
7

See, I deleted the 4th row. Now when my script goes, it doesn't work right, because the 4th row is blank, so it messes it up. How can I avoid this ... or can I make the MySQL table go back to:

1
2
3
4
5
6

[sophiespo]

you may have to modify the script to use a counter variable for the numbers instead of the row ID number..

[Catweasel]

Originally posted by Atomic-Design

See, I deleted the 4th row. Now when my script goes, it doesn't work right, because the 4th row is blank, so it messes it up. How can I avoid this ...

I'm guessing that you're spinning through a loop, listing your users (or whatever) from 1 to last-record, and it's biting the dust because there's now a hole in the middle?

Try thinking a little laterally...

This is untested, off the top of my head, etc ... but I do something similar myself somewhere in my code (I just can't find where at the moment!)

In PHP :

$result=mysql_query("SELECT id, username, password FROM my_table");
while(list($id, $username, $password)=mysql_fetch_row($result) {
print "$id | $username | $password";
}


This'll re-query the db, with a record in each row, and spins through the actual result rather than both valid and non-valid id numbers.

(On second thought, you probably don't want to display the password :))

Hope this helps!

[devinemke]

use a "foreach" loop instead of a "for" loop to avoid row numbering problems