CODEWITHSUNDEEP
mysql
Jayden
Jayden

Reputation: 3

MySQL code to get one tables data, insert it into another and delete the old data

Info: I need to create a MySQL code to select more than one lines of data from the table items_rooms, insert data into table items_users based from the data inside items_rooms THEN delete the data in items_rooms. But inside of that I need to also gather some data from another table items to grab the user Id of the owner who owns the item.

So in the end the only tables that have data are items and items_users, and they work from getting their data from items_rooms.

Seems confusing? I know, here is my code I wrote which I know is completely wrong but It might help you out.

$getItemRooms = $db->query("SELECT * FROM items_rooms WHERE room_id = '" . $roomid . "'");
    while($roomitem = $getItemRooms->fetch_assoc()) {

        $getItems = $db->query("SELECT * FROM items WHERE item_id = '" . $roomitem['item_id'] . "'");
        while($items = $getItems->fetch_assoc()) {

                $sql->query("INSERT INTO items_users (item_id, user_id) VALUES ('" . $roomitem['item_id'] . "', '" . $items['placedBy'] . "')");
                $sql->query("DELETE FROM items_rooms WHERE room_id = '" . $roomid . "'");
                echo 'done';

        }

    }

So for instance, room_id inside of items_rooms could be 3. inside items_rooms there are 10 rows if items that are owned. I need them items to be deleted and replaced into items_users but items_rooms does not have the owner id that items has.

Upvotes: 0

Views: 723

Answers (2)

soeik
soeik

Reputation: 925

If your main problem is grabbing data from items table try to retrieve all you need with one query using join. For instance:

select *
from items_rooms as ir
inner join items as i on ir.item_id=i.item_id
where item_id = ...

It's better to specify only necessary fields from every table instead of using (*). Also you can combine INSERT and SELECT statements into one query. Try to look MySQL reference It will simplify and speed up your php script

Upvotes: 0

eggyal
eggyal

Reputation: 125925

One can use INSERT ... SELECT, together with an appropriate join:

INSERT INTO items_users (item_id, user_id)
SELECT item_id, items.placedBy
FROM   items JOIN items_rooms USING (item_id)
WHERE  room_id = ?

If your application is subject to synchronous database access from multiple clients, be careful to avoid a race hazard arising between the INSERT and DELETE statements; in particular consider using a locking read in the above statement and performing the DELETE statement within the same transaction.

Upvotes: 1

Related Questions