RE: Help! MySQL variables laugh at me
if faq had a sequenced primary key you could do the following select a.sezione, IF(a.sezione = b.sezione, "no","yes") as hasChanged from faq as a, faq as b where faq.id = faq.id -1 -Original Message- From: Luca Accomazzi [mailto:[EMAIL PROTECTED]] Sent: Monday, June 04, 2001 11:02 AM To: [EMAIL PROTECTED] Subject: Help! MySQL variables laugh at me I've been frying my brain over this problem for the past 24 hours and I'll be very grateful for any hints. I'm trying to recognize when the value for a field is changing value from one record to the next. For an example, look at this: mysql> select sezione from faq limit 20; +---+ | sezione | +---+ | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Macintosh | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | +---+ 20 rows in set (0.01 sec) As you see, the field's value is almost always "Internet", but it becomes "Macintosh" in the middle. I'd like to recognize this change (and, later, act on it). I thought I had found the solution by employing a bit of variable magic. And it seemed to work just fine: mysql> select sezione, IF(@a=sezione, "no", "yes") as hasChanged, (@a:=sezione) as newValueForMemory from faq limit 20; +---++---+ | sezione | hasChanged | newValueForMemory | +---++---+ | Internet | yes| Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Macintosh | yes| Macintosh | | Internet | yes| Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | +---++---+ 20 rows in set (0.01 sec) I keep a copy of the previous value in a variable, then check that variable against the current value for the field. All is well. BUT, as soon as I tackle my real problem (on a nasty database, complex queries, and the real problem, all hell breaks loose: mysql> select ordine, IF(@oldOrdine=ordine, "no", "yes") as hasChanged, (@oldOrdine:=ordine) as newValueForMemory from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR; +++---+ | ordine | hasChanged | newValueForMemory | +++---+ | 1 | yes| 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | yes| 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | no | 1 | | 3 | yes| 3 | | 3 | no | 3 | | 3 | yes| 3 | | 3 | no | 3 | | 3 | no | 3 | +++---+ 13 rows in set (0.10 sec) WHY OH WHY is MySQL behaving like this? I tried a bunch on variations (using two variables, applying a function on the field and ever weirder ones), to no avail. It gets even more unpredictable, as if the variables refused to be set and reset. mysql> select @a, ordine, @b:=ordine, @b, IF (@a=@b, 'Very Thin', 'Thin') as divider, @a:=@b from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR; +--+++--+---++ | @a | ordine | @b:=ordine | @b | divider | @a:=@b | +--+++--+---++ | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 |
Re: Help! MySQL variables laugh at me
Ok. This is what I think you are wanting. Have a datetime field or even a datestamp. Before you insert, do a select to find the value of the most recent row that was added then you can calculate what you should insert from there. BTW you should include history incase some else has ideas. - Original Message - From: "Luca Accomazzi" <[EMAIL PROTECTED]> To: "Rolf Hopkins" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, June 04, 2001 20:47 Subject: Re: Help! MySQL variables laugh at me > > I have only had a quick look over this and already have a question for the > > simple example you've provided. How are you determining the last value > > entered before entering the new value? The only way I see you of > > effectively accomplishing this is through the use of a datetime column. Or > > am I missing something? > > Wait wait wait! I see what you mean. What I'm seeing as a loop (rows upon > rows of data from the database) is a single query to MySQL. So it's fist > selecting, then calculating my variable, then sorting. And in sorting, I see > "wrong" values for my "is different from the previous" pseudo field. > > Glact. > > Is there anything I can do to get the functionality I'm after in any other > way? I seem to remember there's a "having" clause which gets computed in the > end? > > L.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help! MySQL variables laugh at me
> I have only had a quick look over this and already have a question for the > simple example you've provided. How are you determining the last value > entered before entering the new value? The only way I see you of > effectively accomplishing this is through the use of a datetime column. Or > am I missing something? Wait wait wait! I see what you mean. What I'm seeing as a loop (rows upon rows of data from the database) is a single query to MySQL. So it's fist selecting, then calculating my variable, then sorting. And in sorting, I see "wrong" values for my "is different from the previous" pseudo field. Glact. Is there anything I can do to get the functionality I'm after in any other way? I seem to remember there's a "having" clause which gets computed in the end? L.A. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help! MySQL variables laugh at me
I have only had a quick look over this and already have a question for the simple example you've provided. How are you determining the last value entered before entering the new value? The only way I see you of effectively accomplishing this is through the use of a datetime column. Or am I missing something? - Original Message - From: "Luca Accomazzi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, June 04, 2001 17:02 Subject: Help! MySQL variables laugh at me > I've been frying my brain over this problem for the past 24 hours and I'll > be very grateful for any hints. > > I'm trying to recognize when the value for a field is changing value from > one record to the next. For an example, look at this: > > mysql> select sezione from faq limit 20; > +---+ > | sezione | > +---+ > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Macintosh | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > +---+ > 20 rows in set (0.01 sec) > > As you see, the field's value is almost always "Internet", but it becomes > "Macintosh" in the middle. I'd like to recognize this change (and, later, > act on it). > > I thought I had found the solution by employing a bit of variable magic. And > it seemed to work just fine: > > mysql> select sezione, IF(@a=sezione, "no", "yes") as hasChanged, > (@a:=sezione) as newValueForMemory from faq limit 20; > +---++---+ > | sezione | hasChanged | newValueForMemory | > +---++---+ > | Internet | yes| Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Macintosh | yes| Macintosh | > | Internet | yes| Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > +---++---+ > 20 rows in set (0.01 sec) > > I keep a copy of the previous value in a variable, then check that variable > against the current value for the field. All is well. > > BUT, as soon as I tackle my real problem (on a nasty database, complex > queries, and the real problem, all hell breaks loose: > > mysql> select ordine, IF(@oldOrdine=ordine, "no", "yes") as hasChanged, > (@oldOrdine:=ordine) as newValueForMemory from aghi1, Not_needle_aux where > (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' > or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by > ordine, SIZEMR, LENGTHMETR; > +++---+ > | ordine | hasChanged | newValueForMemory | > +++---+ > | 1 | yes| 1 | > | 1 | no | 1 | > | 1 | no | 1 | > | 1 | no | 1 | > | 1 | yes| 1 | > | 1 | no | 1 | > | 1 | no | 1 | > | 1 | no | 1 | > | 3 | yes| 3 | > | 3 | no | 3 | > | 3 | yes| 3 | > | 3 | no | 3 | > | 3 | no | 3 | > +++---+ > 13 rows in set (0.10 sec) > > WHY OH WHY is MySQL behaving like this? I tried a bunch on variations (using > two variables, applying a function on the field and ever weirder ones), to > no avail. It gets even more unpredictable, as if the variables refused to be > set and reset. > > mysql> select @a, ordine, @b:=ordine, @b, IF (@a=@b, 'Very Thin', 'Thin') as > divider, @a:=@b from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR > GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or > Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR; > +--+++--+---++ > | @a | ordine | @b:=ordine | @b | divider | @a:=@b | > +--+++--+---++ > | 1| 1 | 1 |
