Jump to content

Adding Filters to SQL database/PHP Query


ankit_dse
Go to solution Solved by Barand,

Recommended Posts

Hello,

 

I am new to PHP. Here is the code i am trying to run in wordpress site, but nothing is appearing on running neither any error message nor any form, etc. Please advise.

 

******************************************************

 

<form name="input" action="" method="post">
City: <Select name="city">
<option "Input" value="<?php echo $_POST['city']; ?>"><?php echo $_POST['city']; ?></option>
<option value="">All</option>
<option value="Jaipur">Jaipur</option>
<option value="Bangalore">Bangalore</option>
</select>
 
Country: <Select name="country">
<option "Input" value="<?php echo $_POST['country']; ?>"><?php echo $_POST['country']; ?></option>
<option value="">All</option>
<option value="India">India</option>
<option value="China">China</option>
</select>
 
 
Occurence: <Select name="occurence">
<option "Input" value="<?php echo $_POST['occurence']; ?>"><?php echo $_POST['occurence']; ?></option>
<option value="">All</option>
<option value="Monthly">Monthly</option>
<option value="Yearly">Yearly</option>
</select>
 
<input type="submit" value="Search Events" />
</form>
 
<?php
 
 
$username=" ";
$password=" ";
$database=" ";
 
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
 
 
if(strlen($_POST['city'])>0){
$model=" model='".$_POST['city']."'";
$where++;
}
 
 
if(strlen($_POST['country'])>0){
$fuel=" fuel='".$_POST['country']."'";
$where++;
}
 
if(strlen($_POST['occurence'])>0){
$year=" year='".$_POST['occurence']."'";
$where++;
}
 
if($where==2){
$and=" AND ";
}
 
if($where==3){
$AND=" AND ";
 
$result="SELECT * FROM Events ".$where.$city.$and.$country.$AND.$occurence";
echo $result;
$result=mysql_query($result);
while($row = mysql_fetch_array($result))
  {
  echo $row['City'];
  echo $row['Country'];
  echo $row['Occurence'];
  echo $row['Description'];
 
  }
?>
Link to comment
Share on other sites

I tried to debug it. The only problem is with this part.

 

if(strlen($_POST['city'])>0){
$city=" city='".$_POST['city']."'";
$where++;
}
 
if(strlen($_POST['country'])>0){
$country=" country='".$_POST['country']."'";
$where++;
}
 
if(strlen($_POST['occurence'])>0){
$occurence=" occurence='".$_POST['occurence']."'";
$where++;
}
 
if($where==2){
$and=" AND ";
}
 
if($where==3){
$AND=" AND ";
}
 
if($where>0){
$where=" WHERE ";
}
 
 
$result="SELECT * FROM Events $where $city $and $country $AND $occurence ";
 
************************
 
If there are 3 filters results shown as below. It looks 1 AND is missing. Please advise.
 
SELECT * FROM Events WHERE city=’Jaipur’ country=’India’ AND occurence=’Yearly’
Link to comment
Share on other sites

Request you to advise how the code will look like if we use array for the part mentioned above. Apologies, actually I am very new to PHP and what I am trying to do is not replicating some of the existing code. Will really appreciate if you can share any sample code could be used for Filtering SQL results using pagination.

 

Thanks in advance! 

Link to comment
Share on other sites

  • Solution

It would look like this

$whereclause = '';
$where = array();

if(strlen($_POST['city'])>0){
    $city = mysql_real_escape_string($_POST['city']);
    $where[] = "city = '$city'";
}
 
if(strlen($_POST['country'])>0){
    $country = mysql_real_escape_string($_POST['country']);
    $where[] = "country = '$country'";
}
 
if(strlen($_POST['occurence'])>0){
    $occurence = mysql_real_escape_string($_POST['occurence']);
    $where[] = "occurence = '$occurence'";
}

if (count($where) > 0) {
    $whereclause = "WHERE " . join(' AND ', $where);
}

$sql = "SELECT * FROM social $whereclause";
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.