...

View Full Version : Select Total (MySql, PHP)



Endi
03-26-2004, 02:07 PM
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

raf
03-26-2004, 02:31 PM
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


$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

Endi
03-26-2004, 03:29 PM
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

raf
03-26-2004, 03:52 PM
Post the complte code so we can have a look at it (enclose it in
and 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 ...

Endi
03-26-2004, 04:02 PM
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

raf
03-26-2004, 04:19 PM
Please put your code between the tags so that it looks like



// $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...)

Endi
03-26-2004, 04:30 PM
I do have error reporting on. Please, let me know what you think is wrong with my code.

Thank you,

Endi

raf
03-26-2004, 10:08 PM
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
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
$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.");
?>

Endi
03-27-2004, 03:24 AM
Wow...
is all I can say

Endi
03-27-2004, 04:03 AM
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".

raf
03-27-2004, 08:42 AM
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

Endi
03-27-2004, 03:32 PM
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
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

raf
03-27-2004, 03:59 PM
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>

Endi
03-27-2004, 04:15 PM
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.

raf
03-27-2004, 04:24 PM
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'

Endi
03-27-2004, 04:41 PM
OMG that's right! they are strings!

I will try to find some way to drop "$" sign from 'ppr' column, I can't do it manually - it's a database of more than 70,000 parts.

Thank you for all your help and advice.

Endi

raf
03-27-2004, 04:54 PM
UPDATE parts SET ppr= SUBSTRING(ppr,2)

should do the trick --> try it on a copy of the table first ...

You should then also change the columntype afterwards to float or double
string are usualy slower then numericals


Note, you could also work with your existing table, for instance if you otherwise need to edit a lott of oter code. Like this

$total += substr($row['ppr'], 1);

Endi
03-27-2004, 05:43 PM
yes, now it works fine.
May I say, you have helped me a lot, but this has been great learning experience for me as well.

thank you.

Endi

raf
03-27-2004, 05:50 PM
You're welcome. Glad you got it running now :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum