Jump to content


Photo

Problem with JOINs or GROUP BYs I think, I'm fairly new to MySQL


  • Please log in to reply
4 replies to this topic

#1 nickholt1972

nickholt1972
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationBury, Lancs, UK

Posted 23 September 2006 - 04:09 PM

THE PROBLEM: I'm developing an online shop using PHP and MySQL. I've got products and each product might have different sizes, colours, etc
I have two tables: products and productdetails

When i list these in my shop I want it to say (e.g.)

Product 1:
Colour: white, Size: small
Colour white, Size: large


But what i'm getting is

Product 1:
Colour: white, Size: small

Product 1:
Colour white, Size: large


This is my SQL Query and the PHP to output it:

$result = @mysql_query('SELECT
products.id, products.productid, products.productname,
productdetails.id, productdetails.size
FROM productdetails RIGHT JOIN products ON products.productid = productdetails.productid');

// Display the text of each entry in a table
  while ($row = mysql_fetch_array($result)) {
  echo '<div id="newproduct"><table border="1">' .
'<tr><td>' . $row['id'] . '</td><td>' .
$row['productid'] . '</td><td>' .
$row['productname'] . '</td></tr>' .
'<tr><td>' . $row['size'] . '</td></tr></table></div>';
}

I'm sure its very simple, it usually is. Any help would be greatly appreciated.
Thanks,
Nick
Nick Holt - Future Guru

#2 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 23 September 2006 - 04:25 PM

This i believe is one way:

SELECT
  products.id, products.productid, products.productname,
  productdetails.id, productdetails.size
  FROM productdetails, products WHERE products.productid = productdetails.productid

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 03:50 AM

Well, if you flip the tables and do a left JOIN, your first idea will work... but you'll have to have a seen hash.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#4 nickholt1972

nickholt1972
  • Members
  • PipPipPip
  • Advanced Member
  • 30 posts
  • LocationBury, Lancs, UK

Posted 25 September 2006 - 11:07 AM

ok, i have 2 questions in reply to that...

firstly,
are you saying there's a difference between

table1 RIGHT JOIN ON table2

and

table2 LEFT JOIN ON table 1

and secondly,

what's a 'seen hash'?

Many thanks,
Nick.
Nick Holt - Future Guru

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 25 September 2006 - 01:36 PM

1 - LEFT JOIN is used by convention, where as RIGHT JOIN shouldn't even exist, and probably is only still around for compatability.

2 - You have to keep track of the products you've already "seen".
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users