change php and mysql insert to mysql only

Question

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?

That's not how SQL works... you're going to have to use PHP... — Cayce K
– Cayce K, Commented Oct 29, 2014 at 20:27
how do I do it? because now I am external SQL sheet that I will import into my db. — Dozeey
– Dozeey, Commented Oct 29, 2014 at 20:30
the data involved is much. its not advice able to insert them one by one via a form — Dozeey
– Dozeey, Commented Oct 29, 2014 at 20:32
I don't fully understand what you are trying to achieve.. You look like you have what needs to be done. I mean if you just make $abc3 = $abc1+$abc2 you're going the right direction, but SQL does not add fields the way you're wanting to. — Cayce K
– Cayce K, Commented Oct 29, 2014 at 20:32

John Bollinger · Accepted Answer · 2014-10-29 20:55:34Z

3

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.

edited Oct 29, 2014 at 20:55

answered Oct 29, 2014 at 20:36

John Bollinger

191k11 gold badges103 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Cayce K Over a year ago

see I can't figure out if the values are already in a table or if he is trying to get them there by another means of input. But this is a great answer for provided as of now.

Dozeey Over a year ago

assuming I drop them, how can I Order by total since I need it to display position s.

Steve Over a year ago

@Gentledozy As above, plus ORDER BY sum

Tony Chiboucas Over a year ago

Also, PLEASE add an auto-index IDENTITY, even if you don't use it anywhere. That will save major headaches later if you run into any corruption or duplication issues later on.

Dozeey Over a year ago

@Steve, asumming I have four students, how can it order by total and show who scored the highest since I am using select?

|

Fergus McBreg · Accepted Answer · 2014-10-29 21:32:53Z

0

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.

answered Oct 29, 2014 at 21:32

Fergus McBreg

1

Collectives™ on Stack Overflow

change php and mysql insert to mysql only

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related