Adding Filters to SQL database/PHP Query

[ankit_dse]

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'];

 

  }

?>

• Quote
• MultiQuote
   
•  

[trq]

What have you done to debug the issue?

[ankit_dse]

Hi, I am new to PHP and SQL. Please advise what things I should try to resolve/debug this. After I publish the page with this new query, I get blank page. 

[ankit_dse]

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’

[mac_gyver]

you can simplify the logic by just putting the comparison terms into an array and imploding the array with the AND keyword. this will produce the correct result even if there's only 1 term.

[ankit_dse]

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! 

[Barand]

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";

[ankit_dse]

Thanks a ton Barand! Really appreciate your quick help. 

 

Do you know how I can paginate filtering results from SQL. i.e showing 5 results per page and then showing links to other pages.

[Barand]

http://lmgtfy.com/?q=php+mysql+pagination+tutorial