Pass SQL query into a PHP array

[Alonso_1]

I have a 2 column SQL table as follows:

Product Price
Drill 20
Hammer 15
Screwdriver 5
Screws 2
Nuts 3
Bolts 6

I eventually want to embed a PHP array into a Google charts script. But first, how do I pass my SQL query into an array? My code below doesn't seem to work.

Here's my code, I'm just placing the SQL results into a table for testing purposes:

<?php
$page_title = 'Exercise 1';

require_once('mysql_connect.php');
try{

      $sql =  "SELECT product, price FROM 'inventory'";

        echo "<table border ='0'>
        <tr>
            <th>Product</th>
            <th>Price</th>
        </tr>";

    $statement = $db->query($query);
    while($row = $statement->fetch(PDO::FETCH_ASSOC)){
        // create arrays and pass in values
        $product = $row['product'];
        $price = $row['price'];
        echo "<tr>";
        echo "<td>" .$product ."</td>";
        echo "<td>" .$price ."</td>";
        echo "</tr>";

    }  // End our while loop
        echo "</table>"; 
    }
catch(PDOException $e){
        $message = '<p>Error</p><p>' . $e->getMessage() . '</p>';
}
?>

Thanks

[eburlea]

Does it help?

Instead:

$statement = $db->query($query);

Write:

$statement = $db->query($sql);

[Alonso_1]

Afraid not. I just get back a blank table with the headings. Am I declaring and using my arrays in the correct way?

[eburlea]

This works for me:

$connection = mysql_connect ("localhost",$database_username,$database_password);
mysql_select_db ($database_name,$connection);
$result = mysql_query ("SELECT * FROM table",$connection);
while($row = mysql_fetch_array($result)){
    echo $row['column_name'];
}

[broj1]

Seems like eburlea was right in his first post since there is no $query variable in your script. Try looping over the query object directly:

foreach ($db->query($sql) as $row) {
    $product = $row['product'];
    $price = $row['price'];
    echo "<tr>";
    echo "<td>" .$product ."</td>";
    echo "<td>" .$price ."</td>";
    echo "</tr>";
}

I saw this here and here.