Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Total (MySql, PHP)

    Hi,

    I have a database of semiconductor parts holding the part vendors, names, numbers and prices. It is easy to pull out the data about the available parts by vendor (1 - 5000 records, depending on the vendor), but sometimes I need to figure out the total price of all parts displayed on specific query.
    (Example page can be found here: http://www.brumleysouth.com/parts_prices.php )
    and here is my code so far:

    <p><table border="1">
    <?
    mysql_connect (...)OR DIE("Unable to connect to database");

    mysql_select_db (...);


    $result = mysql_query ("select * from parts WHERE pvend = '$options' AND pid is not null ORDER BY pnum ASC");



    if ($row = mysql_fetch_array($result)) {
    echo "<form action='parts_prices_processing.php'>";
    do {


    echo "<tr>";
    echo "<td>";
    echo $row['pvend'];
    echo "</td> ";
    echo "<td>";
    echo $row['pnum'];
    echo "</td>";
    echo "<td>";
    echo $row['ppr'];
    echo "</td>";

    ?><td><input type="text" name="<? echo $row['pnum']; ?>"></td> <?

    echo "</tr>";



    }

    while ($row = mysql_fetch_array($result));


    } else {

    echo "PART NOT FOUND.";

    }

    ?>
    <input type='submit' value="submit"></form></table>

    Now, I have to add up all the prices of parts available on a specific query.

    How can I do this?

    Thank you.

    Endi

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if you just need the parts totals, then you can do a

    "select sum(pricecolumn) as totalprice, var1, var2 from parts WHERE pvend = '

    $row['totalprice'] will then contain the total price.

    But it could be smarter to do it inside your recordsetprocessing
    PHP Code:
    $total 0;
    while (
    $row mysql_fetch_array($result)){
       
    $total += $row['quantity'] * $row['price']
     ...

    Depends a bit on what sort of orders you proces and if they can ever have a quantity > 1
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #3
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks.
    you are right, the quality could be > 1.

    But, let me see if I understood,

    //btw my price column is 'ppr' and I do not have 'quantity' column - do I need it?

    $total += $row['ppr'] * $row['quantity'];

    // and then what ? Echo it out?

    echo $total;

    //Well, if that's it, then it didn't work
    I even tried without 'quantity' and that won't work either, interestingly enough - it won't even display quantity as 0, although you decleared $total = 0; on the first line.

    Am I missing something?

    Thanks,

    Endi

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Post the complte code so we can have a look at it (enclose it in [php] and [/php] tags)


    You don't need the quantity-column if you don't want to store the orders. You could use quantitys from a posted form or so ...
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #5
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    here it is:

    // $options is from the pull down menu on the previous page


    <p><table border="1">
    <?
    mysql_connect (...) OR DIE("Unable to connect to database");

    mysql_select_db (...);
    $result = mysql_query ("select * from parts WHERE pvend = '$options' AND pid is not null ORDER BY pnum ASC");



    if ($row = mysql_fetch_array($result)) {
    echo "<form action='parts_new_prices.php'>";
    do {


    echo "<tr>";
    echo "<td>";
    echo $row['pvend'];
    echo "</td> ";
    echo "<td>";
    echo $row['pnum'];
    echo "</td>";
    echo "<td>";
    echo $row['ppr'];
    echo "</td>";


    }



    ?><td><input type="text" name="newprice"></td> <?

    echo "</tr>";








    }

    while ($row = mysql_fetch_array($result));


    } else {

    echo "PART NOT FOUND.";

    }

    ?>
    <input type='submit' value="submit"></form></table>

    <?


    $total = 0;
    while ($row = mysql_fetch_array($result)) {
    $total += $row['ppr'] * $row['quantity'];
    echo $total;
    }

    ?>

    Thanks,

    Endi
    Last edited by Endi; 03-26-2004 at 05:14 PM.

  • #6
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please put your code between the tags so that it looks like
    PHP Code:

    // $options is from the pull down menu on the previous page


    <p><table border="1">
    <? 
    mysql_connect 
    (...) OR DIE("Unable to connect to database");

    mysql_select_db (...);
    $result mysql_query ("select * from parts WHERE pvend = '$options' AND pid is not null ORDER BY pnum ASC");



    if (
    $row mysql_fetch_array($result)) {
    echo 
    "<form action='parts_new_prices.php'>";
    do {


    echo 
    "<tr>";
    echo 
    "<td>";
    echo 
    $row['pvend'];
    echo 
    "</td> ";
    echo 
    "<td>";
    echo 
    $row['pnum'];
    echo 
    "</td>";
    echo 
    "<td>";
    echo 
    $row['ppr'];
    echo 
    "</td>";


    }



    ?><td><input type="text" name="newprice"></td> <?

    echo "</tr>";








    }

    while (
    $row mysql_fetch_array($result));


    } else {

    echo 
    "PART NOT FOUND.";

    }

    ?>
    <input type='submit' value="submit"></form></table>

    <? 


    $total 
    0
    while (
    $row mysql_fetch_array($result)) { 
    $total += $row['ppr'] * $row['quantity'];
    echo 
    $total;

    ?>
    There is a lott wrong with your code. And it should throw more then one error !
    Do you have errorreporting turned on? If not then you probably don't get output because the script has errored out.
    I need to catch my train now, but i'll set the code straight when i get home (unless someone steps in and rewrites it before then...)
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #7
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I do have error reporting on. Please, let me know what you think is wrong with my code.

    Thank you,

    Endi

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    What i THINK is wrong?

    mysql_connect (...) OR DIE("Unable to connect to database");
    --> result, the link-identified should be stored in a variable
    --> the connectionfiles should also be inside an include
    $result = mysql_query ("select * from parts WHERE pvend = '$options' AND pid is not null ORDER BY pnum ASC");
    --> you best use the link-ID of the connection and you certainly need to include some error-reporting
    --> never use select * in embedded sql. Always supply the variablenames
    if ($row = mysql_fetch_array($result)) {
    --> no idea why you use that. You probably wanted somthing like
    if ($mysql_num_rows($result)>=1){
    --> better use mysql_fetch_assoc() since you only use the associated keys
    do {}
    --> do ?? I's not a do while loop and as a seperate language construc it probably doesn't even exists
    while ($row = mysql_fetch_array($result));
    --> it's while (...) {}
    --> no action is execute anyway. So what's the idea behind it?
    $total = 0;
    while ($row = mysql_fetch_array($result)) {
    $total += $row['ppr'] * $row['quantity'];
    echo $total;
    --> even this is copied wrong. You don't close the loop.
    --> you don't need two while-loops on the recordsets. And even if you needed two while loops, then you should first set the pointer back to the first row before stating the loop. With a mysql_data_seek()
    and then there is some nitty grity with unnescecary dropping in and out php mode, unefficient or invalid quoteuse etc. The use of shorttags (which wount be supported on all servers) The tableheader comes to soon, or else, the 'PART NOT FOUND' should be inside a row and cell, a formtag between the table and the row tag ? A formtag without name and id, and even without a method attribute?
    And the submitbutton should be inside a row and cell, not between, a rowtag and a formtag. And the </p> tag is also lost somewhere
    About the textfield --> is this one textfield for each row? then each rows textfield should have another name. (in the code below, i ad the tables primary key value to is (need to change the name of course)
    I'm not even going to start about using a tableless design and making it valid xhtml.

    So this would bring us to this sort of code:
    PHP Code:
    <?php
    require (./includes/connection.php);
    $select=("SELECT var1, var2 FROM parts WHERE pvend ='" $options "' AND pid IS NOT Null ORDER BY pnum ASC");
    $result mysql_query ($select$link) or die ('Queryproblem on line 3');
    if (
    mysql_num_rows($result) >= 1){
        echo(
    '<p>
              <form action="parts_new_prices.php" name="parts" id="parts" method="post">
                 <table border="1">'
    );
        
    $total 0;
        while (
    $row=mysql_fetch_assoc($result)){
             echo 
    '<tr>
                      <td>' 
    $row['pvend'] . '</td>
                      <td>' 
    $row['pnum'] . '</td>
                      <td>' 
    $row['ppr'] . '</td>
                      <td><input type="text" name="newprice' 
    $row['pkvalue'] . '" /></td>
                   </tr>'
    ;
             
    $total += ($row['ppr'] * $row['quantity']);
        }
        echo 
    '<tr><td colspan="4"><input type="submit" value="submit" /></td></tr>
              </table></form></p>'
    );
        echo 
    $total;
    } else {
        echo 
    'PART NOT FOUND.';
    }
    ?>
    yhe included file with the connectionstrings in then looks like
    PHP Code:
    <?php
    $link 
    = @mysql_connect("localhost""...""....")
                or die(
    "Could not connect to the databaseserver. Please go back and try again or try again later.");
    @
    mysql_select_db("....",$link)
                   or die (
    "Could not select database. Please go back and try again or try again later.");
    ?>
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #9
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Wow...
    is all I can say

  • #10
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by raf

    $select=("SELECT var1, var2 FROM parts WHERE pvend ='" . $options . "' AND pid IS NOT Null ORDER BY pnum ASC");
    What is var1, var2 ? If I leave it like that, I get "Query problem on line 3".

  • #11
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Originally posted by Endi
    What is var1, var2 ? If I leave it like that, I get "Query problem on line 3".
    You need to fill the actual columnnames that you use ithere.

    As opposed to using * --> this will send the values for aal column. Even if you need all column, then you still should specify the variablenames as a defensive measure. Imagine you later on add ew columns to the table --> these will also be selected here, but wount be used.
    Keeping your recordsets as small as possible is one of the most important performance-measures.

    If the column from the code are all you'll use, and if there is a column with the quantity in + that your primary key column is called pkvalue, then you'd have

    SELECT pvend, pnum, ppr, pkvalue, quantity FROM parts ...

    But this would mean that your db- design would be wrong.
    You'll normally have a table with the parts-info, a table with the user-info and the order-table or whatever you call it. and then a table with sales info (each sale need to know who made it, which part he bought and which quantity of each part---> so it contains the primary key value of the parts-table and the primary key value of the users-table, the primry key value of the order table (to be able to group the records from one sale) and the quantity.
    This principle is called db-normilisation. The design is then a stardesign where you have 1 factstable (the sales table) and 3 dimensiontables (parts, users, orders). There should be plenty of shoppingcarts oouther that you can download to take a look at their db design (probably snoflake designs because they will probably be using the partstable as a second factstable and have a producer/transporter/taxpercentage ec dimensiontable on it etc)

    The price is then retrieved by joining this table with the parts table. Unless you want the freeze the price when the sale was made --> i that case, you need to also store the price per unit in the sales table
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #12
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for going all the way to explain it to me.
    At this point, however, I am not required to keep records on orders, users and sales. But, I know it might change in the future.

    For now, I was thinking about redesigning the database so that I have one table for part vendors and the other one for parts (as one vendor has more than one part). This table would hold part numbers (they are unique), part prices and quantities (on hand). Does that sound like a normalization?

    Also, the part prices will change from time to time and the purpose of the text box on each row is for me to be able to enter new price and update the database.

    However, I used the code you provided (I changed connection variables of course) and yes, I get data on the parts by vendor just fine, but still total price = 0; it somehow lacks to add up all the prices.

    PHP Code:
    <?php 
    require ('connection.php'); 
    $select=("SELECT pvend, pnum, ppr FROM parts WHERE pvend ='" $options "' AND pid IS NOT Null ORDER BY pnum ASC"); 
    $result mysql_query ($select$link) or die ('Queryproblem on line 3'); 
    if (
    mysql_num_rows($result) >= 1){ 
        echo(
    '<p> 
              <form action="parts_new_prices.php" name="parts" id="parts" method="post"> 
                 <table border="1">'
    ); 
        
    $total 0
        while (
    $row=mysql_fetch_assoc($result)){ 
             echo 
    '<tr> 
                      <td>' 
    $row['pvend'] . '</td> 
                      <td>' 
    $row['pnum'] . '</td> 
                      <td>' 
    $row['ppr'] . '</td> 
                      <td><input type="text" name="newprice' 
    $row['pkvalue'] . '" /></td> 
                   </tr>'

             
    $total += ($row['ppr'] * $row['quantity']); 
        } 
        echo 
    '<tr><td colspan="4"><input type="submit" value="submit" /></td></tr> 
              </table></form></p>'

        echo 
    $total
    } else { 
        echo 
    'PART NOT FOUND.'

    ?>
    http://www.brumleysouth.com/parts_prices.php

    Endi

  • #13
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    from your description, i suppose you don't have a table column called 'quantity'. You don't select it anyway ..

    So I would assume that
    ($row['ppr'] * $row['quantity']) is 0 or Null or whatever.

    Maybe first try it like

    $total += $row['ppr'] ;

    or add the coulmn in the db-table


    Also, if 'pnum' is the unique partidentifier, then you need to switch
    <td><input type="text" name="newprice' . $row['pkvalue'] . '" /></td>

    into

    <td><input type="text" name="newprice' . $row['pnum'] . '" /></td>
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #14
    New Coder
    Join Date
    Mar 2004
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have changed it to $total += $row['ppr']; but that doesn't work either. Total is always 0.

    I can't add the quantity column as I still don't have all the data, so it would be either 0 or null, which would once again give me 0 as a total.

  • #15
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    how do the values inside 'ppr' look like?

    If you use the code you poste above, then your values inside the table look like '$452.10', which means they are stringvalues


    They should be like '452.10'
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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