CODEWITHSUNDEEP
phpmysqldatabasesql-delete
Freddy Benson
Freddy Benson

Reputation: 775

Delete specific row from MySQL database by row number

I'm new to using MySQL queries, so please bear with me. I got an array of objects on my client side of the database connection (iPhone app) and I'm trying to implement a delete button to give the user the option to delete a certain object in the array.

I'm expecting to get an integer returned, which represents the index of the object that got deleted and now I'm creating a PHP script to take that index and use it to delete the matching row in my MySQL table.

How do I specify a specific row number to get deleted? For example, if the fourth object in my array gets deleted, I get an index of 3 returned to me. Is there any way to use this index to specify that I want the fourth row in my MySQL database deleted?

I get the whole syntax of DELETE FROM "MySQLTable" WHERE... but after the WHERE it gets kinda confusing, because it seems to me you always have to specify a condition, instead of just passing in a number of the row you want deleted. Am I wrong about this? Do rows in a MySQL table even have an index? I assume they do. Some tips on how to delete a specific row in the database by passing in a value (preferably an index) would be appreciated.

Upvotes: 0

Views: 7832

Answers (1)

phpchap
phpchap

Reputation: 382

Be careful because arrays are zero indexed (array first item will be 0) and generally database tables aren't (table first item will be 1) so that means that the fourth item of the array is 3, whereas the fourth item of the database table will be 4.

However once you have the id of row that you would like to delete then you have to extend your query by adding in that number, for example:

DELETE FROM "MySQLTable" WHERE rowIndex = 'numberToDelete'

You'll need to change rowIndex and numberToDelete as appropriate for your code.

Generally rows in the database table will have an index but the index has to be defined manually in the table as it won't be added automatically.

---Additional info---

You'll need a column (rowIndex in the example) in your database table that contains the index you have in your array, it may make sense for this to be your primary key assuming all the values are unique.

You can then use your integer value in your sql query to delete the row, for example:

$sql = "DELETE FROM MySQLTable WHERE rowIndex = '" . $numberToDelete . "'";

Upvotes: 1

Related Questions