PHP Concatenate

[ColinS]

Good morning,

 

I have a PHP page that searches a mysql database and returns any results.  the fields are FIRSTNAME and SURNAME, separate fields within the table.  

I have run the following search from the mysql command line and the results are correct:

select * from user_data where concat_ws(' ', firstname, lastname) LIKE 'Fred Smith';

However when I transfer this into my PHP page it doesn't return any results:

$sql = "SELECT * from user_data where concat_ws(' ', firstname, lastname) like '$search'";

 

[requinix]

Seems fine... I think. I mean, kinda no, but that's a different matter.

Have you tried outputting $sql to see if it has the value you think it does?

[maxxd]

Have you tried outputting $search to make sure it equates to Fred Smith? Beyond the basic issue requinix is alluding to, we're gonna need to see more code and you're gonna need to do some debugging.

Edited April 19, 2023 by maxxd
typo

[ginerjm]

Which is it - SURNAME or lastname?

And - maybe it's just me and my style but I would write the query differently:

$q = "select firstname, lastname from user_data
	where firstname='Fred' and lastname='Smith'";

This avoids the unnecessary concatenation and does the proper selection of the fields you need to receive rather than using the * to pull all the columns in.

[ahmedarain24]

Based on the code snippet you provided, it appears that you're using the variable `$search` in your SQL query. However, the issue might lie in the way you are constructing and using this variable in your PHP code. Here are a few things to consider:

1. SQL injection vulnerability: Directly interpolating user input into SQL queries can expose your application to SQL injection attacks. It is highly recommended to use prepared statements or parameterized queries to prevent this vulnerability. Here's an example of how you can modify your code to use prepared statements:

```php
// Assuming you have a database connection established earlier

$search = '%'.$search.'%'; // Add wildcards to match any occurrences of the search term

$sql = "SELECT * FROM user_data WHERE CONCAT_WS(' ', firstname, lastname) LIKE ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$search]);
$results = $stmt->fetchAll();
```

2. Wildcards in the LIKE comparison: To match partial names, you need to include wildcard characters (`%`) in the `LIKE` comparison. I've modified the code snippet above to add the `%` wildcards before and after the `$search` variable.

Make sure that the value of `$search` contains the actual search term you want to use.

3. Debugging and error handling: If you're still not getting any results, it's a good practice to add error handling to your code to catch any potential errors. You can use `try-catch` blocks or check for errors using `PDO::errorInfo()`.

```php
try {
    // Assuming you have a database connection established earlier

    $search = '%'.$search.'%'; // Add wildcards to match any occurrences of the search term

    $sql = "SELECT * FROM user_data WHERE CONCAT_WS(' ', firstname, lastname) LIKE ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$search]);
    $results = $stmt->fetchAll();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
```

By implementing these modifications, you should be able to execute the search query correctly in PHP and retrieve the desired results from the MySQL database.