mysql + php foreign key show data

Question

I have 5 tables.

advertisingcompany Table
  id PK
  name

placement Table
  id PK
  name

advertiser Table
  id PK
  name

location Table
  id PK
  name

ads Table
  ad_id PK
  size
  price
  advertisingcompanyId FG
  placementId FG
  advertiserId FG
  locationId FG

I can't figure out how to show foreign key data. I can get for example "advertisingcompanyId" or "placementId" but the problem is to get instead of id the name of advertisingcompany which is in advertisingcompany table or placement name which is in placement table.

I tried with that query:

$query = "SELECT ad_id, size, price, ads.advertisingcompanyId, ads.placementId, ads.advertiserId, locationId
            FROM ads
            INNER JOIN advertisingcompany ON ads.advertisingcompanyId = advertisingcompany.id
            WHERE ad_id = '$ad_id'";

Is that correct SQL query for data I wanted? If it is how can I add to that query I will get name for all foreign keys? How can I then show that data in php? Thanks

score 1 · Answer 1 · edited May 23 '17 at 11:56

Is that correct SQL query for data I wanted?

Yes.
If it is how can I add to that query I will get name for all foreign keys?

Specify the name columns that you wish to select and add addiitonal JOIN clauses:
```
SELECT ads.*,
       advertisingcompany.name AS adco,
       placement.name          AS placement
FROM   ads JOIN advertisingcompany ON ...
           JOIN placement          ON ...
-- etc.
```
Note that you may wish to use outer joins if some of the foreign keys may be NULL.
How can I then show that data in php?

Connect to MySQL, execute the query, then loop over the resultset:
```
$dbh = new PDO("mysql:dbname=$dbname;charset=utf8", $username, $password);
$qry = $dbh->query($query);
while($row = $qry->fetch()) print_r($row);
```
Please be careful to avoid SQL injection attacks by using prepared statements into which you pass user input as parameters that do not get evaluated for SQL! If you don't know what I'm talking about, read the story of Bobby Tables.

score 0 · Answer 2 · answered Oct 12 '12 at 11:42

SELECT ad_id, size, price, 
       c.name as company_name, 
       p.name as placement_name, 
       a.name as advertiser_name, 
       locationId
FROM ads
INNER JOIN advertisingcompany c ON ads.advertisingcompanyId = c.id
INNER JOIN placement p ON ads.placementId = p.id
INNER JOIN advertiser a ON ads.advertiserId = a.id
WHERE ad_id = '$ad_id'

mysql + php foreign key show data

2 Answers2