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 | 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 | 1 | 1 | Very Thin | 1 |
> | 1 | 1 | 1 | 1 | Very Thin | 1 |
> | 1 | 3 | 3 | 1 | Very Thin | 1 |
> | 1 | 3 | 3 | 1 | Very Thin | 1 |
> | 1 | 3 | 3 | 1 | Very Thin | 1 |
> | 1 | 3 | 3 | 1 | Very Thin | 1 |
> | 1 | 3 | 3 | 1 | Very Thin | 1 |
> +------+--------+------------+------+-----------+--------+
> 13 rows in set (0.09 sec)
>
> Looks like a nasty bug to me (by the way, I'm running v3.23.28), but maybe
> I'm just dumb? And of course the deadline is looming, etc.
>
> What can I do? Please note that I will gladly accept a workaround, but
> everything must be done inside a single MySQL query. (Which will later get
> processed by a big, nasty PHP program which will do unwholesome things on
it
> and which I can not touch).
>
> Thanks in advance.
>
> Luca Accomazzi
>
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
