Select query's order not working in prepared statement

Question 1

I am created a prepared select query and it appears the query is not picking up the DESC or I have the bind_param structured wrong. I am trying to get the last id of the user_id's image to display. The user's image displays, but it is the first id image they have. I tried doing ASC and it was the same thing.

Am I doing this right?

$sql = "
  SELECT *
  FROM profile_img
  WHERE user_id = ?
  ORDER BY ? DESC LIMIT 1
  ";
  if ($stmt = $con->prepare($sql)) {
        $stmt->bind_param("ss", $user_id, `id`);
        $stmt->execute();
        if (!$stmt->errno) {
            // Handle error here
        }
        $stmt->bind_result($id, $user_id, $profilePic);

        $pics = array();
        while ($stmt->fetch()) {
            $pics[] = $profilePic;
        }

        echo '<img id="home-profile-pic" src=" '.$profilePic.'">';
  }

Question 2

I don't think you can :

Use placeholders in an order by clause
Bind column names : you can only bind values -- or variables, and have their value injected in the prepared statement.

You can use number instead of field name in the 'order by' clause

Question 3

Yes, bindParam binds a parameter to a variable name (reference), not a value, as the manual says.

However, there's a simpler syntax for your situation. PDOStatement::execute can take an array of values.

public function insert($table, $cols, $values){

    $placeholder = array();
    for ($i = 0; i < count($values); $i++)
      $placeholder[] = '?';

    $sql = 'INSERT INTO '. $table . ' (`' . implode("`, `", $cols) . '`) ';
    $sql.= 'VALUES (' . implode(", ", $placeholder) . ')';

    $stmt = $this->dbh->prepare($sql);
    $stmt->execute($values);

}

Question 4

Try putting all the parameters into one bindParam call:

$stmt->bind_Param('sds', $username, $picture, $comment);

Question 5

You need to use:

preparedStatement.executeQuery();

instead of

preparedStatement.executeQuery(login);

when you pass in a string to executeQuery() that query is executed literally and thus the ? is send to the database which then creates the error. By passing query string you are not execution the "cached" prepared statement for which you passed the values.

Question 6

Looks like the issues is with the MySQL C++ connector.

LAST_INSERT_ID() returns 0 when the ID field is null, explicit insert.
LAST_INSERT_ID() returns 0 when the ID field is not specified, implicit insert.

I tried insert the BLOB (JPEG image) from the command line (monitor), and it works:

mysql> describe picture_image_data;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| ID_Image_Data | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Image_Data    | mediumblob       | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
2 rows in set (0.03 sec)
mysql> PREPARE blob_stmt
    ->     FROM 'INSERT INTO picture_image_data (ID_Image_Data, Image_Data) VALUES (?, LOAD_FILE(?))';
Query OK, 0 rows affected (0.02 sec)
Statement prepared

mysql> SET @id = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @c = 'KY_hot_brown_picture.jpg';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE blob_stmt USING @id, @c;
Query OK, 1 row affected (0.15 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+

1 row in set (0.00 sec)

For now, the workaround is to use SQL statements to insert the BLOB with a prepared statement rather than the MySQL C++ Connector API.

Select query's order not working in prepared statement

Answers

php,mysql,sql,select,prepared-statement