Php and mysql filter

Question

I'm trying to create a filter in mysql and php. In my database, I have many fields like : name, phoneNumber, email... And even if I do something like

if(!empty($_POST['name'])){ 
$name=$_POST['name']
}
if(!empty($_POST['phoneNumber'])){ 
   $phoneNumber= $_POST['phoneNumber']
    }
if(!empty($_POST['email'])){ 
    $email=$_POST['email']
    }

If someone only enters the name field, how do I do to not include the other fields in the query?

$query=query("SELECT * FROM clients WHERE clientName=$clientName && phoneNumber=$phoneNumber && email=$email)

Rahul · Accepted Answer · 2018-05-23 16:22:52Z

1

you can try something like given below

if(!empty($_POST['name'])){ 
$name=$_POST['name'];
$query="SELECT * FROM clients WHERE clientName='$name'";
}
if(!empty($_POST['phoneNumber'])){ 
   $phoneNumber= $_POST['phoneNumber'];
   $query.= " AND phoneNumber='$phoneNumber'";
    }
if(!empty($_POST['email'])){ 
    $email=$_POST['email'];
    $query.= " AND email='$email'";
    }

NOTE:use prepared query because this is open to sql injection attack. this is demonstration only.

answered May 23, 2018 at 16:22

Rahul

1,6151 gold badge12 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Beeson · Accepted Answer · 2018-05-23 16:28:14Z

What I do for this sort of thing is have all the search terms sent to post within a single JSON object. So in javascript I collect all the search inputs and have an object where each key corresponds to the name of the input and each value corresponds to the val. For example in jQuery

var searchObj = {};
$(".searchInput").each(function() {
     searchObj[$(this).attr('name')] = $(this).val();
});
var searchJSON = JSON.stringify(searchObj);
//later send searchJSON to php

//in php
$seachObj = json_decode($_POST["searchJSON"], true);
$sql = "SELECT * in CLIENTS WHERE ";
foreach($searchObj as $key => $val) {
    $sql .= "$key = '$val' AND ";
}
$sql .= "1";

NOW THIS IS TOTALLY UNSECURED!!! Only included so that it's clear. Better to use PDO or mysqli, to use prepared statements. Like this:

$seachObj = json_decode($_POST["searchJSON"], true);
$sql = "SELECT * in CLIENTS WHERE ";
$vals = [];
foreach($searchObj as $key => $val) {
    $vals[] = $val;
    $sql .= "$key = ? AND ";
}
$sql .= "1";
//learn more about PDO to get this part
$stmt = $pdo->prepare($sql);
$stmt->execute($vars);

akshay khairmode · Accepted Answer · 2018-05-23 16:17:48Z

-1

I think this would do it.

$condition = "";

if(!empty($_POST['name'])){ 
$name=$_POST['name'];
$condition .= " AND clientName LIKE ".$name;
}
if(!empty($_POST['phoneNumber'])){ 
   $phoneNumber= $_POST['phoneNumber'];
    $condition .= " AND phoneNumber ='".$phoneNumber."'";
}
if(!empty($_POST['email'])){ 
    $email=$_POST['email'];
    $condition .= " AND email='".$email."'";
}

if(!empty($condition)){
   $query="SELECT * FROM clients WHERE ".ltrim($condition," AND");
}else{
   $query="SELECT * FROM clients";
}

answered May 23, 2018 at 16:17

akshay khairmode

718 bronze badges

1 Comment

JohnDickinson Over a year ago

Thanks for your answer but when I try your code, this mistake appears : Warning: PDO::query(): SQLSTATE[42S22]: Column not found: 1054 Champ 'qsfd' inconnu dans where clause in C:\wamp64\www\applocations\gestion-client.php on line 39. Do you know why?

Collectives™ on Stack Overflow

Php and mysql filter

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related