Jump to content

Array help...I think?


jwwceo

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.