Masna Posted June 11, 2006 Share Posted June 11, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/ Share on other sites More sharing options...
corillo181 Posted June 11, 2006 Share Posted June 11, 2006 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 Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44179 Share on other sites More sharing options...
Masna Posted June 11, 2006 Author Share Posted June 11, 2006 [!--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. Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44182 Share on other sites More sharing options...
.josh Posted June 11, 2006 Share Posted June 11, 2006 only thing i can think of is to have a hidden unique column? Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44195 Share on other sites More sharing options...
SkullMaster Posted June 11, 2006 Share Posted June 11, 2006 i would always put in an id field, just to make things easier Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44201 Share on other sites More sharing options...
.josh Posted June 11, 2006 Share Posted June 11, 2006 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 44 43 24 45 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. Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44205 Share on other sites More sharing options...
Barand Posted June 11, 2006 Share Posted June 11, 2006 [!--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 Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44235 Share on other sites More sharing options...
Masna Posted June 11, 2006 Author Share Posted June 11, 2006 [!--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? Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44333 Share on other sites More sharing options...
Masna Posted June 11, 2006 Author Share Posted June 11, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44339 Share on other sites More sharing options...
.josh Posted June 11, 2006 Share Posted June 11, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44344 Share on other sites More sharing options...
Barand Posted June 11, 2006 Share Posted June 11, 2006 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 Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44375 Share on other sites More sharing options...
Masna Posted June 11, 2006 Author Share Posted June 11, 2006 Barand, do you think it would matter if I went along with phpmyadmin's method? Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44400 Share on other sites More sharing options...
Barand Posted June 11, 2006 Share Posted June 11, 2006 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 Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44406 Share on other sites More sharing options...
WendyLady Posted June 11, 2006 Share Posted June 11, 2006 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 Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44435 Share on other sites More sharing options...
Masna Posted June 12, 2006 Author Share Posted June 12, 2006 [!--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. Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44506 Share on other sites More sharing options...
poirot Posted June 12, 2006 Share Posted June 12, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/11688-telling-mysql-rows-apart/#findComment-44521 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.