I’m currently trying to link some databases up for a project I’m working on for my business. Here’s my below SQL I’m using for my PHP website.
$sql = "SELECT lname, fname, designation, agency, mobile, direct, email FROM contacts"; $result = $conn->query($sql);
At the moment my output goes like this to show what’s in each row using e.g. $lname=$lname["variable"]
if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { /* Print the row */ echo "<tr>"."<td>". $lname . "</td><td>" . $fname . "</td><td>" . $designation . "</td><td>" . $agency . "</td><td>" . $mobile . "</td><td>" . $direct . "</td><td><a href='mailto:" . $email . "''>". $email. "</a><br>"; } }
I have another “clients” table, where each agency has an id – one that matches with $agency at this point. What I want to do is post the corresponding companyname column to that id.
How would I modify my code to do so (and access the column/rows from the secondary table)?
Advertisement
Answer
If I’ve understood the question correctly, the answer should look something like
SELECT ct.lname, ct.fname, ct.designation, ct.agency, ct.mobile, ct.direct, ct.email, cl.company_name FROM contacts ct JOIN clients cl ON ct.agency = cl.id
The crux of it is the SQL JOIN clause.