Page 1 of 1
trying to create a tax function from tax rate stored in db.
Posted: Tue Jan 01, 2008 1:48 am
by lafflin
Well the name pretty much sums up my question, here is my code:
Code: Select all
function calc_tax($amount) {
if (!isset($_SESSION['tax'])) {
$sql = 'SELECT amt FROM price_functions WHERE descr = \'tax\'';
$result = mysql_query($sql) or die (mysql_query.mysql_error());
if ($result) { $tax_rate = mysql_query($result); }
} else { $tax_rate = $_SESSION['tax']; }
$amount_w_tax = ($amount * $tax_rate);
return $amount_w_tax;
}
I'm obviously a newb, but I just realized that in this project I have created about thirty scripts and not one seems to query my db for a only a single record, so I'm having trouble referencing my past scripts.
I know this is childs play for most of you, and aside from the query itself, I'm sure this whole function is sideways.
If i put $result or $sql as a parameter for mysql_result() I get an invalid parameter error message, every other mysql_x function I put in there just returned a NULL value on var_dump($tax_rate)
I appreciate any help I can get, thanks.
(i'm sorry, I can't figure out why my code is all green either. I have it nested in
tags.
Posted: Tue Jan 01, 2008 2:50 am
by bdlang
Your query, using the single quotes as you've done, looks like this to MySQL:
Code: Select all
SELECT 'amt' FROM 'price_functions' WHERE 'descr' = 'tax'
You're attempting to retrieve data from strings (using quotes to surround the table and column names). You need to remove them. Only actual string data should be quoted. Speaking of which, is your SQL statement supposed to match against columns or against some external data?
Obviously this query fails and so the value of
$result is FALSE, i.e. not a MySQL Result Resource as the call to mysql_result() expects.
Use mysql_error() (note, it's a function) to trap any errors and troubleshoot in the future. I also suggest you run all SQL statements you intend to use in your PHP scripts in mysql first to test them.
PHP Manual :
mysql_query() |
mysql_result()
MySQL Manual :
Tutorial
Posted: Tue Jan 01, 2008 2:59 am
by lafflin
I got the quotes from phpmyadmin, for whatever reason it always gives out weird quoted queries, so I thought I'd add them in while I was trying different things troubleshooting. I took them out and I added $result as a parameter for mysql_query and I am getting the same result.
To clarify though, I am trying to pull a value (amt) which is stored as a decimal from a table(pricefunctions) where the field name(descr) is ="tax".
I'm not sure that mysql_query is the function I want to use or not, I just want the one single value from the column(amt).
Posted: Tue Jan 01, 2008 3:48 am
by crystal ship
You should first learn to connect to database, perform an SQL query and fetch the results from a SQL database.

Posted: Tue Jan 01, 2008 11:11 am
by lafflin
did you really take the time to write that? You really must live a miserable existence, but thanks for your genius.
I must be posting in the wrong forum, I though this was a place to ask questions related to php when you get stuck and needed some help.
honestly though, I really do not mind if you want to be a condescending swallowgagger if you're actually giving advice or help in any way, which you crystal are not.
Posted: Tue Jan 01, 2008 11:34 am
by Inkyskin
Here's how I would write the query you have:
Code: Select all
function calc_tax($amount){
if(!isset($_SESSION['tax')){
$sql = "SELECT amt FROM price WHERE descr = 'tax'";
$query = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($query);
$tax_rate = $row['amt'];
} else {
$tax_rate = $_SESSION['tax'];
}
$amount_with_tax = ($amount * $tax_rate)
return $amount_with_tax;
}
You should take a little more care with the layout of you code (Although that could be down to the use of the code tags). Indentation helps a LOT when trying to figure out what's wrong.
This would pull out from a table like this:
Code: Select all
Table: Price
id descr amt
-----------------
01 tax 0.32
I have simplified the code a bit, this should help determine where any errors would be - hope that helps
Posted: Tue Jan 01, 2008 11:34 am
by lafflin
I have edited the origininal code, so do not be confused, there was a bunch of single quotes. I think that all I really need now is to know how to get a single record back from my query, as opposed to an assoc or indexed array.
Posted: Tue Jan 01, 2008 11:42 am
by Inkyskin
How many rows fo you have in your table? If more than one, then we need to know how you define each row, ie what your index name is. In the example table I provided above, that would be the 'id' column. To pull just that row out, you would need to modify the query to look like this:
Code: Select all
$sql = "SELECT amt FROM price WHERE id = 1";
Swapping 1 for the id of the row you want.
Posted: Tue Jan 01, 2008 12:57 pm
by lafflin
this is what I have came up with, and although it works I just know that it's not the best way:
Code: Select all
function calc_tax($amount) {
if (!isset($_SESSION['tax'])) {
$sql = 'SELECT amt FROM price_functions WHERE descr = \'tax\'';
$result = mysql_query($sql) or die ($sql.mysql_error());
if ($result) { $row = mysql_fetch_assoc($result);
$tax_rate=$row['amt']; }
} else { $tax_rate = $_SESSION['tax']; }
$amount_w_tax = ($amount * $tax_rate);
return $amount_w_tax;
}
The weird thing is that further down the script I var_dump($tax_rate) and get a NULL.
but the tax is actually being calulated when I use the function.
My first question though is that I am using mysql_fetch_assoc to get back one value, is there an easier way?
and thanks for all your advice.....except for you crystal, you get no thanks.
[solved] create tax function from stored value
Posted: Tue Jan 01, 2008 2:04 pm
by lafflin
I'm sorry inkyskin, I didn't see your first post somehow. That's the same answer I cam up with, difference is it took me a million times as long to figure it out.
I appreciate your help very much though, thanks for making this forum the best one going. (not you though Crystal)
Posted: Tue Jan 01, 2008 2:49 pm
by bdlang
Honestly, let's please stop the bickering about the post
crystal ship made. It's not constructive and you are burning bridges here.
Ok, to your questions.
The weird thing is that further down the script I var_dump($tax_rate) and get a NULL.
but the tax is actually being calulated when I use the function.
My first question though is that I am using mysql_fetch_assoc to get back one value, is there an easier way?
The variable
$tax_rate, as far as I can tell, only exists within your custom function 'calc_tax'. The variable scope ends there. It does not exist elsewhere in your script, so accessing it will return a NULL.
I'd go with your original version of the script and use mysql_result() to retrieve only a single value. This is always more efficient than using one of the mysql_fetch_* functions.
Here, for example only, is a more simplified, (probably) more efficient version of your script
Code: Select all
function calc_tax($a) {
if ( !isset($_SESSION['tax']) ) {
$sql = "SELECT ({$a} * amt) AS calc_value ";
$sql.= 'FROM price_functions WHERE descr= \'tax\'';
return mysql_result( mysql_query($sql), 0, 'calc_value' );
} else
return $a * $_SESSION['tax'];
}
Posted: Tue Jan 01, 2008 5:10 pm
by lafflin
ah, I see, thanks.
And I'm, just joking with the comments about crystal, ya gotta have a sense of humor about things.
Posted: Tue Jan 01, 2008 11:59 pm
by crystal ship
I wrote the post by looking at the way you keep ''s in the sql query in the code inside and suggested to write for some good ones. Here, I didn't write that to be a sallowgager or something like that. Thanks to notify me that my post was not of a help to you although it was in a weired manner.