如何在sum [closed]中显示表中的2tabile数据

Question

I want to show some data from 2 table to 1 table. and 1 table's column will be sum as student id.

finally all data will show as student id please see this image for clear my question. PLS help.

<?php

$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="workshop_all"; // Database name


// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

?>


<?php


$sqlnew="SELECT std_id, sum(pay) AS pay FROM payment GROUP BY std_id";
$resultnew=mysql_query($sqlnew);



?>






<table width="400" border="0" cellspacing="1" cellpadding="0">
<tr>
<td>
<table width="400" border="1" cellspacing="0" cellpadding="3">
<tr>
<td colspan="6"><div align="center"><strong>List data from mysql </strong> </div></td>
</tr>

<tr>

<td align="center"><strong>std id</strong></td>

<td align="center"><strong>Email</strong></td>
<td align="center"><strong>pay</strong></td>




<tr>
<?php
while($rows=mysql_fetch_array($resultnew))  {
?>

<td><?php echo $rows['std_id']; ?></td>
<td><?php echo $rows['email']; ?></td>
<td><?php echo $rows['pay']; ?></td>



</tr>

<?php
}

?>



</table>
</td>
</tr>
</table>




<?php
mysql_close();
?>

</div>

Answer 1

Used the join in query like bellow

//change TABLE1 TO your table name

$sqlnew="SELECT p.std_id, sum(p.pay) AS pay,t1.email FROM payment p LEFT JOIN TABLE1 as t1 ON(t1.id=p.std_id) GROUP BY p.std_id";
$resultnew=mysql_query($sqlnew);

change the join type as per your requirement. the above query for only example

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

• FULL JOIN: Return all rows when there is a match in ONE of the tables

more about join click here AND also check this http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/