newbie questions

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have a couple of questions that might help me resolve my problems.  I have  
tried, without success, to be able to return information from my database in  
a particular format.  Here is approximately what I'm wanting to get:

Item Number:  39990

- 10704-02    65321    55.60
- 10704-02    65322    55.60
- 10704-02    65322    55.60

Item Count: 3
Grand Total: 166.80

I'm unable to get the count and grand total of onto my page.  Here is the  
code that I'm currently working with:


include 'config.php';

/*** create a new mysqli object with default database***/
$connection = mysqli_connect($hostname, $username, $password, $dbname) or  
die ("Unable to connect");

//create query
$query = "SELECT distinct item_no FROM production WHERE date > '2006-11-06'  
and fk_prodline_id = '1' group by item_no ";

//excute query
$result = mysqli_query($connection, $query) or die ("Error in query: $query.  

//create list of variables from query results
while(list($item_no) = @mysqli_fetch_row($result))
    //print item_no
    echo "<b>Item Number:</b> $item_no";

    //query details about item numbers (from above query)
    $query2 = "SELECT id, order_no, serial_no, price from production where  
item_no = '$item_no'";

    //excute query
    $result2 = mysqli_query($connection, $query2) or die ("Error in query:  
$query2. ".mysqli_error());

     //echo out results
     echo "<ul>";
        while(list($id, $order_no, $serial_no, $price) =  
            echo "<li>$order_no   $serial_no   $price";

    echo "<p>";

    echo "</ul>";

// close connection


Is there a way that I can do the count and add together the prices of each  
item numbers?  If so where do I place the code for the count and how would I  
get the sum?

I appreciate any input you might be able to give.  Thanks.


Re: newbie questions

contained the following:

Quoted text here. Click to load it

Initialise an variable $total=0;
Each time round the loop add the current price to the array

echo out $total at the end

The count is simply the number of rows.  Look for a function.

Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs

Re: newbie questions

Auddog wrote:
Quoted text here. Click to load it


Quoted text here. Click to load it

You can count and sum in the database, but, seeing you're already
fetching the individual rows, I'd do it with PHP (and avoid another SQL

To use the database for count and sum, you'd have to execute another

    select count(*), sum(price) from production where item_no='$item_no'

But you already have a query that fetches them all and prints them in a
loop. So i'd use that to calculate the $count and $total.

    // ...
    $count = 0;
    $total_price = 0;
    while (list($id, $order_no, $serial_no, $price) = @mysqli_fetch_row($result2)) {
      echo "<li>$order_no   $serial_no   $price";
      $total_price += $price;
#   echo "<p>";
    echo "</ul>";
    echo "Item Count: $count<br>\n";
    echo "Grand Total: " . number_format($total_price, 2) . "<br>\n";
    // ...

I (almost) never check the dodgeit address.
If you *really* need to mail me, use the address in the Reply-To
header with a message in *plain* *text* *without* *attachments*.

Site Timeline