[Resolved] Newbie...Drop Down Menu Linked To Database

[usernot]

I have two pages and a database. The database has 'business' which is the business name and 'category' which is the business category. The first php page is a search page (which is basically a form) and the second is a results page. Here is what the results page looks like...

<?
//connect to mysql
//change user and password to your mySQL name and password
mysql_connect("host","user","password");

//select which database you want to edit
mysql_select_db("database");

$search=$_GET["search"];

if (isset($GET['search'])) {$search = $GET['search'];}
else {$search = '';}

if (!$search)

{

echo 'Please Input Something To Search For';
}
elseif (strlen($search) < 3)
{
echo 'Please Enter At Least Three Characters';
}
else

{

//get the mysql and store them in $result
//change whatevertable to the mysql table you're using
//change whatevercolumn to the column in the table you want to search
$result = mysql_query("SELECT * FROM table WHERE business LIKE '%$search%'");

//grab all the content
while($r=mysql_fetch_array($result))
{
//the format is $variable = $r["nameofmysqlcolumn"];
//modify these to match your mysql table columns

$business=$r["business"];
$category=$r["category"];

//display the row
echo "<strong>$business</strong><br> <u>Category</u>: $category <br><br>";
}
}
?>

This allows me to type in keywords of a business name which will query the database showing me the business name and category of that business that matched the keywords from the search.php page on the results.php page.

What I would like to do is instead of a keyword search is to have a drop down menu that lists all of the categories in the database, which one would select and then submit, to show all the businesses that have that particular category shown on the results.php page.

I would also like the categories in the drop down list to be alphabetical and only show one category for each category...in other words if there are three businesses that have a category of 'cleaning supplies' that it would only show once in the list instead of once for each business. Any help is appreciated!!!!

Thanks much.

[Roger_Ramjet]

What you can use is either a query with DISTINCT or GROUP BY, both will do it. Use the results to fill the option list. Use ORDER BY to sort them.

[usernot]

Roger Ramjet...I am a newbie and was wondering if you could help script that out for me. I understand the basic idea behind your suggestion but am not sure how to code it.

Thank you.

[Roger_Ramjet]

Assuming no seperate categories table then

$sql = "SELECT DISTINCT categories FROM  table ORDER BY categories";

//or

$sql = "SELECT categories FROM table GROUP BY categories ORDER BY categories";

//both will return a sorted list of categories

$result = mysql_query($sql) or die($sql . '<br />' . mysql_error());
//ALWAYS use that form to run your query, it will print the actual query submitted and the error message if you get an error: makes debug a whole lot easier

echo '<select name="cat" tabindex="2" title="select a category to display all businesses">';

while ($row = mysql_fetch_array($result)) {
echo '<option value="' . $row['category'] . '">' . $row['category'] . '</option>';
}
echo '</select>';

This should be about all you need.

[usernot]

Roger Ramjet,

Thanks for the post. Can you help with a modification?? I posted my results.php page above but here is my search.php page to the original keyword search:

<table width="100%" border="0">
<tr>
<td valign="top"><form method="get" action="results.php">
<font size="1" face="Verdana, Arial, Helvetica, sans-serif">Business Keyword Search:</font><br />
<input type="text" name="search" size=25 maxlength=25>
<input type="Submit" name="Submit" value="Submit">
</form></td>
</tr>
</table>

So...a person would come to this page first, which is just a form to type in the keyword. But what you have helped me out with is the drop down list...is there a way to put the drop down on this page (search.php) and then when the user selects their category from the drop down, clicks SUBMIT which would take them to another page (results.php) which would show them the results of the query (the results being all the businesses that fall under the category they selected from the drop down list)? Appreciate your help (once again) in advance.

[Roger_Ramjet]

You're lucky I'm sitting here with nothing to do:

$sql = "SELECT DISTINCT categories FROM  table ORDER BY categories";
$result = mysql_query($sql) or die($sql . '<br />' . mysql_error()); 

echo '<table width="100%" border="0">';
echo '<form method="POST" action="results.php">';
echo '<tr><td><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Business Keyword Search:</td>';
echo '<td><input type="text" name="word" size=25 maxlength=25></td></tr>';
echo '<tr><td>or Select a Category:</td>';
echo '<td><select name="cat" tabindex="2">';
while ($row = mysql_fetch_array($result)) {
echo '<option value="' . $row['category'] . '">' . $row['category'] . '</option>';
}
echo '</select></td></tr>';
echo '<tr><td colspan="2"><input type="Submit" name="Submit" value="Submit"></td></tr></form></table>';

Never use get on a form, always use post.

[usernot]

Roger Ramjet,

I put your code on the search.php page and then when I "ran" the page I got an error of "No Database Selected." So I added this database info at the top:

mysql_connect("localhost","user","password");
mysql_select_db("database");

Now when I select a category and select submit, it doesn't pull anything from the database and just says "Please Input Something To Search For" on the results.php. It also does that now if I try to type in a keyword.

The code that you previously posted was for the search.php right? So what exactly would need to be on the results.php?

I appreciate your time and instruction! Hopefully once again you have nothing to do :-) Thanks in advance.

[Roger_Ramjet]

//first check if the user has pressed submit button
if (isset($_POST['submit'])) {
//then check that they have selected a category
if (isset($_POST['cat'])) {
//create query if they have, I'm using generic names for table and field, you must use the names in your database
$sql = "SELECT * FROM table WHERE category = '". $_POST['cat'] , "' ORDER BY name";
//then run query
$result = mysql_query($sql) or die($sql . '<br />' . mysql_error()); 
//then display the results in a table
echo '<table>';
while ($row = mysql_fetch_array($result)) {
echo '<tr><td>'. $row['name'] . '</td></tr>';
}
echo '</table>';
}
}

Do not just cut and paste my code (see my signature). Apart from the fact that I'm dyxlexic so it will be full of typos, you will not learn or understand anythinh that way. Type it up yourself, and in the process think about where it is going in your existing code and what you need to change - be it just the odd } .

[usernot]

Roger Ramjet,

Thanks again for the reply. I modified the code a bit but on the results.php page I get an error with this line:

$sql = "SELECT * FROM table WHERE category = '". $_POST['cat'] , "' ORDER BY name";

I can't figure out what is wrong with it...could you help explain excatly what that line is doing? Maybe another way to code it?

Thanks!!!

[Roger_Ramjet]

It is a select query, one that reads the data from the table

SELECT = the sql operator
* means all feild for each record
FROM table means the database table = here you must insert the name of your table

WHERE = sql operator for limiting the records returned based upon conditions you supply next

category = '". $_POST['cat'] = the condition I am specifying, here you must use the name of the field in your table that contains the catagory data, my condition specifies that I want all businesses in the category selected and posted to this page by the user.

ORDER BY = sql operator that specifies the sort order in wgich the results are to be returned. My sort order is for businesses to be returned in alphebetical order. Here you must use the name of the field on your table that contains the business names.

[usernot]

Roger Ramjet,

Thanks for all your help...I was finally able to get the script working!

Happy Holidays.