p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   PHP Databases (http://p2p.wrox.com/forumdisplay.php?f=97)
-   -   nested while loop doesn't loop (http://p2p.wrox.com/showthread.php?t=5927)

hosefo81 November 8th, 2003 05:41 AM

nested while loop doesn't loop
 
i has problem looping the second while Loop.It will only loop once.So i can only get the value for the first row for the second loop.Below are my problems area of my code.


while($row1 = mysql_fetch_array($result1))
{
    $cat_id= $row1["category_id"];
    echo " <tr>
        <td>$row1[category_name] </td>";

    while($row2 = mysql_fetch_array($result2))
    {
        if($cat_id==$row2["prod_cat"])
            echo " <td>$row2[Total] </td>";

    }
    echo" </tr>";
}




richard.york November 8th, 2003 03:56 PM

I'm not seeing where your nested loop is failing.

Let's see some more code. And can you explain what you expect this to do?

: )
Rich



:::::::::::::::::::::::::::::::::
Smiling Souls
http://www.smilingsouls.net
:::::::::::::::::::::::::::::::::

nikolai November 8th, 2003 04:08 PM

Well, look at what you're doing: in the first iteration of your outer loop, your inner loop will extract ALL the rows from the result set associated with $result2.

You don't do anything to modify, recreate, or reset $result2's result set in the second iteration, so mysql_fetch_array() will of course return FALSE because it's already returned all the result rows.

Be more specific with what you want to do and, if you can't figure out how to do it, we'll help you get there.

I've got a hunch that you can get everything you want with just one query and not have to deal with two queries and nested loops. If you'd like to know whether this is the case, describe your database tables and post your queries. Also, show us what YOU think the output SHOULD be. That'll help us write the code that produces what you want.



Take care,

Nik
http://www.bigaction.org/

hosefo81 November 9th, 2003 02:04 AM

Thanks. Please help me if the code can be changed to print the below table.I want to display total sales for the duration of five years from 1999 to 2003 grouped by product category.
As such, my first loop is to display the category name and second loop is to display all the values from 1999 -2000.
My display should be something like below but what i get is all the category name and values for oral care only.

    1999 2000 2001 2002 2003
Oral care 258 159 357 753 741
Skin care 321 456 789 120 325
Shampoo 963 741 258 657 314

Below are my database tables related to the question.
CREATE TABLE CATEGORY
 (
    CATEGORY_ID INT NOT NULL,
    CATEGORY_NAME VARCHAR(30) NOT NULL,
    PRIMARY KEY (CATEGORY_ID),
) ;

CREATE TABLE PRODUCT
(
    PROD_ID VARCHAR(5) NOT NULL,
    PROD_NAME VARCHAR(20) NOT NULL,
    PROD_DESC VARCHAR(20) NOT NULL,
    PROD_PRICE DECIMAL NOT NULL,
    PROD_CAT INT NOT NULL,
    PRIMARY KEY(PROD_ID),
    FOREIGN KEY(PROD_CAT) REFERENCES CATEGORY,
);

CREATE TABLE SALES
(
    SALES_ID INT NOT NULL,
    SALES_DATE DATE NOT NULL,
    CUST_ID VARCHAR(5) NOT NULL,
    EMP_ID VARCHAR(6) NOT NULL,
    PRIMARY KEY(SALES_ID),
    FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER,
    FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE,
);

CREATE TABLE SALES_DETAIL
(
    SALES_ID INT NOT NULL,
    PROD_ID VARCHAR(5) NOT NULL,
    QUANTITY INT NOT NULL,
    PRIMARY KEY (SALES_ID, PROD_ID),
);

<?php
include "common_db.inc";

$link_id = db_connect();
if(!$link_id) die (sql_error());

mysql_select_db("project", $link_id) or die (sql_error());

$query1 = "select category_id, category_name from category order by category_id ";

$result1 = mysql_query ($query1);

if(!$result1) error_message (sql_error());

$query2 = "select year(s.sales_date) as Year, p.prod_cat, sum((p.prod_price * sd.quantity)) as Total
       from product p, sales_detail sd, sales s, category c
      where sd.sales_id=s.sales_id and p.prod_id=sd.prod_id and year(s.sales_date) between '1999' and '2003'
      group by p.prod_cat, year(s.sales_date) ";

$result2 = mysql_query ($query2);

if(!$result2) error_message (sql_error());

$color1 = "aliceblue";
$color2 = "beige";
$row_count = 0;

echo "<table border =1 cellpadding=8 >";
echo "<tr bgcolor= gainsboro> ";
    echo " <td align=\"center\"> Year </td> ";
    echo " <td> 1999 </td>";
    echo " <td> 2000 </td> ";
    echo " <td> 2001 </td>";
    echo " <td> 2002 </td>";
    echo " <td> 2003 </td>";
echo " </tr> ";

while($row1 = mysql_fetch_array($result1))
{
    $row_color = ($row_count % 2) ? $color1 : $color2;
    $cat_id= $row1["category_id"];
    echo " <tr bgcolor=$row_color>
        <td>$row1[category_name] </td>";

    while($row2 = mysql_fetch_array($result2))
    {
        if($cat_id==$row2["prod_cat"])
            echo " <td>$row2[Total] </td>";
    }
    echo" </tr>";
    $row_count++;
}
echo "</table>";
?>


nikolai November 10th, 2003 02:53 PM

Yeah, I don't see any reason why you don't just get all the information in a single query. In fact, I can't think of any good way of using TWO queries and loops to do it. Since MySQL is a relational database, it deals with sets of data, with no specific order imposed on the result set unless you specify one. That said, there's really no guarantee that the data you're trying to output in the second loop is the data corresponding to the row being used in the first loop.

Actually, I take that back -- there is a way you can use two loops, but you'll make several queries:

$result1 = mysql_query($query1);
while($row1 = mysql_fetch_array($result))
{
    $query2 = "SELECT ... WHERE the data corresponds to something in $row1";
    $result = mysql_query($query2);
    while($row2 = mysql_fetch_array($result2))
    {
        ...
    }
}

But that's obviously pretty inefficient, since you're making an additional database query for each iteration of the loop.

Since the first query is only there to give you your category names, why not return the category name as part of the second query?


Take care,

Nik
http://www.bigaction.org/

hosefo81 November 12th, 2003 08:46 AM

Thanks! It works. i know it's not a good method but.....
      Actually i wanted to group the data according to a prod_cat such as cat1[], cat2[], cat3[]and so on because i need it to built graph using jpgraph. But i can't think of any other way than this. i use the second loop to check if the total belong to that product category.
      Could you please advice on other way to put the data according to prod_cat?



All times are GMT -4. The time now is 09:08 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.