如何从SELECT查询中获得正确的输出？

Question

I have a query like so:

SELECT * FROM `purchases` p 
JOIN `purchase_types` pt ON p.purchase_type = pt.node

When I run it in PHPmyAdmin it returns the proper result set like so:

node | purchase_type | amount_spent | node | name
--------------------------------------------------
2    | 5             | 8.5000       | 5    | Lunch
3    | 5             | 1.5000       | 5    | Lunch
4    | 6             | 4.6600       | 6    | Dinner

This is my PHP code:

$sql = "SELECT * FROM `purchases` p 
        JOIN `purchase_types` pt ON p.purchase_type = pt.node";

$query = mysql_query($sql);
$result = mysql_fetch_assoc($query);

$purchases = array();
while($row = mysql_fetch_assoc($query)) {
    $purchases[] = array(
        'name' => $row['name'],
        'amount_spent' => $row['amount_spent']
    );
}

for eaching over $expenses returns the following output:

3    | 5             | 1.5000       | 5    | Lunch
4    | 6             | 4.6600       | 6    | Dinner

What happens to the first "Lunch"? How can I have the PHP output be the same as the direct MySQL query output?

Answer 1

You call mysql_fetch_accoc before while. Don't.

---

You should also realize that ext/mysql will be deprecated and upgrade your code to use properly parameterized queries with PDO or mysqli