CODEWITHSUNDEEP
phpmysqlsql
Raphael_b
Raphael_b

Reputation: 1510

SQL SELECT to multiple table

I have a basic SQL SELECT function:

$stmt = $mysqli->prepare("SELECT a.*,
                            b.nom_organisation
                            FROM roster a
                            INNER JOIN organisation b
                            ON a.id_organisation = b.id_organisation 
                            WHERE a.id_roster = ? LIMIT 1");

if ($stmt) {
    $stmt->bind_param('i', $id_roster);  // Bind "$email" to parameter.
    $stmt->execute();    // Execute the prepared query.
    $stmt->store_result();

// get variables from result.
$stmt->bind_result($id_roster, $nom_roster, $description_roster, $created_by, $creation_date, $nom_organisation);
$stmt->fetch();

I get a variable I called $created_by

This variable is an integer (a user id). I need to access another table called 'membre'. to get the name and surname of the user. for now I am doing another query:

$stmt = $mysqli->prepare("SELECT a.first_name,
                                a.last_name
                            FROM membre a
                            WHERE a.id_membre = ? LIMIT 1");

    $stmt->bind_param('i', $created_by);  // Bind "$email" to parameter.
    $stmt->execute();    // Execute the prepared query.
    $stmt->store_result();

It is working but seems a lot of code for not so much. Is there a way to do that in only one query?

Upvotes: 0

Views: 101

Answers (2)

idavidmcdonald
idavidmcdonald

Reputation: 103

You could join the membre table to your original query? This would be done in a similar way to the Inner join you used in your first query. You could either use an inner join again or a left join may be appropriate. Using a second join means you would not need to do two separate queries.

For more information about SQL joins, you may find http://dev.mysql.com/doc/refman/5.0/en/join.html useful. It contains several examples of joins, for example:

An inner join:

SELECT * FROM table1 INNER JOIN table2 ON table1.id=table2.id;

A left join:

SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

Upvotes: 0

Konstantin
Konstantin

Reputation: 3450

How about something like this:

SELECT r.id_roster
    , r.nom_roster
    , r.description_roster
    , r.created_by
    , r.creation_date
    , o.nom_organisation
    , m.first_name
    , m.last_name
FROM roster r
INNER JOIN organisation o ON r.id_organisation = o.id_organisation
INNER JOIN member m ON r.created_by = m.id_membre
WHERE r.id_roster = ? LIMIT 1

It's also a good practice to list all selected fields explicitly instead of using *

Upvotes: 1

Related Questions