mysqlmysql-dependent-subquery

How to code a "multi-command" mysql query in php


Please note that this question is not about the mysql query per se, but HOW TO RUN IT from php. For lack of a better term, I called it a multi-command query. I have a query which runs perfectly in mysql commandline client, and in phpmyadmin, as follows:

SET @counter = 0;
SET @category = '';

SELECT
    *
FROM
(
    SELECT
        @counter := IF(press.newscat = @category, @counter+1, 0) AS counter,
        @category := press.newscat,
        press.*
    FROM
    (
        SELECT
                *
        FROM press
        ORDER BY newscat, added DESC
    ) press
) press
HAVING counter < 2 limit 50

I would like to execute the same, in php script but for the life of me, cannot. I tried using gemini transaction, but that gives one result....the commandline and phpmyadmin give me more results...as intended.

I have experience and am fairy well versed in the usual mysql_fetch_object mysql_fetch_array bits...but how to I run this query?

Something like

$phpquery = mysql_query("SET @counter = 0;
SET @category = '';

SELECT
    *
FROM
(
    SELECT
        @counter := IF(press.newscat = @category, @counter+1, 0) AS counter,
        @category := press.newscat,
        press.*
    FROM
    (
        SELECT
        *
        FROM press
        ORDER BY newscat, added DESC
    ) press
) press
HAVING counter < 2 limit 50") 

...doesnt work.

my table structure is simple:

table name: press
newscat int(11) not null, headline varchar (255)

Solution

  • You should split multiple queries, like

    mysql_query('SET @counter = 0');
    mysql_query('SET @category = ""');
    $phpquery = mysql_query(" ... /* your length query */ ");
    

    The above will work if the connection is to the same database server.

    OR you can wrap three statements into a stored procedure / function