Hi, I am new in mysql and having problem with deleting rows from multiple tables.

I have 3 tables

1. table users
us_id <- primary key

2. table items

od_id <- primary key
pd_id <- primary key
us_id <- primary key
it_qty

3.table orders

od_id <- primary key


In table "items" all are foreign keys from another tables except it_qty.
The problem is when I delete user from table "user", I want delete all his records from "items" and "order" tables. My tables are in MyIsam. so far I couldn't get any valid query.
Please help me with this query. Thanks in advance.

Hiya,

You could always run multiple queries. Or is this a problem for you?

Thank you for reply. I did run select query and i can see all rows related to user.
Below my select query,

$query = " SELECT * FROM user, items, orders WHERE user.us_id = items.us_id AND items.od_id = orders.od_id AND user.us_id = '$usrID'";

but i dont know how to convert it to delete query. I have tried

$query = " Delete FROM user, items, orders WHERE user.us_id = items.us_id AND items.od_id = orders.od_id AND user.us_id = '$isrID'" ;

and I get error:

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 'WHERE user.us_id = items.us_id AND items.od_id = orders.od_id A

What will be correct delete query based on select query above?

I got it
DELETE user,items,orders FROM user LEFT JOIN items ON user.us_id = items.us_id LEFT JOIN orders ON items.od_id = orders.od_id WHERE user.us_id = $usrID