CODEWITHSUNDEEP
phpmysql
Dozeey
Dozeey

Reputation: 35

change php and mysql insert to mysql only

I have a tables in my database. The table contain 6 rows. Two rows TOTAL and AVERAGE are self inserted by the sum of other rows I have been usung php amd mysql to insert datas into the table and it works fine but now it requires that i have to use only nysql . how can i write it so that the sum perform fine?

i have the below codes to emphasize more.

 my_tb
 field1   field2   field3   field4  total  average
 10        12       32          5          sum    sum

my codes

$total = $abc1+$abc2+$abc3+$abc4;
                    $average= ( $abc1+$abc2+$abc3+$abc4 ) / 4;
                // write new data into database
                $sql = "INSERT INTO my_tb (field1, field2, field3, field4,total,average)
                  VALUES('" . $abc1 . "', '" . $abc2 . "',
                   '" . $abc3 . "',  '" . $abc4 . "', '" . $total . "',  '" . $average . "', '" . $total_score . "'  );";
                $query_new_user_insert = $this->db_connection->query($sql);

                // if user has been added successfully
                if ($query_new_user_insert) {
                    $this->messages[] = "Your data has been created successfully. You can now log in.";
                } else {
                    $this->errors[] = "Sorry, your data upload failed. Please go back and try again.";
                }
            }

            now i am using SQL only

            INSERT INTO `my_tb` (`field1`, `field2`,`field3`,`field4`,`total`,`average`,) VALUES
 ('10', '31',  '31',  '31' , 'field1+field2.....', 'field1+field2...../4');

 but it doesnt work. it inserts the varriables instead of its sum.

pls can someone tell me how to achieve this?

Upvotes: 0

Views: 88

Answers (2)

Fergus McBreg
Fergus McBreg

Reputation: 1

Mysql

INSERT INTO my_tb (field1, field2,field3,field4,total,average) VALUES (@abc1:='10', @abc2:='31', @abc3:='31', @abc4:='31' , @abc1+@abc2+@abc3+@abc4, (@abc1+@abc2+@abc3+@abc4)/4);

PHP

$sql = "INSERT INTO my_tb (field1, field2,field3,field4,total,average) VALUES (@abc1:='" . $abc1 . "', @abc2:='" . $abc2 . "', @abc3:='" . $abc3 . "', @abc4:='" . $abc4 . "' , @abc1+@abc2+@abc3+@abc4, (@abc1+@abc2+@abc3+@abc4)/4)";

I suppose you will get that you want.

Upvotes: 0

John Bollinger
John Bollinger

Reputation: 180351

That's not a table with 6 rows, it's a table with 6 columns, of which you present just one row. It also seems strange in that it has no obvious keys, and it is seriously denormalized in that the total and average columns are functionally dependent on the other four.

It's not clear what your objective is here, but note that because of the functional dependency it is unnecessary to have the total and average columns at all. If you drop them then you can still query the total and average like so:

SELECT
  field1,
  field2,
  field3,
  field4,
  (field1 + field2 + field3 + field4) AS total,
  (sum / 4.0) AS average
FROM my_tb

Edited to rename columns to match the original table; sum -> total, avg -> average.

Upvotes: 3

Related Questions