Page 1 of 1
Looping Problem
Posted: Tue Mar 02, 2010 2:40 am
by jakeneuman
hi guys, i need some help for this problem. I made a query in database to display our product numbers with corresponding category, but the problem is when i display the category. Like below sample.
Product 1
cat 1
cat 2
cat 3
cat 4
cat 5
Product 2
cat 6
cat 7
cat 8
cat 9
cat 10
supposedly the Product 2 category will start at cat 1, cat 2, cat3, cat4, cat5 since it is another product.
anyone can hlp me on this?
thanks
Re: Looping Problem
Posted: Tue Mar 02, 2010 3:20 am
by JakeJ
I can't answer your question because I have no idea how your database is formed.
It SHOULD be formed this way though:
categories:
id (primary key)
name
products:
id (primarykey)
category_id (categories.id)
This way you could query products AND the proper categories. This is assuming that Category 1 can be assigned to multiple products. If each product has it's own set of categories, you have an entirely different problem and need to rethink your data structure.
Re: Looping Problem
Posted: Tue Mar 02, 2010 3:27 am
by jakeneuman
yup thats the design of my database. but my problem is in displaying the result,
Re: Looping Problem
Posted: Tue Mar 02, 2010 3:45 pm
by JakeJ
You need two queries and two loops.
The first query will query all of your products.
Code: Select all
<?php
While ($row = mysql_fetch_array($productsqry) {
Echo $row['product'],'<br>';
While ($subrow = mysql_fetch_array($categoriesqry) {
If ($row['category_id'] == $subrow['id']) {
Echo $subrow['name'],'<br>';
}
}
?>
Re: Looping Problem
Posted: Tue Mar 02, 2010 5:56 pm
by jakeneuman
i did this looping but it would display like this
Product 1
cat1
cat2
cat3
Product 2
cat4
cat5
cat6
what i would like to display is like this
Product 1
cat1
cat2
cat3
Product 2
cat1
cat2
cat3
since it belong to different product it would reset the counter and start counting to 1.
Re: Looping Problem
Posted: Tue Mar 02, 2010 6:26 pm
by JakeJ
Post your code.
Re: Looping Problem
Posted: Tue Mar 02, 2010 6:55 pm
by jakeneuman
this is my code
Code: Select all
<?php
$productcount = 0;
$get_product = mysql_query("Select * FROM `product` WHERE product_id='$prodid'");
if(mysql_num_rows($get_product) > 0){
while($myproduct = mysql_fetch_array($get_product)){
$productcount = $productcount + 1;
echo'<tr><td class="cell1" width="50%" valign="top">';
echo'<table width="100%" cellspacing="1" cellpadding="4" border="0" class="border"><tr><td class="cell1" width="30%">Product No.</td>
<td class="cell1" width="30%">'.$myproduct['product_id'].'</td></tr>';
$category_id = $myproduct['product_id'];
$get_category = mysql_query("Select * FROM category WHERE cat_id='$category_id'");
if(mysql_num_rows($get_category) > 0){
while($mycategoryl = mysql_fetch_array($get_category)){
$catcount = $catcount + 1;
echo'<tr><td class="cell1" width="30%"><center>Category $nbsp;['.$catcount.']</center></td><td class="cell1" width="30%">'.($mycategory['category_name']).'</td></tr>';
}
}
?>
Re: Looping Problem
Posted: Tue Mar 02, 2010 8:00 pm
by JakeJ
I made a mistake in my database design.
Try this instead.
products:
id
name
categories:
id
name
prodcat:
product_id
category_id
In the prodcat table, each entry of a product id and a category id will create a unique key.
Since a product can have multiple categories it makes sense that you have to have a many-to-many table to express that.
So now now you can execute a loop that runs through the products, and inside that loops, loop through the prodcat table and wherever prodcat.product_id == products.id display the category. This will of cousre mean that you have to do a join query so that you can pull the category name from the inside loop. Make sense now?
Re: Looping Problem
Posted: Tue Mar 02, 2010 8:06 pm
by jakeneuman
let me try it..

thansk bro. ill give you feedback later
Re: Looping Problem
Posted: Tue Mar 02, 2010 8:08 pm
by JakeJ
BTW - There is NO need to actually count the rows to loop through them. The While loop will handle that all on it's own. The count is unnecessary unless you're displaying it for some other reason.