在php中选择mysql缺失列

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 *

这输出了我需要的订单和客户的一切，除了1因此这就是为什么我使用*

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:

现在，在我的PHP我有：

$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?

此时我的订单不正确，为什么？

1 个解决方案

#1

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

您的customers.id正在覆盖order.id，因为您使用的是相同的列名。

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:

正如您在本示例中所看到的，您有两个id，因此PHP在使用mysql_fetch_assoc检索数据时会覆盖第二个id，因为它与数组中的密钥相同。要解决此问题，您必须在查询中指定列：

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?

这里还有一个你应该阅读的主题：为什么SELECT *被认为是有害的？

#1