查询适用于mysql但不适用于PHP

Question

Following is my prepared statement which I am using to make insertion in a table. When I run the same query in mysql by only changing ? in actual values then the query runs successfully but it doesnot in PHP and gives following error:

Call to a member function execute() on a non-object

Kindly let me know what I did wrong:

$stmt = $con->prepare("
    INSERT INTO test_given (
        test_id, test_giver, test_completed, dt_created
    )
    SELECT * FROM (
         SELECT ?, ?, '0',NOW()
    ) AS tmp
    WHERE NOT EXISTS (
        SELECT test_id FROM test_given WHERE test_id  = ? AND test_giver  = ?
    ) limit 1
");

// s means only string input is allowed 
$stmt->bind_param("ssss", $qid, $username,$qid, $username);

Note: I echoed $qid, $username and they are getting right values.

Answer 1

You can't use ? for table names. In SQL, values look like "value", while table/column names look like `colname` (backtick, not single quote); they're not interchangeable.

You can use sprintf and manually escape the table name with mysqli_escape_string, ie.:

$stmt = $con->prepare(sprintf("
    INSERT INTO test_given (
        test_id, test_giver, test_completed, dt_created
    )
    SELECT * FROM (
         SELECT `%s`, `%s`, '0',NOW()
    ) AS tmp
    WHERE NOT EXISTS (
        SELECT test_id FROM test_given WHERE test_id  = ? AND test_giver  = ?
    ) limit 1
"), $qid, $username);

// s means only string input is allowed 
$stmt->bind_param("ss", $qid, $username);