How to display value against foreign key from parent table?

Question

I have 2 tables makers and cars.

makers have 2 columns make_id & make.make_id of this table is used as foreign key

in cars table.cars table have 3 columns car_id,make_id,price.

Now i want to show the values against make_id in makers table. Now its not displaying value it just display id .

<?php
        $conn=mysqli_connect("localhost","root","","practice");
        if($conn-> connect_error){
        die("Connection field:". $conn-> connection_error);
    }$sql="SELECT car_id,make_id,price from cars ";
    $result=$conn->query($sql);
   if($result->num_rows>0){
   while($row=$result->fetch_assoc()){
   echo"<tr><td>".$row["car_id"]."</td><td>".$row["make_id"]."</td> 
   <td>".$row["price"]."</td></tr>";
   }
   echo"</table>";
   }else {
   echo"0 result";
   }
    $conn->close();
        ?>

As an aside: Rather than calling `$conn-> connect_error` twice, just check `!$conn` in the condition. Also, `if($result->num_rows>0){` will work without `>0`. — mickmackusa, Aug 25 '18 at 10:09
Possible duplicate of [How can I query using a foreign key in MySQL?](https://stackoverflow.com/questions/12483109/how-can-i-query-using-a-foreign-key-in-mysql) — mickmackusa, Aug 25 '18 at 10:17

Chukwuemeka Inya · Accepted Answer · 2018-08-25T10:50:26.650

1

Change your query to this:

$sql = "SELECT c.car_id, m.make, c.price 
        FROM cars AS c JOIN makers AS m ON c.make_id = m.make_id ORDER BY c.car_id";

You are working with two tables (cars and makers) that are connected via a foreign key. So, you have to use the JOIN query to select columns from both tables and map the foreign key on cars table to the primary key on makers table (in your case, make_id on cars table and make_id on makers table) that match on both tables using the ON keyword.

Your new code should now look like this:

<?php
    $conn = mysqli_connect("localhost", "root", "", "practice");
    if ($conn->connect_error) {
        die("Connection field:" . $conn->connection_error);
    }
    $sql = "SELECT c.car_id, m.make, c.price FROM cars AS c JOIN makers AS m ON c.make_id = m.make_id ORDER BY c.car_id";
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            echo "<tr><td>" . $row["car_id"] . "</td><td>" . $row["make"] . "</td>  // Notice that I changed $row['make_id'] to $row['make']
       <td>" . $row["price"] . "</td></tr>";
        }
        echo "</table>";
    } else {
        echo "0 result";
    }
    $conn->close();
?>

edited Aug 25 '18 at 10:50

answered Aug 25 '18 at 09:51

Chukwuemeka Inya

2,575
1
17
23

it worked thks a little problem but i will handle now – Zeeshan Haider Aug 25 '18 at 10:02
Did you change $row['make_id'] to $row['make'] ? – Chukwuemeka Inya Aug 25 '18 at 10:04
Good one. Just a side note... You might want to always name the id (primary key) column of your table as 'id'. For example, you have 'car_id' as the primary key in your car table. It's better that in future projects, you name it 'id' and then use 'car_id' if you intend to use it as a foreign key in other tables. In a nutshell, Your id(primary key) column should follow the 'id' naming convention...while your foreign keys should follow...'table-name_id' convention. Do you understand?... Pls don't forget to accept the answer. Thanks – Chukwuemeka Inya Aug 25 '18 at 10:24
why its not showing rows according to car_id values in cars table infect its showing rows according to value against foreign key in makers table. – Zeeshan Haider Aug 25 '18 at 10:36
like if BMW is stored against car_id 5 then it should appear in 5th row but as it is saved in 1st row in makers table so it is displaying on 1st row in browser – Zeeshan Haider Aug 25 '18 at 10:38
Please answer these questions...How many cars do you have on your cars table? How many makes do you have on your makers table? Do all your cars on your cars table have make_id? How many results are returned? – Chukwuemeka Inya Aug 25 '18 at 10:41
in make column of makers table i stored 5 values(5 car names).then i use foriegn key and link only 4 values in make column of makers to the cars table – Zeeshan Haider Aug 25 '18 at 10:45
I have edited the query. I added an order directive. It should order it properly now. Visit https://www.w3schools.com/sql/default.asp to learn more about SQL. – Chukwuemeka Inya Aug 25 '18 at 10:51
hy Chukwuemeka Inya , are u there.I need some help...? – Zeeshan Haider Aug 29 '18 at 05:23
Yeah Zeeshan...but I'm transiting to work. Let's chat in 45 mins. – Chukwuemeka Inya Aug 29 '18 at 05:27
Can you help me on this?I am developing one web application and android app for two restaurants.i have developed db on xampp and created php web application using Sublime text.Now i want to connect web application to android and want to show db on android using API.I worked with android studio and sublime text before but not on API yet.Please help me by telling how to create API on sublime using its PUT,GET,DELETE,POST request.and how to call this API in android.will be very thankful.here is code to show 1 table of menu .you just guide on 1 then i will manage on others. – Zeeshan Haider Aug 29 '18 at 06:26

How to display value against foreign key from parent table?

1 Answers1