how do i delete a full row in mysql ?

[jd2007]

how do i delete a full row in mysql ?

 

 

[chigley]

DELETE FROM table WHERE x = 'y'

[thedarkwinter]

Every row should have a an ID number for indexing, preferably a auto_increment field.

 

Therefore, the command to delete a row is

 

DELETE FROM mytable WHERE rowid=xxx;

 

 

[dbo]

Well it depends on the structure of the table you're trying to delete from, but the general syntax is:

 

DELETE FROM <tablename> WHERE <field> = <value>;

 

For example:

 

This is my table structure....

COLOR

--------

ID

Name

Description

 

This is some sample data

1, Red, A red color

2, Green, A green color

 

DELETE FROM `COLOR` WHERE `ID` = 2;

 

No more row 2.

 

I suggest always deleting from tables by using the primary key... when possible. I'd suggest against saying: DELETE FROM `COLOR` WHERE `Name` = 'GREEN';

[jd2007]

what happens is after its deleted the id for other's doesnt change .... how do i update this id ?

[dbo]

They won't change. Also primary keys are not updatable. Why would you want them to?

[jd2007]

is there a way to change all ids ?

[thedarkwinter]

you really don't want to do that, ever  ???

[dbo]

If you're just wanting the numbers to be in order.... consider this.

 

$result = mysql_query("SELECT `ID`, `Name` FROM `COLOR`");

$rows = mysql_num_rows($result);

 

echo "<table><tr>";

echo "<th>Fake ID</th>";

echo "<th>Real ID</th>";

echo "<th>Name</th>";

echo "</tr>";

 

for($i = 0; $i < $rows; ++$i )

{

  $row = mysql_fetch_row($result);

  echo "<tr>";

  echo "<td>" . $i . "</td>";

  echo "<td>" . $row[0] . "</td>";

  echo "<td>" . $row[1] . "</td>";

  echo "</tr>";

}

echo "</table>";

 

So then you get a "fake id" that is ordered how you want (i think). But behind the scenes you have the real ids that aren't being updated... and the user doesn't have to see them (and probably shouldnt).