Jump to content


Photo

Array help...I think?


  • Please log in to reply
3 replies to this topic

#1 jwwceo

jwwceo
  • Members
  • PipPipPip
  • Advanced Member
  • 212 posts

Posted 07 September 2006 - 10:47 PM

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

#2 Caesar

Caesar
  • Members
  • PipPipPip
  • Advanced Member
  • 1,025 posts

Posted 07 September 2006 - 10:58 PM

Sounds like poor database design.

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

#3 jwwceo

jwwceo
  • Members
  • PipPipPip
  • Advanced Member
  • 212 posts

Posted 07 September 2006 - 11:07 PM

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

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'"


#4 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 07 September 2006 - 11:35 PM

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

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

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users