Page 1 of 1

Drop Down List + Database Connectivity

Posted: Thu Jun 18, 2009 3:17 am
by shakeelmoosa
Hi,

I would like to have 2 Form Field and also would like to have the Php MySql Database Connectivity.

Here is what i need -

1. City List (Drop Down)
2. Categories (Drop Down)

* Categories would have Business | Personal | Homemade etc etc (just example)

So when the user Select City + Category (like Business). It should display all the relevant business in that Selected City.

To Be Precise -

1. City - Mumbai
2. Category - Business


[It should display BUSINESS IN MUMBAI CITY.

It would be great if anyone can take trouble to explain this in details.

Thanx in Advance.

Shakeel

Re: Drop Down List + Database Connectivity

Posted: Thu Jun 18, 2009 11:20 am
by jgadrow
I'm not exactly sure what your requirements for this are because there are two options:
  • Submission of the form data returns a result
  • Changing the drop-down values populates an additional segment
For the first option, simply use the values submitted to build a SQL query and return the results to the user. For the second option, you will be doing the same thing, however, you will need to use AJAX (or a similar technology) to refresh the page's data without creating an additional page load.

I'm not an AJAX expert, so that's probably best left to someone else. However, I can provide a little help with the SQL portion. Assuming your cities and categories are numerically indexed in your database and you have form fields defined as:

Code: Select all

<select name="city" />
    <option value="1">
        Sacramento
    </option>
    <option value="2">
        San Francisco
    </option>
    <option value="3">
        Seattle
    </option>
</select>
<select name="category">
    <option value="1">
        Business
    </option>
    <option value="2">
        Homemade
    </option>
    <option value="3">
        Personal
    </option>
</select>
You could build your query with the following code:

Code: Select all

<?php
// declare arrays to be used in validating drop-down form data
$cities = array
(
    1 => 'sacramento',
           'san francisco',
           'seattle',
);
 
$categories = array
(
    1 => 'business',
           'homemade',
           'personal',
);
 
// validate submitted form data
// if your PHP version is less than 4.3, you'll need to compile PHP with the CType functions available
// if your PHP version is less than 5.1, you'll need to change the following conditional to:
// if (!isset ($_POST ['city']) || !isset ($_POST ['category']) || !ctype_digit ($_POST ['city']) || !ctype_digit ($_POST ['category']) || empty ($_POST ['city']) || empty ($_POST ['category']))
if (!isset ($_POST ['city']) || !isset ($_POST ['category']) || !ctype_digit ($_POST ['city']) || !ctype_digit ($_POST ['category']))
{
    throw new Exception ('Invalid data received!');
 
    // because we have a defined data set violation of this may indicate a hacking attempt
    // consider creating a specific Exception that would log suspicious activity
}
 
// store integer values from $_POST fields so we don't need to call intval () multiple times
$selectedCity        = intval ($_POST ['city']);
$selectedCategory = intval ($_POST ['category']);
 
// validate selected items
if (!isset ($city [$selectedCity]) || !isset ($category [$selectedCategory]))
{
    throw new Exception ('Invalid data received!');
 
    // insert same warning about security considerations here...
}
 
// since we're now certain that only valid data is present we can build the query
$query = sprintf ('SELECT * FROM someTable WHERE city = '%s' && category = '%s', $city [$selectedCity], $category [$selectedCategory]);
?>