Array help...I think?

[jwwceo]

I have made a query in MySQL that returns too many rows. It returns many rows for each product_id because theres quite a few many-to-many relationships. I'm ok with this...I've asked on a bunch if boards and nobody can think of an easy way to fix it....so I've decided to run the query with a DISTINCT column, which will return me unique rows but nothing else from the table I need.

I am hoping to save the product_ids as an array and then pass the values into the kind of query I need, which will give me all the data to populate my page.

but I'm not exactly sure how to do this...

if I have an array say,  $array. that contains all the product_id's I need.. how would  I go about inserting that into the while statement that I use to populate my page. That is, how do I advance the array counter each time the while loop passes through...

I hope this makes sense....

[Caesar]

Sounds like poor database design.

Can you possibly post any code that we can look at?

[jwwceo]

Heres the code that returns the table with too many rows...if that's what you're asking for...

It's a query that searches multiple tables in a master search box on a site...keywords, colors, and a few fields from a shirt table... the problem is that if I enter a color...it returns a row for each color, and vice versa...where all I need is one row per shirt_id....kinda complicated I think....

I was pretty much resigned to just requesting the DISTINCT shirt_id's and manipulating that...

[code]SELECT *

FROM shirts
INNER JOIN shirtcolors on shirtcolors.shirt_id = shirts.shirt_id
INNER JOIN colors ON colors.color_id = shirtcolors.color_id
INNER JOIN shirtkeywords ON shirtkeywords.shirt_id = shirts.shirt_id
INNER JOIN keywords ON shirtkeywords.keyword_id = keywords.keyword_id

WHERE
(
colors.color = '$query'
OR keywords.keyword like '%$query%'
or shirts.name like '%$query%'
or shirts.site like '%$query%'

)
AND active='true'"[/code]

[HuggieBear]

If you're going to go the selecting distinct way, I'd do it like this....

[code]
<?php
  $sql = "SELECT DISTINCT product_id FROM table_name"; // Initial select to get your distinct ID's
  $result = mysql_query($sql);
  while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
      $in .= $row['product_id'] . ","; // Concatenate the ID's to the end of the string, followed by a comma
  }
  $in = rtrim($in, ","); // Remove the last comma

  $sql2 = "SELECT column_name, column_name2, column_name3, FROM table_name WHERE product_id IN ($in)"; // SQL with ready-build 'IN' list
?>
[/code]

Regards
Rich