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?
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With