Solved

Nested Loop nightmare

Posted on 2012-04-04
9
275 Views
Last Modified: 2012-06-27
Hi again.

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.

Example.

Product 1
Product 1 Description, price etc
Listed in: Sub-catA, Sub-catB

Product 2
Product 2 Description, price etc
Listed in: Sub-catC, Sub-catE

etc.

My current code looks like this:

<?php
#
$searchSQL = "SELECT *,MATCH(pdescription, pname, pintro, psku) AGAINST ('{$searchTermDB}') as Relevance FROM products WHERE 
MATCH(pdescription, pname, pintro, psku) AGAINST ('{$searchTermDB}' IN BOOLEAN MODE)
ORDER BY pname DESC LIMIT ".$class->offset.", ".$class->entries_per_page;"";

$searchRLT = mysql_query($searchSQL) or die("Error: ".mysql_error()); # output error message

while($prdROW = mysql_fetch_array($searchRLT)) {

echo $prdROW['psku'].' | '.$prdROW['pname'].' | '.$prdROW['pdescription'].'<br />';
echo 'Listed in: ';
#Running my 2nd query to the products_subcategories table
$subcatSQL = "SELECT subcategory FROM e2o_products_subcategories WHERE psku = '{$prdROW['psku']}'"; 
$subcatRLT = mysql_query($subcatSQL) or die("Error: ".mysql_error()); # output error message;
while($subcatROW = mysql_fetch_array($subcatRLT))
{
$listed .=  $subcatROW['subcategory'];
}
echo $listed;
?>

Open in new window


Assuming we show 3 products on the search result page, I get the following for the sub categories

001 | Product 1 | Product 1 description here
Listed in: hair-and-lashes-care | cosmetics |

002 | Product 2 | Product 2 description here
Listed in: hair-and-lashes-care | cosmetics | hair-and-lashes-care | cosmetics |

003 | Product 3 | Product 3 description here
hair-and-lashes-care | cosmetics | hair-and-lashes-care | cosmetics | hair-and-lashes-care | cosmetics |


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?

Again, any help is greatly appreciated.

Thank you.
0
Comment
Question by:slickip
  • 5
  • 2
  • 2
9 Comments
 
LVL 83

Assisted Solution

by:Dave Baldwin
Dave Baldwin earned 150 total points
ID: 37809265
Somewhere about line 15 you need to set $listed = ""; so you can start over each time.
0
 

Author Comment

by:slickip
ID: 37809339
Hi DaveBaldwin,

Thanks for the reply. When adding the $listed=""; on line 15, the duplication increase stops, but shows the data as such:

001 | Product 1 | Product 1 description here
Listed in: hair-and-lashes-care | cosmetics |

002 | Product 2 | Product 2 description here
Listed in: hair-and-lashes-care | cosmetics |

003 | Product 3 | Product 3 description here
Listed in: hair-and-lashes-care | cosmetics |

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 hope this will be possible to achieve.

Thanks.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 37809402
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 109

Accepted Solution

by:
Ray Paseur earned 350 total points
ID: 37809728
Please post the CREATE TABLE statements for these tables.

And before line 16, add this statement
$listed = NULL;

Open in new window

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
 

Author Comment

by:slickip
ID: 37810162
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'];
}
}

Open in new window


I hope this makes sense, but it works great now!

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?

Regards to all.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 37810301
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'];
    }
}

Open in new window

0
 

Author Comment

by:slickip
ID: 37810418
Hi Ray.

Thanks for the reply. I totally agree with you and will indent my code by adding the
$listed = NULL;

Open in new window

on my backup file, on line 16.

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

Open in new window


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

Open in new window



I hope this helps.

Thank you.
0
 

Author Comment

by:slickip
ID: 37810467
Hi Ray,

I have followed your advice and amended my code. After adding the
$listed = NULL;

Open in new window

before line 16 I got the result that I was after.

Everything works exactly they way I wanted it. Perfect!
I appreciated the afford in explaining. Greatly appreciated.

Best regards.
0
 

Author Closing Comment

by:slickip
ID: 37810477
Thank you to all parties involved.

Regards to all.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how can i gnupg encrypt a PDF document? 12 32
echo paypal data on screen 5 51
php extract($_REQUEST) 5 49
Showing random records from database 10 37
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question