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)

You're only executing fetch_array() once, so it will only fetch one row.... execute fetch_array() in a loop to fetch all rowa — Mark Baker
– Mark Baker, Commented Jun 23, 2014 at 23:35

Zerquix18 · Accepted Answer · 2014-06-23 23:36:18Z

1

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.

answered Jun 23, 2014 at 23:36

Zerquix18

7346 silver badges19 bronze badges

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

Comments

Sean Johnson · Accepted Answer · 2014-06-23 23:36:20Z

0

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
    }
}

answered Jun 23, 2014 at 23:36

Sean Johnson

5,6172 gold badges19 silver badges23 bronze badges

1 Comment

Sean Johnson Over a year ago

Yes, it will return an "entire array" but only representing a single row of the query. If you want to select all of the rows into a single array, use fetch_all instead, as @showdev mentioned. Edit: the comment this was a response to was deleted.

Collectives™ on Stack Overflow

PHP MYSQLI returns one row instead of many

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related