Delete specific row from MySQL database by row number

Question

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.

Rows in a database do have an index, it's called the primary key value; and your table should have a primary key that is a unique identifier for that row. Use the primary key of the rows, not the position in a PHP array — Mark Baker
– Mark Baker, Commented Sep 10, 2016 at 16:57
@JayBlanchard Where would I get the row_identifier? If I specify a condition like that, it expects a column, so the only way to specify the row number like this, is to add a new column and call it 'row_identifier'. — Freddy Benson
– Freddy Benson, Commented Sep 10, 2016 at 20:05
row_identifier is just an example. Use a piece of data from the row which matches it uniquely — Jay Blanchard
– Jay Blanchard, Commented Sep 10, 2016 at 20:22

phpchap · Accepted Answer · 2016-09-10 21:04:41Z

1

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 . "'";

edited Sep 10, 2016 at 21:04

answered Sep 10, 2016 at 17:22

phpchap

3822 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Freddy Benson Over a year ago

I understand that. It wouldn't really be a big deal, because in the client side code, I can just increment the returned index by 1 to get the row number (index of '3' would become row number '4'). I'm just wondering how I would get the rowIndex to contain a number representing the row number of the row I want to delete. As far as I know, I would have to create a new column with the index and assign it to a variable 'rowIndex', right?

phpchap Over a year ago

Added additional info to the answer

Collectives™ on Stack Overflow

Delete specific row from MySQL database by row number

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related