MySQL query with session (@variables) working in MySQL Workbench, but not in PHP

Question

Been wrestling with a version of this question for a few hours, and I know that it is probably something little that I'm missing.

I have a query based off of the first answer in this post:

How do I limit the number of rows per field value in SQL?

Which does exactly what I want it to do in MySQL Workbench, but does not ever set the session variable to '2' when run from:

mysql_query()

in PHP.

The following is a table, 'mytab' that demonstrates the problem:

+----+--------------+---------------+
| id | first_column | second_column |
+----+--------------+---------------+
|  1 |            1 |             1 |
|  2 |            1 |             4 |
|  3 |            2 |            10 |
|  4 |            3 |             4 |
|  5 |            1 |             4 |
|  6 |            2 |             5 |
|  7 |            1 |             6 |
+----+--------------+---------------+

And a simplified query:

select
    id, first_column, second_column, row_num
from 
(
    select *,
    @num := if(@first_column = first_column, 2, 1) as row_num,
    @first_column := first_column as c
    from mytab order by first_column,id
) as t
having row_num <= 1;

From MySQL workbench I get this:

+----+--------------+---------------+---------+
| id | first_column | second_column | row_num |
+----+--------------+---------------+---------+ 
|  1 |            1 |             1 |       1 |
|  3 |            2 |            10 |       1 |
|  4 |            3 |             4 |       1 |
+----+--------------+---------------+---------+

And from PHP I get this:

+----+--------------+---------------+---------+
| id | first_column | second_column | row_num |
+----+--------------+---------------+---------+
|  1 |            1 |             1 |       1 |
|  2 |            1 |             4 |       1 |
|  3 |            2 |            10 |       1 |
|  4 |            3 |             4 |       1 |
|  5 |            1 |             4 |       1 |
|  6 |            2 |             5 |       1 |
|  7 |            1 |             6 |       1 |
+----+--------------+---------------+---------+

Am I doing something wrong?

Many thanks!

EDIT: Here is my pared-down PHP code, since the problem in reality is tied in to some more complex stuff.

class sql_helper extends other
{
    public function query_handler($sql, $error_message) 
    {
        $this->connect(); // Not shown, but works without issue

        $result = mysql_query($sql) or die($error_message.''.mysql_error());

        return $result;
    }

    public static function sql_result_to_assoc($sql_result) 
    {
        $result_array = array();

        while($row = mysql_fetch_assoc($sql_result))
            $result_array[] = $row;

        return $result_array;
    }

    public function sql_to_assoc($sql, $error_message)
    {
        $result = $this->query_handler($sql, $error_message);

        return $this->sql_result_to_assoc($result);
    }
}

$sql = "
    select
    id, first_column, second_column, row_num
    from 
    (
        select *,
        @num := if(@first_column = first_column, 2, 1) as row_num,
        @first_column := first_column as c
        from mytab order by first_column,id
    ) as t
    having row_num<=1";

$sql_helper   = new sql_helper();
$result_array = $sql_helper->sql_to_assoc($sql, '');

"Am I doing something wrong?" Yes. Please post your PHP code. — Mark Byers, May 09 '12 at 20:13
@MarcusAdams: Thank you. This particular query actually only needs to store the session variable for the duration of the query, so there should be no worries about browser requests. — Nathan Lippi, May 09 '12 at 20:35

jerahian · Answer 1 · 2013-07-02T08:37:44.067

2

You need to initialize the session variables:

SET @num:=0;
SET @first_column:=0;

This must be done in the same MySQL session and before your query.

edited Jul 02 '13 at 08:37

answered Jul 02 '13 at 08:31

jerahian

23
5

score 1 · Answer 2 · answered Jul 02 '13 at 10:15

As Jerahian answered, they need to be in the same session. I have typically done inline declarations but you have to wrap as if it was a select and assign an "alias" such as

select
    id, first_column, second_column, row_num
from 
   ( select *,
           @num := if(@first_column = first_column, 2, 1) as row_num,
           @first_column := first_column as c
        from 
           mytab,
           ( select @first_column := 0 ) as Sqlvars
      order by 
           first_column,
           id
   ) as t
having 
   row_num <= 1;

Notice the very subtle initialization of @first_column := 0 as "sqlvars" alias... Then it can be used as you are intending. And if you have multiple variables, just comma separate as you would other columns... such as

   ( select @first_column := 0, 
            @someOtherField := '2013-06-02',
            @someString := 'testing' ) as Sqlvars

MySQL query with session (@variables) working in MySQL Workbench, but not in PHP

2 Answers2