Doing a three table join in MySQL

Question

This is my table

program_join ('program_join_id, member_ID, program_schedule_id');

program_schedule (program_scedule_id, program_id, datetime');

program ('program_id, program_name);

This is my mysql

$mySql = "SELECT 
            program_join.program_join_id,
            member.member_username, 
            program_join.program_schedule_id

            FROM program_join

                INNER JOIN member ON
                program_join.member_ID=member.member_ID
                ORDER BY program_join_id";


$myQry = mysql_query($mySql, $DB)  or die ("wrong query : ".mysql_error());
$number = $hal; 
while ($myData = mysql_fetch_row($myQry)) {
    $number++;
    $code = $myData[0];
?>
<tr align="center">
    <td><?php echo $number;?></td>
    <td><?php echo  $myData[1];?></td>
    <td><?php echo  $myData[2];?></td>

result

No      username      program schedule
1        lalala          1
2        bababba         2

and under the 'program schedule', i want to show the program name that i have from the ' program_join.program_schedule_id'

please help me

So what's the problem? You already know how to do a `join`, since you have one in your query already. — Marc B, Sep 13 '16 at 17:32
i want to show the program name under the program schedule instead of the ID — lalala, Sep 13 '16 at 17:34
so join in the table that contains that information... using the exact same type of join you've already used. — Marc B, Sep 13 '16 at 17:35
Due to `LIMIT $hal, $line` [your query is vulnerable to a SQL injection attack](https://stackoverflow.com/questions/22886670/sql-injection-and-the-limit-clause#29612558). — Schwern, Sep 13 '16 at 17:36
**WARNING**: If you're just learning PHP, please, do not learn the obsolete [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface. It's awful and has been removed in PHP 7. A replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) and a guide like [PHP The Right Way](http://www.phptherightway.com/) helps explain best practices. Make **sure** your user parameters are [properly escaped](http://bobby-tables.com/php) or you will end up with severe [SQL injection bugs](http://bobby-tables.com/). — tadman, Sep 13 '16 at 18:10
@lalala Please do not vandalize your posts or the posts of others. If you try to revandalize a post that has been fixed, a moderator will be informed. The moderator will roll back your vandalism, and hand you a suspension which will prevent you from further editing. **Attempts at vandalism are ultimately futile.** — Louis, Sep 14 '16 at 19:18

devlin carnate · Accepted Answer · 2016-09-13T19:37:23.213

1

Just use joins to get the program_name from the program table:

SELECT 
pj.program_join_id,
m.member_username, 
p.program_name,
ps.datetime

FROM program_join pj
INNER JOIN member m ON pj.member_ID=m.member_ID
INNER JOIN program_schedule ps ON ps.program_schedule_id = pj.program_schedule_id
INNER JOIN program p ON p.program_id = ps.program_id
ORDER BY ... blah blah

edited Sep 13 '16 at 19:37

answered Sep 13 '16 at 17:39

devlin carnate

8,309
7
48
82

hi can you check for me. Edit is this the correct format? – lalala Sep 16 '16 at 13:07
@lalala - I'm not sure what you're asking. That doesn't appear to be related to MySQL. – devlin carnate Sep 16 '16 at 15:31
// copy file if (trim($_FILES['Filename']['name']) =="") { $file_name = $_POST['txtFilename']; } else { // If the old image file exists, will be deleted if(file_exists("images1/classes/".$_POST['txtFilename'])) { unlink("images1/classes/".$_POST['txtFilename']); } //new image file (added) $file_name = $_FILES['Filename']['name']; $file_name = stripslashes($file_name); $file_name = str_replace("'","",$file_name); $file_name = $code.".".$file_name; copy($_FILES['Filename']['tmp_name'],"images1/classes/$file_name.jpg"); } – lalala Sep 16 '16 at 17:46
for updating image, but it doesnt work, can you help me? – lalala Sep 16 '16 at 17:47
@lalala - your best bet is to open a new question so you can give an [MCVE](http://stackoverflow.com/help/mcve). – devlin carnate Sep 16 '16 at 21:42

score 0 · Answer 2 · answered Sep 13 '16 at 17:36

Assuming there is one more table named program_schedule, I would add one more join and get the required column, below is the example query (with assumed table/column names):

SELECT 
            program_join.program_join_id,
            member.member_username, 
            program_join.program_schedule_id,
            program_schedule.program_name

            FROM program_join

                INNER JOIN member ON
                program_join.member_ID=member.member_ID
                JOIN program_schedule on program_schedule.id = program_join.program_schedule_id,
                ORDER BY program_join_id ASC LIMIT $hal, $line";

but i dont have program_name in me program_schedule – lalala Sep 13 '16 at 17:42 — lalala, Sep 13 '16 at 17:42

Doing a three table join in MySQL

2 Answers2