How to select all options from a drop list in php / mysql
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 ManchesterChoose last name:
All last names Lennon GallagherThe 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);
?>
Just have a special value for all (such as ALL) and put in:
if ($_POST['city'] == 'ALL')
{
$sql_city = '1';
}
else
{
$sql_city = "city = '".mysql_real_escape_string($_POST['city'])."'";
}
if ($_POST['lastname'] == 'ALL')
{
$sql_lastname = '1';
}
else
{
$sql_lastname = "lastname = '".mysql_real_escape_string($_POST['lastname'])."'";
}
$query = "SELECT * FROM user WHERE ".$sql_city." AND '".$sql_lastname."";
Then build the SQL statement based upon the the if's. The reason you use 1 is that it always evaluates to true.
加载中,请稍侯......
精彩评论