Creating Advanced Search Query With PHP Mysqli

Creating advanced search query with PHP Mysqli

Either use the REGEXP as:-

SELECT * FROM table WHERE columnName REGEXP "regular_expression";

or use REPLACE() function in WHERE clause as:-

SELECT * FROM table WHERE REPLACE(columnName, ":", "") LIKE "like_expression";

PHP Mysqli Advanced Search (Multi-Options)

Just figured it out guys after many many hours. thought I would post the code for you incase anyone else is facing the same issues. Thanks again for all your replies:
$query = array();

if (!empty($_GET['vType'])) {
$vType = $_GET['vType'];
$query[] = "vType='$vType'";
}
if (!empty($_GET['make'])) {
$make = $_GET['make'];
$query[] = "make='$make'";
}
if (!empty($_GET['model'])) {
$model = $_GET['model'];
$query[] = "model='$model'";
}
if (!empty($_GET['minyear'])) {
$yearfrom = $_GET['minyear'];
$query[] = "years>='$yearfrom'";
}
if (!empty($_GET['maxyear'])) {
$yearto = $_GET['maxyear'];
$query[] = "years<='$yearto'";
}
if (!empty($_GET['minPrice'])) {
$minprice = $_GET['minPrice'];
$query[] = "vPrice>='$minprice'";
}
if (!empty($_GET['maxPrice'])) {
$maxprice = $_GET['maxPrice'];
$query[] = "vPrice<='$maxprice'";
}
if (!empty($_GET['location'])) {
$location = $_GET['location'];
$query[] = "location='$location'";
}
if (!empty($query)){
$where = "WHERE";
}else{
$where = "";
}

$searchquery = implode(' AND ', $query);
$getsearch = "SELECT * FROM searchdata $where $searchquery";

$ressearch = mysqli_query($conDB, $getsearch);
if (mysqli_num_rows($ressearch) === 0) {
echo 'Nothing Found';
}else{
while($r = $ressearch->fetch_assoc()) {
//echo results here...
}

Thanks everyone :)

Query for PHP/MySql AND/OR for an advanced search

You can check which of the fields are set and make query accordingly at runtime. something like this you can do:

$query = "";
$keyword = $_REQUEST['keyword'];
$country = $_REQUEST['country'];
$category = $_REQUEST['category'];
if(isset($keyword)){//if keyword set goes here
   $query = "SELECT * FROM table1 WHERE Name LIKE '%$keyword%' OR ZipCode LIKE '%$keyword%' OR country LIKE '%$keyword%'";
   if(isset($category)){
     $query .= "AND category LIKE '$category'";
   }
   if(isset($country)){
     $query . = "AND country LIKE '$country'"
   }
}else if (isset($category)){ //if keyword not set but category set then goes here
  $query = "SELECT * FROM table1 WHERE category LIKE '$category'";
  if(isset($country)){
    $query . = "AND country LIKE '$country'";
  }
}else if(isset($country)){//if only country set goes here
  $query = "SELECT * FROM table1 WHERE country LIKE '$country'"
}

Advanced Search PHP, MySQL

First of all, I feel I should make a comment about preferring prepared statements over injecting request values directly into a query string.

The simplest solution to your issue would be something like this:

<?php

$sql="select * from Book_info where user_id=$u_id";

$title = $_REQUEST['title'];
$location = $_REQUEST['location'];

if($title != null) {
    $sql .= " AND title like '%$title%'"; 
}

if($location != null) {
    $sql .= " AND location like '%$location%'";
}

//and so on...

the idea being that you check if the value is null, and then append a new where clause to your query so long as the value is not null (or empty or whatever other predicates you want to add).

Advanced search in data tables using PHP

Let me give you example for search by name you can do same for other search field also.

add below input tag in your html for search field

<input type="text" name="user_name" id="user_name" />

now change your script as per below,

<script type="text/javascript">
 $(document).ready(function() {
                    var table=$('#example').DataTable( {
                        dom: 'Bfrtip',
                        buttons: [
                            'copy', 'csv', 'excel', 'pdf', 'print'
                        ],
                        "bProcessing": true,
                        "ajax": {
                               url: "dtServerSideBasicScript.php",
                               data: function (d) {

                                   d.user_name = function () {

                                     return $("#user_name").val();
                                   };
                               },
                          },

                        "aoColumns": [{
                            mData: 'name','gender','age'
                        }]
                    } );

                 $('#user_name').keyup(function () {
                    table.draw();
                 });
    });
</script>

on server side you will get user_name parameter in $_GET, like $_GET['user_name']. then you can use that value in your sql query with like.

Same way you can implement the age range and gender selection.

Search Filtering with PHP/MySQL

Like all the other post you will need to append all the conditions with AND like so. This is the cleanest answer so far. Remember to real escape your strings though use the mysqli OOP way instead of the old mysql. Just a suggestion.

Heres an example of a typical query.

The correct way:

SELECT * FROM donar WHERE name='dxenaretionx' AND sex='M';

The way you are doing it

SELECT * FROM donar WHERE name='dxenaretionx' sex='M';

Code:

function search_donar($_POST) {
    $by_name = $_POST['by_name'];
    $by_sex = $_POST['by_sex'];
    $by_group = $_POST['by_group'];
    $by_level = $_POST['by_level'];

    //Do real escaping here

    $query = "SELECT * FROM donar";
    $conditions = array();

    if(! empty($by_name)) {
      $conditions[] = "name='$by_name'";
    }
    if(! empty($by_sex)) {
      $conditions[] = "sex='$by_sex'";
    }
    if(! empty($by_group)) {
      $conditions[] = "blood_group='$by_group'";
    }
    if(! empty($by_level)) {
      $conditions[] = "e_level='$by_level'";
    }

    $sql = $query;
    if (count($conditions) > 0) {
      $sql .= " WHERE " . implode(' AND ', $conditions);
    }

    $result = mysql_query($sql);

    return $result;
}

---

Related Topics