Looping Query

[e1seix]

I'm all confuzzled with the following:-

 

Have code to search my database for all my products

$go=mysql_query("SELECT * FROM fragrances WHERE avail='true'")or
die(mysql_error());
$numrow=mysql_num_rows($go);

while($row = mysql_fetch_array( $go )) {
print $row[brand]." ".$row[title]." ".$row[sku]."<br />";
}

 

which returns something like

American Crew Classic Fragrance 2206
American Crew Classic Fragrance 2245
Puig Quorum 2209
Aramis Aramis - Original 2210
Aramis Aramis Life 2211
Burberry Brit for Men 2245
Burberry Burberry for Men 2217 ...etc

 

What I'm trying to do is separate all of these products that have the same sku number, in this case the 2nd and the 6th one. I can imagine it involves another loop within the while loop that's already there.

 

From here I need to establish which one has the cheaper price and use the UPDATE function to determine which one to update to "avail" and which one to show as "NULL" so only the cheaper product is showing. I can manage the UPDATE bit fine, but any help going with the other part.

 

I do know I take the mick sometimes with my asking for coding help, but it really is appreciated every time dudes.

 

Thank you,

[PHP Monkeh]

Are you just wanting to only show products of a specific number?  Or just have them all grouped together?

 

If you're just wanting them grouped together you could just add an ORDER BY to your query, and order it by the sku number (either descending or ascending of course).

[e1seix]

No, that's not it mate. Cheers though. I'll explain a bit better.

 

Let's just say the first piece of code returns

American Crew Classic Fragrance 2206
American Crew Classic Fragrance 2245
Puig Quorum 2206
Aramis Aramis - Original 2210
Aramis Aramis Life 2210

 

Both Aramis Aramis - Original 2210 & Aramis Aramis Life 2210 have the same sku number of 2210 as do American Crew Classic Fragrance 2206 & Puig Quorum 2206 both with 2206.

 

What I'm looking to do is implement a loop of some kind that picks up when a sku is used by more than one product and only display these products alone so my loop would then return

American Crew Classic Fragrance 2206
Puig Quorum 2206
Aramis Aramis - Original 2210
Aramis Aramis Life 2210

 

leaving out American Crew Classic Fragrance 2245 because no other result shares 2245 as its sku.

 

My idea is to then insert an if statement to deduce which of the products sharing a sku has the lower price.

 

Any ideas?

 

[PHP Monkeh]

Do you have a table that contains all sku numbers by any chance?

[sasa]

try

SELECT * FROM `fragrances` WHERE sku IN(select sku from fragrances WHERE avail='true' GROUP BY sku HAVING COUNT(*) > 1) AND avail='true' ORDER BY sku

[PHP Monkeh]

If that works that clears something up for me!   I was going to suggest using GROUP BY but I couldn't think of how to implement the > 1 part, cheers from me anyway sasa 

[sasa]

i hope that works

[e1seix]

That works great, cheers.

 

Can I ask one last question cos it's REALLY bugging me now and I won't be able to sleep until I've solved it. What is wrong with this code. order doesn't update with anything at all. I don't get it.

 

$rally=mysql_query("SELECT * FROM fragrances WHERE sku='2900' ORDER BY pound")or
die(mysql_error());
$numrow5=mysql_num_rows($rally);

$i=1;
$sku=$row[sku];

while($row = mysql_fetch_array( $rally )) {

print $i.". ".$row[brand]." ".$row[title]." ".$row[sku]." ".$row[order]." ".$row[pop]." ".$row[pound]."<br />";
mysql_query("UPDATE fragrances SET order='$i' WHERE sku='$sku'");
print $i.". ".$row[brand]." ".$row[title]." ".$row[sku]." ".$row[order]." ".$row[pop]." ".$row[pound]."<br />";

$i++;
}

 

Cheers 

[sasa]

you set $sku=$row[sku]; before $row is set

it's mine $sku='' (NULL)