[Resolved] [Resolved] Loop problem

[Shawazi]

Here is the code I am using. The comment lines says what I want to do.

			$presult = mysql_query("select * from products where fabid='$srow[id]' order by rand() limit 1");
			$prow = mysql_fetch_array($presult);
			$cats = explode("|",$prow[catid]) or die ("explode error");
			$z = 0;
			while($cats[$z] != "")
			{
				if ($cats[$z] != $catid)
				{
// go back and try to get another random product until it is of the right category (this is part of another loop where it goes along different fabrics and the pictures are not linked so I can't just point it at a specific product. the category IDs are delimited like 2|10|4 hence the need for explode)
				}
				z++;
			}

[crosbystillsnas]

not sure about this, but doesnt mysql_fetch_array() return a 2-dim-array...?

explode("|",$prow[0][catid]);

$z++;

[Shrike]

Use a while loop to step through each row in the database

while($prow = mysql_fetch_array($presult)){
  $cats = explode("|",$prow[catid]) or die ("explode error"); 
  $z = 0; 
  while($cats[$z] != "") { 
    if ($cats[$z] == $catid) { // Note this bit is changed to trigger _when_ the product is found
       displayProduct(); // Do whatever is needed to show the product
       exit;
    } 
    z++; 
}

This does seem a rather inefficient way to display a random product. Perhaps if the categories were available straight from the database, you could alter the SQL to select all products from the category and then just loop through those. Not sure if this is possible with the rest of your code set up though 🙂

[planetsim]

No mysql_fetch_array() doesnt return a 2d Array

2d Arrays are $myarray['index1']['index2'];

MySQL - mysql_fetch_array() returns a normal i guess 1D array $myarray['index1'];

Anyway Shawazi what you have looks like it will work, however you are only returning 1 result. You may want to query the whole Table

[bubblenut]

No it returns a mixed assoc and regular array.
SELECT one, two, three FROM table;
gives
Array (
[0] => 'oneValue',
['one'] => 'oneValue',
[1] => 'twoValue',
['two'] => 'twoValue',
[2] => 'threeValue',
['three'] => 'threeValue'
);

With regard to your problem it stems from poor design, having multiple values in one field is bound to cause problems, you should have another table, that's how relational databases work. However, if redesigning the database is not an option then we must find another way around. The easiest way (and probably the fastest, but I'll leave you to do the benchmarking) i think will be to do it in the SQL. Right, let's go.

You want one of the | seperated values in 'catid' to be equal to $catid and the 'fabid' to be equal to $srow['id'] (you should always place quotes around your array key's), let's see what we get.
SELECT * FROM products WHERE fabid='".$row['id']."' && catid REGEXP "(^||)\d+(||$)" order by rand() limit 1
Let's explain the regex
"(" : Group the first alternation (the | )
"^" : match the begining of the string
"|" : OR
"|" : Escaped special character means the literal string
")" : close the grouping
"\d" : A digit
"+" : one or more times
"(" : Group the second alternation
"|" : same
"|" : same
"$" : match the end of the string
")" : same
One thing you should not is that REGEX takes a lot more work than doing a straight equals (it takes even more than doing a LIKE) so you should do an explain on the query to get the stats on it and then try to optimize it as much as possible. Also, you should try to do it a similar way as you have started below and then benchmark the two because, depending on your data getting PHP to analyze it may be quicker.
HTH
Bubble

[Shawazi]

I solved it a different way but I will keep REGEX in mind for another time.

$c = 1;
		$presult = mysql_query("select * from products order by rand()");
				while ($prow = mysql_fetch_array($presult))
				{

				$cats = explode("|",$prow[catid]) or die ("explode error");
				$z = 0;
				#echo "prow[id]: $prow[name] | prow[fabid]: $prow[fabid] | srow[id] $srow[id]<br> ";
				while($cats[$z] != "")
				{
					#echo "catsz = $cats[$z]<br>";
					if ($cats[$z] == $catid)
					{
						$eligible = "$eligible|$prow[id]";
					}
					$z++;
					#echo "z++<br>";
				}
			}
			$eligible = substr($eligible, 1);
			$eligible = explode("|",$eligible);
	while ($srow = mysql_fetch_array($sresult))
	{
		$done = 0;
		$z = 0;
		unset($name);
		unset($fabid);
		unset($thumbloc);
		while($eligible[$z] != "" and $done < 1)
		{
			$presult2 = mysql_query("select * from products where id='$eligible[$z]'");
			$prow2 = mysql_fetch_array($presult2);
			if ($prow2[fabid] == $srow[id])
			{
				$name = $prow2[name];
				$fabid = $prow2[fabid];
				$thumbloc = $prow2[thumbloc];
				$done = 1;
			}
			$z++;
			#echo "z++<br>";
		}
		if (isset($name))
		{
// do the echoing junk
}