MySQL查询与会话（@variables）在MySQL Workbench中工作，但不在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, '');

Answer 1

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.