RE: [PHP] need help with PHP/MySQL

---------------------------------------------------------------------------- ----------------- Disclaimer: The information contained in this email is intended only for the use of the person(s) to whom it is addressed and may be confidential or contain legally privileged information. If you are not the intended recipient you are hereby notified that any perusal, use, distribution, copying or disclosure is strictly prohibited. If you have received this email in error please immediately advise us by return email at postmaster@normandy.com.au and delete the email document without making a copy. ---------------------------------------------------------------------------- ----------------- Gustavo, I have to agree with David you would be much better off with a redesign as you are going to cause your self some long term pain with this "fullprice field" If you have 100's of legacy apps built around this well you may not have a choice... but this table is absolutly abhorent, people who design tables like that will be the first against the wall when the revolution comes... Sorry i lost it a bit there... one of the problems you will always have is "where is the numeric figure"... is it always going to be the 6th character on? or is it padded to the right.. what happens when you have 2 Real instead of 300? I assume that since you have to specify Real you store two or more currencies, maybe due to foreign investement... so what will happen when you need to store US currency? What does that look like (US$) 300.00 or maybe Australian (AUS$) 300.00 If this is the case... redesign redesign redesign... Otherwise if the currency amount will always the 6th character on you could try something like: SELECT SUM(substr(preco,6)) from preco This may work and it may not (im not familiar with MySQL, but it works in Oracle i think) If you find that the numeric is right aligned you are in a world of pain (R$) 300.00 (R$) 30.00 (R$) 3.00 You may get away with the following in PHP... $price = substr($row[preco]); $totalprice += $price; echo "Price of item: $price Total: $totalprice"; But what happens when you hit 3000 Real? In this case or with the multiple currencies you may have to look a regular expression to find where the first space is and go from there.... but i would recommend a redesign Good luck, mn Mark Nold markn@enspace.com <mailto:markn@enspace.com> Senior Consultant   Change is inevitable, except from vending machines. -----Original Message----- From: David Robley [mailto:huntsman@hermes.nisu.flinders.edu.au] Sent: Tuesday, November 21, 2000 12:50 PM To: Gustavo Souza; php-general@lists.php.net Cc: php-db@lists.php.net Subject: RE: [PHP] need help with PHP/MySQL On Tue, 21 Nov 2000, Gustavo Souza wrote: > SELECT SUM() dont work for me cause the fields have (R$) 300.00 > and not only the numbers .... > i need to do it on php ... > > thanks > > GS You should consider changing your database structure; there is no need to store the currency symbol for every record, and it is only needed when you want to display the values, so you can just echo it in front of the relevant result. Using the SUM function in SQL is a great deal faster than performing the calculation using a loop in PHP; as the size of your DB gets larger, this has the potential to become a slow process. But if you absolutely insist, recheck your value that is returned by $price = substr ("$fullprice", 3); If you echo $price as you go through the while loop, you may see something unexpected :-) > > At 14:39 21/11/2000 +1000, you wrote: > >Instead of all this, you should have SQL perform the SUM for you. > > > >"SELECT SUM(preco) from preco" > > > >This will sum all rows in the 'preco' table. You should put a where clause > >in if you want to only sum some of the rows. > > > > > -----Original Message----- > > > From: Gustavo Souza [mailto:gus74@terra.com.br] > > > Sent: Tuesday, 21 November 2000 14:28 > > > To: php-general@lists.php.net > > > Subject: [PHP] need help with PHP/MySQL > > > > > > > > > oks, this code works for what i was looking for : > > > > > > $mysql = mysql_connect("host", "user", "pass"); > > > > > > mysql_select_db(teste); > > > > > > $sqlquery = "SELECT * FROM preco"; > > > > > > if ($resultset = mysql_query($sqlquery, $mysql)) > > > { > > > > > > echo "<TABLE>"; > > > while ($row = mysql_fetch_array($resultset)) > > > { > > > > > > /* Remove R$ from Preco field */ > > > > > > $fullprice = ($row[preco]); > > > $price = substr ("$fullprice", 3); > > > > > > $totalprice += ($price); > > > > > > } > > > echo > > > "<TR><TD>Total:</TD>"."<TD>".sprintf('%0.2f', > > > $totalprice)."</TD>"."</TABLE>"; > > > } > > > > > > but this code just calculate the sum fom one row .. how can i > > > calculate the > > > all rows from a table ... > > > > > > thanks > > > > > > GS > > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net > For additional commands, e-mail: php-general-help@lists.php.net > To contact the list administrators, e-mail: php-list-admin@lists.php.net -- David Robley | WEBMASTER & Mail List Admin RESEARCH CENTRE FOR INJURY STUDIES | http://www.nisu.flinders.edu.au/ AusEinet | http://auseinet.flinders.edu.au/ Flinders University, ADELAIDE, SOUTH AUSTRALIA