Page 1 of 1
retrieve value from table
Posted: Sat Apr 23, 2011 3:50 pm
by todd2011
I am having this code where I want to get the value from the table
Code: Select all
$title = getrate ($sales,$transaction);
So I am calling this function getrate
and here is the code
Code: Select all
function getrate ($sale,$transaction)
{
$conn = mysql_connect ($mysql_host, $mysql_user, $mysql_password);
$sql = "SELECT Rate from Sheet1 where ((Lower <= $sale) AND (Upper >= $sale))";
$result = mysql_query ($sql, $conn);
$num=mysql_numrows($result);
$i=0;
while ($i < $num)
{
$title=mysql_result($result,$i,"Rate");
}
}
so the design of the Sheet1 table is like this
so for example one of the record is saved like this
so if the sale value is 17000 i want to return 125 in the $title variable
but its not returning any value can someone tell me whats wrong in my code
thanks
toddd
Re: retrieve value from table
Posted: Sat Apr 23, 2011 4:34 pm
by andym01480
Code: Select all
function getrate ($sale,$transaction)
{
$conn = mysql_connect ($mysql_host, $mysql_user, $mysql_password);
$sql = "SELECT Rate from Sheet1 where Lower <= '$sale' AND Upper >= '$sale'";//you should escape $sale with mysql_real_escape_string()
$result = mysql_query ($sql, $conn) OR DIE(mysql_error());//worth error checking in development
if(mysql_num_rows($result)==1)
{
$row=mysql_fetch_assoc($result);
return $row['Rate'];
}
else{return FALSE;}
}
You weren't returning the value, if you are only looking for the one result, my code would be quicker and easier to understand IMHO!
Re: retrieve value from table
Posted: Sun Apr 24, 2011 2:52 pm
by todd2011
Andy,
I tried your code it doesnt return values
i have a php page called start.php which calls another page which has the getrate function
and i dont see any values been returned
I even tried to do echo in the other page to see if it is connecting to database
so i did a echo in the page but it didnt type anything back
sorry new to php here is my code
Code: Select all
include ("getrates.php");
$title = getrate ($sale, $transaction);
Now here is the code for getrates.php code
Code: Select all
<?php
function getrate ($sale,$transaction)
{
$mysql_host = "localhost";
$mysql_database = "Title";
$mysql_user = "BASE";
$mysql_password = "test2";
echo $mysql_host;
echo $mysql_database;
echo $mysql_user;
echo $mysql_password;
$conn = mysql_connect ($mysql_host, $mysql_user, $mysql_password);
$sql = "SELECT Rate from Sheet1 where Lower <= '$sale' AND Upper >= '$sale'";//you should escape $sale with mysql_real_escape_string()
echo $sql;
$result = mysql_query ($sql, $conn) OR DIE(mysql_error());//worth error checking in development
if(mysql_num_rows($result)==1)
{
$row=mysql_fetch_assoc($result);
return $row['Rate'];
}
else{return FALSE;}
}
?>
Re: retrieve value from table
Posted: Sun Apr 24, 2011 3:49 pm
by superdezign
- Firstly, unless Lower and Upper are stored as string values, you should be comparing to a number, not a string. Remove the single quotes andy added in.
- Secondly, mysql_result will work fine. You don't need a loop, though.
- Thirdly, I'd suggest you try the actual query in MySQL before assuming that something is going wrong in your code. Sometimes, your data is formatted incorrectly.
- Fourth, why are you even taking $transaction as a parameter...?
And finally:
You forgot to select a database.
The code to use:
Code: Select all
function getrate($sale) { // You can add $transaction back in later if you plan to use it
$connection = mysql_connect('locahost', 'username', 'password') or die(mysql_error());
mysql_select_db('database_name'); // <-- IMPORTANT!!
$result = mysql_query("select rate from sheet1 where lower <= $sale and upper >= $sale", $connection) or die(mysql_error());
if ($result) {
return mysql_result($result, 0);
}
return false;
}
Personally, I would define your database connection elsewhere and either send it to the function or pull it in as a global instead.
Code: Select all
$db = mysql_connect('locahost', 'username', 'password') or die(mysql_error());
mysql_select_db('database_name');
function getrate($sale) {
global $db;
$result = mysql_query("select rate from sheet1 where lower <= $sale and upper >= $sale", $db) or die(mysql_error());
return is_resource($result) ? mysql_result($result, 0) : false;
}
Re: retrieve value from table
Posted: Sun Apr 24, 2011 8:49 pm
by todd2011
thanks for your reply appreciate it
here is my new code
Code: Select all
<?php
function getrates ($sales,$transaction)
{
$mysql_host = "localhost";
$mysql_user = "BASE";
$mysql_password = "test2";
$conn = mysql_connect ($mysql_host, $mysql_user, $mysql_password);
$sql = "SELECT Rate from Sheet1 where Lower <= '$sales' AND Upper >= '$sales'";
mysql_select_db('Title');
$result = mysql_query ($sql, $conn) OR DIE(mysql_error());
if(mysql_num_rows($result)==1)
{
$row=mysql_fetch_assoc($result);
return $row['Rate'];
}
else{return FALSE;}
}
?>
lower and upper are varchar(6) in the table
now its connecting to the database its just not returning the value
can i do an echo statement in getrate function and then stop processing the rest of the code
can you tell me whats the syntax to stop procesing the rest of the code in php i am new and learning as i m going
again thanks for your help
Re: retrieve value from table
Posted: Mon Apr 25, 2011 2:55 am
by ramblin54321
In your first code your variable was $sale and now you have it as $sales. And it should not be in quotes.
Re: retrieve value from table
Posted: Mon Apr 25, 2011 6:16 am
by getmizanur
i suggest you print out your $sql statement and execute it using either phpmyadmin or mysql command line. use var_dump and die function as i have done below in your code to get your $sql value.
on this occasion single quotes should not matter.
Code: Select all
<?php
function getrates ($sales,$transaction)
{
$mysql_host = "localhost";
$mysql_user = "BASE";
$mysql_password = "test2";
$conn = mysql_connect ($mysql_host, $mysql_user, $mysql_password);
mysql_select_db('Title');
$sql = "SELECT Rate from Sheet1 where Lower <= '$sales' AND Upper >= '$sales'";
var_dump($sql); // new line
die('break'); // new line
$result = mysql_query ($sql, $conn) OR DIE(mysql_error());
if(mysql_num_rows($result)==1)
{
$row=mysql_fetch_assoc($result);
return $row['Rate'];
}
else{return FALSE;}
}
?>
Re: retrieve value from table
Posted: Mon Apr 25, 2011 6:23 am
by andym01480
Guys just to help me - when should you and should you not use single quotes in a mysql query then?
Re: retrieve value from table
Posted: Mon Apr 25, 2011 6:25 am
by superdezign
The change from $sale to $sales happened in the function definition as well, so it shouldn't have any effect. And since he changed the data from integers to strings, he needs to use quotation marks now.
The
die() and
exit constructs will end a script indiscriminately. That's why I used
die(mysql_error()) both when you connect to your database and when you select a database instead of only at the query.
Take the rest of the advice from my post. Try the actual query in MySQL. Also, find out what your function is giving you. Is it returning false? Also, note that I did not check if mysql_num_rows() resulted in 1, considering it's completely possible that you'll have data in there that would result in more than one record being returned. I instead check if it is empty.
Re: retrieve value from table
Posted: Mon Apr 25, 2011 6:26 am
by superdezign
andym01480 wrote:Guys just to help me - when should you and should you not use single quotes in a mysql query then?
Single quotes (and double quotes) are use to delimit a string. If the data you are using is of a numerical type (i.e. INT), then do not use quotes.
Re: retrieve value from table
Posted: Mon Apr 25, 2011 10:14 pm
by todd2011
Thanks superdezign it worked . Appreciate your help.
Todd
Re: retrieve value from table
Posted: Tue Apr 26, 2011 5:56 am
by superdezign