-1

What is the proper way to filter results? What I have got so far is form where you can input firstname, lastname, phone and rest of stuff. How do I make statement ignore empty input fields instead of searching values which are empty?

I'm using prepared statements and when I filter stuff I use WHERE firstname = ? and lastname = ? and conditions change statement if something is empty. How can I prevent making hundreds of statements with conditions?

$stmt = $this->mysqli->prepare("SELECT * FROM cust");

    if(!empty($firstname) && !empty($lastname)) {
        $stmt = $this->mysqli->prepare("SELECT * FROM cust WHERE as_first = ? AND as_last = ?");
        $stmt->bind_param("ss", $firstname, $lastname);
    } else if(!empty($firstname) && empty($lastname)) {
        $stmt = $this->mysqli->prepare("SELECT * FROM cust WHERE as_first = ?");
        $stmt->bind_param("s", $firstname);
    } else if(empty($firstname) && !empty($lastname)) {
        $stmt = $this->mysqli->prepare("SELECT * FROM cust WHERE as_last = ?");
        $stmt->bind_param("s", $lastname);
    }
  • Do you have an example of your code thus far? – Luke Sep 24 '18 at 10:15
  • Hello, can you please include code from php file and html file to see how are you populating (using js from client side with ajax or directly from server side). thanks – Sigma Sep 24 '18 at 10:16
  • I think this covers it: https://stackoverflow.com/questions/8247970/using-like-wildcard-in-prepared-statement – noid Sep 24 '18 at 10:16
  • Yes, added. Took a while as I am on mobile and cloud ide didn't support a phone. –  Sep 24 '18 at 10:27
  • How can I prevent making hundreds of statements with conditions? Is clear in my opinion. –  Sep 24 '18 at 10:28

1 Answers1

1

Unfortunately you should write your own conditions, like:

$sql = 'SELECT...' // your original query
$sql .= !empty($phone) ? ' phone = :phone';

and binding params manually:

if (!empty($phone)) {
    $stmt->bindParam(':phone', '%'.$phone.'%', PDO::PARAM_STR);
}
fabrik
  • 14,094
  • 8
  • 55
  • 71
  • Thank you. Resolved the issue. Your method is better. –  Sep 24 '18 at 10:31
  • @Epz in the long run, you may want to use some decent ORM, but it's a good thing to start with the basics. Keep up the enthusiasm! – fabrik Sep 24 '18 at 10:33