Join tables problem

[dweed-Ly]

Could you tell me what wrong with my code to join 2 table.

$query ="select customer.first_name, customer.last_name, order.description, order.quantity, order.purchase_date from customer, order where customer.customer_id=order.customer_id";
$result=mysql_query($query, $connect);

[Kid Charming]

Change your mysql_query() line to:

 $result=mysql_query($query, $connect) or die(mysql_error()); 

That will kill the script and give you the db server's error message. On first glance, it looks ok, syntax-wise.

[dweed-Ly]

Thanks for your answer,

I still have problem to joint my tables. The error message is :

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order where customer.customer_id=order.customer_id' at line 1

[Kid Charming]

ORDER is a reserved word in MySQL. You can still use it by enclosing it with backticks (`), but you should rename it instead.

[BlueHippo]

Hi dweed-Ly,

u didn't declare customer_id in the Select....

$query ="select customer.customer_id, customer.first_name, customer.last_name, order.customer_id, order.description, order.quantity, order.purchase_date from customer, order where customer.customer_id=order.customer_id";
$result=mysql_query($query, $connect);

Hope this can help.....

[Kid Charming]

That's ok; you don't have to select customer_id to use it as a join conditional.

[BlueHippo]

hi Kid Charming,

sorry for the wrong direction....i really dont know what is the problem with ur script ad....it looks fine....

perhan u can try those code....it have try this in my Postgres and it works well.

CODE example:

$query ="select a.customer_id, a.first_name, a.last_name, b.customer_id, b.description, b.quantity, b.purchase_date from customer as a, order as b where a.customer_id=b.customer_id";
$result=mysql_query($query, $connect);

i think in mySql is like this....

$query ="select a.customer_id, a.first_name, a.last_name, b.customer_id, b.description, b.quantity, b.purchase_date from customer a, order b where a.customer_id=b.customer_id";
$result=mysql_query($query, $connect);


hope can help....

[dweed-Ly]

Dear friends,

Thanks for your help.

My code work now, as Kid charming say ORDER is a reserved word in MySQL so in use backticks (`).

Thanks again,