How to fetch data from database using prepare statement in php?

Question

I have a database in which I have user_id & associated_id.There can be multiple associated_id for a single user_id. Now I want to fetch all the associated_ids into a single array. I have tried this method but don't know how to get them in array.

$stmt = $this->conn->prepare("SELECT * FROM my_contacts WHERE user_id = ?");
$stmt->bind_param("s", $user_id);
if ($stmt->execute())
    {
        while ($stmt->fetch())
        {
            //what  to write here 

        }


        //echo var_dump($user);
        $stmt->close();

    }

Answer 1

Try this:

        $stmt = $mysqli->prepare("SELECT associated_id FROM my_contacts WHERE user_id = ?")) {
        $stmt->bind_param('s', $user_id);  // Bind "$user_id" to parameter.
        $stmt->execute();    // Execute the prepared query.
        $stmt->store_result();

        // get variables from result.
        $stmt->bind_result($associated_id);
        $stmt->fetch();

The results will be stored in the $associated_id array.

Answer 2

You can bind parameters like this and use fetchall method to get all the results in a array

$stmt = $this->conn->prepare("SELECT * FROM my_contacts WHERE user_id = :user_id");
$stmt->bind_param(":user_id", $user_id, PDO::PARAM_INT);
if ($stmt->execute())
    {
      $result = $stmt->fetchall(PDO::FETCH_ASSOC);
        //echo var_dump($user);
        $stmt->close();

    }

Answer 3

According to your code you used mysqli.

$stmt = $this->conn->prepare("SELECT * FROM my_contacts WHERE user_id = ?");
if($stmt->execute()){
  $result = $stmt->get_result();
  if($result->nom_rows > 0){
     while($row = $result->fetch_assoc()){
       var_dump($row)
     }
  }else{
    echo "Sorry NO data found";
  }
}else{
  echo "Some thing is wrong";
}

here you can't used $stmt->bind_result(); instead of use $stmt->get_result()

$stmt->bind_result(); is only used when you define field in select query

with * you need to used $stmt->get_result()

refer this link for more information

Example of how to use bind_result vs get_result