Code always returns 0 records

[gavs_sc]

My PHP search code always returns 0 records, even when the record exists.

Any help would be appreciated.

<?php
$conn = new mysqli("localhost", "root", "xxxxxx", "SAVR1984");

$Surname =$_POST['Surname'];
$Maidenname = $_POST['Maidenname'];
$Firstname = $_POST['Firstname'];
$sql="SELECT * FROM 1984 WHERE Surname LIKE $Surname AND Maidenname LIKE '%".$Maidenname."%' AND Firstname LIKE '%".$Firstname."%'";
If(! $conn ) {
die('Could not connect: ' . mysqlerror());
}

$result = $conn->query($sql);

if ($result->num_rows > 0){
while ($row = $result->fetch_assoc() ){
    echo $row["Surname"] . " " . $row["Maidenname"] . " " . $row["Firstname"] . " " . $row["ID"] . " " . $row["Occupation"] . " " . $row["Address"] . " " . $row["Notes"] . "<BR>";
      }
} else {
echo "0 records found";
}

$conn->close();
?>

[ginerjm]

1 - Please use the <> icon above to post your code proerly as I am doing now.

$conn = new mysqli("localhost", "root", "xxxxxx", "password");
$Surname = $_POST['Surname'];
$Maidenname = $_POST['Maidenname'];
$Firstname = $_POST['Firstname'];
$sql = "SELECT * FROM 1984 WHERE Surname LIKE '$Surname' AND Maidenname LIKE '%$Maidenname%' AND Firstname LIKE '%$Firstname%'";
If(!$conn) 
	die('Could not connect: ' . mysqlerror());	// MYSQLI ERROR????
$result = $conn->query($sql);
if ($result->num_rows > 0)
{
	while ($row = $result->fetch_assoc())
	{
		echo $row["Surname"] . " " . $row["Maidenname"] . " " . $row["Firstname"] . " " . $row["ID"] . " " . $row["Occupation"] . " " . $row["Address"] . " " . $row["Notes"] . "<BR>";
	}
} 
else 
	echo "0 records found";
$conn->close();

You should read up on using prepared queries and change this to use them.  Security.

Don't know if you are using mysqli or PDO for your database access but I'm guessing that mysqlerror is not a valid function.  Could be wrong.

Note the changes I made to your query.  Could be the whole issue.

Programming change:  you should check the connection results right away instead of moving on to building the query  and gathering the data items.  What's the point of doing more steps if the connection won't allow you to use them?

Suggestion.  Add the query statement to your echo when you get 0 records.  That way you can see the actual query you ran.

[Barand]

Bad SQL syntax, not uncommon when using concatenated segments.

If I provide variable values then echo your SQL

$Surname = 'Smith';
$Maidenname = 'Jones';
$Firstname = 'Mary';
$sql="SELECT * FROM 1984 WHERE Surname LIKE $Surname AND Maidenname LIKE '%".$Maidenname."%' AND Firstname LIKE '%".$Firstname."%'";
echo $sql;

giving

SELECT * FROM 1984 WHERE Surname LIKE Smith AND Maidenname LIKE '%Jones%' AND Firstname LIKE '%Mary%'
                                      ^^^^^ 

Don't use SELECT *, specify the columns you need.

1984 table name implies you have separate tables for the people for different years (of birth?). Why not put them in one table and add year column to the data.

As G said, use prepared statements, then the query becomes SELECT * FROM 1984 WHERE Surname LIKE ? AND Maidenname LIKE ? AND Firstname LIKE ? ;

Provide the data values when you execute the query.