Select User id from one table and update in another table

Question

I have two table called tbl_users and tbl_img_status. I have column in tbl_img_status like below

id, user_id,status_text, scd_time,status,post_time

I am looking for run cron using PHP for publish post on time. So I have query like below

$results = mysqli_query($mysqli,"UPDATE tbl_img_status SET post_time=NOW(),status=1 WHERE status=0 AND scd_time<NOW()");

Now My issue is I also need to update tbl_users column called total_post. I want increase 1 in total_post of that user id which we have published with first query. I am new in PHP and MYSQL so not getting proper idea for do it. Let me know if someone can help me for it.

nbk · Accepted Answer · 2020-10-23T03:13:33.700

You can use Triggers for that purpose.

This would update for a specific user if the status changed from 0 to 1

DELIMITER $$

CREATE TRIGGER after_tbl_img_status_update
AFTER UPDATE
ON tbl_img_status  FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status AND NEW.status = 1 THEN
        UPDATE tbl_users  SET total_post  = total_post +1 WHERE id = NEW.user_id;
    END IF;
END$$

DELIMITER ;

IF you don't want to change the Database, you can use a INNER JOIN with And update both table as one.

Your php will then look like:

$results = mysqli_query($mysqli,"UPDATE tbl_users tu INNER JOIN  tbl_img_status  tis ON tu.id = tis.user_id SET tis.post_time=NOW(), tis.status=1, tu.total_post = tu.total_post+1 WHERE tis.status=0 AND tis.scd_time<NOW();");

if you don't like the clean and simple mysql version i add also a version, that runs a query like before — nbk, Oct 23 '20 at 03:14

score 2 · Answer 2 · edited Oct 23 '20 at 10:43

2

You can use one query to update two tables. Try this query if that works. I have got a hint from this. MySQL, update multiple tables with one query

UPDATE tbl_users, tbl_img_status 
SET tbl_img_status.post_time=NOW(),
    tbl_img_status.status=1,
    tbl_users.total_post = tbl_users.total_post+1
WHERE
    tbl_users.id= tbl_img_status.user_id
    AND tbl_img_status.status=0 AND tbl_img_status.scd_time<NOW()

edited Oct 23 '20 at 10:43

Dharman

30,962
25
85
135

answered Oct 23 '20 at 02:56

development-ninja

524
2
7

Riya please use that query in your mysqli_query method. Let me know. – development-ninja Oct 23 '20 at 03:05
Riya, I ve just checked from my phpmyadmin. I created two tables and added 1 for tbl_users and 2 for tbl_img_status containing status=0. And I run over query so the user was updated where has id equals to user_id of tbl_img_status So it runs cool! :) – development-ninja Oct 23 '20 at 03:31

Select User id from one table and update in another table

2 Answers2