0

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?

Dozeey
  • 35
  • 7
  • That's not how SQL works... you're going to have to use PHP... – Cayce K Oct 29 '14 at 20:27
  • what requires you to do this ... – Cayce K Oct 29 '14 at 20:28
  • how do I do it? because now I am external SQL sheet that I will import into my db. – Dozeey Oct 29 '14 at 20:30
  • the data involved is much. its not advice able to insert them one by one via a form – Dozeey Oct 29 '14 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 Oct 29 '14 at 20:32
  • http://stackoverflow.com/questions/14877797/how-to-sum-two-fields-within-an-sql-query check this out... I don't know if it helps, but I think this is what you're looking for. – Cayce K Oct 29 '14 at 20:33
  • MySQL and PHP don't do the same thing. You can't switch one for the other. If that was possible people wouldn't use them both together, they would use one or the other. The SQL you use in your PHP is valid SQL. Period. If you need to use it outside of PHP the SQL doesn't change. – I wrestled a bear once. Oct 29 '14 at 20:39

2 Answers2

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.

John Bollinger
  • 160,171
  • 8
  • 81
  • 157
  • 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. – Cayce K Oct 29 '14 at 20:37
  • assuming I drop them, how can I Order by total since I need it to display position s. – Dozeey Oct 29 '14 at 20:44
  • @Gentledozy As above, plus `ORDER BY sum` – Steve Oct 29 '14 at 20:46
  • 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. – Tony Chiboucas Oct 29 '14 at 20:49
  • @Steve, asumming I have four students, how can it order by total and show who scored the highest since I am using select? – Dozeey Oct 29 '14 at 20:54
  • What do students have to do with this? To what do they correspond? If they are supposed to correspond to `field1` - `field4` then you need a more substantial change. – John Bollinger Oct 29 '14 at 20:58
  • @john they don't correspond to that. assuming I have 4 rows – Dozeey Oct 29 '14 at 21:01
  • Then as @Steve told you (twice). I changed column names on him, but all you have to do is add `ORDER BY total` to the end of the query to get results ordered by the `total` column of the results. If you want the largest total first then make it `ORDER BY total DESC`. Any way around, though, I don't see how there's any association between students and rows of the base table, neither the original nor my version. Therefore, no query can tell you which student goes with which row. – John Bollinger Oct 29 '14 at 21:13
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.

Fergus McBreg
  • 1