PHP/MySQL Question. Exact match vs broad match script problem.?

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

    See also  How can I write pseudocode instructions to carry out this computational operation?

    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.

  • Leave a Comment