delete specific row from mysql query

[uioppoiuuioppoiu]

Is there a way to delete a specific row from a query? I have a page that retrieves all the information from an user specified database and table so my query is "SELECT * FROM {$dbtable}" and I have checkboxes for each row. I am wondering if there is some variation on "DELETE FROM {$dbtable} ROW '{$row}' OF (SELECT * FROM {$dbtable})" that is syntactically right.

 

[AdaHacker]

Well, it's theoretically possible to do something like this using ORDER BY and LIMIT clauses in your DELETE query, but it's a really bad idea. SQL is set-oriented, and sets, by definition, do not have an ordering. So saying "delete the third item in this table" doesn't really make sense because "third item" is not strictly defined.

What you really want to do is to delete items based on their primary key. If the table doesn't have a primary key, then you should add one. It will give you better performance and more data integrity than trying to rely on offsets in result sets.

As far as code goes, all you need to do is to set up your form so that the check boxes in your table pass back the primary key values to your database access layer. Then you can just delete a particular record with a simple simple "DELETE FROM dbtable WHERE id = $keyval", or something along those lines.

 

[ingresman]

I'd recomend a read of an SQL book before trying to delete rows. Unless you have a back up you can't get the data back.

 

[uioppoiuuioppoiu]

Thanks a lot AdaHacker. I took your suggestion and it works now. Another question: does mysql_query("LOAD DATA INTO {$dbtable} FROM ... "); return a boolean value or an object like the SELECT statement does? ingresman: I'm using a test database and table for this project so no harm done

 

[AdaHacker]

Another question: does mysql_query("LOAD DATA INTO {$dbtable} FROM ... "); return a boolean value or an object like the SELECT statement does?

I've never had occasion to run that from PHP, but I'd assume it returns a boolean. Why don't you just try it and find out?