CODEWITHSUNDEEP
phpmysqlsql
meer
meer

Reputation: 13

subtracting value from 2 different table then insert the result to another table

(question updated) still new to php.

i have here table 'users' (sorry i've changed some column names)

| idn | name | balance | 
  12    matt     500

and table 'transactions'

| id | store_id | balance | 
  12      1          100
  12      1           50
  12      1           50

and so far this is what i've done(not working)

 <?php 
 $page_title = 'Student Transaction Log';
 include ('includes/header.html');

 echo "<br><h2>Welcome {$_COOKIE['first_name']}</h2>";

 require_once ('mysqli_connect.php');

 $q = "SELECT s.idn, s.balance - 
  COALESCE(
  SELECT SUM(t.balance) FROM transactions t 
  WHERE t.id = s.idn AND t.store_id = 1)
  ,0)
  AS result FROM users s
  WHERE s.idn = {$_COOKIE['idno']} ";   
  $r = @mysqli_query ($dbc, $q);

  if (mysqli_num_rows($r) == 1) {
  echo '
  <p>Balance: ' . $row['result'] . ' </p>'; 
   }
   mysqli_close($dbc);

   ?>

how can i or what should i do to subtract 'result' from the 'balance'(users) and to insert there result together with the student_id to this table 'result' (well i have not yet included insert statement in my code)

table 'result'

| student_id | new balance |

thanks guys for the help last time but still my code is not working. so i decided to post my entire code. whats wrong with my code? thanks so much

Upvotes: 1

Views: 813

Answers (2)

Zymon Castaneda
Zymon Castaneda

Reputation: 759

You can try this mate:

DELIMITER //  
START TRANSACTION;
  SET @balance1 = 0, @bal_total = 0, @student_id = <insert_studentID_here>,  
    @store_id = <insert_storeID_here>;  

  SELECT SUM(balance) INTO @bal_total FROM transactions  
  WHERE student_id = @studentID AND store_id = @storeID;  

  SELECT balance INTO @balance FROM users  
  WHERE student_id = @studentID;  

  INSERT INTO result (student_id, balance)  
  VALUES (@student_id, @balance1 - @bal_total);
COMMIT;  
DELIMITER ;

Using the MySQL Transaction will make the process satisfy the 'A' in the ACID structure.
Link: MySQL Documentation - ACID

The 'A' means Atomicity, and by using a Transaction you'll make it run successfully or rollback and make no changes.

You can also convert this Transaction query into a php one by using this: PHP Transaction

Also, the @variable_name is a session variable. I've put something to handle clearing the value incase you'll be using the query several times.

Hope this can help you

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15389

Try this:

INSERT INTO result
SELECT s.student_id, s.balance_1 - 
    COALESCE(
        (SELECT SUM(t.balance2) FROM transactions t
        WHERE t.student_id = s.student_id
        AND t.store_id = 1)
   ,0)
FROM users s
WHERE s.student_id = 12

In this way if you have not rows in transactions table you'll have only balance_1

Upvotes: 2

Related Questions