Problem with apostrophe

[brenda]

I have the following code to search my database (obviously some of the surrounding code is not provided) but I hope this provides enough for me to be clear about my problem) :

 

$sql="SELECT *  FROM member_details WHERE state='$state' AND location='$locn' ORDER BY Surname, Given_name";

      $result=mysqli_query($conn, $sql) or die("Error in selection -".mysqli_error($conn));

      $numrows=mysqli_num_rows($result);

      if($numrows==0)

{

echo "There are no members listed in this State/Territory/location.";

}

else

{

                while($row=mysqli_fetch_array($result))

        {

                        $surname=$row['Surname'];

                        if (strstr($surname, "'")) 

                        echo "yes";

                        else echo "no";

                }

        }

This works fine if I am searching for a surname that contains a letter such as ''a".  However when I search on the apostrophe, even though I know I have several surnames in the database which contain the apostrophe, I get a 'no' response for all of them.

 

 

Thank you.

Can anyone see what I am doing wrong here please or suggest a different approach? 

 

[phpSensei]

This query is searching by state and location, and according to your operator both location and match have to match exactly in order to return a result.

 

I think you're looking for the LIKE sql method no?

[harristweed]

From PHP Manual:

Note:

 

If you only want to determine if a particular needle occurs within haystack, use the faster and less memory intensive function strpos() instead.

[phpSensei]

MySQL has a nice built-in tool called "LIKE"

[brenda]

I presume you are suggesting that I change the code to something like the following (apologies if I have misunderstood what you are saying):

 

$sql='SELECT *

              FROM member_details WHERE Surname LIKE "%'%"';

 

Again, this works if I use LIKE "%a%" but not when I substitue the apostrophe for 'a'. 

 

In fact I get the following error message:

 

Parse error: syntax error, unexpected T_STRING in /home/...  and this message points to my $result=... line.

 

Did I misunderstand you?

[phpSensei]

you need to use mysql_real_escape_string on the variable which you are passing to the LIKE statement. This is protection against mysql injections, adding a Single quote will break the query...

[brenda]

Thanks PhpSensei,

 

I have been offline for a while 'cos my computer crashed so have just got back to this issue.  I didn't know about mysql_real_escape_string so will try that.

 

Brenda

[dodgeitorelse]

does that mean to use LIKE "%\'%"?

[brenda]

phpSensei,

 

The variable I am passing to the like statement is a column name - even so I tried that and it didn't work.  Did I misunderstand what you were saying?

 

Thanks again.