So i’m pretty new to sql and i’m trying to figure out how to connect two tables together.
I have a table named customers and a table named pets and i want to assign the pets to specific customers. I am able to assign them a customer value but only as the id, i can’t figure out how to take that id and change it to say, a customer name when i reference it back in a table that displays my data.
so for example in my customer table the
customer id = 10; customerName = "John Smith";
then i have the pets table
petId = 16; petName = Alfredo; customerId = 10;
Is there a way to reference that customerID = 10 back to the customer table from the pets table so I can pull the name of the customer instead of the id?
this is my code to display the table that list the pets query, where $row['customer']
I want to show the customer name, not the id.
Thanks
<?php $sql = "SELECT * from pets ORDER BY petName ASC"; echo "<table class='tableInfo' cellpadding='8'>"; echo "<tr><th>Pet Name</th><th>Owner</th><th colspan='2'>Action</th></tr>"; $result = mysqli_query($con, $sql); while($row = mysqli_fetch_array($result)){ echo "<tr>"; echo '<td>' . $row['petName'] .'</td>'; echo '<td>' . $row['customerId'] .'</td>'; echo '<td><a href="editPets.php?id=' . $row['id'] . '">Edit</a></td>'; echo '<td><a href="deletePets.php?id=' . $row['id'] . '">Delete</a></td>'; echo "</tr>"; } echo "</table>"; ?>
Advertisement
Answer
Yes hi there, you can definitely do that with an inner join:
select * from pets join customers on pets.customerId = customers.customerId order by petName
It sounds the query may be returning an error. Perhaps print the error with:
$res = mysqli_query($con, $sql) or die ('Query failed: ' . mysqli_error($con)); while ($row = mysqli_fetch_assoc($res)) { // Do something with row }