Stack Exchange
PHP query not working with variable
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?

check your date format m-d-Y should be Y-m-d maybe, for second query check with %keywords%

query is working but not mysql_num_rows($resultdb).

your query is returning nothing, 0 record. what is theresult of this echo echo $query="SELECT * FROM tenantmaster WHERE propertyaddress like '".$pdetail."'";

it will display SELECT * FROM tenantmaster WHERE propertyaddress = '1934 Heron Ave Unit D Schaumburg IL 60193' and it's just for understanding...so,query is displaying right but then mysql_num_rows($resultdb) is not working.

run directly: $query="SELECT * FROM tenantmaster WHERE propertyaddress = '".trim($res['propertyaddress'])."'";

i tried it @devro but not any success...

what you want to say @cmnardi i am little week in english and program debugging???

run var_dump($res['propertyaddress']) and var_dump($resultdb)

he is telling, use this before $query >>>>> echo trim($res['propertyaddress']);

I really hope you don't have die in your production code.

@cmnardi var_dump($res['propertyaddress']) display string(42) and var_dump($resultdb) display resource(6) of type (mysql result).

You are fetching a single row? If not put your fetch in a while loop: while($res=mysql_fetch_array($q)){ echo "<br/>pdetail=".$pdetail=trim($res['propertyaddress']);  };

You should switch to mysqli and prepared statements or PDO, mysql is DEPRECATED.

yes i am fetching single row. @M.I

Dont use mysql_* functions...

before you even start debugging your code, you need to bin the mysql driver. its deprecated and nasty.  use mysqli instead.

try once as you are matching with like with % accessors
$query="SELECT * FROM tenantmaster WHERE TRIM(propertyaddress) like '".$pdetail."'";

did you check the query with % in like propertyaddress like '%".$pdetail."%'"?

yes i checked with that but not any success.

In your example you use like statement and you say to have result with the equal = statement (of the WHERE clause). Fix that typo please. Now about mysql_num_rows() I had this issue many times and was unable to fix it. Give a try to mysqli_ : this is the same code, just add an i in each function.

Why shouldn't I use mysql_* functions in PHP?

@Divyesh Jesadiya . I have provided with solutions and explanations. Share thoughts about my code. And if you face any hindrance let me know

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

"Get more help here on SO" should probably read "Get less flak here on SO"!

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

    $connect = new PDO("mysql:host=$mysql_hostname;dbname=$mysql_database", $mysql_user, $mysql_password);

    $q = "SELECT propertyaddress FROM propertydetail WHERE active='yes' and leasedatefrom='".date("m-d-Y", strtotime('+1 months'))."'";
    $result = $connect->prepare($q);
    $status = $result->execute();
    $res = $result->fetch(PDO::FETCH_ASSOC);
    $pdetail = $res["propertyaddress"];

    $q = "SELECT * FROM tenantmaster WHERE propertyaddress = ".$connect->quote($pdetail);
    /* or
    $q = "SELECT * FROM tenantmaster WHERE propertyaddress like ".$connect->quote($pdetail);
    */
    $result = $connect->prepare($q);
    $status = $result->execute();

    echo "<br/>num of row===".$result->rowCount();

    if (($status) && ($result->rowCount() > 0))
    {
        $res = $result->fetch(PDO::FETCH_ASSOC);
        echo "<br/>row===".$res['name'];
    }

    $connect = null;
?>


First of all it is recommended to use the mysqli along with prepared statement since it will avoid the SQL Injections that will occur. Now your code is purely injectable and it can be rectified with the help of mysqli along with prepared statements or with the help of PDO.

Mysqli with Prepared Statement: http://php.net/manual/en/mysqli.prepare.php
PDO: http://php.net/manual/en/book.pdo.php
PDO with Prepared: http://php.net/manual/en/pdo.prepare.php


Explanations

