CODEWITHSUNDEEP
phpmysqlformssearch
user3707442
user3707442

Reputation: 3

Searching a MySQL table using PHP

I am trying to create a PHP file to help search a table built in MySQL from a webpage. I have built the form, which allows the user to enter keywords into two of the search criteria and a drop-down menu for the third. However, I am having trouble with the PHP file itself. I have appeared to do something wrong and cant quite figure out what is going wrong. If anyone can spot an error in the code below I'd really appreciate the help. Thanks.

// define variables and set to empty values
$Location = $Commemorating =  "";
if (isset($_GET['Region']) && !empty($_GET['Region'])) 
{
    $Region_name = $_GET['Region'];

    if (empty($_GET["Location"])) 
    {
        $Location = "";
    } 
    else 
    {
        $Location = ($_GET["Location"]);
    }

    if (empty($_GET["Commemorating"])) 
    {
        $Commemorating = "";
    } 
    else 
    {
        $Commemorating = ($_GET["Commemorating"]);
    }

$query = "SELECT Monument,
                Location,
                Commemorating,
                Region,

        FROM MONUMENTS 
        WHERE Region = '$Region'";
        //..if a location is specified run this query
        if ($Location != "") 
        {
            $query .= " AND Location LIKE '%$Location%'";
        }
        //..and if a name is entered run this query
        if ($Commemorating != "") 
        {
            $query .= " AND Commemorating LIKE '%$Commemorating%'";
        } 
        //..and if a region is specified run this query
        if ($Region != "All") 
        {
            $query .= " AND Region LIKE '$Region'";
        }
$query_run = mysql_query($query);
}

Upvotes: 0

Views: 80

Answers (2)

Alexander
Alexander

Reputation: 494

$query = "SELECT Monument,
            Location,
            Commemorating,
            Region,

Looks like you should strip list comma in field list from the query:

$query = "SELECT Monument,
            Location,
            Commemorating,
            Region

Like this.

There is a bit misunderstanding since you check is Region is not empty, then query for items in given Region and then add another cause in case of Region is not 'All'. So if I run your code with Region = 'All' then the query will return only the items that have Region set to 'All', which sounds a bit odd (I'd say monuments are at a single region, isn't it?).

You also use LIKE while may simple use = since you add sibgle quotes (') around strings so it won't give you any 'wildcard' match but slow down the query. Another thing to do is to do some mysql escape function to be sure you won't get SQL code in one of your GET query.

May I also suggest to short your code a bit:

$Region_name = isset($_GET['Region']) ? trim($_GET['Region']) : '';
if ($Region_name) {
    $Location = isset($_GET['Location']) ? trim($_GET['Location']) : '';
    $Commemorating = isset($_GET['Commemorating']) ? trim($_GET['Commemorating']) : '';
    $query = sprintf("SELECT 
                    Monument,
                    Location,
                    Commemorating,
                    Region
            FROM MONUMENTS
            WHERE 1=1%s%s%s",
                    $Region!='All' ? "AND Region='".mysql_real_escape_string($Region)."'",
                    $Location ? "AND Location='".mysql_real_escape_string($Location)."'",
                    $Commemorating ? "AND Region = '".mysql_real_escape_string($Region)."'",
    );
    ...etc...

I add 1=1 so I can easily add AND to the following causes without worry.

Upvotes: 1

Bas
Bas

Reputation: 61

Use $Region_name instead of $Region in your query. I see you depend on user input (via $_GET). Make sure you sanitize user input: https://stackoverflow.com/a/3126175/1071063

Upvotes: 0

Related Questions