I'm new to this ng, sorry if I ask a faq, but I searched for an answer
in mysql docs and on google to no avail.

I created a query where the result set should contain "field" values
based on some condition.
In the specific I'd like to return a field table value if the value is
numeric and blank if there's some other character. And I want to return
in another "field" the same value if it's not numeric and blank if it is
numeric.

Example given:

myTable
id myField
1 12345
2 a23

My result set should be:
field1 field2
12345 blank
blank a23

select ...
if(trim(myTable.myField) REGEXP
'^-?[0-9]+$',concat('''',trim(myTable.myField)),'') AS field1,
....,
if(trim(myTable.myField)='', '',
if(trim(myTable.myField) REGEXP
'^-?[0-9]+$','',concat('',trim(myTable.myField)))
) as field2
from...

In phpmyadmin I get strange results.
When I run the query field1 and field2 values seem to be ok. If the
result set has more than 30 records (phpmyadmin LIMIT default values),
when I move the record pointer to the following results, field1 is
always blank and field2 has always a value, be it numeric or not.
I tried to use "case when" but the result is the same.

I'm using MySQL: 5.0.51b-log and phpMyAdmin - 2.11.1.2 on slackware 12.1.

I used a similar query in other dbs (mssql, access) and it always worked
as expected. Is there some limit on mysql use of if or case? Or is my
query wrong?

Thank you
maxx

On 12/17/2011 4:46 AM, emmexx wrote:
Do you get the correct results if you run the query from the CLI?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Il 12/17/2011 02:42 PM, Jerry Stuckle scrisse:

GUIs make people lazy... ;-)
From the CLI it works.

I tried to export to excel from phpmyadmin and it didn't work too.

Is there a phpmyadmin ng? :-)

thank you
maxx

On 17-12-2011 10:46, emmexx wrote:
mysql will do some explicit conversions when trying to calculate with a
string-value.
(see: http://dev.mysql.com/doc/refman/5.0/...onversion.html)

mysql> select * from myTable;
+------+---------+
2 rows in set (0.00 sec)

mysql> select if(myField+1<>1,MyField,'blank') field1, if
(myField+1<>1,'blank',myField) field2 from myTable;
+---------+---------+
2 rows in set (0.00 sec)


--
Luuk

On 12/17/2011 1:12 PM, emmexx wrote:
A good place to start would be http://www.lmgtfy.com/?q=phpmyadmin

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Il 12/17/2011 07:49 PM, Luuk scrisse:
I don't think there's a conversion problem here.
I forgot to mention it in in my 1st post but the query works properly if
there's just one if or case. The problem arises when there's an if
condition on a field and the negative condition on the same field (sorry
but my english is lacking the right words here, what I mean is that I test
if (condition,true-part,false-part)
and then I test
if (!condition,true-part,false-part)
(I used C or php or javascript notation for the *not* operator)
E.g.

select ...
if (field1>=0, 'pos', 'neg') as positive,
if (field1<0), 'neg', 'pos') as negative
....

If the same field gets tested => error, if I test the field only once
the query works properly.

Thank you
maxx

On 17-12-2011 21:46, emmexx wrote:
so, you are saying that mu statment (which was:
mysql> select if(myField+1<>1,MyField,'blank') field1, if
(myField+1<>1,'blank',myField) field2 from myTable;"

fails if i write it like:
mysql> select if(myField+1<>1,MyField,'blank') field1, if
(!(myField+1<>1),myField,'blank') field2 from myTable;

test shows:
mysql> select if(myField+1<>1,MyField,NULL) field1, if
(!(myField+1<>1),myField,NULL) field2 from myTable; +--------+--------+
2 rows in set (0.00 sec)

So, it must be something in your REGEXP, or my Italian

--
Luuk

Il 12/17/2011 10:46 PM, Luuk scrisse:

No, I'm just saying that the problem is phpmyadmin and not a conversion
problem.
As I can see in your post your tests were conducted in the CLI. As
suggested by Jerry Stuckle, and I confirmed it, my query works properly
in the CLI. The problem arises only in phpmyadmin.

Not in my REGEXP but in phpmyadmin REGEXP. ;-)

There's something weird happening in phpmyadmin when moving the record
pointer.

I modified my query in order to use your syntax instead of regexp.
My query takes as input 2 fields containing telephone numbers. I need to
put in 2 different fields in the result set the numbers that are
composed only by digits (no spaces, no other characters). If the field
value is "only digits" I put it in the first field, otherwise I put it
in another special rubbish field where I concatenate all the values in
the wrong format (I'm exporting data from one db to another db with
different tables.)

This is what I put in phpmyadmin SQL window:

SELECT
if(p.cellulare+1<>1,concat('''',trim(p.cellulare)) ,'') AS cellulare,
if(p.tf_pers+1<>1 ,concat('''',trim(p.tf_pers)),'') AS telefono,
p.i_pers AS 'data di nascita',
'' AS 'comune di nascita',
concat(
if(trim(p.tf_pers)='', '',
if(p.tf_pers+1<>1,'',concat('tel: ',trim(p.tf_pers)))
),
if(trim(p.cellulare)='', '',
if(p.cellulare+1<>1,'',concat('tel: ',trim(p.cellulare)))
)
)
AS note
FROM ...

To begin with the results are wrong. :-(
This is what I get for the first 30 records:

'02 419834
'3388338313
'3335249862 '0239444472

'02 730419
'3495060547 '02 58100642
'02 29517093
'389 07378015
'02-2403609
'02 39313056
'3489896607 '0229515701
'3286481911

'3392785709
'0248300604
'3356278841 '0258300098

'0276116764

'02 66986072
'333 4089289 '02 69005242

'339 8893622 '02 64109528
'3333581989 '0258105976
'02 3548763
'3481724014 '02 33100589
'3385325715 '0257301022
'0229527292
'3485154900

(sorry for the format)
Ok, I could have translated your method the wrong way, More on that later.
Now I click on phpmyadmin > button to see the next 30 records and I get
an error. I've never seen anything like that... :-(
And, worse than that, the query shown in phpmyadmin error page is wrong
and different from the query I entered:

SELECT if( p.cellulare1 <>1, concat( '''', trim( p.cellulare ) ) , '' )
AS cellulare, if( p.tf_pers1 <>1, concat( '''', trim( p.tf_pers ) ) , ''
) AS telefono, concat( if( trim( p.tf_pers ) = '', '', if( p.tf_pers1
<>1, '', concat( 'tel: ', trim( p.tf_pers ) ) ) ) , if( trim(
p.cellulare ) = '', '', if( p.cellulare1 <>1, '', concat( 'tel: ', trim(
p.cellulare ) ) ) ) ) AS note
FROM ...

As you can see
p.cellulare+1<>1
became
p.cellulare1 <>1 (plus missing)

If I run the query in the CLI the results are not what I expect but I
get no error.

As per your query it works if there's a non digit character in the first
position of the string but it fails (ok, I had not given you all the
details about what I want to get :-) ) when there's a non-digit char
inside the string.

Thank you
maxx

On 18-12-2011 13:01, emmexx wrote:

Hmmm, what version of PhpMyAdmin are you using? The current version is
3.4.8
If you use an older version, than please upgrade

And when you say that "p.cellulare+1<>1" was converted to "p.cellulare1
<>1", i would try to use back-quotes around field-names like:
`p.cellulare`+1<>1

(I dont have PhpMyAdmin to test if that makes a difference


Also the version of MySQL you are using (5.0.51b) is quite old, i'm
using 5.0.67, which is way too old too

--
Luuk

Il 12/18/2011 04:09 PM, Luuk scrisse:
I suppose I should... :-)
That's my development notebook, not a production server.

p.`cellulare`+1<>1

No way, same error. I'll update phpmyadmin or stop using it. ;-)

Thank you
maxx