mysqli— prepare statement failing with error “no t

问题:

This question already has an answer here:

• Can I parameterize the table name in a prepared statement? 2 answers

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.

回答1:

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.