a mysql query that works fine in phpmyadmin, but errors out on PHP

Forum Moderators: open

Message Too Old, No Replies

a mysql query that works fine in phpmyadmin, but errors out on PHP

fails on mysql_query when trying to seperate multiple queries with ";"

urbanzen

4:03 am on Oct 30, 2007 (gmt 0)

Hello everyone. the following query does not work in the mysql commandline, and php "mysql_query" calls.

[b]SET @num :=0,
@provider := '';[/b]SELECT product, productcode, row_number
FROM (SELECT product, productcode, @num :=
IF (
@provider = provider, @num +1, 1
) AS row_number, @provider := provider AS dummy
FROM products
WHERE product LIKE '%ell%'
ORDER BY provider, productcode
) AS x
WHERE x.row_number <=4
LIMIT 40

But however on phpMyAdmin it gives out the desired output, returning 4 results each from the provider row.

using a commandline, the semi-colon after the SET @num :=0,
@provider := '' line breaks out the consecutive query, rendering the variables in the next queries useless, and returning the "dummy" count columns as 1.

And using php just flatout gave an error message.

My question is, is there a way to join the queries together using the same connection to the database, with both PHP, and the mysql command line? phpMyAdmin seems to do that...

urbanzen

7:06 am on Oct 30, 2007 (gmt 0)

Shoot my time to edit post has passed..

This is running on mysql version 4.1.22-standard

This is strange now. The first time all row_numbers would return 1.

If I run the query the second time, this time omitting

[b]
SET @num :=0,
@provider := '';[/b]

The row_number column is correct, and the aggrigated group by will work like below:

[fixed]
+------+-------------+-------------+-----------+------------+-------------------+
¦ @num ¦ @vall    ¦ provider  ¦ product  ¦ row_number ¦ @vall := provider ¦
+------+-------------+-------------+-----------+------------+-------------------+
¦ 1  ¦ glomarket10 ¦ BYTDZ    ¦ foo    ¦     1 ¦ BYTDZ       ¦
¦ 1  ¦ BYTDZ    ¦ BYTDZ    ¦ foo    ¦     2 ¦ BYTDZ       ¦
¦ 2  ¦ BYTDZ    ¦ BYTDZ    ¦ foo    ¦     3 ¦ BYTDZ       ¦
¦ 3  ¦ BYTDZ    ¦ CXSLRY   ¦ bar    ¦     1 ¦ CXSLRY      ¦
¦ 1  ¦ CXSLRY   ¦ DFBE    ¦ foo    ¦     1 ¦ DFBE       ¦
¦ 1  ¦ DFBE    ¦ DFBE    ¦ foo    ¦     2 ¦ DFBE       ¦
¦ 2  ¦ DFBE    ¦ DFBE    ¦ foo    ¦     3 ¦ DFBE       ¦
¦ 3  ¦ DFBE    ¦ DFBE    ¦ foo    ¦     4 ¦ DFBE       ¦
¦ 4  ¦ DFBE    ¦ glomarket10 ¦ foo    ¦     1 ¦ glomarket10    ¦
¦ 1  ¦ glomarket10 ¦ glomarket10 ¦ foo    ¦     2 ¦ glomarket10    ¦
¦ 2  ¦ glomarket10 ¦ glomarket10 ¦ foo    ¦     3 ¦ glomarket10    ¦
¦ 3  ¦ glomarket10 ¦ glomarket10 ¦ foo    ¦     4 ¦ glomarket10    ¦
[/fixed]

I have tried putting dummy variables into @num and @provider, which still don't work.

[fixed]
+------+-------------+-------------+-----------+------------+-------------------+
¦ @num ¦ @vall    ¦ provider  ¦ product  ¦ row_number ¦ @vall := provider ¦
+------+-------------+-------------+-----------+------------+-------------------+
¦ 0  ¦       ¦ BYTDZ    ¦ foo    ¦     1 ¦ BYTDZ       ¦
¦ 1  ¦ BYTDZ    ¦ BYTDZ    ¦ foo    ¦     1 ¦ BYTDZ       ¦
¦ 1  ¦ BYTDZ    ¦ BYTDZ    ¦ foo    ¦     1 ¦ BYTDZ       ¦
¦ 1  ¦ BYTDZ    ¦ CXSLRY   ¦ bar    ¦     1 ¦ CXSLRY      ¦
¦ 1  ¦ CXSLRY   ¦ DFBE    ¦ foo    ¦     1 ¦ DFBE       ¦
¦ 1  ¦ DFBE    ¦ DFBE    ¦ foo    ¦     1 ¦ DFBE       ¦
¦ 1  ¦ DFBE    ¦ DFBE    ¦ foo    ¦     1 ¦ DFBE       ¦
¦ 1  ¦ DFBE    ¦ DFBE    ¦ foo    ¦     1 ¦ DFBE       ¦
¦ 1  ¦ DFBE    ¦ glomarket10 ¦ foo    ¦     1 ¦ glomarket10    ¦
¦ 1  ¦ glomarket10 ¦ glomarket10 ¦ foo    ¦     1 ¦ glomarket10    ¦
¦ 1  ¦ glomarket10 ¦ glomarket10 ¦ foo    ¦     1 ¦ glomarket10    ¦
[/fixed]

What gives..I don't understand the logic behind running the code again. it just appears to set @num and @provider to a certain value. Does the database character set (utf-8) has something to do with it?

urbanzen

7:28 am on Oct 30, 2007 (gmt 0)

Editing the post above screws up the HTML, so I'm sorry by posting another post here. I feel that I'm very close, but looking throughout [dev.mysql.com ]

I'm not sure if it's because the variable types are wrong, the default values are bad, or if the "order by" screws up the query.