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();

    }

Have you tried to google? http://php.net/manual/de/pdostatement.fetch.php — Jens, Jun 19 '15 at 05:23

score 1 · Answer 1 · edited Jun 23 '15 at 20:26

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.

edited Jun 23 '15 at 20:26

Nisse Engström

4,738
23
27
42

answered Jun 19 '15 at 05:26

Jash Parekh

1,097
2
10
15

how do i get the array of elements – TechChain Jun 19 '15 at 05:35
`$assosiated_id` is your array. – Jash Parekh Jun 20 '15 at 15:56

score 0 · Answer 2 · answered Jun 19 '15 at 05:27

0

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();

    }

answered Jun 19 '15 at 05:27

Mehar

2,158
3
23
46

1

Please suggest something relevant to my code.I don't understand PDO & all – TechChain Jun 19 '15 at 05:41

score 0 · Answer 3 · edited May 23 '17 at 10:28

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

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

3 Answers3