Drafting SQL search query based on user entry

Question

I'm currently coding a simple search script in PHP that requires three variables from the user namely: $Capacity, $Location and $RoomType

Capacity is a required field which the jquery validate plugin checks for numerical entry on input - but Location and RoomType are optional.

I'm trying to now draft a SQL query that will search the table rooms.

There are three columns in the table also called Capacity, Location and RoomType that I want to search using the variables.

How would I write this SQL query? Especially with $Capacity being required, $Location / $RoomType expected to be left blank or filled in at the users discretion?

Kavi Siegel · Accepted Answer · 2012-02-20 18:13:09Z

1

You could use LIKE ...% in your sql query, so that even when blank, it'll be treated as a wildcard.

$q = 'SELECT * FROM foo WHERE capacity = "'.$capacity.'" AND location LIKE "'.$location.'%" AND roomtype LIKE "'.$roomtype.'%"';

Of course, remember to escape the inputs.

answered Feb 20, 2012 at 18:13

Kavi Siegel

2,9942 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

methuselah Over a year ago

How do I check whether the Capacity variable is in a specific range for example if the user types in 50 then the entry in the table is 100 then it falls in range?

Kavi Siegel Over a year ago

In php, you can add $capacityA = $capacity - 50; $capacityB = $capacity + 50; and in MySQL change it to WHERE capacity BETWEEN $capacityA AND $capacityB

CodeCaster · Accepted Answer · 2012-02-20 18:13:29Z

0

Something like this should work:

function BuildSearchQuery($Capacity, $Location, $RoomType)
{

    $where = array();

    if (!empty($Capacity))
    {
        $where[] = "Capacity = '" . mysql_real_escape_string($Capacity) . "'";
    }
    if (!empty($Location))
    {
        $where[] = "Location = '" . mysql_real_escape_string($Location) . "'";
    }
    if (!empty($RoomType))
    {
        $where[] = "RoomType = '" . mysql_real_escape_string($RoomType) . "'";
    }

    if (empty($where))
    {
        return false;
    }

    $sql = "select * from `table` where ";
    $sql += implode(" AND ", $where);

    return $sql;

}

Although nowadays many frameworks exists that allow you to do this more easily and less error-prone than manually crafting queries.

answered Feb 20, 2012 at 18:13

CodeCaster

153k24 gold badges237 silver badges287 bronze badges

1 Comment

methuselah Over a year ago

I'm using Dreamweaver can you recommend any that I can use?

abhig10 · Accepted Answer · 2012-02-20 18:15:56Z

0

$query =select * from table where Capacity =$Capacity

if(isset($Location) && $Location!='') $query.= and Location LIKE '%$location%'

if(isset($RoomType) && $RoomType!='') $query.= and RoomType LIKE '%$RoomType%'

Making use of LIKE or = operator in query is upto you.

answered Feb 20, 2012 at 18:15

abhig10

5557 silver badges24 bronze badges

1 Comment

Kavi Siegel Over a year ago

if(isset($Location) && $Location!='') is equivalent to if(!empty($location))

Tony Hopkinson · Accepted Answer · 2012-02-20 18:16:59Z

0

Depend on how complex it is (and or not ???)

But basically

Select ... From Rooms Where Capacity = @Capacity 
and ((Location = @Location) Or (IsNull(@Location,'') = '')) 
and ((RoomType = @RoomType) or (IsNull(@RoomType,'') = ''))

Or some such.

If you aren't using parameterised queryies then replace @.... with the escaped inputs.

answered Feb 20, 2012 at 18:16

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

Collectives™ on Stack Overflow

Drafting SQL search query based on user entry

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related