Php search Splitting criteria type

Question

I have a php search form with two fields. One for $code another for '$name'.The user uses one or the other, not both. The submit sends via $_POST. In the receiving php file I have:

SELECT * FROM list WHERE code = '$code' OR name = '$name' ORDER BY code"

Everything works fine, however I would like that $code is an exact search while $name is wild. When I try:

SELECT * FROM list WHERE code = '$code' OR name = '%$name%' ORDER BY code

Only $code works while $name gives nothing. I have tried multiple ways. Changing = to LIKE, putting in parentheses etc. But only one way or the other works.

Is there a way I can do this? Or do I have to take another approach?

Thanks

Answer 1

If you only want to accept one or the other, then only add the one you want to test.

Also, when making wild card searches in MySQL, you use LIKE instead of =. We also don't want to add that condition if the value is empty since it would become LIKE '%%', which would match everything.

You should also use parameterized prepared statements instead of injection data directly into your queries.

I've used PDO in my example since it's the easiest database API to use and you didn't mention which you're using. The same can be done with mysqli with some tweaks.

I'm using $pdo as if it contains the PDO instance (database connection) in the below code:

// This will contain the where condition to use
$condition = '';

// This is where we add the values we're matching against
// (this is specifically so we can use prepared statements)
$params = [];

if (!empty($_POST['code'])) {
    // We have a value, let's match with code then
    $condition = "code = ?";

    $params[] = $_POST['code'];
} else if (!empty($_POST['name'])){
    // We have a value, let's match with name then
    $condition = "name LIKE ?";

    // We need to add the wild cards to the value
    $params[] = '%' . $_POST['name'] . '%';
}

// Variable to store the results in, if we get any
$results = [];

if ($condition != '') {
    // We have a condition, let's prepare the query
    $stmt = $pdo->prepare("SELECT * FROM list WHERE " . $condition);
    
    // Let's execute the prepared statement and send in the value:
    $stmt->execute($params);

    // Get the results as associative arrays
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
}    

The variable $results will now contain the values based on the conditions, or an empty array if no values were passed.

Notice

I haven't tested this exact code IRL, but the logic should be sound.