Direct Insert versus using MySQLi: query behaves differently

Question

I'm facing a rather odd issue. I use PHP 7.0 with a MySQL 5.5 DB on Azure. When testing queries on the database, I use Sequel Pro to directly perform queries and check the result. From PHP, I use the exact same query (but with MySQLi prepared statements), but I receive an error when executing this query from PHP.

In particular, I have issues with a conditional INSERT statement where I define a temporary table in the FROM clause. The query in question is:

INSERT INTO tryouts (t_id, user_id, tryout_count)
    SELECT entry.t_id, entry.user_id, 0
    FROM
        (SELECT ? AS t_id, ? AS user_id) AS entry,
        tournaments
    WHERE
        (tournaments.t_id = entry.t_id) AND (tournaments.tryout_limit IS NOT NULL);

Replacing the '?' with actual values in Sequel Pro executes the query as expected: a new row is inserted.

However, when executing this query from PHP using MySQLi, I get the following error message:

Unknown column 'entry.t_id' in 'field list'

This is the PHP code used, of which the error_log is triggered with the above message:

$query = "
    INSERT INTO tryouts (t_id, user_id, tryout_count)
        SELECT entry.t_id, entry.user_id, 0
        FROM
            (SELECT ? AS t_id, ? AS user_id) AS entry,
            tournaments
        WHERE
            (tournaments.t_id = entry.t_id) AND (tournaments.tryout_limit IS NOT NULL);";

$statement = $dbLink->stmt_init();

if (!$statement->prepare($query) ||
    !$statement->bind_param('ii', $tournamentID, $userID) ||
    !$statement->execute())
{
    error_log("EnterTournamentDetails: Something went wrong while letting the user enter the tournament: ".$dbLink->error);
    return NULL;
}

Anyone that can shine a light on the difference, or what that could mean? I face this same issue on other, similar insertion queries, but this one is one of the more simple examples I could find.

Thanks in advance!

Why are you selecting from that subquery `(SELECT ? AS t_id, ? AS user_id) AS entry` if you're selecting static values? — miken32, Mar 13 '17 at 22:17
@miken32 I use such sub-queries to kind of store them for use as variables in the WHERE clause. This kind of code is based on examples I could find across the web for conditional inserts. Instead of using a '?' every time and filling that in with mysqli, I define them once, and use those values across the whole query where needed. This query is rather simple for that, but in more complex ones, I find it to be useful to do it that way. — Jan Discart, Mar 13 '17 at 22:24
As a note `stmt_init` is not really intended to be exposed like that. `$dbLink->prepare(...)` will generate a statement for you in one step. — tadman, Mar 13 '17 at 22:28
That makes sense; I'm used to PDO with named parameters. Can you set something up at sqlfiddle.com for easier testing? — miken32, Mar 13 '17 at 22:30
@miken32 I can't seem to get sqlfiddle working properly for this problem... It reports a schema error, but doesn't display where it's located. But I will take a look at PDO for its named parameters though. This could already help a lot when dealing with the same parameter multiple times in the same query. I went with MySQLi since it seemed to be the easier one to get started with, but I might have to rethink that when fiddling around with PDO a bit. Thanks for the help though! — Jan Discart, Mar 13 '17 at 22:53
I find PDO much easier to work with. The other big advantage is no binding parameters. You just pass an array to `execute()`. No more counting `s` and `i`! — miken32, Mar 13 '17 at 22:54
@tadman Ah, I see it in the documentation now that prepare indeed returns a statement object. However, I think the current code structure as presented above makes it nicely uniform to read. :) Not really a solid reason not to use mysqli::prepare though... — Jan Discart, Mar 13 '17 at 23:09
Switching over to PDO will not solve anything, the error is raised by mysql. Actually, if you connected via CLI and issued a prepare statement, then you would get the same error message. The duplicate topic will nicely explain you why. Btw, your query in the question is overcomplicated, there is no need for subquery at all. — Shadow, Mar 13 '17 at 23:53
@Shadow Thanks for the link. I didn't consider PDO as a solution to the problem, rather an alternative way of doing things that I did not consider before and could ease my way of working. — Jan Discart, Mar 14 '17 at 08:14

Direct Insert versus using MySQLi: query behaves differently

0 Answers0