PHP & MySQL - How to delete a users records from two tables

Hello I'm new to PHP & MySQL and I was wondering how can I delete all of user_id 3 records from these two tables below using PHP & MySQL?

My MySQL tables

CREATE TABLE ls ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, skill VARCHAR(255) DEFAULT NULL, experience VARCHAR(22) DEFAULT NULL, self_grade VARCHAR(10) DEFAULT NULL, date_created DATETIME NOT NULL, date_updated DATETIME DEFAULT NULL, PRIMARY KEY (id) ); CREATE TABLE us ( id INT(13) UNSIGNED NOT NULL AUTO_INCREMENT, skill_id INT(13) UNSIGNED NOT NULL, user_id INT(13) UNSIGNED NOT NULL, PRIMARY KEY (id) );

us table values

id skill_id user_id 9 7 3 10 8 3 11 9 3 12 10 3 13 17 6

ls table values

id skill experience self_grade date_created date_updated 7 html 5 years A 2010-10-19 07:11:08 2010-10-19 07:12:06 8 jquery 10 years B 2010-10-19 07:11:27 2010-10-19 07:12:06 9 css 1 year NULL 2010-10-19 07:11:38 2010-10-19 07:12:06 10 php 2 years C 2010-10-19 07:13:05 NULL 17 php 2 years C 2010-10-19 07:19:05 NULL

--------------Solutions-------------

You could fetch all rows belonging to user_id = 3 and then build a DELETE statement with a generated WHERE clause.

But, however, I'm wondering if you have an inappropriate table design as you have a m:n relation between user and skill and you want to delete all skills the user with id 3 is related with. But this implicates a 1:m relation between user and skill (1 user has multiple skills, not sharing the skills with others). Or maybe I got something wrong...?

MySQL does support multi-table deletions, use:

DELETE a, b
FROM ls a
JOIN us b ON b.skill_id = a.id
WHERE b.user_id = 3

Yes, you can use a multi-table DELETE statement, eg.

DELETE FROM ls, us USING ls
JOIN us ON us.skill_id = ls.id
WHERE ls.user_id = 3

Category:php Time:2010-10-19 Views:1
Tags: php mysql sql

Related post

  • Delete all the records from a table 2011-07-03

    How can we delete all the records from a table in DELPHI? we are not allowed to use loop like this: for k:=1 to table1.recordcount do begin table1.Last; table1.Delete; end; is there any solutions? --------------Solutions------------- The best way to

  • How do I delete all the records in a table that have corresponding records in another table 2008-11-07

    I have two tables A and B. I would like to delete all the records from table A that are returned in the following query: SELECT A.* FROM A , B WHERE A.id = B.a_id AND b.date < '2008-10-10' I have tried: DELETE A WHERE id in ( SELECT a_id FROM B WH

  • SQL to delete the oldest records in a table 2010-06-28

    I'm looking for a single SQL query to run on an oracle table that will retain n number of records in a table and delete the rest I tried the following delete from myTable where pk not in (SELECT pk FROM myTable where rownum <5 order by created DES

  • Delete all duplicate records from Oracle table except oldest 2011-02-24

    I have 2 tables, one parent TableA and one child TableB. TableB has 1 or more records with a parent record in TableA. I need to delete all records from TableB except the earliest date i.e. all duplicates in TableB. I don't think TableA needs to be in

  • How to delete all duplicate records from SQL Table? 2011-06-15

    Hello I have table name FriendsData that contains duplicate records as shown below fID UserID FriendsID IsSpecial CreatedBy ----------------------------------------------------------------- 1 10 11 FALSE 1 2 11 5 FALSE 1 3 10 11 FALSE 1 4 5 25 FALSE

  • How to speed up the MySQL query needed for pagination - million records in the table 2011-04-20

    There is about 1 milion records. Query is needed for the pagination system. Query looks like this: SELECT field1, field2, field3 FROM table WHERE field4 = '$value' ORDER BY field5 ASC limit $offset, 30; There is index on field4 and field5. All fields

  • Can't nail a MySQL query where I require multiple records from a table 2011-03-24

    I have the following tables: CATEGORIES -- entry_id, cat_id TITLES -- entry_id, title DATA -- entry_id, date, body For each record entered into the DB the same entry_id is entered into all three entry_id fields in the three tables. In the Categories

  • MySQL database design, two types of records - use one table or two separate tables? 2011-04-06

    I'm building an application that will have two different types of users, lets call one User_type_a and the other User_type_b. I'm wondering if I should create 1 table in my database for both types of users and have a property distinguishing what type

  • How to delete all records in a table using SubSonic 3 2009-08-26

    I'm trying to delete all the records from a table using this approach: new Delete<Contact>().Execute(); This statement fails with a NullReferenceException in BuildDeleteStatement method at line: sb.Append(query.FromTables[0].QualifiedName); Bec

  • How to delete all records of a table above a certain number for clean up purpose 2011-10-14

    To illustrate my question, let's take the example of rolling log files. You set a cap in bytes and every bytes that go over it, is deleted to let place to the new entries. I need something similar for my database and I want to create a query that wil

  • How to delete the contents of a related record from two tables? 2011-12-11

    The StudentDetails.Students is the master table which forms a relationship with RegistrationDetails.Registration. Therefore, StudentID is the primary key in the former whereas it is a foreign key in the latter. Now I've tried each of the following co

  • How to delete the contents of a related record from two tables( using SQLTransaction)? 2011-12-15

    I'm getting "Format of the initialization string does not conform to specification starting at index 0" error having used the following code. The commands are supposed to delete a related record from two tables namely StudentDetails.Students as the m

  • To delete the all record from table in android SQllite Database? 2011-12-29

    Am trying to delete the all record form the table in database.But i Cannot delete.anybody help me. update *am using this link Delete* Thanks in advance public void delete() { String DELETEPASSCODE_DETAIL = "DELETE * FROM Payment;"; db.execSQL(DELETEP

  • Deleting the oldest record using SQLite 2012-04-23

    I want to delete the oldest record from a table using SQLite so should i use a condition (the lowest id ) or there is another remedy --------------Solutions------------- delete from table1 as a where a.id = (select min(id) from table1) or if you have

  • Deleting duplicate records from my table 2014-11-03

    I have a table with numerous duplicate records throughout. I would like to delete these duplicate records from the table. Can this be done in a SQL query? Thanks! --------------Solutions------------- If you can change the query that creates the table

  • Can someone tell why a query will not display a specific record in a Table to a Form 2012-01-30

    I have asked this question several times and I am still trying to figure out the problem. I have a Form with a combo box. I select an item form the combo box and then click on the command button. Next the query opens another Form and displays my sele

  • MySQL Delete Records from 2 Tables 2010-07-08

    Im looking to delete information in two different tables in 1 query, based on an ID. I've tried several solutions on here to accomplish this task but still have not accomplished what I'm trying to do. Table 1 - Content ---------- --------- ContentID

  • delete duplicate record from same table in mysql 2010-09-22

    I have one table named tblm_customer. It contain field named firstname and lastname. Now I want to delete all records from the table that contain the same firstname and lastname that are already in the table. I used mysql database and customerid is t

  • delete 1 record from a table and automatically delete all records associated with the user_id in mysql 2011-09-08

    I have been trying to learn from reading tutorials online and stuff but I just can't put my finger on it. I have 2 tables at the moment (i'll have a lot more later on as I build my application) so I want to knock out this issue before expanding and c

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.818 (s). 13 q(s)