PHP query not working with variable

Question

In my page I have this code with echo's.

<?php

include("../config.php");

$q = mysql_query("SELECT propertyaddress FROM propertydetail WHERE active='yes' and leasedatefrom='".date("m-d-Y", strtotime('+1 months'))."'");
$res = mysql_fetch_array($q);
echo "<br/>pdetail=".$pdetail=trim($res['propertyaddress']);
echo $query="SELECT * FROM tenantmaster WHERE propertyaddress like '".$pdetail."'";
//echo $query="SELECT * FROM tenantmaster ";
//echo $query="SELECT * FROM tenantmaster WHERE propertyaddress = '1934 Heron Ave Unit D Schaumburg IL 60193'";

$resultdb = mysql_query($query);
if (!$resultdb) {
    die('Invalid query: ' . mysql_error());
}
else{
    echo "<br/>right query";
}

echo "<br/>num of row===".mysql_num_rows($resultdb);
$rowt = mysql_fetch_array($resultdb);
echo "<br/>row===".$rowt['name'];
exit;

?>

config.php

<?php

$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "";
$mysql_database = "gms_estate";

/*
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "";
$mysql_database = "gms_estate";
*/

$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) 
or die("Opps some thing went wrong");
mysql_select_db($mysql_database, $bd) or die("Opps some thing went wrong");

?>

And problem is my first query $q is working but query $query is also working but mysql_num_rows($resultdb) is not working and display 0 rows but, when I run echo query to database it's displaying 1 row. Why?

I tried $res['propertyaddress'] variable with trim() but not any success.

But when I use 1934 Heron Ave Unit D Schaumburg IL 60193 (that's my variable value) instead of $res['propertyaddress'] then it's working.

So, when I give value of variable directly then it's working but when I give variable then not. Why?

Answer 1

A common problem with comparing text entry from multi-line fields is that you probably have a "newline" or "tab" in the results from the first query, but that is not in the second query. (Other gotchas are "non-breaking space").

As you are echoing in HTML you won't see those in the output (so copying and pasting works), but they will be used in the query (so direct input fails). Try "View Source" (which shows newlines) or run in command line as that might give you more clues.

For now, strip out anything other than alpha numeric and spaces using preg_replace

$pdetail = trim( preg_replace("/[^0-9a-zA-Z ]/", "", $res['propertyaddress']) );

Eventually you'll want to adjust that to cover all your use cases, or of you find it's a "newline" just remove those - but you need to find what's different.

---

And, as per comments: check out mysqli / PDO parameterized queries. If the original address contained a single quote mark, that would also fail (with unknown results). It's a pain first off, but it'll save you a lot later on, makes your code easier to read and also will get more help here on SO (as your code is easier to read).

http://php.net/manual/en/pdo.prepared-statements.php