Using php,mysql to query two tables

Question

My query look like this at the moment:

  $result  = mysql_query("SELECT * FROM promo JOIN items ON promo.id = items.promo_id WHERE promo.id='$promo_id'") or die('Error : ' . mysql_error()); 
  while($row = mysql_fetch_array($result))
  {
  echo "<p>".$row['title']."</p>";
  echo "<p>".$row['description']."</p>";
  echo "<p>".$row['filename']."</p>";
  }

I'd like to end up with the following:

Title of Promo 1
This is Promo 1 Description, isn't it great?!
filename_1.jpg
filename_2.jpg and however many files are linked to this promo listed...

However, based on the query above I get:

This is Promo 1 Description, isn't it great?!
filename_1.jpg
This is Promo 1 Description, isn't it great?!
filename_2.jpg

So it repeats (I can sort of see why) and doesn't pull in the 'title' of the promo at all! Can anyone recommend whether the problem is with my JOIN or my PHP? Many thanks for your help :)

If your selecting on promo id it would only find the promo that is linked to that id, my guess is you have 2 files joined to promo 1 — Luke, Aug 10 '10 at 12:12
Can we please see the structures of the `promo` and `items` tables? — Bobby, Aug 10 '10 at 12:12
Why, I just wrote a really lengthy question and answer that covers this exact [sort of question](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) which I hope will help to clarify how joins work on tables and how to get information from multiple tables in your database! — Fluffeh, Sep 18 '12 at 14:03

score 0 · Answer 1 · answered Aug 10 '10 at 12:22

0

Don't use an ***** in your queries but name the columns you need and apply proper aliasses.

answered Aug 10 '10 at 12:22

Frank Heikens

117,544
24
142
135

score 0 · Accepted Answer · answered Aug 10 '10 at 12:53

Probably description and filename appears because these names exists on only one of the tables but title is on both tables.

If you use the query:

SELECT 
     * 
FROM 
     promo JOIN items ON promo.id = items.promo_id WHERE promo.id='$promo_id'"

The names of the fields are need to be in the way table.field . it's very in useful to expecify the names of the fields you need to get to help you and to fetch only the data you need and not all the table. This makes the method faster to retrive the rows and prevent problems with the names.

Also you can specify the name of a column wit the sentence "AS" Example:

SELECT 
   promo.title AS title,
   promo.description AS description,
   items.filename AS filename
FROM 
    promo JOIN items ON promo.id = items.promo_id WHERE promo.id='$promo_id'"

And you get a table like (i setted imaginary data on it):

title | description | filename
-------------------------------
title1|description1 | filename1
title1|description1 | filename2
title2|description2 | filename3

Tips:

If you got more than one item per promo all data is repeated on each row.
- You need to queries to display all items per promo without repeat the promo data,make more complex query or add some code.
If you don't have any item for the promo the promo is does not appears into the list.
- You need to use list al promos and by each list its own items to show the promos without items or use an OUTER JOIN query.

Using php,mysql to query two tables

2 Answers2