silly question, how to delete a row!!!

netdur

hey,
I try to learn php/mysql, so I build my own mysql manager and there table have records looks like this:

| name | age |

.many records.

| adel | 22 |

| same | 20 |

| adel | 22 |

| same | 20 |

.many records.

how to delete the row number three [adel - 22]
if I write (DELETE FROM table_name WHERE name="adel" AND age="22") that will delete three rows!
thank in advance

ruski

You're stuck with your current table design.

You should always create tables with a unique index. One way to do this with the example you've shown, would be to add an additional column to your table to use as an object ID. You could call the column anything you want. In this example, it's called "oid", and looks like this in the CREATE TABLE command:

oid int unsigned not null auto_increment primary key

It's an integer column that auto increments itself, thus giving every row a unique identifier.

You will then be able to delete a row using:

DELTE FROM table_name WHERE oid=3 (or whatever number)

r270ba

you should have an index column which would automatically incriment and be a primary key. that way for every entry it will generate a new unique key to label each row. Then your DELETE statement can be

DELETE . . . . . . . WHERE index='3'

You should read some database tutorials for new guys. . .it will help you out on how to develop databases.

netdur

so nothing can I do for that?
I was thinking about "DELETE ... LIMIT" but i'm sure yet if that is correct syntax to use LIMIT with DELETE,
I know about

oid int unsigned not null auto_increment primary key

thank about replys

superwormy

DELETE FROM table WHERE ( field = ' whatever ' ) LIMIT 1

But your tables really should always have a unique ID number for every row...

cap97

Not sure you can do this after-the-fact, but assuming your table name is "people", try this:


ALTER TABLE people ADD 'id' INT AUTO_INCREMENT PRIMARY KEY FIRST;

This could add an primary key field at the beginning of your table. However, as I've never tried to add a primary field after-the-fact, I'm not sure it will work. But hey, give it a shot.

tbach2

Here's how to save your data:

create a new table

newtable
id (primary key, auto-increment)
name
age

now,

INSERT INTO newtable (name,age) SELECT name,age FROM original_table

(I know the syntax might look wrong, but it is correct for mysql)

viola, your old table has been imported into the new table and now has a primary key.

netdur

thank guys, I'll try all that tonight but meanwhile I have check what they did other guys at phpmyadmin do the same work! see you

magic-chef

The suggestion to add an auto_incrementing PRIMARY KEY to the table is good advice. It will allow you to generate table listings of your data with edit and delete "keys" for each record to take care of these issues. Also, if you ever want to relate the user names to other tables you will need a foreign unique key to allow definition of relationships.

netdur

on phpmyadmin I click "delete" to erase the record number three, and query was

DELETE FROM data WHERE name = 'adel' AND age = '20' LIMIT 1

that deleted the first record!

next time I'll be carefull to add ID
thank guys

magic-chef

you can add the field with phpMyadmin easily:

look for option to add field (beginning of table is customary but not necessary) then:
1. field name ie. uid
2. select type int or double if size of DB warrants
3. set field to not null
4. select auto_increment under "extra" tab

save the change and browse you table again...the uid will be updated and auto_incremented.

It will not affect your table data.