Jump to content

Archived

This topic is now archived and is closed to further replies.

jwwceo

Array help...I think?

Recommended Posts

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....

Share this post


Link to post
Share on other sites
Sounds like poor database design.

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.