Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > PHP/MySQL > PHP Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
PHP Databases Using PHP in conjunction with databases. PHP questions not specific to databases should be directed to one of the other PHP forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the PHP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 8th, 2003, 05:41 AM
Authorized User
 
Join Date: Oct 2003
Location: KL, wilayah persekutuan, Malaysia.
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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>";
}



Reply With Quote
  #2 (permalink)  
Old November 8th, 2003, 03:56 PM
richard.york's Avatar
Wrox Author
Points: 5,506, Level: 31
Points: 5,506, Level: 31 Points: 5,506, Level: 31 Points: 5,506, Level: 31
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Camby, IN, USA.
Posts: 1,706
Thanks: 0
Thanked 6 Times in 6 Posts
Default

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
:::::::::::::::::::::::::::::::::
Reply With Quote
  #3 (permalink)  
Old November 8th, 2003, 04:08 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #4 (permalink)  
Old November 9th, 2003, 02:04 AM
Authorized User
 
Join Date: Oct 2003
Location: KL, wilayah persekutuan, Malaysia.
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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>";
?>

Reply With Quote
  #5 (permalink)  
Old November 10th, 2003, 02:53 PM
Friend of Wrox
Points: 2,570, Level: 21
Points: 2,570, Level: 21 Points: 2,570, Level: 21 Points: 2,570, Level: 21
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: San Diego, CA, USA
Posts: 836
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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/
Reply With Quote
  #6 (permalink)  
Old November 12th, 2003, 08:46 AM
Authorized User
 
Join Date: Oct 2003
Location: KL, wilayah persekutuan, Malaysia.
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested For-Each Loop In XSLT dghosh XSLT 10 July 2nd, 2008 04:34 AM
QueryTables nested loop Sherman McCoy Excel VBA 0 June 4th, 2008 02:37 PM
nested loop adnan2 Java Basics 1 July 13th, 2007 04:21 PM
Nested Do Until Loop goels Access VBA 2 September 30th, 2004 08:44 AM



All times are GMT -4. The time now is 10:18 PM.


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