CODEWITHSUNDEEP
phpmysqlarrayspdowhile-loop
John Barner
John Barner

Reputation: 13

Getting query result into array using while

I'm very new to PHP. In fact, I just finished reading an introduction to it, so excuse me if I explain things poorly as I'm very inexperienced. I'm learning how to connect to databases using PDO, and I want to dump the results of a query into an array so that a graph library I want to use can chart the number of visits to a particular place in the last day. Everything else works fine, and the query returns the expected results when ran using phpMyAdmin. I can't use the results directly from the query, as the library doesn't understand it for some reason.

Because of this, I had the idea to use while to iterate through the results and add the information to an array. But for some reason, only the latest result is being added. I know I'm missing something really simple here, but I'm still too ignorant to know what it is I need to do.

# Establish the connection
$conn = new PDO("mysql:host=$host;dbname=$database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$data = $conn->query('SELECT HOUR( TIMESTAMP ) AS HOUR , COUNT( detected_key ) AS num_rows
                            FROM Visitors
                            GROUP BY HOUR( TIMESTAMP ) 
                            LIMIT 0 , 24');

while($row = $data->fetch(PDO::FETCH_ASSOC)) {
$visit = array (
$row['HOUR'] => $row['num_rows']
);
}

Upvotes: 1

Views: 75

Answers (1)

Shankar Narayana Damodaran
Shankar Narayana Damodaran

Reputation: 68546

Rewrite your while like this.

while($row = $data->fetch(PDO::FETCH_ASSOC)) {
$visit[$row['HOUR']]= $row['num_rows'];
}

print_r($visit); // "prints your array"

You are getting the last value because you are overwritting each and everytime.

Upvotes: 1

Related Questions