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.
精彩评论