CODEWITHSUNDEEP
phpmysqldatabase
Jordan
Jordan

Reputation: 2523

Insert row into table when select from another table does not have row to join on

We have a messages table that holds messages, and we have a message_statuses table that holds a users' individual interactions with each message

The messages table has a message that the user should see in the app. When they GET messages from our API we want to create a new row in the messages_status table that will track the users id and the read/seen status on that table so we can determine when to show them the message and weather or not to mark it as unseen and show a notification bubble.

When we select messages that the user should get, I know I can join on message_id like so

SELECT * FROM messages AS a JOIN message_statuses AS b ON a.id = b.message_id;

But if there is a new message added to the messages and we haven't added a new row to the message_statuses table to start tracking interaction for that user with that message then we want to add a new row so we can track open/read statuses as well as a deleted status.

Is there a way possibly using a sub query to not only select with that join but also insert a new row when we don't have a row in the message_statuses table?

I see this page here http://dev.mysql.com/doc/refman/5.7/en/insert-select.html but I'm having some trouble understanding if this can work for us.

We're using PHP so I know I can work around it with some separate queries but I'm really wondering if we can get this to work in one query. I know I've seen insert on duplicate key with seems like it may work as well but I'm not terribly familiar with it.

[UPDATE]

I have tried adding this trigger before the sql gets run

$trigger = "CREATE TRIGGER insert_status_row AFTER SELECT ON text2bid_messages
 FOR EACH ROW
 BEGIN
   IF selected.opened IS NULL THEN
     INSERT INTO text2bid_message_statuses (user_id, message_id) = ($user_id,selected.id);
   END IF;
 END";
mysql_query($trigger);

But this so far doesn't seem to be doing what we need, maybe I've added the trigger wrong? It kind of seems that maybe I can't make a trigger on select?

Upvotes: 1

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

First, you can get all messages by using a left join:

SELECT *
FROM messages m LEFT JOIN
     message_statuses ms
     ON m.id = ms.message_id;

The columns from message_status will be NULL.

If you want to insert a record into message_status every time a new message is created, you can do so using an insert trigger.

Upvotes: 1

Related Questions