0

I want to filter the exact information from a custom WordPress table no matter the user has filled all the text boxes or not. I have used a LIKE query but it`s not working properly if the user left one or two boxes but working fine with all the boxes are filled, I have also used OR query but that's too not working.

$table_name = $wpdb->prefix . "credofy_contact_form";
$sql_query = "SELECT * FROM $table_name WHERE your_name LIKE '$name' AND your_email LIKE '$email' AND your_phone LIKE '$phone' AND your_hobby LIKE '$hobby'";

enter image description here

0

First of all, you should never build SQL queries like you do - concatenating SQL with raw variables is insecure and it causes SQL Injection vulnerabilities. You should always prepare all queries.

As for the main part of question... You use AND operator in your query, so it will retrieve only rows, that will satisfy all conditions (so every field has to be like given value, so name has to be like given name and email has to be like given email, and so on).

If you change AND to OR then it will match rows that satisfy any of these conditions.

But if I understand your question correctly, then the user is filling filter form, right? So if I fill only email, then I should get all rows that match given email, and if I fill two fields, then I should get rows that match these two fields.

In such case you have to construct your query dynamically:

$table_name = $wpdb->prefix . "credofy_contact_form";
$sql = "SELECT * FROM $table_name WHERE 1=1 ";
$params = [];
if ( trim($name) ) {
    $sql .= " AND your_name LIKE %s";
    $params[] = $wpdb->esc_like($name);
}
... // same for other fields 
$results = $wpdb->get_results( $wpdb->prepare( $sql, $params ) );
  • Thank you very much for the help :-) , Your solution is working like a charm, Can you please explain what is 1=1? in select query. :-) – gaurav mishra Apr 21 at 17:04
0

You might want to use OR operator instead of AND e.g.

$sql_query = "SELECT * FROM $table_name WHERE your_name LIKE '$name' OR your_email LIKE '$email' OR your_phone LIKE '$phone' OR your_hobby LIKE '$hobby'";

AND operator is used when all the condition should met the criteria whereas OR operator will work even if single criteria is matched.

With LIKE operator you can also use wild cards

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.