Trying to connect two tables together by using id value from one table on the other

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>";     
    ?>

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
}

Advertisement

Answer