display related records from my database using php

Question

i want to display record related to a specific primary key based on the foreign keys in other tables. How to display records for that primary key in other tables using php??

for example:

table1

primary key 1: plate#1
primary key 2: plate#2
primary key 3: plate#3

table2

primary key 1: destination|route|revenue|plate# 1
primary key 2: destination|route|revenue|plate# 3

table3

primary key 1: diesel price|number of liters|plate# 1

primary key 2: diesel price|number of liters|plate# 3

I already created a page that will display all the data in table1. I want to display the data in table1 and table2 that are related to the data in table1 when I made the database they already had relationship with each other. My problem is just displaying the record related to table1. I want to display records for just plate#1, another for plate#2 and so on.

See my answer to this post. [Exploring data modelling](http://stackoverflow.com/questions/6170774/exploring-data-modelling-how-to-hobble-a-sensible-database-together/6170801#6170801) — Ibu, Dec 22 '12 at 07:07
my desired result is when clicking the plate number in the table it will go to a page called delivery and truckdelivery the delivery and fuel records related to the plate number — Justin Capuno Cuizon, Dec 22 '12 at 07:22

score 0 · Answer 1 · answered Dec 22 '12 at 07:54

0

You are probably looking to join tables to get the final results. Join allows you to essentially merge tables together and get a single result based on the selections. Google php mysql join table and look at some of the examples. Here's one for you: Join Tables

answered Dec 22 '12 at 07:54

MrTechie

1,797
4
20
36

i joined the table already but when i display it it shows all the joined data i just want the data that is for the specific plate number – Justin Capuno Cuizon Dec 22 '12 at 08:01
Make sure in your WHERE clause it's specifying just that plate number you wanted. – MrTechie Dec 22 '12 at 08:04
@JustinCapunoCuizon - would you like an example? – MrTechie Dec 22 '12 at 08:06
ahhhh so just the plate number i wanted tnx2 for the info – Justin Capuno Cuizon Dec 22 '12 at 08:07
how about i made a table for the plate and if i add a plate number it increments the table automatically i made the plate number inside the table into a link that will take them to another page called truckdelivery.php this is the code for the link href="truckdelivery.php?id_truck= i wanted to make a different result everytime i click another plate number – Justin Capuno Cuizon Dec 22 '12 at 08:10
if you have a page called truckdelivery.php and at the top of that page has say $truck_id = $_GET['id']; Then you can query the database to get just the data for that id/plate, and have it render on the page. – MrTechie Dec 22 '12 at 08:13
i dont know the get[id] part can u give me an example please mrtechie? – Justin Capuno Cuizon Dec 22 '12 at 08:14
mrtechie this is the code i made based in your suggestions xD http://stackoverflow.com/questions/14002831/i-dont-know-what-error-i-have – Justin Capuno Cuizon Dec 22 '12 at 12:37

MrTechie · Accepted Answer · 2012-12-22T08:34:25.677

0

Here's a crude example:

<?php
  $truck_id = mysql_real_escape_string($_GET['truck_id']);



 $sql_PK = "SELECT * FROM table1 WHERE id = '{$truck_id}'";
 $res_PK = mysql_query($sql_PK);
 $row_PK = mysql_fetch_assoc($res_PK);

 $truck_plate = $row_PK['plate'];



$truck_plate = mysql_real_escape_string($truck_plate);



$sql = "SELECT table2.plate, table2.destination, table2.route, table3.plate, table3.diesel_price, table3.num_of_liters FROM table2, table3 WHERE table2.plate = table3.plate AND table2.plate = '{$truck_plate}'";
$res = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($res);


    // this will give you the details from the following

        //TABLE2
        // plate
        // destination
        // route

        //TABLE3
        // plate
        // diesel_price
        // num_of_liters


?>

There's a couple of important things to remember. First is get in the habit of naming your fields without spaces in the database and use _ instead. so diesel price is diesel_price, etc. Secondly is to make sure that you are protecting yourself from any injections like I have shown here using the mysql_real_escape_string

So when someone clicks on truckdetails.php?plate=mrtchi it's going to query the database based on the plate number: mrtchi

edited Dec 22 '12 at 08:34

answered Dec 22 '12 at 08:21

MrTechie

1,797
4
20
36

ahhhhhh so how does this get[id] work?? what do i do to activate it to get the plate i want? – Justin Capuno Cuizon Dec 22 '12 at 08:23
on the say showtrucks.php page you need to loop over all possible plates from a table in the database. One you do the query do this: while($row = mysql_fetch_assoc($res)){ $plate = $row['plate']; echo '' . $plate . ''; } – MrTechie Dec 22 '12 at 08:27
mrtechie can primary keys also work like truckdelivery.php?id_truck= – Justin Capuno Cuizon Dec 22 '12 at 08:28
@JustinCapunoCuizon you can use whatever you want, but you just need to make sure that along the trail things match up. So if you start off with a primary key, you need to either make sure that primary key from table1 is in table2 and table3 as a reference. Or you need to extract the plate# and then go from there. – MrTechie Dec 22 '12 at 08:31
yes tnx i have already foreign key for my primary keys for the other tables so i will just join the other primary key and the foreign key and use it for the query tnx mtechie – Justin Capuno Cuizon Dec 22 '12 at 08:35
i tried modifying the code above it displays just the first record in the table that i included mrtechie – Justin Capuno Cuizon Dec 22 '12 at 11:35
not the record that is for the specific plate everytime i click a plate no it displays the same record the first record in the table – Justin Capuno Cuizon Dec 22 '12 at 11:44
and mrtechie i dont know what to echo out to see the records i just echo out $row_PK['delivery_details_destination'] and it displays the first record not every plate but the first record for all – Justin Capuno Cuizon Dec 22 '12 at 12:10

display related records from my database using php

2 Answers2