mysqli-- prepare statement failing with error "no table used"

Question

new to writing in php, I'm using a $mysqli->prepare() function to get a statement ready for execution, but it keeps failing and I don't know why. THe error inside the $mysqli->error is "no table used," and I can't seem to find much documentation for that either... if anyone could help out, that would be hugely appreciated. Code below:

$datasqli=new mysqli(HOST, USERNAME, PASSWORD, DATABASE); // defined elsewhere
if ($datasqli->connect_errno) {
    printf("Connect failed: %s\n", $datasqli->connect_error);
    exit();
}

if ($usertest = $datasqli->prepare("INSERT INTO eeg (Identifier) 
    SELECT * FROM (SELECT ? ) AS tmp WHERE NOT EXISTS (
    SELECT Identifier FROM eeg WHERE Identifier = ?
    ) LIMIT 1")) {
    // this stuff never gets executed...
} else {
    echo $datasqli->error; // "no table used"
}

I've tried executing that code block directly inside the mysql environment and it works fine.

Why do you think it's prepared statement problem at all? Dud you try your query in console? — Your Common Sense, Oct 08 '13 at 21:17
Sorry--yes I did try it in the console, and it executed fine, which is why I am a bif confused... — bibliolytic, Oct 09 '13 at 12:38

geomagas · Accepted Answer · 2013-10-09T15:20:47.750

Apparently,

SELECT * FROM (SELECT ? )

...is not recognized as a valid MySQL syntax. A table name is missing.

EDIT, Concerning your comments:

First of all, please note that executing this statement in a console by substituting ? with a constant does not emulate your situation, so I would consider the result invalid for comparison.

But then again, executing it without substituting ? would, naturally, give an error.

That's because executing just the select is irrelevant to your situation. In your php code, it's not the execution that fails but rather the preparation. So the proper way to emulate this using a console, would be the PREPARE statement.

So doing a

PREPARE myStmt 
  FROM 'SELECT * FROM (SELECT ? ) AS tmp WHERE NOT EXISTS (
    SELECT Identifier FROM eeg WHERE Identifier = ?
    ) LIMIT 1'

would reproduce your issue more accurately.

Now, it seems that PREPARE has a difficulty understanding parametrized nested queries that appear in the FROM clause. Take a look at these examples:

PREPARE myStmt FROM "select * from (select ? from eeg) tmp";

(doesn't work)

PREPARE myStmt FROM "select *,? from (select * from eeg) tmp";

(works)

PREPARE myStmt FROM "select *,? from (select 'asdf') tmp";

(works)

PREPARE myStmt FROM "select * from eeg where Identifier in (select ?)";

(works)

Curious behaviour, but I can only guess that when a nested SELECT in the FROM clause has parameters, MySQL is missing clues in order to prepare the statement.

As for my suggestion, if I understand what you are trying to do, you don't need a parameter in the nested select. You could move it outside and hardcode a constant in the nested select, for the sake of FROM. The following code

if ($usertest = $datasqli->prepare("INSERT INTO eeg (Identifier) 
    SELECT ? from (select 1) tmp WHERE ? NOT IN
      (SELECT Identifier FROM eeg WHERE Identifier = ?)")) {

...should do the trick.

That's curious--if I try the query in console, substituting the ? for a string, it goes through without complaint. Can you suggest another way of phrasing this query? — bibliolytic, Oct 09 '13 at 13:43
Well, that would depend on the possible values to substitute `?`. What are you using in your tests? — geomagas, Oct 09 '13 at 13:52
A string that corresponds to an "Identifier" value--in the test, 'asdf' but that's less relevant. It's a nice little bif of SQL I found on here to insert values into a table only if certain non-primary keys didn't exist, though I confess,`SELECT * FROM (SELECT ?) AS tmp` I don't entirely understand — bibliolytic, Oct 09 '13 at 13:54
Ooops, I missed the part about what I'd suggest. Please see _updated_ edit. — geomagas, Oct 09 '13 at 15:21
Thank you! And even more so for the explanation than the fix — bibliolytic, Oct 09 '13 at 15:22
You cannot use placeholders for table and column names, as shown in the duplicate. MySQL recognizes nested prepared queries just fine if you set it up correctly. Your `SELECT ?` will fail as you're trying to use a placeholder for a column name. Same for `WHERE ?` In other words, your code will not *"do the trick"*. — Jay Blanchard, Oct 03 '17 at 20:22

mysqli-- prepare statement failing with error "no table used"

1 Answers1

Linked