I have now the next mind boggling task in front o me and it comes in nested while loops. Explanation:
A customer performs a search on my products and gets shown his search result. On the search result page,
I displaya list of products through a while loop.
Now, I have another table that contains thats basically holds info in what subcategory each product is.
1 product might be listed in 2 or 3 categories etc. I would like to be able to show this to the customer.
It seems that the subcategory loop doubles, starting on the Product 1 and seems to double again for each other product.
Has anyone got an idea on how to make this work?
The problem still is that Product 2 should only show 2 subcategories and the rest should show their assigned subcategories, meaning that Product 1 & Product 3 have completely different subcatgories assigned.
I think you need to check your 'e2o_products_subcategories' table. $prdROW['psku'] is the only thing in the WHERE and it is different for each product.
0
Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Overall you will find that programming is easier to understand if you indent your control structures according to a coding standard. I use a modified ZEND standard that calls for 4 spaces of indentation for the stuff inside the curly braces. It makes the code easy to read!
0
slickipAuthor Commented:
Hi all and thanks for all the replies.
I managed, after reading and more reading, to find a workaround myself. The initial search query to my products table caused some issues. Explaining why exactly, I cannot, as I am not so good in PHP, yet.
What I did is basically the following: The initial search query to my products table gave me my required psku. I then ran a loop for this query as:
while ($searchROW = mysql_fetch_array($searchRLT)){$types[] = "`psku` = '{$searchROW['psku']}'";$searchSQL = implode(" OR ", $types). ' ';}//Inside this loop I basically formed a clean string for my new query to my products table:$finalPSKUSQL = "SELECT * FROM e2o_products WHERE $searchSQL";$finalPSKURLT = mysql_query($finalPSKUSQL) or die("Error: ".mysql_error()); # output error message;while($prdROW = mysql_fetch_array($finalPSKURLT)) {$prdROW[psku'] .' | '.$prdROW['panme'].' | '.$prdROW['pdescription'];echo 'Listed in: ';$subcatSQL="SELECT * FROM e2o_products_subcategories WHERE psku = '{$prdROW['psku']}'"; $subcatRLT = mysql_query($subcatSQL);while($subROW = mysql_fetch_array($subcatRLT)){echo $subROW['subcategory'];}}
Thanks for everyone involved. If its OK with DaveBaldwin & Ray_Paseur, I will split the points equally? Would this be the right way do to in an event were the Asker finds his/her own solution?
It seems that the subcategory loop doubles, starting on the Product 1 and seems to double again for each other product.
Has anyone got an idea on how to make this work?
The reason for this phenomenon was that the $listed variable had fields added to it over and over again (line 18) without ever being reset. It appears to me that if you set it to NULL before the inner query you would get reasonable output without the "double again" effect.
I'm not particularly worried about the points - I have enough points to orbit Saturn. But I do wish that you would indent your code. It will help you find the logic errors, and I believe you still have a logic error in the way the script constructs the WHERE clause. This is how I believe the most recent code snippet should read (not sure - I cannot test it without your data base and we never got the CREATE TABLE statements).
while ($searchROW = mysql_fetch_assoc($searchRLT)){ $types[] = "`psku` = '{$searchROW['psku']}'";}$searchSQL = implode(" OR ", $types) . ' ';$finalPSKUSQL = "SELECT * FROM e2o_products WHERE $searchSQL";$finalPSKURLT = mysql_query($finalPSKUSQL) or die("Error: $finalPSKUSQL<br/>" . mysql_error()); while($prdROW = mysql_fetch_assoc($finalPSKURLT)) {// $prdROW[psku'] .' | '.$prdROW['panme'].' | '.$prdROW['pdescription']; THIS STATEMENT DOES NOTHING echo 'Listed in: '; $subcatSQL = "SELECT * FROM e2o_products_subcategories WHERE psku = '{$prdROW['psku']}'"; $subcatRLT = mysql_query($subcatSQL) or die("Error: $subcatSQL<br/>" . mysql_error()); while($subROW = mysql_fetch_assoc($subcatRLT)) { echo $subROW['subcategory']; }}
See below CREATE statements for products table and products_subcategories table:
CREATE TABLE `e2o_products` ( `id` int(7) NOT NULL AUTO_INCREMENT, `maincategory` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `subcategory` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `pname` varchar(130) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `pseoname` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `pbrandname` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `pbrandnameseo` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `pprefix` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `psku` varchar(14) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `pdate` datetime NOT NULL, `pdateunix` varchar(12) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `modpdate` datetime NOT NULL, `modpdateunix` varchar(12) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `psize` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `psizequantifier` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `pintro` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `pdescription` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `expdate` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `expdateoption` int(1) NOT NULL, `pweightdisp` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `ppriceretailuk` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '', `newppriceretailuk` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `prdsavingsuk` varchar(6) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `ppriceretailus` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `newppriceretailus` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `prdsavingsus` varchar(6) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `isdiscount` int(1) NOT NULL DEFAULT '0', `prdlargimg` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `prdmedimg` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `prdsmlimg` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `pqty` int(2) NOT NULL DEFAULT '0', `pstatus` int(1) NOT NULL, `psellcount` int(10) NOT NULL DEFAULT '0', `isfront` int(1) NOT NULL, `isfeatured` int(1) NOT NULL, `istopseller` int(1) NOT NULL, `instock` int(1) NOT NULL, PRIMARY KEY (`id`), FULLTEXT KEY `pdescription` (`pdescription`,`pname`,`pintro`,`psku`)) ENGINE=MyISAM AUTO_INCREMENT=31 DEFAULT CHARSET=utf8
CREATE TABLE `products_subcategories` ( `id` int(11) NOT NULL AUTO_INCREMENT, `psku` varchar(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `subcategory` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `maincategory` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, `pstatus` int(1) NOT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=63 DEFAULT CHARSET=utf8