How to make a dynamic select column on MySQL for my search

Example $_GET on my search form.

• $_GET['s'] = keyword
• $_GET['c'] = category
• $_GET['r'] = region
• $_GET['t'] = type

Example if statement :

if(isset($s)) {
  $s = "title LIKE '%{$ke开发者_运维技巧yword}%";
}

if(isset($c)) {
  $c = " AND category='{$category}'";
}

if(isset($r)) {
  $r = " AND region='{$region}'";
}

if(isset($t)) {
  $t = " AND type='{$type}'";
}

Then MySQL query :

$select = "SELECT * FROM ads WHERE $s.$c.$r.t AND status='1'";

Question :

How to make my $select can be dynamic? Current way is only worked if all input is not empty. Other than that give me syntax error. :P

Let me know..

---

Don't create multiple variables, you can do something like this:

$s = '';

if(isset($s)) {
  $s .= " AND title='%$keyword%'";
}

if(isset($c)) {
  $s .= " AND category='{$category}'";
}

if(isset($r)) {
  $s .= " AND region='{$region}'";
}

if(isset($t)) {
  $s .= " AND type='{$type}'";
}

$select = "SELECT * FROM ads WHERE status='1' $s";

---

Something like that?

$conditions = array("status='1'");
if(isset($s)) {
  $conditions []= "title LIKE '%{$keyword}%'";
}
if(isset($c)) {
  $conditions []= "category='{$category}'";
}
if(isset($r)) {
  $conditions []= "region='{$region}'";
}
if(isset($t)) {
  $conditions []= "type='{$type}'";
}
$select = "SELECT * FROM ads WHERE " . implode(" AND ", $conditions);

---

Are you sure it works as is now even if all the inputs are not empty?

Change your query to as follows AND add a space at the end of each criteria:

if(isset($s)) 
{   
    $s = " AND title LIKE '%$keyword%' "; //Changed the = to LIKE
}  
if(isset($c)) 
{   
    $c = " AND category='{$category}' "; 
}  
if(isset($r)) 
{   
    $r = " AND region='{$region}' "; 
}  
if(isset($t)) 
{   
    $t = " AND type='{$type}' "; 
}

$select = "SELECT * FROM ads WHERE status='1' $s.$c.$r.t"; 

---

Here you go:

$sArr = array("status='1'");

if(isset($s)) {
  $sArr[] = "title LIKE '%{$keyword}%";
}

if(isset($c)) {
  $sArr[] = "category='{$category}'";
}

if(isset($r)) {
  $sArr[] = "region='{$region}'";
}

if(isset($t)) {
  $sArr[] = "type='{$type}'";
}

$where = "WHERE " . join(' AND ', $sArr);
$select = "SELECT * FROM ads " . $where;

---

You can change inialization to $s = ' 1=1 '; to fix the problem.

Hope this helps.