Save MySQL results in 2D array - php

Question

I'm trying to fetch the result of a query to my database. I have no problems doing so when the resultset is just 1 row, but when it's multiple rows I only get the first one.

Here is my db:

-----keys-------------------
|  id  |  key_nbr |  date  |
----------------------------
|  42  |  abc123  |  xxxx  |
|  49  |  789xyz  |  wxyz  |
----------------------------

My function:

function get_key_info($mysqli) {
   if (isset($_SESSION['user_id'], $_SESSION['username'], $_SESSION['login_string'])) {
       $user_id = $_SESSION['user_id'];
       if ($stmt = $mysqli->query("SELECT id, key_nbr, date FROM keys WHERE id=$user_id")){
           $row = $stmt->fetch_array(MYSQLI_ASSOC);
           return $row;
       }
   }
   return null;
}

Output when doing print_r($row); is only the first row:

Array ( [id] => 42 [key_nbr] => abc123 [date] => xxxx) How to make it print all rows?

$row = $stmt->fetch_all(MYSQLI_ASSOC); instead of $row = $stmt->fetch_array(MYSQLI_ASSOC); — Sean
– Sean, Commented Sep 12, 2014 at 16:21
Sidenote: keys is a MySQL reserved keyword. Wrap it in backticks ` or use another name for it. Having used error reporting which you're most probably not doing, would have signaled the error. Always use error reporting. — Funk Forty Niner
– Funk Forty Niner, Commented Sep 12, 2014 at 16:32

Hardik Thaker · Accepted Answer · 2014-09-12 16:19:20Z

2

You have to check for total number of rows before fetching the data, if it's not zero then execute the loop and fetch all records.

function get_key_info($mysqli) {
   if (isset($_SESSION['user_id'], $_SESSION['username'], $_SESSION['login_string'])) {
       $user_id = $_SESSION['user_id'];
       if ($stmt = $mysqli->query("SELECT id, key_nbr, date FROM keys WHERE id=$user_id")){
           if($stmt->num_rows != 0) {
               $row = array();
               while($r = $stmt->fetch_array(MYSQLI_ASSOC)) {
                   $row[] = $r;
               } 
               return $row;
           }
       }
   }
   return null;
}

answered Sep 12, 2014 at 16:19

Hardik Thaker

3,0881 gold badge29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Save MySQL results in 2D array - php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related