Mysqli Prepare Statement - Returning False, but Why?

Question 1

I have a function that generates a prepared INSERT statement based on an associative array of column names and values to be inserted into that column and a table name (a simple string):

function insert ($param, $table) {
        $sqlString = "INSERT INTO $table (".implode(', ',array_keys($param)).') VALUES ('.str_repeat('?, ', (count($param) - 1)).'?)';
        if ($statement = $this->conn->prepare($sqlString)):
            $parameters = array_merge(array($this->bindParams($param), $param));
            call_user_func_array(array($statement, 'bind_param', $parameters));
            if (!$statement->execute()):
                die('Error! '.$statement->error());
            endif;
            $statement->close();
            return true;
        else:
            die("Could Not Run Statement");
        endif;
    }

My problem is that $this->conn->prepare (it's part of a class, conn is a NEW mysqli object, which works with no issues) returns false, but does not give me a reason why!

Here is a sample $sqlString that gets built for the prepare statement:

INSERT INTO students (PhoneNumber, FirstName, MiddleInit, LastName, Email, Password, SignupType, Active, SignupDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

Can anyone see a problem with this parameterized statement? Any reason the prepare function would return false?

Question 2

I'm copying the solution into this answer so this can be given an upvote, otherwise the question will appear in the "unanswered questions" forever. I'm marking this answer CW so I won't get any points.

@Andrew E. says:

I just turned on mysqli_report(MYSQLI_REPORT_ALL) to get a better understanding of what was going on - turns out that one of my field names was incorrect - you'd think that prepare() would throw an exception, but it fails silently.

Question 3

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.

Question 4

Before second usage mysqli::prepare() you must either free mysqli result or close current mysqli statement:

...
//do sth with the data

$mysqli->free(); // or $stmt->close();

$query = "SELECT * FROM answers WHERE question_id = ?";
$stmt  = $mysqli->prepare($query);
...

Question 5

you want the following:

$start = 1; $postsPerPage = 1;
$sql = "SELECT id, title, author, LEFT(description, 40) AS excerpt, 
               image_small, image_med, date 
        FROM posts 
        ORDER BY id DESC 
        LIMIT ?, ?";

$stmt = $connect->prepare($sql) or die ('error');
$stmt->bind_param('ii', $start, $postsPerPage);
$stmt->execute();
$stmt->bind_result($id, $title, $author, $excerpt, $image_small, $image_med, $date);

while($stmt->fetch()) {
  printf('<h1>%s</h1><p>%s <small> by %s on %s</small></p>',
    htmlspecialchars($title),
    htmlspecialchars($excerpt),
    htmlspecialchars($author),
    htmlspecialchars($date)
  );
}

this binds both question marks to integer (i) values of $start and $postsPerPage. do NOT use variables directly in prepared statements, because that would defeat the whole purpose of prepared statements (apart from eliminating parsing time)

Question 6

If you need to perform a selection of all of the columns:

SELECT * FROM `table`

You would use PHP's get_result() rather than bind_result().

bind_result() is better when you're specifying each column that you're retrieving where get_result() will allow you to work with a more generic return of data from your tables.

Mysqli Prepare Statement - Returning False, but Why?

Answers

php,mysqli,prepared-statement