As per the usage of trim() in your variable you will be getting the strategy as per this alone.
trim- Strip whitespace (or other characters) from the beginning and end of a string
Description: This function returns a string with whitespace stripped from the beginning and end of str. Without the second parameter, trim() will strip these characters: 

" " (ASCII 32 (0x20)), an ordinary space. 
"\t" (ASCII 9 (0x09)), a tab.
"\n" (ASCII 10 (0x0A)), a new line (line feed).
"\r" (ASCII 13 (0x0D)), a carriage return. 
"\0" (ASCII 0 (0x00)), the NUL-byte. 
"\x0B" (ASCII 11 (0x0B)), a vertical tab.

Note:

But trim() does not remove the white space which is present at the middle of the string that is given.

Example:
trim() trims characters from the beginning and end of a string, it may be confusing when characters are (or are not) removed from the middle. trim('abc', 'bad') removes both 'a' and 'b' because it trims 'a' thus moving 'b' to the beginning to also be trimmed. So, this is why it "works" whereas trim('abc', 'b') seemingly does not. 
Scenario: Hence in order to remove all teh white space that is present in the string you have to use the following.

You have to first remove all the character other that alpha numeric and white spaces with the help of preg_replace() function.
After replacing all the above mentioned items you have to then trim upon the variable so that it will remove all the white spaces that has been present and hence your string will look as the string which you give in hard code or directly.

3. You can directly adopt the method by strong the trimmed value into a variable and then echo it.
preg_match - Perform a regular expression match
Description: Searches subject for a match to the regular expression given in pattern. 
Return Values: preg_match() returns 1 if the pattern matches given subject, 0 if it does not, or FALSE if an error occurred. 

Solution to your Problem


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.

Reason: This Error occurs when you printing the values directly from the Database.

If you have used any editor it will store the content directly to the DB as HTML tags alone.
Hence in order remove the HTML tags you have first store the DB value into a variable by replacing all the values and then you have to display it.
If you echo it directly you will not be seeing the HTML tags but if you view it by using CTRL+U you will be seeing it in the seeing it and it is not recommended. Hence you have to remove or strip of the parameters and then trim it.

Query:
preg_replace("/(\W)+/", "", $word_to_undergo);

Note: \W - Anything that isn't a letter, number or underscore.
So, in terms of Unicode character classes, \W is equivalent to every character that are not in the L or N character classes and that aren't the underscore character.
Alternative Solution:
To remove just put a plain space into your character class: 
Query:
$needed_text = preg_replace("/[^A-Za-z0-9 ]/", "", $word_to_undergo);

Along with the above Solution you have to preform the trim so that it produces a perfect string as per your choice and it will match up with the query and produce the result.

As per Suggestion One: It should be

$final_value = preg_replace("/(\W)+/", "", $word_to_undergo);
$final_value = preg_replace("/(\W)+/", "", $res['propertyaddress']);


As per Suggestion Two: It should be

$final_value = preg_replace("/[^A-Za-z0-9 ]/", "", $word_to_undergo);
$final_value = preg_replace("/[^A-Za-z0-9 ]/", "", $res['propertyaddress']);

Addition to the above solution you can try using like this to.
<?php
$display=trim($res['propertyaddress']);
echo $display; 
?>


Change your query from 
echo $query="SELECT * FROM tenantmaster WHERE propertyaddress like '".$pdetail."'";

To 
echo $query="SELECT * FROM tenantmaster WHERE propertyaddress like '%".$pdetail."'%";


Instead
echo "<br/>pdetail=".$pdetail=trim($res['propertyaddress']);

Use
$pdetail=trim($res['propertyaddress']);
echo "<br/><pre>pdetail='".$pdetail."'</pre>";

And you will can see real variable value 

Please try with this query. It will be helpful for getting your result
$query='SELECT * FROM tenantmaster WHERE propertyaddress like "'.$pdetail.'";


You are missing mysql_free_result($q); and mysql_free_result($query) to announce that you are finished with the query.
And do change to mysqli (or PDO).
