i would like to ask about Mysql Select with multiple tables

Question

I have a table which is session, and has a session_id, another tables which are namespmember1, namespmember2, namespmember3

Everytime i save the form which has the session table, namespmember1, namespmember2, namespmember3. They are all save also. which there ID's are equal becuase they are save as batch.

my question is. i want to edit those tables or view... heres my code which is redundant and i guess there is a solution for it.

Im getting the ID for the session table.

$id = $_GET['id'];
$id = mysql_real_escape_string($id);
$q = mysql_query("SELECT * FROM session WHERE session_id='$id'");
$row = mysql_fetch_object($q);

And now i will display the tables namespmember1, namespmember2, namespmember3

<?php
$sql = mysql_query("SELECT * FROM namespmember1 WHERE hon1_id='$id'");
while ($row = mysql_fetch_object($sql))
{
    echo "<tr>";
    echo "<td>$row->spmember_name</td>";
    echo "<td>$row->present</td>";
    echo "<td>$row->absent</td>";
    echo "<td>$row->leave_sick</td>";
    echo "<td>$row->business</td>";
    echo "</tr>";
}
$sql = mysql_query("SELECT * FROM namespmember2 WHERE hon2_id='$id'");
while ($row = mysql_fetch_object($sql))
{
    echo "<tr>";
    echo "<td>$row->spmember_name</td>";
    echo "<td>$row->present</td>";
    echo "<td>$row->absent</td>";
    echo "<td>$row->leave_sick</td>";
    echo "<td>$row->business</td>";
    echo "</tr>";
}
$sql = mysql_query("SELECT * FROM namespmember3 WHERE hon3_id='$id'");
while ($row = mysql_fetch_object($sql))
{
    echo "<tr>";
    echo "<td>$row->spmember_name</td>";
    echo "<td>$row->present</td>";
    echo "<td>$row->absent</td>";
    echo "<td>$row->leave_sick</td>";
    echo "<td>$row->business</td>";
    echo "</tr>";
}
?>

session_id is equal to all the ids in differnt tables namespmember1, namespmember2, namespmember3

Thank you for the help.

First of all, you need to tell us more about your actual data model. How does the data in tables namespmember1, namespmember2 and namespmember3 differ in its structure f.e.? — CBroe, Oct 14 '14 at 12:25
Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). [This article](http://php.net/manual/en/mysqlinfo.api.choosing.php) will help you decide. — Jay Blanchard, Oct 14 '14 at 12:47

score 0 · Answer 1 · answered Oct 14 '14 at 12:29

You can use UNION, just listen to your query, all subqueries should have the same amount of columns.

(SELECT spmbmbme_name, present, absent, leave_sick, business FROM namespmember1 WHERE hon1_id = '$id')
UNION
(SELECT spmbmbme_name, present, absent, leave_sick, business FROM namespmember2 WHERE hon2_id =  '$id')
UNION
(SELECT spmbmbme_name, present, absent, leave_sick, business FROM namespmember3 WHERE hon3_id = '$id')

score 0 · Accepted Answer · answered Oct 14 '14 at 12:43

0

Try it

 mysql_query("SELECT * FROM session s,namespmember1 n1,
    namespmember2 n2,namespmember3 n3 WHERE s.session_id='$id' and s.session_id=n1.hon1_id and n1.hon1_id=n2.hon2_id and n2.hon2_id=n3.hon3_id");

answered Oct 14 '14 at 12:43

Hara Prasad

704
6
15

i would like to ask about Mysql Select with multiple tables

2 Answers2