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
– Todor Simeonov, Commented May 20, 2017 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
– Debasish Choudhury, Commented May 20, 2017 at 22:22
@DebasishChoudhury Read into mysql joins dev.mysql.com/doc/refman/5.7/en/join.html and addition operator stackoverflow.com/questions/12387061/… 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 php.net/manual/de/book.pdo.php — xate
– xate, Commented May 20, 2017 at 22:35

Community · Accepted Answer · 2017-05-23 11:55:10Z

1

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.

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered May 20, 2017 at 22:35

Todor Simeonov

8061 gold badge6 silver badges11 bronze badges

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

3 Comments

Debasish Choudhury Over a year ago

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 Over a year ago

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 Over a year ago

after adding i need average of the value and then i have to store it in another table total_survey. how to do that?

Oluwafemi Sule · Accepted Answer · 2017-05-20 22:40:52Z

1

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();

answered May 20, 2017 at 22:40

Oluwafemi Sule

39.3k1 gold badge63 silver badges88 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related