PHP MYSQLI returns one row instead of many

Question

I'm getting 1 results from the MySQL database when many results are expected.

Look below and you'll see the MySQLI object has NUM_ROWS = 42, yet I only get 1 result when I print_r the fetch_array(MYSQLI_ASSOC)

The debug response:

    SELECT * from edi_packets_to_send WHERE `send` = 't' AND `network_id`  = '8012'

    mysqli_result Object
    (
        [current_field] => 0
        [field_count] => 13
        [lengths] =>
        [num_rows] => 42
        [type] => 0
    )
    Array
    (
        [id] => 413
        [packet] => 02 07 00 01 ff 14 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff
        [priority] => 4
        [send] => t
        [seq] =>
        [network_id] => 8012
        [times] => 2014-06-23 22:52:28
        [termostat_location] => 0007
        [packet_type] => 14
        [no_of_attemps] => 4
        [network_network_id] => 61
        [cms_room_id] => 157
        [action_code] => 4
    )   

My Code:

<pre>
<?php

define('DB1_HOSTNAME','localhost');
define('DB1_USERNAME','xxxxxxx');
define('DB1_PASSWORD','xxxxxxx');
define('DB1_DATABASE','xxxxxxx');
define('DB1_PORT','3306');

/* DB connection */
$db = mysqli_connect(DB1_HOSTNAME, DB1_USERNAME, DB1_PASSWORD, DB1_DATABASE);
if (mysqli_connect_errno($db)) {throw new exception("Failed to connect to MySQL: " . mysqli_connect_error());}


$sql = "SELECT * from edi_packets_to_send WHERE `send` = 't' AND `network_id`  = '8012' ";
echo "<br>".($sql)."<br><br>";

if(!$results = $db->query($sql)){throw new Exception("SQL Failed ".__file__." on line ".__line__.":\n".$sql."\n".mysqli_error($db));}
print_r($results);

if ($results->num_rows > 0){

    $array = $results->fetch_array(MYSQLI_ASSOC);
    print_r($array);
}

Database table:

    DROP TABLE IF EXISTS `edi_packets_to_send`;
    CREATE TABLE `edi_packets_to_send` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `packet` varchar(249) DEFAULT NULL,
    `priority` int(11) DEFAULT NULL,
    `send` varchar(15) DEFAULT NULL,
    `seq` varchar(6) DEFAULT NULL,
    `network_id` int(11) DEFAULT NULL,
    `times` datetime DEFAULT NULL,
    `termostat_location` varchar(12) DEFAULT NULL,
    `packet_type` varchar(6) DEFAULT NULL,
    `no_of_attemps` int(11) DEFAULT NULL,
    `network_network_id` int(11) DEFAULT NULL,
    `cms_room_id` int(11) DEFAULT NULL,
    `action_code` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `id` (`id`),
    KEY `priority` (`priority`),
    KEY `cms_room_id` (`cms_room_id`),
    KEY `network_id` (`network_id`),
    KEY `network_network_id` (`network_network_id`),
    KEY `send` (`send`),
    KEY `action_code` (`action_code`)
    ) ENGINE=InnoDB AUTO_INCREMENT=413 DEFAULT CHARSET=utf8;



    INSERT INTO `edi_packets_to_send` VALUES ('413', '02 07 00 01 ff 14 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '4', 't', null, '8012', '2014-06-23 22:52:28', '0007', '14', '4', '61', '157', '4');
    INSERT INTO `edi_packets_to_send` VALUES ('414', '02 07 00 01 ff 1e ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '5', 't', null, '8012', '2014-06-23 22:10:25', '0007', '1e', '1', '61', '157', '5');
    INSERT INTO `edi_packets_to_send` VALUES ('415', '02 07 00 01 ff 1f ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '111', 't', null, '8012', '2014-06-23 22:05:30', '0007', '1f', '1', '61', '157', '111');
    INSERT INTO `edi_packets_to_send` VALUES ('416', '02 07 00 01 ff 32 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '18', 't', null, '8012', '2014-06-23 22:09:19', '0007', '32', '1', '61', '157', '18');
    INSERT INTO `edi_packets_to_send` VALUES ('417', '02 07 00 01 ff 0a ff ff 00 ff ff ff ff ff 05 14 05 1e 3c 1e 00 01 00 3e 0a 37 ff ff', '20', 't', null, '8012', '2014-06-23 22:07:57', '0007', '0a', '1', '61', '157', '20');
    INSERT INTO `edi_packets_to_send` VALUES ('418', '02 07 00 01 ff 0b 0a 52 4c 40 4d 01 01 ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '21', 't', null, '8012', '2014-06-23 22:07:24', '0007', '0b', '1', '61', '157', '21');
    INSERT INTO `edi_packets_to_send` VALUES ('419', '02 07 00 01 ff 32 ff 00 00 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '90', 't', null, '8012', '2014-06-23 22:06:11', '0007', '32', '1', '61', '157', '90');
    INSERT INTO `edi_packets_to_send` VALUES ('420', '02 08 00 01 ff 14 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '4', 't', null, '8012', '2014-06-23 22:11:14', '0008', '14', '1', '61', '158', '4');
    INSERT INTO `edi_packets_to_send` VALUES ('421', '02 08 00 01 ff 1e ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '5', 't', null, '8012', '2014-06-23 22:10:08', '0008', '1e', '1', '61', '158', '5');
    INSERT INTO `edi_packets_to_send` VALUES ('422', '02 08 00 01 ff 1f ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '111', 't', null, '8012', '2014-06-23 22:05:13', '0008', '1f', '1', '61', '158', '111');
    INSERT INTO `edi_packets_to_send` VALUES ('423', '02 08 00 01 ff 32 ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff ff', '18', 't', null, '8012', '2014-06-23 22:09:03', '0008', '32', '1', '61', '158', '18');

Any idea why this particular query isn't working correctly for me ?

PS: I'm running it on localhost Windows IIS 7.5 PHP Version 5.3.28 (dev)

Answer 1

You need to do a loop, fetch_array is designed to grab one row, return whatever format you requested, and increment the row index so that the next call will get the next row.

Try this instead:

if ($results->num_rows > 0){
    for($rowid=0; $rowid < $results->num_rows; $rowid++) {
        $array=$results->fetch_array(MYSQLI_ASSOC);
        print_r($array); //print the current row array
    }
}

Answer 2

You must use while() to load all the results. If you use if, you'll get just the first result.

if ($results->num_rows > 0){
$array = array();
while($r = $results->fetch_array(MYSQLI_ASSOC))
    $array[] = $r;
}
print_r($array); // an array with all the results.