Tuesday, February 14, 2012

PHP query does not return result


This query is not returning any result as there seems to be an issue with the sql.




$sql = "select region_description from $DB_Table where region_id='".$region_id."' and region_status =(1)";
$res = mysql_query($sql,$con) or die(mysql_error());
$result = "( ";
$row = mysql_fetch_array($res);
$result .= "\"" . $row["region_description"] . "\"";
while($row = mysql_fetch_array($res))
{
echo "<br /> In!";
$result .= " , \"" . $row["region_description"] . "\"";
}
$result .= " )";
mysql_close($con);
if ($result)
{
return $result;
}
else
{
return 0;
}



region_id is passed as 1.



I do have a record in the DB that fits the query criteria but no rows are returned when executed. I beleive the issue is in this part ,




region_id='".$region_id."'



so on using the gettype function in my php it turns out that the datatype of region_id is string not int and thus the failure of the query to function as my datatype in my tableis int. what would be the way to get parameter passed to be considered as an int in php. url below




GetRegions.php?region_id=1



Thanks

6 comments:

  1. Try it like this:

    $sql = "SELECT region_description FROM $DB_Table WHERE region_id = $region_id AND region_status = 1"

    The region_id column seems to be an integer type, don't compare it by using single quotes.

    ReplyDelete
  2. Try dropping the ; at the end of your query.

    ReplyDelete
  3. First of all - your code is very messy. You mix variables inside string with escaping string, integers should be passed without '. Try with:

    $sql = 'SELECT region_description FROM ' . $DB_Table . ' WHERE region_id = ' . $region_id . ' AND region_status = 1';


    Also ; should be removed.

    ReplyDelete
  4. try this

    $sql = "select region_description from $DB_Table where region_id=$region_id AND region_status = 1";

    When you are comparing the field of type integer, you should not use single quote

    Good Luck

    Update 1

    Use this.. It will work

    $sql = "select region_description from " .$DB_Table. " where region_id=" .$region_id. " AND region_status = 1";

    ReplyDelete
  5. Who are all these people?
    What is this site?
    Where am I?

    Five answers and every one of them telling the OP to remove things that has absolutely no effect on the query.

    Why to answer a question if you have no knowledge nor practical experience on the matter at all?

    ReplyDelete
  6. You do not need the single quotes around the region id i.e.

    $sql = "SELECT region_description FROM $DB_Table WHERE region_id = $region_id AND region_status = 1"

    ReplyDelete