SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Nested while loop

    Hi all.I have two tables sales and purchase.And it has supporting sales_dy and purchase_dy tables respectively which stores quantities and rates etc.All other details are stored in sales and purchase.Problem is I want to display the stock from a particular date and I am using nested while loop for this.But it doesn't seem working.
    #1.If any of the tables dont have values, it shows only the portion above while loop
    #2.If both the tabals have values, it displays items name same in all the columns.
    #3.It shows purchase quantity properly but dont show sales quantity.
    #4.Im not sure about GROUP BY is right choice here.

    Here is my code:

    PHP Code:
    <?PHP 
    if(isset($_POST['Submit']))
    //if isset
    $to=$_POST['to'];
    $from=$_POST['from'];

                
    $sales=mysql_query
                
    ("SELECT 
                        sales_dy.item_id, 
                        SUM(sales_dy.qty) as sales_quantity, 
                        item_name.name,
                        sales.inv_date
                FROM 
                        sales_dy
                INNER JOIN 
                        item_name 
                ON 
                        sales_dy.item_id=item_name.item_id
                INNER JOIN 
                        sales 
                ON 
                        sales.number=sales_dy.number

                WHERE     
                        ((sales.inv_date >='"
    .$to."' 
                AND 
                        sales.inv_date <='"
    .$from."'))
                GROUP BY
                        sales_dy.item_id"
    );    
                        
                    
                
    $purchase=mysql_query
                
    ("SELECT 
                        purchase_dy.item_id, 
                        SUM(purchase_dy.qty) as purchase_quantity, 
                        item_name.name,
                        purchase.inv_date
                FROM 
                        purchase_dy
                INNER JOIN 
                        item_name 
                ON 
                        purchase_dy.item_id=item_name.item_id
                INNER JOIN 
                        purchase 
                ON 
                        purchase.number=purchase_dy.number

                WHERE     
                        ((purchase.inv_date >='"
    .$to."' 
                AND 
                        purchase.inv_date <='"
    .$from."'))
                GROUP BY
                        purchase_dy.item_id"
    );    
                        
                    
                                    if(
    mysql_num_rows($sales) > || mysql_num_rows($purchase) > 0)
                                    { 
    //if num rows
                                    
    echo "<div align='center'><table border='0' width='40%'>";
                                    echo 
    "<tr>
                                    <th scope='col'>Name</th>
                                    <th scope='col'>Purchase</th>
                                    <th scope='col'>Sales</th>
                                    <th scope='col'>Balance</th>
                                    </tr>"
    ;
                                    while(
    $sales_qty=mysql_fetch_array($sales))
                                    { 
    //while row 
                                    
    while($purchase_qty=mysql_fetch_array($purchase))
                                        { 
    //while row1
                                    
    $balance=$purcahse_qty['purchase_quantity']-$sales_qty['sales_quantity'];
                                    echo 
    "<tr>
                                    <td>"
    ; echo $sales_qty['name']; echo "</td>
                                    <td>"
    ; echo $purchase_qty['purchase_quantity']; echo "</td>
                                    <td>"
    ; echo $sales_qty['sales_quantity']; echo "</td>
                                    <td>"
    ; echo $balance; echo "</td>

                                    </tr>"
    ;
                                    }
    //while row
                                    
    }//while row1
                                    
    echo "</table></div>";
                                    }
    //if num rows
                                    
    else
                                    { 
    //else num rows
                                    
    echo "<div align='center' style='height:50px; width:auto; margin-top:20px' class='information'>Sorry No Records Found..</div>";
                                    } 
    //else num rows
    }    //if isset
    ?>
    Similarly, I would like to know how can I show the same records date wise.Like,
    01/12/2011 Computer - purchase-10 sales-2 and so on.Please help me in this regard.
    It's easy once you know how...

  2. #2
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am not sure if it can be managed with a single query from by modifying you database table design but from the current queries you can get the results as follows (untested whole code though):
    PHP Code:
    if(isset($_POST['Submit'])) 

        
    //if isset 
        
    $to $_POST['to']; 
        
    $from $_POST['from']; 
        
        
    $sql "SELECT 
                    sales_dy.item_id, SUM(sales_dy.qty) as sales_quantity,  item_name.name, sales.inv_date 
                FROM sales_dy 
                    INNER JOIN item_name ON sales_dy.item_id=item_name.item_id 
                    INNER JOIN sales ON sales.number=sales_dy.number 
                WHERE sales.inv_date BETWEEN '"
    .$to."'  AND '".$from."'
                GROUP BY sales_dy.item_id"
    ;
        
    $sales mysql_query($sql) or die(mysql_error());
        
        
    $sql "SELECT  
                    purchase_dy.item_id, SUM(purchase_dy.qty) as purchase_quantity, item_name.name, purchase.inv_date 
                FROM purchase_dy 
                    INNER JOIN item_name ON purchase_dy.item_id=item_name.item_id 
                    INNER JOIN purchase    ON purchase.number=purchase_dy.number 
                WHERE purchase.inv_date BETWEEN '"
    .$to."' AND '".$from."'
                GROUP BY purchase_dy.item_id"
    ;
        
    $purchase mysql_query($sql) or die(mysql_error());

        if(
    mysql_num_rows($sales) > || mysql_num_rows($purchase) > 0
        { 
            
    //if num rows 
            
    echo '<div align="center">
                    <table border="0" width="40%">
                        <tr> 
                            <th scope="col">Name</th> 
                            <th scope="col">Purchase</th> 
                            <th scope="col">Sales</th> 
                            <th scope="col">Balance</th> 
                        </tr>'
    ;
            for(
    $i 0$i mysql_num_rows($sales); $i++){
                
    mysql_field_seek($sales$i);
                
    mysql_field_seek($purchase$i);
                
    $sales_qty mysql_fetch_array($sales);
                
    $purchase_qty mysql_fetch_array($purchase);
                
                
    $balance $purcahse_qty['purchase_quantity'] - $sales_qty['sales_quantity'];
                echo 
    '<tr>
                    <td>' 
    $sales_qty['name'] . '</td> 
                    <td>' 
    $purchase_qty['purchase_quantity'] . '</td> 
                    <td>' 
    $sales_qty['sales_quantity'] . '</td> 
                    <td>' 
    $balance '</td> 
                </tr>'
    ;
            }
            echo 
    "</table>
            </div>"

        }
    //if num rows 
        
    else 
        { 
    //else num rows 
            
    echo "<div align='center' style='height:50px; width:auto; margin-top:20px' class='information'>Sorry No Records Found..</div>"
        } 
    //else num rows 
    }    //if isset 
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  3. #3
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow thats working just fine now.Can you suggest me how I can show same records date wise??
    It's easy once you know how...

  4. #4
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you mean 'sales.inv_date' then add 'order by sales.inv_date desc/asc' in both of the SQL queries and add a column in the table and echo it in the same way as others.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  5. #5
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok.I got it
    It's easy once you know how...

  6. #6
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Raju Gautam View Post
    If you mean 'sales.inv_date' then add 'order by sales.inv_date desc/asc' in both of the SQL queries and add a column in the table and echo it in the same way as others.
    Well its working well but not displaying balance quantities.I altered the code as per my requirements and it displays records date wise.But the balance quantity is not displayed properly.
    Here is my code:
    PHP Code:
    <?PHP 
    if(isset($_POST['Submit']))  
    {  
        
    //if isset  
        
    $to $_POST['to'];  
        
    $from $_POST['from'];  
         
        
    $sql "SELECT  
                    sales_dy.item_id, sales_dy.qty as sales_quantity,  item_name.name, sales.inv_date  
                FROM sales_dy  
                    INNER JOIN item_name ON sales_dy.item_id=item_name.item_id  
                    INNER JOIN sales ON sales.number=sales_dy.number  
                WHERE sales.inv_date BETWEEN '"
    .$to."'  AND '".$from."' 
                ORDER BY sales.inv_date"

        
    $sales mysql_query($sql) or die(mysql_error()); 
         
        
    $sql "SELECT   
                    purchase_dy.item_id, purchase_dy.qty as purchase_quantity, item_name.name, purchase.inv_date  
                FROM purchase_dy  
                    INNER JOIN item_name ON purchase_dy.item_id=item_name.item_id  
                    INNER JOIN purchase    ON purchase.number=purchase_dy.number  
                WHERE purchase.inv_date BETWEEN '"
    .$to."' AND '".$from."' 
                ORDER BY purchase.inv_date"

        
    $purchase mysql_query($sql) or die(mysql_error()); 
        
    //query to fetch total purchase quantity so as to be used to calculate total balance available 
        
                
    $sql "SELECT   
                            item_id, 
                            SUM(qty) as total_purchase_quantity
                        FROM 
                            purchase_dy  
                        GROUP BY 
                            item_id"

        
    $total_purchase mysql_query($sql) or die(mysql_error()); 

        
    //query to fetch total sales quantity so as to be used to calculate total balance available 

        
    $sql "SELECT   
                    item_id,
                    SUM(qty) as total_sales_quantity  
                FROM 
                    sales_dy  
                GROUP BY 
                    item_id"

        
    $total_sale mysql_query($sql) or die(mysql_error()); 


        if(
    mysql_num_rows($sales) > || mysql_num_rows($purchase) > 0)  
        {  
            
    //if num rows  
            
    echo '<div align="center"> 
                    <table border="0" width="40%"> 
                        <tr>  
                            <th scope="col">Date</th>  
                            <th scope="col">Name</th>  
                            <th scope="col">Purchase</th>  
                            <th scope="col">Sales</th>  
                            <th scope="col">Balance</th>  
                        </tr>'

            for(
    $i 0$i mysql_num_rows($purchase); $i++){ 
                
    mysql_field_seek($sales$i); 
                
    mysql_field_seek($purchase$i); 
                
    mysql_field_seek($total_purchase$i); 
                
    mysql_field_seek($total_sale$i); 

                
    $sales_qty mysql_fetch_array($sales); 
                
    $purchase_qty mysql_fetch_array($purchase); 
                
    $total_sales_qty mysql_fetch_array($total_sale); 
                
    $total_purchase_qty mysql_fetch_array($total_purchase); 

                
                
    $balance $total_purchase_qty['total_purchase_quantity'] - $total_sales_qty['total_sales_quantity']; 

                if(empty(
    $sales_qty['inv_date'])){
                        
    $inv_date=(date('d-m-Y'strtotime($purchase_qty['inv_date'])));
                }
                else{
                        
    $inv_date=(date('d-m-Y'strtotime($sales_qty['inv_date'])));
                }

                echo 
    '<tr> 
                    <td>' 
    $inv_date'</td>  
                    <td>' 
    $purchase_qty['name'] . '</td>  
                    <td>' 
    $purchase_qty['purchase_quantity'] . '</td>  
                    <td>' 
    $sales_qty['sales_quantity'] . '</td>
                    <td>' 
    $balance '</td>  
      
                </tr>'

            } 
            echo 
    "</table> 
            </div>"
    ;  
        }
    //if num rows  
        
    else  
        { 
    //else num rows  
            
    echo "<div align='center' style='height:50px; width:auto; margin-top:20px' class='information'>Sorry No Records Found..</div>";  
        } 
    //else num rows  
    }    //if isset  
    ?>
    Result is :
    Untitled.jpg

    which must be :
    Purchase Sales Balance
    10 1 9
    10 0 10
    5 0 14


    Is there any solution for this?
    It's easy once you know how...

  7. #7
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Per day stock report

    Hi all.I found this code on forum.But dont know how to alter this to show the records datewise.I want to display sales and purchase for given period of time and show the balance quantity by deducting total sales from the total purchase.

    Here is my code:

    PHP Code:
    <?PHP 
    if(isset($_POST['Submit']))  
    {  
        
    //if isset  
        
    $to $_POST['to'];  
        
    $from $_POST['from'];  
         
        
    $sql "SELECT  
                    sales_dy.item_id, sales_dy.qty as sales_quantity,  item_name.name, sales.inv_date  
                FROM sales_dy  
                    INNER JOIN item_name ON sales_dy.item_id=item_name.item_id  
                    INNER JOIN sales ON sales.number=sales_dy.number  
                WHERE sales.inv_date BETWEEN '"
    .$to."'  AND '".$from."' 
                ORDER BY sales.inv_date"

        
    $sales mysql_query($sql) or die(mysql_error()); 
         
        
    $sql "SELECT   
                    purchase_dy.item_id, purchase_dy.qty as purchase_quantity, item_name.name, purchase.inv_date  
                FROM purchase_dy  
                    INNER JOIN item_name ON purchase_dy.item_id=item_name.item_id  
                    INNER JOIN purchase    ON purchase.number=purchase_dy.number  
                WHERE purchase.inv_date BETWEEN '"
    .$to."' AND '".$from."' 
                ORDER BY purchase.inv_date"

        
    $purchase mysql_query($sql) or die(mysql_error()); 
        
    //query to fetch total purchase quantity so as to be used to calculate total balance available 
        
                
    $sql "SELECT   
                            item_id, 
                            SUM(qty) as total_purchase_quantity
                        FROM 
                            purchase_dy  
                        GROUP BY 
                            item_id"

        
    $total_purchase mysql_query($sql) or die(mysql_error()); 

        
    //query to fetch total sales quantity so as to be used to calculate total balance available 

        
    $sql "SELECT   
                    item_id,
                    SUM(qty) as total_sales_quantity  
                FROM 
                    sales_dy  
                GROUP BY 
                    item_id"

        
    $total_sale mysql_query($sql) or die(mysql_error()); 


        if(
    mysql_num_rows($sales) > || mysql_num_rows($purchase) > 0)  
        {  
            
    //if num rows  
            
    echo '<div align="center"> 
                    <table border="0" width="40%"> 
                        <tr>  
                            <th scope="col">Date</th>  
                            <th scope="col">Name</th>  
                            <th scope="col">Purchase</th>  
                            <th scope="col">Sales</th>  
                            <th scope="col">Balance</th>  
                        </tr>'

            for(
    $i 0$i mysql_num_rows($purchase); $i++){ 
                
    mysql_field_seek($sales$i); 
                
    mysql_field_seek($purchase$i); 
                
    mysql_field_seek($total_purchase$i); 
                
    mysql_field_seek($total_sale$i); 

                
    $sales_qty mysql_fetch_array($sales); 
                
    $purchase_qty mysql_fetch_array($purchase); 
                
    $total_sales_qty mysql_fetch_array($total_sale); 
                
    $total_purchase_qty mysql_fetch_array($total_purchase); 

                
                
    $balance $total_purchase_qty['total_purchase_quantity'] - $total_sales_qty['total_sales_quantity']; 

                if(empty(
    $sales_qty['inv_date'])){
                        
    $inv_date=(date('d-m-Y'strtotime($purchase_qty['inv_date'])));
                }
                else{
                        
    $inv_date=(date('d-m-Y'strtotime($sales_qty['inv_date'])));
                }

                echo 
    '<tr> 
                    <td>' 
    $inv_date'</td>  
                    <td>' 
    $purchase_qty['name'] . '</td>  
                    <td>' 
    $purchase_qty['purchase_quantity'] . '</td>  
                    <td>' 
    $sales_qty['sales_quantity'] . '</td>
                    <td>' 
    $balance '</td>  
      
                </tr>'

            } 
            echo 
    "</table> 
            </div>"
    ;  
        }
    //if num rows  
        
    else  
        { 
    //else num rows  
            
    echo "<div align='center' style='height:50px; width:auto; margin-top:20px' class='information'>Sorry No Records Found..</div>";  
        } 
    //else num rows  
    }    //if isset  
    ?>
    It displays result as follows:
    Date Purchase Sales Balance
    23-12-2011 10 1 13
    25-12-2011 10 1 9
    25-12-2011 5 1 -1

    Where it has to show balance 9 on 23-12-2011 and 13 on 25-12-2011.Please tell me where I am going wrong?Is this possible through query?
    It's easy once you know how...

  8. #8
    SitePoint Addict anita_86's Avatar
    Join Date
    Aug 2010
    Location
    Nagpur, India
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There seems one more problem.If any of the two tables are empty, it dosent show anything related to it at all.I guess its because of
    PHP Code:
    for($i 0$i mysql_num_rows($purchase); $i++){ 
    how can I resolve this problem?
    It's easy once you know how...

  9. #9
    Non-Member
    Join Date
    Nov 2010
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As per my understanding you still have the problem with the nested looping. Please read this thread, I wish it will work for you.
    www(dot)dmxzone(dot)com/go?4937


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •