Why does not my MySQL query work in PHP, I get error?

Question

I just had some help here on Stackoverflow and got the correct query. However he didn't know any PHP so now I need help with that as well.

Here's the query and the PHP:

if($do_resync) {
    $sql = "
        with MY_VIEW as
        (
        SELECT
            room_user.*,
            user.username AS user_name,
            user.group_id AS group_id, USER.USER_ID
        FROM {$x7->dbprefix}room_users room_user
        INNER JOIN {$x7->dbprefix}users user ON
            user.id = room_user.user_id
        WHERE
            room_id IN ({$rooms})
        )
        SELECT COUNT(id) AS block FROM
            blocks AS B, MY_VIEW AS M
        WHERE
            B.user_id = M.user_id
        AND B.blocked_id = B.user_id
    ";
    $st = $db->prepare($sql);
    $st->execute(); (THIS IS LINE 230 that the error points at)
    $users = $st->fetchAll();
    $output['users'] = $users;
}

echo json_encode($output);

And here's the error I get:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MY_VIEW as ( SELECT room_user.*, user.username AS user_name, u' at line 6' in pages/sync.php:230 Stack trace: #0 pages/sync.php(230): PDOStatement->execute() #1 index.php(50): require('/home/vol5_3/by...') #2 {main} thrown in pages/sync.php on line 230

EDIT:

SELECT COUNT(id) AS block FROM
    blocks AS B, 
(
    SELECT
        room_user.*,
        user.username AS user_name,
        user.group_id AS group_id, USER.USER_ID
    FROM {$x7->dbprefix}room_users room_user
    INNER JOIN {$x7->dbprefix}users user ON
        user.id = room_user.user_id
    WHERE
        room_id IN ({$rooms})
) AS M
WHERE
    B.user_id = M.user_id
AND B.blocked_id = B.user_id

Not *exactly* a duplicate question, but it would appear that the information you're looking for is in the answer here: http://stackoverflow.com/a/325243/328193 — David, Nov 13 '13 at 18:47
I don't know SQL All that well, but is `with MY_VIEW as(...) SELECT ...` valid syntax? What is that supposed to do? — gen_Eric, Nov 13 '13 at 18:47
[**mysql with clause**](http://stackoverflow.com/questions/324935/mysql-with-clause) — M Khalid Junaid, Nov 13 '13 at 18:48
Okay so it doesen't support it. How can I achieve what I want in a query that works with PHP? — user2965339, Nov 13 '13 at 18:48
As the message says - you have a syntax error. `echo` the `$sql` variable just before you `prepare` it and post the result. — , Nov 13 '13 at 18:48
@RocketHazmat It's supposed to do this: http://stackoverflow.com/questions/19961368/how-do-i-use-the-results-of-the-first-query-to-power-a-subsequent-query-in-sql?noredirect=1 — user2965339, Nov 13 '13 at 18:50
@MikeW Here's the output of the echo: SELECT COUNT(id) AS block FROM blocks AS B, MY_VIEW AS M WHERE B.user_id = M.user_id AND B.blocked_id = B.user_id with MY_VIEW as ( SELECT room_user.*, user.username AS user_name, user.group_id AS group_id, USER.USER_ID FROM room_users room_user INNER JOIN users user ON user.id = room_user.user_id WHERE room_id IN (1,0) ) — user2965339, Nov 13 '13 at 18:51
The problem is your use of the `WITH` clause which is not supported by MySQL. — , Nov 13 '13 at 18:54

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

Mysql does not support WITH clause how ever you can do it like below, join you data sets from your queries

SELECT * FROM 
        (
        SELECT
            room_user.*,
            user.username AS user_name,
            user.group_id AS group_id, USER.USER_ID
        FROM {$x7->dbprefix}room_users room_user
        INNER JOIN {$x7->dbprefix}users user ON
            user.id = room_user.user_id
        WHERE
            room_id IN ({$rooms})
        ) AS M
JOIN (SELECT * FROM blocks ) AS B
ON (B.user_id = M.user_id)
WHERE B.blocked_id = B.user_id

EDIT

SELECT * FROM 
            (
            SELECT
                room_user.*,
                user.username AS user_name,
                user.group_id AS group_id, USER.USER_ID
            FROM {$x7->dbprefix}room_users room_user
            INNER JOIN {$x7->dbprefix}users user ON
                user.id = room_user.user_id
            WHERE
                room_id IN ({$rooms})
            ) AS M
    JOIN blocks  B
    ON (B.user_id = M.user_id)
    WHERE B.blocked_id = B.user_id

or move your where condition in the on part

    JOIN blocks  B
    ON (B.user_id = M.user_id AND B.blocked_id = B.user_id)

edited May 23 '17 at 12:05

Community

1
1

answered Nov 13 '13 at 18:55

M Khalid Junaid

62,293
9
87
115

Thanks! I will try this, hang om ;) – user2965339 Nov 13 '13 at 18:55
Right now my host is down... I'll let you guys know how this goes! – user2965339 Nov 13 '13 at 19:23
I tested it now, and I get SQL errors... This is what I get (blocked_id exists I might add): Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'B.blocked_id' in 'where clause'' in pages/sync.php:228 Stack trace: #0 pages/sync.php(228): PDOStatement->execute() #1 index.php(50): require('/home/vol5_3/by...') #2 {main} thrown in pages/sync.php on line 228 – user2965339 Nov 13 '13 at 20:08
@user2965339 Make sure your table `blocks` have column `blocked_id` – M Khalid Junaid Nov 13 '13 at 20:47
it already has! That's the wierd part... – user2965339 Nov 13 '13 at 20:53
@user2965339 see my updated answer hope it makes sense – M Khalid Junaid Nov 13 '13 at 20:56
check my updated answer, that's what I want to do, but can't get it to work. – user2965339 Nov 13 '13 at 22:06

score 0 · Answer 2 · edited Nov 13 '13 at 20:17

I could think of two ways to solve your problem:

1 Create a view:

    $sql="CREATE VIEW MY_VIEW as
    SELECT
        room_user.*,
        user.username AS user_name,
        user.group_id AS group_id, USER.USER_ID
    FROM {$x7->dbprefix}room_users room_user
    INNER JOIN {$x7->dbprefix}users user ON
        user.id = room_user.user_id
    WHERE
        room_id IN ({$rooms})";

and then do your SELECT

2 create a temporary table: (http://dev.mysql.com/doc/refman/5.1/en/create-table.html)

    $sql1 = "CREATE TEMPORARY TABLE tmp_room_users_x0001 (
     -- the list of fields here ...
    )";


    // populate the table ...
    $sql2 = "INSERT INTO tmp_room_users_x0001 (SELECT
        room_user.*,
        user.username AS user_name,
        user.group_id AS group_id, USER.USER_ID
    FROM {$x7->dbprefix}room_users room_user
    INNER JOIN {$x7->dbprefix}users user ON
        user.id = room_user.user_id
    WHERE
        room_id IN ({$rooms})";

Why does not my MySQL query work in PHP, I get error?

2 Answers2