Mysql joining sharing data between tables

Question

I have a problem with join in mysql, I am trying to populate Name field from bands table in to band_name field in orders table when band_id from orders table matches Band_id from bands table. its all confusing and would like some advise or help.

my code

 <?php
        }   

        $user = new User();
        $user_name = escape($user->data()->username);

        $result = mysql_query("SELECT bands.Name FROM bands LEFT JOIN orders ON bands.Band_id = orders.band_id WHERE orders.band_id = 1 AND user_name = '"mysql_real_escape_string($user_name)"'");
       //$result = mysql_query("SELECT * FROM orders WHERE user_name = '".mysql_real_escape_string($user_name)."'");


        echo '<table border="1">
        <tr>
        <th>My bookings</th>
        <th>gig No</th>
        </tr>';

        if(mysql_num_rows($result) > 0){
            while($row = mysql_fetch_array($result)){
                echo 
                "<tr>
                <td>".$row['user_name']."</td>
                <td>".$row['band_id']."</td>
                </tr>";
            }
        }else{
            echo "<tr><td>No bookings</td></tr>";
        }
        echo "</table>";
        ?>

Answer 1

SELECT * FROM bands
LEFT JOIN orders as user_orders ON(
user_orders.band_id = bands.band_id
AND user_orders.user_name = "'.mysql_real_escape_string($user_name).'"
)

Answer 2

I think you're approaching your use of the DB slightly wrong.

Your bands table should contain all the info you need on a band. Such as this:

| id | name        |
====================
| 1  | The Beatles |
| 2  | Blur        |
etc

Your orders table should contain all or your order data:

| id | band_id |
================
| 1  | 2       |
| 2  | 1       |
| 3  | 1       |

As you an see, there are 2 orders for The Beatles, and 1 order for Blur.

You could get the band name for an order like so:

SELECT bands.name FROM orders, bands, WHERE orders.id = 1 AND orders.band_id = bands.id;

Using an inner join:

SELECT bands.name FROM bands INNER JOIN orders ON bands.id = orders.band_id WHERE orders.id = 1;

Using a left join:

SELECT bands.name FROM bands LEFT JOIN orders ON bands.id = orders.band_id WHERE orders.id = 1;

UPDATE:

Refer to this image for more details on joins:

Taken from this stack overflow question.