1. When you need a dynamic Search Query where variable parameters are presents you can use the following approach:

$sql = "SELECT * FROM users WHERE "; 
$where  = array("dist_id = :dist_id");
$params = array(':dist_id' => $distID);
if (isset($p->username) && !empty($p->username))
{
	array_push($where, "username LIKE :username");
	$params[':username'] = "%{$p->username}%";
}
if (isset($p->firstname) && !empty($p->firstname))
{
	array_push($where, "first_name LIKE :first_name");
	$params[':first_name'] = "%{$p->firstname}%";
}
if (isset($p->lastname) && !empty($p->lastname))
{
	array_push($where, "last_name LIKE :last_name");
	$params[':last_name'] = "%{$p->lastname}%";
}
if (isset($p->status) && !empty($p->status))
{
	if ($p->status == "Active") $status = 1;
	if ($p->status == "Disabled") $status = 0; 
	
	array_push($where, "customer_active = :customer_active");
	$params[':customer_active'] = "{$status}";
}
if (count($where) > 1 ) $whereStr = implode(" AND ", $where);
else $whereStr = $where[0];
$sql .= $whereStr;		
$contacts = $this->queryDB($sql, $params);

2. When you need a dynamic Insert Query where variable parameters are presents you can use the following approach:

$userid = $this->mysqli->real_escape_string($p->user['userid']);
$username = $this->mysqli->real_escape_string(@$p->username ? $p->username : "");
$email = $this->mysqli->real_escape_string(@$p->email ? $p->email : "");
$mobile = $this->mysqli->real_escape_string(@$p->mobile ? $p->mobile : "");
$bio= $this->mysqli->real_escape_string(@$p->bio ? $p->bio : false);

$params = array(
':userid' => $userid, 
':username' => $username, 
':email_address' => $email,
':mobile_number' => $mobile,
':status' => 1
);

$fields = array();

array_push($fields, 'userid, fullname, email_address, mobile_number, status');

if (isset($bio) && $bio != null) 
{		
	array_push($fields, "bio");			
	$params[':bio'] = $bio;
}

$fields = implode(", ", $fields);
$paramString = implode(", ", array_keys($params));		
$sql = "insert into users ($fields) values ($paramString)";

3. When you need a dynamic Update Query where variable parameters are presents you can use the following approach:

$userid = $this->mysqli->real_escape_string(@$p->userid ? $p->userid : "");
$username = $this->mysqli->real_escape_string(@$p->username ? $p->username : "");
$email = $this->mysqli -> real_escape_string(@$p->email ? $p->email : "");
$mobile = $this->mysqli -> real_escape_string(@$p->mobile ? $p->mobile : "");
$bio = $this->mysqli->real_escape_string(@$p->bio ? $p->bio : "");
$where  = " WHERE user_id = :user_id limit 1";
$params = array(':user_id' => $userid);
$update = array();

if ($username) 
{
	array_push($update, "username =:username");
	$params[':username'] = $username;
}

if ($email) 
{
	array_push($update, "email =:email");
	$params[':email'] = $email;
}

if ($mobile) 
{
	array_push($update, "mobile =:mobile");
	$mobile = preg_replace("/[^0-9,.]/", "", $mobile);
	$params[':mobile'] = $mobile;
}

if ($bio) 
{
	array_push($update, "bio =:bio");
	$params[':bio'] = $bio;
}

if (count($update) > 1 ) $updateQuery = implode(", ", $update);
$sql = "UPDATE user_contacts SET " . $updateQuery .  $where;		

Category: technology
Tagged with: MySQL • PHP

Copy & Share

Building Dynamic Queries in PHP MySQL PDO
https://bhupalsapkota.com/building-dynamic-queries-in-php-mysql-pdo/