Databases
StackOverflow2013
Postsdbo
POLoop products by category
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLoop products by category
    text
    copied!<p>I've managed to get this working, but I have a slight bug I cannot fix and hope somebody can help.</p> <p>I works fine if there is a product in the parent category however if the parent is empty even though the SQL returns the correct results the PHP loop repeats the data depending on how many products there are. ie. 4 products = 4 sets of results. But as I stated if there is a record in the parent category it works perfectly.</p> <pre><code> &lt;?php $_GET["category_id"]=$category_id; ?&gt; &lt;?PHP $sql = " SELECT DISTINCT JVPCX.category_id, JVP.product_id, JVP.product_error, JVP.product_exchange, JVP.product_sku, JVP.product_name, JVP.product_url, JVP.product_in_stock, jos_vm_category.category_name FROM jos_vm_product_category_xref JVPCX INNER JOIN jos_vm_product JVP ON (JVPCX.product_id = JVP.product_id) INNER JOIN jos_vm_category ON (JVPCX.category_id = jos_vm_category.category_id) WHERE JVPCX.category_id IN (SELECT jos_vm_category_xref.category_child_id FROM jos_vm_category_xref WHERE jos_vm_category_xref.category_parent_id =".$category_id.") OR (JVPCX.category_id =".$category_id.")"; $res = mysql_query($sql); $list = array(); while ( $r = mysql_fetch_object( $res ) ) { if ( ! isset( $list[ $r-&gt;category_name ] ) ) { $list[ $r-&gt;category_name ] = array(); } $list[ $r-&gt;category_name ][ $r-&gt;product_id ] = array( 'SKU' =&gt; $r-&gt;product_sku, 'Name' =&gt; $r-&gt;product_name, 'Prod_id' =&gt; $r-&gt;product_id, 'Prod_error' =&gt; $r-&gt;product_error, 'Prod_exchange' =&gt; $r-&gt;product_exchange, 'Prod_url' =&gt; $r-&gt;product_url, 'Prod_in_stock' =&gt; $r-&gt;product_in_stock ); } ?&gt; &lt;table width="70%" border="0"&gt; &lt;tr&gt; &lt;td align="center" valign="middle"&gt; Error: &lt;img src="alert.gif" title="Error" alt="Error" /&gt; &lt;/td&gt; &lt;td align="center" valign="middle"&gt; Exchange: &lt;img src="exchange.gif" title="Exchange" alt="Exchange" /&gt; &lt;/td&gt; &lt;td align="center" valign="middle"&gt; Information:&lt;img src="info.gif" title="info" alt="info" /&gt; &lt;/td&gt; &lt;td align="center" valign="middle"&gt; Stock:&lt;img src="stock.gif" title="Stock" alt="Stock" /&gt; &lt;/td&gt; &lt;td align="center" valign="middle"&gt; Buy:&lt;img src="cart-icon.png" title="Stock" alt="Buy" /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;?PHP echo("&lt;BR/&gt;"); foreach ($list as $catID =&gt; $products) { ?&gt; &lt;?php print($catID); echo("&lt;BR/&gt;"); ?&gt; &lt;table width="100%" border="1" class="product-head"&gt; &lt;tr&gt; &lt;th width="20%"&gt; &lt;?php echo("Part Number:"); ?&gt; &lt;/th&gt; &lt;th width="45%"&gt; &lt;?php echo("Description:"); ?&gt; &lt;/th&gt; &lt;th width="15%"&gt; &lt;?php echo("Information:"); ?&gt; &lt;/th&gt; &lt;/tr&gt; &lt;/table&gt; &lt;table width="100%" border="1" class="product"&gt; &lt;?php foreach ($products as $prodID =&gt; $data) { ?&gt; &lt;tr&gt; &lt;td width="20%"&gt; &lt;?php print($data['SKU']); echo("&lt;BR/&gt;"); ?&gt; &lt;/td&gt; &lt;td width="45%"&gt;&lt;?php print($data['Name']); echo("&lt;BR/&gt;"); ?&gt;&lt;/td&gt; &lt;td width="15%" valign="middle"&gt;&lt;?php if ($data['Prod_error'] == "Y"){ echo '&lt;img src="alert.gif" title="Error" alt="Error" /&gt;'; } if ($data['Prod_exchange'] == "Y"){ echo '&lt;img src="exchange.gif" title="Exchange" alt="Exchange" /&gt;'; } if ($data['Prod_url'] &gt; ""){ echo '&lt;a href="'. $data['Prod_url'] .'"&gt;'.'&lt;img src="info.gif" title="info" alt="info" /&gt;'.'&lt;/a&gt;'; } if ($data['Prod_in_stock'] &gt; "0"){ echo '&lt;img src="stock.gif" title="Stock" alt="Stock" /&gt;'; } ?&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;?php echo("&lt;BR/&gt;"); ?&gt; &lt;?php } ?&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload