select mysql missing columns in php

Question

i need to get the latest order (from our custon admin panel). here's my query:

select * 
from order 
  left join customer 
    on (customer.id = order.fk_cid) 
where date = curdate() 
order by time desc 
limit 1;

this output everything from orders and customers which i need except 1 therefore that is why i use the *

here's my table structure:

order table: 
id, fk_cid, date, time

customer table: 
id, name, lastname, street, city, zip, country, phone, email, lastlogin

now, in my php i have:

$result = mysql_query("
    select * 
    from `order` 
    left join customer 
    on (customer.id = order.fk_cid) 
    where date = curdate() 
    order by time desc 
    limit 1");
$row = mysql_fetch_assoc($result, MYSQL_ASSOC);

at this point my order is not correct, why?

Answer 1

Your customers.id is overwriting the order.id because you are using the same column name.

select * 
from `order` 
left join customer on (customer.id = order.fk_cid) 
where date = curdate() order by time desc limit 1;
+------+--------+------------+----------+------+-------+------
| id   | fk_cid | date       | time     | id   | name  | ....
+------+--------+------------+----------+------+-------+------
|    1 |      2 | 2011-11-30 | 07:01:23 |    2 | asasd | ....
+------+--------+------------+----------+------+-------+------
1 row in set (0.03 sec)

As you can see in this example you have two id, so PHP when retrieve the data using mysql_fetch_assoc it overwrites the second id because it's the same key in the array. To fix this, you will have to specify the columns in your query:

select `order`.id AS order_id, customer.id AS customer_id, customer.name /* etc... */

This will output:

Also, I recommend to use different name for your tables and fields. order, date, time since they are reserved word (in case you forget for use the ` ).

Array
(
    [order_id] => 1
    [customer_id] => 2
    // etc...
)

Also here's a topic you should read: Why is SELECT * considered harmful?