How to add values from multiple tuples in a MySQL database and fetch them with php code?

Question

I have two database tables

     1. graduate_survey
     G_id(pk),PO1,PO2

     2. Alumni_survey
     A_id(pk),PO1,PO2

Suppose i have inserted values in both the tables. Now i have to add the values of PO1, PO2 from graduate_survey with PO1,PO2 of Alumni_survey. How to add them with the help of MySQL query? And how to store the new value in an another table with the help of php?

Please provide an example. It is unclear if you want to add the values from certain rows with a certain relation or all from both tables. — Todor Simeonov, May 20 '17 at 22:18
i have to add all from both tables. Suppose PO1 and PO2 from graduate_survey have values 80 and 90 and PO1 and PO2 from Alumni_survey have values 70,80... i have to add PO1 from graduate_survey with PO1 from Alumni_survey and same procedure in case of PO2. — Debasish Choudhury, May 20 '17 at 22:22
@DebasishChoudhury Read into mysql joins https://dev.mysql.com/doc/refman/5.7/en/join.html and addition operator http://stackoverflow.com/questions/12387061/how-to-add-column-values-in-mysql and then you will be able to do what you want. For the PHP part. Simply read into PHP database connections and how to fire queries. See http://php.net/manual/de/book.pdo.php — Xatenev, May 20 '17 at 22:35

score 1 · Accepted Answer · edited May 23 '17 at 11:55

SELECT SUM(t.P01) AS total_P01, SUM(t.P02) AS total_P02
    FROM (SELECT P01, P02 FROM graduate_survey
          UNION ALL
          SELECT P01, P02 FROM Alumni_survey) t

This works. Based on: How can I sum columns across multiple tables in MySQL? I'm not marking it as duplicate because the resource is for one value, here are two.

Adding results of type DECIMAL(10,6) from 2 real tables with 18k+57k entries the query takes about 0.15s to complete.

Part 2: INSERT

Assuming your new table has AUTO INCREMENT on T_id (pk) and you have fetched the previous result with mysql_fetch_assoc() into $row:

$query_string = "INSERT INTO Total_survey 
 SET P01 = '".floatval($row['total_P01'])."', 
 P02 = '".floatval($row['total_P02'])."' ";

or intval() if your results are integers.

Edit: For 3 tables:

SELECT SUM(t.P01) AS total_P01, SUM(t.P02) AS total_P02
    FROM (SELECT P01, P02 FROM graduate_survey
          UNION ALL
          SELECT P01, P02 FROM Alumni_survey
          UNION ALL
          SELECT P01, P02 FROM faculty_survey) t

But I don't think it is a good idea. You have tables with the same structure holding similar data. A better approach would be to make just 1 more column in one table and write for each record is it for graduates, alumnies or faculties. Holding 3 separate tables for this job just makes your database structure hard to understand and hard to use. You can make these two tables:

Survey: id(pk), group_id(int), PO1, PO2

and a description table:

Groups: group_id(pk), group_name(varchar)

This way you can have unlimited number of survey groups, you can extend their number dynamically from your program and handle their data much easier.

After adding when we get the new values of PO1 and PO2 can we store the new value of PO1 and PO2 in another table say Total_survey(T_id (pk), PO1,PO2)? — Debasish Choudhury, May 20 '17 at 22:50
If I want the result from three tables say one more table faculty_survey where f_id(pk),PO1,PO2 then how can do this? — Debasish Choudhury, May 21 '17 at 19:42
after adding i need average of the value and then i have to store it in another table total_survey. how to do that? — Debasish Choudhury, May 22 '17 at 22:20

score 1 · Answer 2 · answered May 20 '17 at 22:40

Using PDO connection:

$sql = 'SELECT gs.G_id G_id, as_.A_id A_id, (gs.PO1 + gs.PO2 + as_.PO1 + as_.PO2) total from graduate_survey gs, Alumni_survey as_';
$stmt = $conn->prepare('INSERT INTO table_name(G_id, A_id, total) VALUES(?, ?, ?)');
$conn->beginTransaction();
foreach ($conn->query($sql) as $row) {
   $stmt->bindValue(1, $row['G_id'], PDO::PARAM_INT);
   $stmt->bindValue(2, $row['A_id'], PDO::PARAM_INT);
   $stmt->bindValue(3, $row['total'], PDO::PARAM_INT);
   $stmt->execute();
}
$stmt->commit();

How to add values from multiple tuples in a MySQL database and fetch them with php code?

2 Answers2