Displaying Foreign Key data in MySQL

Question

I have a BusinessID in both my staff and business table and I'm wanting to display the staff members for everyone in a particular business. The query below gives me this error.

ERROR: Could not able to execute SELECT * FROM business b inner join BusinessID b ON b.BusinessID = s.BusinessID WHERE b.BusinessID = 1. Not unique table/alias: 'b'

This is my foreign key file

<html>

<body>

<?php

include_once("connect.php");


$BusinessID = $_GET['BusinessID'];


$sql=   "SELECT *
        FROM business b
        inner join BusinessID b
        ON b.BusinessID = s.BusinessID
        WHERE b.BusinessID = $BusinessID"; 




if($result = $conn->query($sql)){
    if($result->num_rows > 0){
        echo "<table>";
            echo "<tr>";
                echo "<th>Name</th>";
                echo "<th>BusinessID<th>";
            echo "</tr>";
        while($row = $result->fetch_array()){
            echo "<tr>";
                echo "<td>" . $row['Name'] . "</td>";  
                echo "<td>" . $row['BusinessID'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        // Free result set
        $result->free();
    } else{
        echo "No records matching your query were found.";
    }
} else{
    echo "ERROR: Could not able to execute $sql. " . $conn->error;
}

// Close connection
$conn->close();
?>


    </body>

</html>

Below is the fix

$sql=   "SELECT *
        FROM staff 
         WHERE BusinessID = $BusinessID";

What is the current output from this query? What are your table definitions? — Tim Biegeleisen, Mar 21 '17 at 01:16
I'm passing BusinessID from another file. "href=\"foreignKey.php?BusinessID=$row[BusinessID]" — , Mar 21 '17 at 01:28
Why not just query select * from staff where s.BusinessID = that value? And please see my first comment. Eg "doesn't seem to do much" means nothing. — philipxy, Mar 21 '17 at 01:46
my apologises I've updated it to show what i've tried and i hope that is a bit better. — , Mar 21 '17 at 01:50
Just fixed it. Thanks for the help everyone and especially you phillipxy you pretty much fixed it with that suggestion. Sorry about not really explaining much. — , Mar 21 '17 at 01:53
Now that you have the form of your query, beware of SQL code injection. PS I just posted my comment as an answer (also addressing injection). PPS Keep working on clarity. — philipxy, Mar 21 '17 at 06:48

score 1 · Answer 1 · answered Mar 21 '17 at 01:17

1

You need to use a join statement

Select * 
From staff as S
Join business as B
on s.businessID=b.businessID
--Where clause <--- If you want to filter by anything.

answered Mar 21 '17 at 01:17

Das Nuk

46
8

1

The comma is a join, and the where (& your on) already is filtering. – philipxy Mar 21 '17 at 01:23
1

It's still giving me my weird output. – Mar 21 '17 at 01:32
What the output? – Das Nuk Mar 21 '17 at 02:37

score 0 · Answer 2 · edited May 23 '17 at 10:29

You say you "have a BusinessID". If it were in $BusinessID, the form of query you want seems to be:

query select * from staff where s.BusinessID = $BusinessID

However be warned of SQL code injection as addressed at How can I prevent SQL injection in PHP? (See also the coincidental meta post from today Should the answer be the simplest ever possible, even at the expense of quality/security?) The correct way to deal with this in a PHP context is to query via prepared statements.

Displaying Foreign Key data in MySQL

2 Answers2