I am accessing a MySQL database by using a small php script on my webserver. I have created a search function that is not working correctly. There are no results being displayed currently. Here is the code snippet
[CODE]
$srch = $_POST[‘search_by’]; //db column to search
$srch_type = $_POST[‘search_type’]; // exact or broad match search
$search = $_POST[‘query’]; //search string
if ( $srch_type == “broad” ){
$srch_method = “LIKE” . “$search”;
}
else {
$srch_method = “=” . “$search”;
}
mysql_connect(“host”,”username”,”pass”);
mysql_select_db(“db_name”);
if ( $srch == “LastName” ){
$result = mysql_query(“select * from user_records WHERE LastName ‘$srch_method'”);
}
else if ( $srch == “ProjectAddress” ){
$result = mysql_query(“select * from user_records WHERE ProjectAddress ‘$srch_method'”);
}
[/CODE]
The script was working fine until my boss told me to implement a “exact match” or “broad match” variable. So I am positive all my variables are correct. I’m just missing something thats not obvious to a novice in php.
✅ Answers
? Favorite Answer
In MySQL, when you use the LIKE clause, you must use a % before and after your search string, otherwise you’re doing an exact match. Use the following as a guide:
LIKE the – match the word the exactly
LIKE %the – match anything that ends in the
LIKE the% – match anything that starts with the
LIKE %the% – match anything with the in it
Basically, % is the wildcard character for LIKE in MySQL
Maybe I’m wrong about mysql, but don’t you have to quote strings in where clauses?
You say
SELECT * FROM USER_RECORDS WHERE LastName = ‘Tester’
instead of
SELECT * FROM USER_RECORDS WHERE LastName = Tester
and
SELECT * FROM USER_RECORDS WHERE LastName ‘= Tester’
is also incorrect, which appears to be how you write the query currently
Also doesn’t mysql have a % at the end of like queries like other databases do?
Finally, you may know this, but don’t use select * ever on a production database, use a column list instead, so that you may have your query cached by the query optimizer.