Getting query result into array using while

Question

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']
);
}

Shankar Narayana Damodaran · Accepted Answer · 2014-01-19 03:51:18Z

1

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.

answered Jan 19, 2014 at 3:51

Shankar Narayana Damodaran

68.6k43 gold badges102 silver badges129 bronze badges

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

3 Comments

John Barner Over a year ago

That did it! Can you point me to someplace that explains exactly what you did?

Shankar Narayana Damodaran Over a year ago

Actually you were creating a new array $visit each and every time inside the while loop. That was the issue.

Shankar Narayana Damodaran Over a year ago

Best resource on arrays would be the PHP Manual. in2.php.net/manual/en/function.array.php

Collectives™ on Stack Overflow

Getting query result into array using while

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related