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

Answer 2

<?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;
?>

Answer 3

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.

1. Mysqli with Prepared Statement: http://php.net/manual/en/mysqli.prepare.php
2. PDO: http://php.net/manual/en/book.pdo.php
3. 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.

1. You have to first remove all the character other that alpha numeric and white spaces with the help of preg_replace() function.
2. 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.

1. If you have used any editor it will store the content directly to the DB as HTML tags alone.
2. 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.
3. 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; 
?>

Answer 4

Change your query from

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

To

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

Answer 5

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

Answer 6

Please try with this query. It will be helpful for getting your result

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

Answer 7

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).