Searching dropdown lists to get results from database

[flowers1]

Hi.. I am curretly developing on a site which has two dropdown lists which are linked to two database tables; one of which is 'station' and the other 'type'.
I have a third table called 'stores' which includes all the information, and has Type_ID and Station_ID as foreign keys.

Could anyone please help me with creating a search button that will search what is selected in both dropdown lists, and use this to display the relevent inforation i have in my 'stores' table?!
Is it possible to first look up the ID, so for example if Bond Street is selected in the dropdown list, could I look up the ID for this in the 'stations' table and then use this to get the information with this ID from the 'stores' table??

I hope this makes sense!! If not please ask any questions!
Any help at all will much be appreciated!!
Thanks!

[newbie2010]

Hi flowers1,

Maybe u can use this select to gain the selected street, gain the stations id out of the database and from there on get the information out of your stores table....

Hope it helps:

//next 4 should come out of database:
$opt[0]="main street";
$opt[1]="1st street";
$opt[2]="2nd street";
$opt[3]="3rd street";

echo '<form action="" method="POST">';
echo '<select name="streetname" onchange="this.form.submit();">';
for($var=0; $var<=3; $var++)
{
echo '<option value="'.$opt[$var].'"';
if ($_POST == $opt[$var])
{
echo 'selected="'.$opt[$var].'" >'.$opt[$var].'</option>';
} else {
echo '>'.$opt[$var].'</option>';
}
}
echo '</select>';
echo '</form>';
echo $_POST;

[JRM]

well, you start with writing the form with the two select elements.
Once the form is submitted, take the selections and put them into their respective table queries.
However, write the code and post it if you have any further problems.

[JRM]

Hi flowers1,

Maybe u can use this select to gain the selected street, gain the stations id out of the database and from there on get the information out of your stores table....

Hope it helps:

//next 4 should come out of database:
$opt[0]="main street";
$opt[1]="1st street";
$opt[2]="2nd street";
$opt[3]="3rd street";

echo '<form action="" method="POST">';
echo '<select name="streetname" onchange="this.form.submit();">';
for($var=0; $var<=3; $var++)
{
echo '<option value="'.$opt[$var].'"';
if ($_POST == $opt[$var])
{
echo 'selected="'.$opt[$var].'" >'.$opt[$var].'</option>';
} else {
echo '>'.$opt[$var].'</option>';
}
}
echo '</select>';
echo '</form>';
echo $_POST;

This never ceases to amaze me. By the OP text, you really have no idea what database structure he has, yet you assume all of this and write code for him!
Why don't you just write the whole thing , including the forms, PHP, table creation and query sql, debug it and send it to him?

[newbie2010]

This never ceases to amaze me. By the OP text, you really have no idea what database structure he has, yet you assume all of this and write code for him!
Why don't you just write the whole thing , including the forms, PHP, table creation and query sql, debug it and send it to him?

if i would've known the db structure maybe i would have....
and if you don't have positive critical comments don't comment at all

[JRM]

if i would've known the db structure maybe i would have....
and if you don't have positive critical comments don't comment at all

My grandmother used to say in such situations:
"You are so smart, you can walk on yourself."
My positive critical comment would be :
You seem to have the world by the tail with all of three posts under your belt...
Don't be a sap, but if you insist....go for it.

[flowers1]

Thank you all for your resonses!!

I tried to code provided however this only created a dropdown list and when the street is selected, it appears in writing below the list, not exactly what I need but thank you so much for the reply!!

So far I have..

<?php

<?php

echo "<select>";
while($row = mysql_fetch_array($r))
{
echo "<option>";
echo $row;
echo "</option>";
}
echo "</select>";

$q = "SELECT * FROM `type` ";
// Run query
$r = mysql_query($q);

echo "<select>";
while($row = mysql_fetch_array($r))
{
echo "<option>";
echo $row;
echo "</option>";
}
echo "</select>";

?>

<html>

<form action="<?=$_SERVER?>" method="post">
<input type="submit" value="Search">
</form></html>

What code would i need to make the search button produce the information i have in my 'stores' table using what has been selected from the dropdown lists?

Thanks again!!
Much appreciated!

[JRM]

You have to use the value attribute inside the option

while($row = mysql_fetch_array($r))
{
echo "<option  value=  {$row['Station_Name']}   >";

echo "</option>";
}

You may have to tinker with that a bit , but there is a good chance that it will work as is.
Please use code tags in your next post

[flowers1]

Hi JRM,

thanks for your quick response.

The code I previously posted works fine and provides me with the dropdown boxes and a search button.
What I would like to do is to use the dropdown boxes I have to produce search results displaying the information I have in my 'stores' table.
I tried the code you provided but this got rid of the information I had in my dropdown menus, I think that part of my code is fine, its just adding more to create actually search results from this?

Thanks!

[JRM]

Take the return value from the option and change queries in a switch statement or just use a given vaule in a query. It depends on how you have things set up.
Why not post what you have so we don't have to guess?
(use code tags)
Thanks

[newbie2010]

Hi JRM,

thanks for your quick response.

The code I previously posted works fine and provides me with the dropdown boxes and a search button.
What I would like to do is to use the dropdown boxes I have to produce search results displaying the information I have in my 'stores' table.
I tried the code you provided but this got rid of the information I had in my dropdown menus, I think that part of my code is fine, its just adding more to create actually search results from this?

Thanks!

In your code you will not know what is selected by the user, so you don't have the needed information for your query.
You need to add a name to the select so you can pass on the value to get a result from your query. (this is why i have the last echo in my script).