CODEWITHSUNDEEP
phpmysqlsqlselectprepared-statement
Paul
Paul

Reputation: 3368

Select query's order not working in prepared statement

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.'">';
  }

Upvotes: 2

Views: 642

Answers (2)

Amrinder
Amrinder

Reputation: 79

Why you have put ? after "order by" statement?

Your order by should reference to either id of your "profile_img" table or any timestamp field in that table...

e.g. $sql = " SELECT * FROM profile_img WHERE user_id = ? ORDER BY id DESC LIMIT 1 ";

here replace id (i am assuming this name) with the primary key field name of profile_image table

or

    $sql = "
 SELECT *
 FROM profile_img
 WHERE user_id = ?
 ORDER BY created_on DESC LIMIT 1
";

here created_on (which i have also assumed) can be replaced by any timestamp field if you any in profile_img table

Upvotes: 0

Jagrati
Jagrati

Reputation: 12222

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

Upvotes: 4

Related Questions