/    Sign up×
Community /Pin to ProfileBookmark

MySQL Search column – optional

I’m writing a PHP function which will search for user. lastname, firstname, location etc.

In my SQL command, I’m doing the following in my where clause AND `lastname` LIKE CONCAT(‘%’, ? , ‘%’)

The question is, if the user doesn’t use Last Name field in the search form it’s looking for an empty string. I want to disregard this if not used. 

How can I do this?

to post a comment
PHP

1 Comments(s)

Copy linkTweet thisAlerts:
@markwilliams21Jun 18.2024 — To disregard the lastname field in your SQL query if it is not used, you can dynamically build your SQL query based on the provided search parameters. Here's a PHP function that demonstrates how you can achieve this:



Here's a breakdown of what this function does:

1. Initialize the base query: The query starts with SELECT * FROM users WHERE 1=1. The 1=1 condition is always true and is a common trick to simplify adding subsequent AND conditions.
2. Check each parameter: For each potential search parameter (firstname, lastname, location), the function checks if it is not empty.
3. Build the query dynamically: If a parameter is provided (i.e., it is not empty), the corresponding AND clause is added to the query, and the parameter value is added to the $params array.
4. Prepare and execute the statement: The final query is prepared and executed using PDO, with the parameters passed in the correct order.
5. Fetch and return results: The results are fetched and returned as an associative array.

This approach ensures that if a user does not provide a value for lastname (or any other field), that condition is simply not included in the final SQL query, avoiding any issues with searching for an empty string.
×

Success!

Help @kiwis spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 7.24,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ,
analytics: Fullres
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: Anonymous,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 1000 SATS,

tipper: @Nanditha,
tipped: article
amount: 1000 SATS,
)...