Jump to content

Archived

This topic is now archived and is closed to further replies.

Masna

Telling MySQL Rows Apart-->

Recommended Posts

Hey everyone! Is there an efficient way/function for telling MySQL rows apart based on one definite, distinct, unchangeable character? I'm working on a MySQL admin (similar to phpmyadmin), and am currently working on the Delete portion. But, I realized that I don't know how to distinguish two MySQL rows apart if they have the exact same information in the given fields. For example, if I have a table with two fields, field1 and field2, and I have two rows in that table, both with the value "hey" for field1, and "yo" for field2, I don't know how to distinguish them from one another. Do you? Thanks in advance.

Share this post


Link to post
Share on other sites
if you going to delete rows there must be a way for you to echo the rows out, if they are echo out they will be dferent by the auto increment id wich is never the same..

Share this post


Link to post
Share on other sites
[!--quoteo(post=382404:date=Jun 11 2006, 04:14 AM:name=Richard181)--][div class=\'quotetop\']QUOTE(Richard181 @ Jun 11 2006, 04:14 AM) [snapback]382404[/snapback][/div][div class=\'quotemain\'][!--quotec--]
if you going to delete rows there must be a way for you to echo the rows out, if they are echo out they will be dferent by the auto increment id wich is never the same..
[/quote]

You must be new around here [img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]. There's not always an auto_increment field in a table.

Share this post


Link to post
Share on other sites
i don't think that's the point of the script, skullmaster. (s)he is making a script like phpmyadmin. go to phpmyadmin and make a table with 2 columns named column1 and column2. then insert a couple of rows into it, but make some of them with the exact same data:
[code]
column1 (int)  column2 (int)
4                   4
4                   4
3                   2
4                   4
5                   1
[/code]
now do a select * from table query and you will notice that they appear in the same order. there is no sorting by or unique id's to go by. but phpmyadmin just knows what order it goes in.

all i can assume is that phpmyadmin has a hidden column automatically made when you create a table.

Share this post


Link to post
Share on other sites
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]now do a select * from table query and you will notice that they appear in the same order. there is no sorting by or unique id's to go by. but phpmyadmin just knows what order it goes in. [/quote]
Beacause that is physical order when they were added.

auto_increment id's (or other column used as primary key) are not just for sequencing data, they are to provide a means of unique identification for each row, which is exactly what is required here

Share this post


Link to post
Share on other sites
[!--quoteo(post=382462:date=Jun 11 2006, 11:03 AM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 11 2006, 11:03 AM) [snapback]382462[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Beacause that is physical order when they were added.

auto_increment id's (or other column used as primary key) are not just for sequencing data, they are to provide a means of unique identification for each row, which is exactly what is required here
[/quote]

So what I'm trying to accomplish is impossible without a hidden identification system?

Share this post


Link to post
Share on other sites
Interesting news everyone! phpmyadmin works just as I had suspected! If there exists more than one row in a table with the exact same information in each field, phpmyadmin just edits/drops the first row in that table that it can find with the information of the row selected. So if you have 3 rows, all with the exact same information, and want to edit 2 of them, phpmyadmin just looks for two rows with the same information as the one(s) you checked with WHERE clauses. In this case, we all must take note of the fact that phpmyadmin may not always be working with the row in a MySQL table that you chose for it to. I'm still trying to figure out whether or not this will be 100% efficient.

Share this post


Link to post
Share on other sites
well let's think about this for a second. if you have 3 rows and they are the [i]exact[/i] same, and you edit 2 of them, so that you want 2 of them to be different now, but the 3rd one to remain unchanged, what difference does it make which one is changed?

barand:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Beacause that is physical order when they were added.
[/quote]
yeah but how does phpmyadmin keep track of that order? WE know when it was physically added, but without some kind of timestamp or id, phpmyadmin wouldn't know. and yet it does.

Share this post


Link to post
Share on other sites
I wrote 10 records to a text file then appended another 10.

When I read the file I got all 20 listed in the same order. Should I now assume there is some timestamp hidden somewhere?

In fact, with MySql, if you "SELECT * FROM mytable" then you get the records listed in their physically stored order. This is not necessarily the order they were added, even if you have an auto_increment id column, since it will insert a new record in places vacated by a deleted record if there is sufficient space.

So, if I have a table with 4 records then
- delete the second
- add a fifth (same size or smaller then the deleted second record)

they now appear in the order (of id, auto increment) 1, 5, 3, 4

Share this post


Link to post
Share on other sites
Barand, do you think it would matter if I went along with phpmyadmin's method?

Share this post


Link to post
Share on other sites
I don't think it is very reliable. If you have client who uses MS SQL Server then it certainly will NOT work. In the instance you decribe, any update or deletion in MS SQL wouls be applied to all the records with same content as it would be unable to distinguish between them.

The safest way is for each record to have a uniquely distinguishable identifer.

This can be an id field, another unique field or a unique combination, just so long as you cannot have 2 or more identical rows.

Share this post


Link to post
Share on other sites
I have a stupid question.

Under what circumstances would you be storing identical rows with NO way to differentiate them? What purpose would it serve? I can understand duplicate entries, but only if they have an ID that links to another table or something so that you can tell them apart.

The only possible thing I can think of is something like a poll, but if you're going to be modifying or deleting them, it still stands to reason that they need some sort of unique identifier, such as an auto-increment ID field.

Wendy

Share this post


Link to post
Share on other sites
[!--quoteo(post=382639:date=Jun 11 2006, 08:32 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 11 2006, 08:32 PM) [snapback]382639[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I don't think it is very reliable. If you have client who uses MS SQL Server then it certainly will NOT work. In the instance you decribe, any update or deletion in MS SQL wouls be applied to all the records with same content as it would be unable to distinguish between them.

The safest way is for each record to have a uniquely distinguishable identifer.

This can be an id field, another unique field or a unique combination, just so long as you cannot have 2 or more identical rows.
[/quote]

Well, this is solely a MySQL admin, but, anyway.. Even if it was for MS SQL... Doesn't MS SQL allow limits on deletions?

[!--quoteo(post=382669:date=Jun 11 2006, 10:31 PM:name=WendyLady)--][div class=\'quotetop\']QUOTE(WendyLady @ Jun 11 2006, 10:31 PM) [snapback]382669[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have a stupid question.

Under what circumstances would you be storing identical rows with NO way to differentiate them? What purpose would it serve? I can understand duplicate entries, but only if they have an ID that links to another table or something so that you can tell them apart.

The only possible thing I can think of is something like a poll, but if you're going to be modifying or deleting them, it still stands to reason that they need some sort of unique identifier, such as an auto-increment ID field.

Wendy
[/quote]

Well, this is a MySQL admin that I plan to redistribute. Besides, there's probably a lot more instances in which all fields among more than one row have the same values, than you know. I'm trying to figure out if it matters... So far, it would appear that it doesn't.

Share this post


Link to post
Share on other sites
Funny thing, but phpMyAdmin will add a "LIMIT 1" clause when you UPDATE/DELETE rows.

Maybe this means phpMyAdmin doesn't actually "know" which is which, but MySQL itself will figure that out when you run the query.

Also, when you have duplicate rows and you select more than one of them, phpMyAdmin will delete only one.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.