Multiple queries

[tomhoad]

I am not too experienced with MySQL and I've read a few tutorials on JOIN and UNION but can't get my head around it to be honest - can someone help me out please? I'm fairly sure I need to combine these queries to get my desired result...

 

I have 2 tables, one called promo and another called items. What I want is...

 

This is a Promotional [title]

I am the promotional description for this promo! [description]

Items in this promotional are:

- Item 1

- Item 2

- Item 3

 

Code so far:

 

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

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

 

Using MySQL 5. Thanks for your help 

[kgenly]

I think the mySQL you are looking for is something like...

 

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

 

The important part is ON promo.id = item.promo_id. This tells mysql to take your item table and merge the items together with their promotions, but only where the promo_id field matches an id of a promotion on the promo table.

[tomhoad]

Thanks for that. I have made some progress but I am still struggling slightly. Believe me I've worked on this for a few hours now and can't crack it!!! Maybe I'm just too simple!

 

My php now looks like so:

 

		
$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['promo.title']."</p>";
	echo "<p>".$row['description']."</p>";
	echo "<p>".$row['filename']."</p>";
	}

 

The outcome is:

 

Description test 1

 

filename.jpg

 

Description test 1

 

filename_2.jpg

 

I don't seem to be able to access the Promo Title at all ?

 

Also it's repeating and I can sort of see why from the PHP - what do you recommend to just get my desired outcome (OP).

[sasa]

try to change $row['promo.title'] to $row['title']

[tomhoad]

Still the same - I have a 'title' column in both tables, perhaps that's where the problem lies?

[fenway]

Still the same - I have a 'title' column in both tables, perhaps that's where the problem lies?

Probably -- use a column alias.