How to select all options from a drop list in php / mysql
- by Mirage81
Thanks to stackoverflow.com's frienly experts I've managed to create my first php + mysql application.
The code searches a mysql database for last names and cities. The choices are made through two drop lists like these:
Choose city:
All cities
Liverpool
Manchester
Choose last name:
All last names
Lennon
Gallagher
The code would return eg. all the Lennons living in Liverpool.
However, I haven't been able to make the options "All cities" and "All last names" to work so that the code would return eg. all the Lennons living in any city or all the people living in Liverpool. So, how can that be done?
The code so far:
index.php
<?php
$conn = mysql_connect('localhost', 'user', 'password') or die("Connection failed");
mysql_select_db("database", $conn) or die("Switch database failed");
//this gets the cities from the database to the drop list
$query = "SELECT DISTINCT city FROM user".mysql_real_escape_string($city);
$result = mysql_query($query, $conn);
$options="";
while ($row=mysql_fetch_array($result)) {
$city=$row["city"];
$options.="<OPTION VALUE=\"$city\">".$city;
}
//this gets the last names from the database to the drop list
$query2 = "SELECT DISTINCT lastname FROM user".mysql_real_escape_string($lastname);
$result2 = mysql_query($query2, $conn);
$options2="";
while ($row2=mysql_fetch_array($result2)) {
$lastname=$row2["lastname"];
$options2.="<OPTION VALUE=\"$lastname\">".$lastname;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
<title>test</title>
</head>
<body>
<form action="get.php" method="post">
<p>
<select name="city">
<option value=0>Choose
<option value=1>All cities
<?=$options?>
</select>
</p>
<p>
<select name="lastname">
<option value=0>Choose
<option value=1>All last names
<?=$options2?>
</select>
</p>
<p>
<input value="Search" type="submit">
</p>
</form>
<br>
</body>
</html>
get.php
<?php
$conn = mysql_connect('localhost', 'user', 'password') or die("Connection failed");
mysql_select_db("database", $conn) or die("Switch database failed");
$query = "SELECT * FROM user WHERE city = '".mysql_real_escape_string($_POST['city'])."' AND lastname = '".mysql_real_escape_string($_POST['lastname'])."'";
$result = mysql_query($query, $conn);
echo $rowcount;
$zerorows=true;
while ($row = mysql_fetch_assoc($result))
{
$zerorows=false;
echo '<b>City: </b>'.htmlspecialchars($row[city]).'<br />';
echo '<b>Last name: </b>'.htmlspecialchars($row[lastname]).'<br />';
echo '<b>Information: </b>'.htmlspecialchars($row[information]).'<br />'.'<br />';
}
if($zerorows) echo "No results";
mysql_close($conn);
?>
