retrieve value from table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
todd2011
Forum Newbie
Posts: 9
Joined: Sat Apr 23, 2011 3:30 pm

retrieve value from table

Post 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

Code: Select all

id
Lower
Upper
Rate

so for example one of the record is saved like this


Code: Select all


3
15000
20000
125
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
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Re: retrieve value from table

Post 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!
todd2011
Forum Newbie
Posts: 9
Joined: Sat Apr 23, 2011 3:30 pm

Re: retrieve value from table

Post 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;}
    }

?>

User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: retrieve value from table

Post by superdezign »

  1. 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.
  2. Secondly, mysql_result will work fine. You don't need a loop, though.
  3. 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.
  4. 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;
}
todd2011
Forum Newbie
Posts: 9
Joined: Sat Apr 23, 2011 3:30 pm

Re: retrieve value from table

Post 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
ramblin54321
Forum Commoner
Posts: 32
Joined: Wed Nov 18, 2009 5:31 am

Re: retrieve value from table

Post by ramblin54321 »

In your first code your variable was $sale and now you have it as $sales. And it should not be in quotes.
User avatar
getmizanur
Forum Commoner
Posts: 71
Joined: Sun Sep 06, 2009 12:28 pm

Re: retrieve value from table

Post 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;}
    }
?>
 
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Re: retrieve value from table

Post by andym01480 »

Guys just to help me - when should you and should you not use single quotes in a mysql query then?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: retrieve value from table

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: retrieve value from table

Post 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.
todd2011
Forum Newbie
Posts: 9
Joined: Sat Apr 23, 2011 3:30 pm

Re: retrieve value from table

Post by todd2011 »

Thanks superdezign it worked . Appreciate your help.

Todd
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: retrieve value from table

Post by superdezign »

:)
Post Reply