CODEWITHSUNDEEP
php
user2371301
user2371301

Reputation: 3474

Select 2 tables in one php select statement

I have two tables in mysql

practice_sheets and parent_pin

And I want to use one select statement and get data from both tables.

I have tried

$result = mysqli_query($con,"SELECT * FROM practice_sheets AND parent_pin 
WHERE student_name='$_SESSION[SESS_FIRST_NAME] $_SESSION[SESS_LAST_NAME]'");

and also: 

$result = mysqli_query($con,"SELECT * FROM practice_sheets, parent_pin 
WHERE student_name='$_SESSION[SESS_FIRST_NAME] $_SESSION[SESS_LAST_NAME]'");

I've never tried to do this before and the previous solutions are what I found searching.

Update

I think it would help if I included my full code. the table data is going into a table on my page. the student_name field from the practice_sheets and parents_student from parent_pin will be matched.

<?php
$con=mysqli_connect();
// Check connection

if (mysqli_connect_errno()){
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM practice_sheets
                                                    WHERE student_name='$_SESSION[SESS_FIRST_NAME] $_SESSION[SESS_LAST_NAME]'");
$numrows = mysqli_num_rows($result);

if($numrows == 0)                    {
    echo "<div class='alert alert-danger'>";
    echo "No Entries, See your instructor for details.";
    echo "</div>";
} else                    {
    echo "<table class='mws-table table-striped table-hover'>";
    echo "<thead align='center'>";
    echo "<tr>";
    echo "<th>Sheet Number</th>";
    echo "<th>Total Minutes</th>";
    echo "<th>Due Date</th>";
    echo "<th>PIN</th>";
    echo "<th>View</th>";
    echo "</tr>";
    echo "</thead>";
    echo "<tbody align='center'>";
    while($row = mysqli_fetch_array($result)){

        if ($row["total_min"]>=$row["required_min"]) {
            echo "<tr class='success'>";
            echo "<td>" . $row['id'] . "</td>";
            echo "<td>" . $row['total_min'] . "</td>";
            echo "<td>" . $row['due_date'] . "</td>";
            echo "<td>" . $row['parent_pin'] . "</td>";
            echo "<td> <a href='account/practiceSheets?id=" . $row["id"] . "&total_min=" . $row["total_min"] ."&due_date=" . $row["due_date"] ."&mon_min=" . $row["mon_min"] ."&tues_min=" . $row["tues_min"] ."&wed_min=" . $row["wed_min"] ."&thurs_min=" . $row["thurs_min"] ."&fri_min=" . $row["fri_min"] ."&sat_min=" . $row["sat_min"] ."&sun_min=" . $row["sun_min"] ."&name=" . $row["student_name"] ."&assignment=" . $row["assignment"] ."&required_min=" . $row["required_min"] ."'> <i class='icon-eye-open'> </i> </a> </td>";
            echo "</tr>";
        } else {
            echo "<tr class='info'>";
            echo "<td>" . $row['id'] . "</td>";
            echo "<td>" . $row['total_min'] . "</td>";
            echo "<td>" . $row['due_date'] . "</td>";
            echo "<td>" . $row['parent_pin'] . "</td>";
            echo "<td> <a href='account/practiceSheets?id=" . $row["id"] . "&total_min=" . $row["total_min"] ."&due_date=" . $row["due_date"] ."&mon_min=" . $row["mon_min"] ."&tues_min=" . $row["tues_min"] ."&wed_min=" . $row["wed_min"] ."&thurs_min=" . $row["thurs_min"] ."&fri_min=" . $row["fri_min"] ."&sat_min=" . $row["sat_min"] ."&sun_min=" . $row["sun_min"] ."&name=" . $row["student_name"] ."&assignment=" . $row["assignment"] ."&required_min=" . $row["required_min"] ."'> <i class='icon-eye-open'> </i> </a> </td>";
            echo "</tr>";
        }

    }

    echo "</tbody>";
    echo "</table>";
    mysqli_close($con);
}

?>

Upvotes: 0

Views: 116

Answers (2)

adrenalin
adrenalin

Reputation: 1658

Use explicit names for WHERE statament, e.g.

$result = mysqli_query("SELECT student_name.practice_sheets FROM practice_sheets AND parent_pin WHERE student_name.practice_sheets = '{$_SESSION['SESS_FIRST_NAME']} {$_SESSION['SESS_LAST_NAME']}'");

MySQL will not AFAIK automatically check where the constraints are and rightly so considering that you may have conflicting names. Note that this is still pseudo code and you will need to change the fetched results accordingly. Usually it is considered to be good practice to also define explicitly the columns you wish to fetch, but otherwise you can use JOIN as well.

And to help writing shorter code, you can also use shorthands for the table names, e.g.

$result = mysqli_query("SELECT student_name.ps AS name, pin.pp AS pin FROM practice_sheets AS ps, parent_pin AS pp WHERE student_name.ps = '{$_SESSION['SESS_FIRST_NAME']} {$_SESSION['SESS_LAST_NAME']}'");

Update

You also have in your updated version an issue. You call mysqli_fetch_array, which returns an ordered (i.e. numbered) array. If you wish to use keyed, use mysqli_fetch_assoc.

And you are closing the MySQL connection at the moment only if the query was successful. Move mysqli_close outside of the brackets.

Upvotes: 0

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

$result = mysqli_query($con,"SELECT * 
                             FROM practice_sheets, parent_pin 
                             WHERE student_name = parents_student 
     AND student_name='$_SESSION[SESS_FIRST_NAME] $_SESSION[SESS_LAST_NAME]'");

Upvotes: 2

Related Questions