Recovers Ids from table and delete them from another table

Question

I have two tables.

ticket:

+--------------------+-----------------+------+-----+---------+----------------+
| Field              | Type            | Null | Key | Default | Extra          |
+--------------------+-----------------+------+-----+---------+----------------+
| ticket_id          | int(8) unsigned | NO   | PRI | NULL    | auto_increment |
| ticket_user_id     | int(8) unsigned | NO   | MUL | NULL    |                |
| ticket_status      | varchar(6)      | NO   |     | opened  |                |
| ticket_subject     | varchar(100)    | NO   | MUL | NULL    |                |
| ticket_message     | text            | NO   |     | NULL    |                |
+--------------------+-----------------+------+-----+---------+----------------+

ticket_reply:

+------------------------+-----------------+------+-----+---------+----------------+
| Field                  | Type            | Null | Key | Default | Extra          |
+------------------------+-----------------+------+-----+---------+----------------+
| ticket_reply_id        | int(8) unsigned | NO   | PRI | NULL    | auto_increment |
| ticket_reply_ticket_id | int(8) unsigned | NO   | MUL | NULL    |                |
| ticket_reply_showname  | varchar(40)     | YES  |     | NULL    |                |
| ticket_reply_message   | text            | NO   |     | NULL    |                |
+------------------------+-----------------+------+-----+---------+----------------+

ticket_reply_ticket_id correspond to ticket_id to link these 2 tables.

In PHP, I have an $userId and I need to delete all tickets from the ticket table of $userId but also to delete all replies corresponding these tickets Id.

Is it possible to dot it in one or 2 sql request or do I have to make a delete of ticket_reply for each ticket_id of $userId ?

Thanks?

Yes, and `ticket_user_id` just `user_id`, etc. Because when you refer to a field you use notation `.`, and with current format will say `._` — Juan Carlos Oropeza, Dec 08 '16 at 16:18
Possible duplicate of [MySQL foreign key constraints, cascade delete](http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete) — Juan Carlos Oropeza, Dec 08 '16 at 16:21
@JuanCarlosOropeza I started renaming my tables but now I have duplicates column with the same name like "id". How do you deal with it? — London Smith, Dec 08 '16 at 18:17
You shouldnt ... probably you make `ticket_id and ticket_user_id` both `id` instead of `id, user_id` — Juan Carlos Oropeza, Dec 08 '16 at 18:24
@Strawberry of course I use alias, but this format cause unnecesary long name, isnt? Or there is a reason you would use it? — Juan Carlos Oropeza, Dec 08 '16 at 18:25
Ok aliases http://stackoverflow.com/questions/431391/php-mysql-how-to-resolve-ambiguous-column-names-in-join-operation — London Smith, Dec 08 '16 at 18:26
@JuanCarlosOropeza I think it's basically fine. While I agree it's irritating to prefix every column with the tablename, I prefer table_id to id. — Strawberry, Dec 08 '16 at 18:30
@Strawberry yes me too, but only because make JOIN more clear, but the rest of the fields is overkill. — Juan Carlos Oropeza, Dec 08 '16 at 18:32

Recovers Ids from table and delete them from another table

0 Answers0