How to select all options from a drop list in php / mysql
Posted
by Mirage81
on Stack Overflow
See other posts from Stack Overflow
or by Mirage81
Published on 2010-03-30T08:00:41Z
Indexed on
2010/03/30
8:03 UTC
Read the original article
Hit count: 256
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);
?>
© Stack Overflow or respective owner
