Jump to content


Photo

Telling MySQL Rows Apart-->


  • Please log in to reply
15 replies to this topic

#1 Masna

Masna
  • Staff Alumni
  • Advanced Member
  • 288 posts
  • LocationNew York

Posted 11 June 2006 - 04:10 AM

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.
I like LoL.

#2 corillo181

corillo181
  • Members
  • PipPipPip
  • Advanced Member
  • 896 posts

Posted 11 June 2006 - 04:14 AM

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..

#3 Masna

Masna
  • Staff Alumni
  • Advanced Member
  • 288 posts
  • LocationNew York

Posted 11 June 2006 - 04:17 AM

[!--quoteo(post=382404:date=Jun 11 2006, 04:14 AM:name=Richard181)--][div class=\'quotetop\']QUOTE(Richard181 @ Jun 11 2006, 04:14 AM) View Post[/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.
I like LoL.

#4 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 11 June 2006 - 06:05 AM

only thing i can think of is to have a hidden unique column?
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#5 SkullMaster

SkullMaster
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 11 June 2006 - 06:18 AM

i would always put in an id field, just to make things easier

#6 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 11 June 2006 - 06:25 AM

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:
column1 (int)  column2 (int)
4                   4
4                   4
3                   2
4                   4
5                   1
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.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 11 June 2006 - 11:03 AM

[!--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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 Masna

Masna
  • Staff Alumni
  • Advanced Member
  • 288 posts
  • LocationNew York

Posted 11 June 2006 - 05:50 PM

[!--quoteo(post=382462:date=Jun 11 2006, 11:03 AM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 11 2006, 11:03 AM) View Post[/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?
I like LoL.

#9 Masna

Masna
  • Staff Alumni
  • Advanced Member
  • 288 posts
  • LocationNew York

Posted 11 June 2006 - 06:01 PM

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.
I like LoL.

#10 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 11 June 2006 - 06:07 PM

well let's think about this for a second. if you have 3 rows and they are the exact 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.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#11 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 11 June 2006 - 07:22 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#12 Masna

Masna
  • Staff Alumni
  • Advanced Member
  • 288 posts
  • LocationNew York

Posted 11 June 2006 - 08:25 PM

Barand, do you think it would matter if I went along with phpmyadmin's method?
I like LoL.

#13 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 11 June 2006 - 08:32 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#14 WendyLady

WendyLady
  • Members
  • PipPipPip
  • Advanced Member
  • 38 posts

Posted 11 June 2006 - 10:31 PM

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

#15 Masna

Masna
  • Staff Alumni
  • Advanced Member
  • 288 posts
  • LocationNew York

Posted 12 June 2006 - 04:36 AM

[!--quoteo(post=382639:date=Jun 11 2006, 08:32 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 11 2006, 08:32 PM) View Post[/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) View Post[/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.

I like LoL.

#16 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 12 June 2006 - 05:14 AM

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.
~ D Kuang




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users