0

I have two tables :

table1

ID X
1111111 10000
2222222 20000
3333333 30000

table2

ID Y
4444444 1234
5555555 5678
6666666 3456

I need substract the X in tables1 with Y in tables2 | something like X = X - Y

I've tried something like this

    "INSERT INTO messages (incoming_msg_id, outgoing_msg_id, msg) 
     VALUES ({$incoming_id}, {$outgoing_id}, '{$message}'), 

     SELECT table1.X, table2.Y, (table1.X-table2.Y)
     UPDATE table1
     INNER JOIN table2 ON table1.ID = table2.ID
     SET table1.X = (table1.X-table2.Y)"

I want the UPDATE TABLE1 executed when the INSERT INTO triggered

But i did'nt notice whats wrong with the SELECT UPDATE code ? Any idea?

edit : full code

if(isset($_SESSION['id'])){
include_once "config.php";
$outgoing_id = $_SESSION['id'];
$incoming_id = mysqli_real_escape_string($conn, $_POST['incoming_id']);
$message = mysqli_real_escape_string($conn, $_POST['message']);
if(!empty($message)){
$sql = mysqli_query($conn, "INSERT INTO messages (incoming_msg_id, outgoing_msg_id, msg)
                            VALUES ({$incoming_id}, {$outgoing_id}, '{$message}')") or die();
Dharman
  • 30,962
  • 25
  • 85
  • 135
Azis Sofyan Prasetyo
  • 39
  • 6
  • The rule which allows to define the value from what row of the table 2 must be substracted from definite row of the table 1 must exist. What is this rule? Remember - positional order is not safe for this purposes... – Akina Sep 23 '21 at 07:02
  • You can't use `SELECT` and `UPDATE` in the same query. Just do the `UPDATE`. – Barmar Sep 23 '21 at 07:05
  • If you want this to be triggered when inserting into `messages`, use `CREATE TRIGGER`. – Barmar Sep 23 '21 at 07:05
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) – Dharman Sep 23 '21 at 10:50

2 Answers2

1

Use CREATE TRIGGER to execute the UPDATE whenever inserting into `messages.

CREATE TRIGGER messages_t1_t2 AFTER INSERT on messages
FOR EACH ROW
    UPDATE table1 AS t1
    CROSS JOIN table2 AS t2
    SET t1.X = t1.X - t2.Y
    WHERE t1.id = NEW.incoming_msg_id AND t2.id = NEW.outgoing_msg_id;
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • since the $incoming_id used to search X value in table1 and $outgoing_id to search Y value in table2 and then do X-Y, your JOIN SET, didn't update the table value. And i still trying to figure it out – Azis Sofyan Prasetyo Sep 23 '21 at 10:04
  • Is it supposed to be `t1.id = $incoming_id and t2.id = $outgoing_id` instead of `t1.id = t2.id`? – Barmar Sep 23 '21 at 15:48
  • I've updated the answer to use the `NEW` values from the `INSERT` to get the appropriate IDs. – Barmar Sep 23 '21 at 15:51
  • #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE t1.id = NEW.incoming_msg_id AND t2.id = NEW.outgoing_msg_...' at line 5 it gives me this error – Azis Sofyan Prasetyo Sep 24 '21 at 07:28
  • Sorry, had the clauses out of order. `SET` goes before `WHERE`. – Barmar Sep 24 '21 at 13:57
1

You should split it into step by steps:

  • Update X = X - Y
  • Insert to messages
  • Select origin values, I guess the values you need is X_Old, Y, X_New. So I changed SELECT table1.X, table2.Y, (table1.X-table2.Y) to SELECT (table1.X + table2.Y), table2.Y, table1.X because the data was updated.

You can check the full script here:

UPDATE table1
INNER JOIN table2 ON table1.ID = table2.ID
SET table1.X = (table1.X-table2.Y);

INSERT INTO messages (incoming_msg_id, outgoing_msg_id, msg) 
VALUES ({$incoming_id}, {$outgoing_id}, '{$message}');

SELECT (table1.X + table2.Y), table2.Y, table1.X
FROM table1
INNER JOIN table2 ON table1.ID = table2.ID;
Tuan Dao
  • 2,647
  • 1
  • 10
  • 20