Database Search

[tascam424]

Hi i am an absolute PHP/MYSQL Novice but i guess we all gotta start somewhere.

 

I have a table in my database with names and addresses, f_name, s_name, street, zip_code, city etc etc etc

 

What i want to do is display a drop down of all cities in my database then click on any city in the list to display all the details of people in that city, showing all fields.

 

So i have got this far ..

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'cities_wrdp1';
mysql_select_db($dbname);


$sql = "SELECT DISTINCT City FROM PBM";
$result = mysql_query($sql);

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

 

This produces the nice drop down list of cities from the database ...

 

Now on click i would like to return results similar to this 

 

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'cities_wrdp1';
mysql_select_db($dbname);


$query = sprintf("SELECT * FROM PBM");

$result = mysql_query($query);


if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}

echo "<br />";
echo "<table border='0'>";


while ($row = mysql_fetch_assoc($result))
{
echo "<tr><td>";
echo "<a href=\"" .$row['profileURL'] . "\">
<img src=\"" . $row['faviconURL'] . "\" border=0 alt=\"" . $row["Thumbnail"] . "\">
</a>";
echo "</td><td align='center'>";
echo $row['f_name'];
echo "</td><td align='center'>";
echo $row['s_name'];
echo "</td><td align='center'>";
echo $row['number'];
echo "</td><td align='center'>";
echo $row['street'];
echo "</td><td align='center'>";
echo $row['zip'];
echo "</td><td align='center'>";
echo $row['city'];
echo "</td><td align='center'>";
echo $row['Country'];
echo "</td><td align='center'>";
echo $row['email_address'];
echo "</td><td align='center'>";
echo $row['fb'];
echo "</td><td align='center'>";
echo $row['web_site'];
echo "</td><td align='center'>";
echo $row['phone'];
echo "</td><td align='center'>";
echo $row['land'];
echo "</td><td align='center'>";
echo $row['age'];
echo "</td></tr>";
}

echo "</table>";


mysql_free_result($result);

 

So my question is .. how do i make the first piece of code selectable to produce similar results to the second piece of code based on the city selected ?

Thanks for any help .. and please excuse my ignorance ..

[dcro2]

First you have to make that <select> part of a form.

<form action="displaycity.php" name="selectCity" method="get">
<?php

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'cities_wrdp1';
mysql_select_db($dbname);


$sql = "SELECT DISTINCT City FROM PBM";
$result = mysql_query($sql);
?>
<select name='City' onchange="document.forms['selectCity'].submit()">
<?php
while ($row = mysql_fetch_array($result)) {
    echo "<option value='" . $row['City'] . "'>" . $row['City'] . "</option>";
}
?>
</select>
</form>

 

Then, in displaycity.php:

<?php
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'cities_wrdp1';
mysql_select_db($dbname);


$query = "SELECT * FROM people WHERE city = '".mysql_real_escape_string($_GET['City'])."'";
$result = mysql_query($query);


if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}
?>
<table>
<tr><th>First Name</th><th>Last Name</th><th>Street</th><th>City</th><th>Zip Code</th></tr>
<?php
while($row = mysql_fetch_array($result)) {
echo "<tr>"
echo "<td>".$row['f_name']."</td>\n";
echo "<td>".$row['s_name']."</td>\n";
echo "<td>".$row['street']."</td>\n";
echo "<td>".$row['city']."</td>\n";
echo "<td>".$row['zip_code']."</td>\n";
echo "</tr>";
}
?>
</table>