CODEWITHSUNDEEP
mysqli
Shrishail Uttagi
Shrishail Uttagi

Reputation: 436

How to fetch data from database (mysql) based on multiple search factor.

I am developing real estate website, user can search homes, flat based on budget,city,location, etc. I don't have idea how to fetch record from the database based user multiple search factor.Please can any one help me in this.

Upvotes: 0

Views: 198

Answers (4)

Uttam Kumar Roy
Uttam Kumar Roy

Reputation: 2058

Here is a simple example I have tested in SQLFiddle.

SQL Fiddle Exmple

CREATE TABLE house_type 
    (house_type_id int, name varchar(30) )
;

CREATE TABLE house 
    (house_id int,house_type_id int, name varchar(30) ,city varchar(30) ,location varchar(30),house_rent int)
;

INSERT INTO house_type
    (`house_type_id`, `name`)
VALUES
    (1, 'House'),   
    (2, 'Flat')
;

INSERT INTO house
    (house_id,house_type_id,name,city,location,house_rent)
VALUES
    (1, 1,'Duplex House 1','New York','Your location',1000),    
    (2, 1,'Duplex House 2','Washington DC ','My location',2000),    
    (3, 1,'Duplex House 3','Berkley','Other location',200)  ,
    (4, 2,'500 Sq2 Flat 1','Berkley','Other location',500), 
    (5, 2,'1000 Sq2 Flat 2','Washington DC','Yourlocation',2500)
;

here is SQL QUERY :

SELECT 
    ht.name ,
    h.name,
    h.city,
    h.location,
    h.house_rent
FROM 
    house h
    INNER JOIN house_type as ht ON ht.house_type_id=h.house_type_id 
WHERE 
    ht.name like '%House%' OR 
    h.name like '%house%' OR 
    h.location like '%My location%' OR
    h.house_rent > 1000

Upvotes: 0

dsp_user
dsp_user

Reputation: 2119

If you want to be able to search by either of the parameters (or all of them), then you need a slightly different approach from the ones suggested above. This would involve creating certain flags which will be used in the sql query.

Java code

public void getDbResults(Integer budget, String city ){ 
    Connection c=null;
    Statement stmt=null;
    ResultSet rs =null;
    //these 2 variables will tell you whether you'll search by budget or city (or both)
    int budgetPresent = (budget != null && budget.intValue()>0) ? 1 : 0;//>0 or perhaps some more appropriate value
    int cityPresent = (city != null && !city.isEmpty()) ? 1 : 0;

    String sqlQuery = "SELECT * FROM YOUR_TABLE WHERE (BUDGET <="+budget+" OR 0="+budgetPresent+") AND (CITY='"+city+"' OR 0="+cityPresent+")";

    try{
        c=getConnection();//getConnection returns a connection
        stmt=c.createStatement();
        rs = stmt.executeQuery( sqlQuery ); 

        while(rs.next()){
            //get values from the ResultSet 
        }
    }catch(SQLException sqle){
        //log or do something
    }finally{
        rs.close();
        stmt.close();       
    }   
}

It should be fairly simple to apply the above to your use case.

Upvotes: 1

GiapLee
GiapLee

Reputation: 436

You can try this code for demo in php:

<?php
     $city = 'london';
        $budget = '10000';
        $location = 'anycalocation';

        $query = "SELECT * From estate_table WHERE city = '" . $city . "' AND budget <= " . $budget . " AND location = '" . $location . "'";

        $result = $conn->query($query); //conn is connection object to your mysqldb

        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                      $id = $row['id'];
                      $city = $row['city'];        
                      $budget = $row['budget'];
                      $location = $row['location'];

                echo 'something you want';
            }
        } else {
            echo "0 results";
        }

?>

Upvotes: 0

Matt
Matt

Reputation: 2869

Look into the MySQL OR operator

For example

SELECT * FROM houses WHERE city = 'London' OR budget < 10000 OR location='potato'

Upvotes: 1

Related Questions