Dear All,
I have sql db containing one table with these fields: ZipCode, TOwest_price, ToSouth_Price, email
I need to create a form in HTML and PHP to allow user to select the zipcode and towest or to south and get the price according to selection in a new page showing the user selection and the price, I am new to Php, so I am looking for any help or hint.
Thank you
Get Data From MYSQL using dropdown list
Moderator: General Moderators
-
Peter Kelly
- Forum Contributor
- Posts: 143
- Joined: Fri Jan 14, 2011 5:33 pm
- Location: England
- Contact:
Re: Get Data From MYSQL using dropdown list
So you want to have a dropdown with all the ZIPCodes, then you want to get the towest and tosouth listen in 2 dropdown fields?
Re: Get Data From MYSQL using dropdown list
Thank you Peter for your response.
Yes, I need 2 dropdown list, one for the zip and other contain the towest, tosouth, the idea is that: if I need to move from XXXXX zip Towest, how much it cost. all the price is stored in database my table look like this:
zipcode: Tosouth| TO West|
XXXXX $50 $60
Thank you
Yes, I need 2 dropdown list, one for the zip and other contain the towest, tosouth, the idea is that: if I need to move from XXXXX zip Towest, how much it cost. all the price is stored in database my table look like this:
zipcode: Tosouth| TO West|
XXXXX $50 $60
Thank you
Code: Select all
<?php require_once('../Connections/connection1.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
mysql_select_db($database_connection1, $connection1);
$query_FromZip = "SELECT price.zipcode FROM price";
$FromZip = mysql_query($query_FromZip, $connection1) or die(mysql_error());
$row_FromZip = mysql_fetch_assoc($FromZip);
$totalRows_FromZip = mysql_num_rows($FromZip);
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<form id="form1" name="form1" method="get" action="">
From
<select name="zip" size="1" id="zip">
<option value="value">Zip Code</option>
<?php
do {
?>
<option value="<?php echo $row_FromZip['zipcode']?>"><?php echo $row_FromZip['zipcode']?></option>
<?php
} while ($row_FromZip = mysql_fetch_assoc($FromZip));
$rows = mysql_num_rows($FromZip);
if($rows > 0) {
mysql_data_seek($FromZip, 0);
$row_FromZip = mysql_fetch_assoc($FromZip);
}
?>
</select>
To
<select name="to" id="to">
<option>South</option>
<option>West</option>
</select>
<input type="submit" name="cmdg" id="cmdg" value="Submit" />
</form>
</body>
</html>
<?php
mysql_free_result($FromZip);
?>Re: Get Data From MYSQL using dropdown list
Hi, I am still looking for any hint.
Thanks
Thanks
Re: Get Data From MYSQL using dropdown list
So your first web page needs a <form> that (perhaps among other things) has a drop-down to select a zip code, and it needs another drop-down to select one of 2 possible choices: To West, or To South. When the form is submitted, a PHP script must accept the form data (normally as "Post" data), do a database lookup to find whichever of the 2 prices for the selected zip code and send that data back to another web page. Right?
For the first drop-down, your PHP will look something like this:That will create your drop-down with zip codes, within your form, and when the form is submitted, the $_POST array index of the selected value will be 'zipcode', the name of the <select> element.
For the direction drop-down, all you need is this HTML:That will create your drop-down with 2 choices. Here I suggest simply giving the values of 1 or 2 to the choices, but you can use whatever values you want, numeric or text. When the form is submitted, the $_POST array index of the selected value will be either 1 or 2 (or "To West" or "To South" if you prefer).
In your "action" PHP script, you will recover these selections like this:Then, of course, you will use those values to form your query to find the price. By using numeric values for the direction choice, it will make it simple to write your query:
For the first drop-down, your PHP will look something like this:
Code: Select all
...
<select name='zip'>
<?php
$sql="Select ZipCode From price Order By ZipCode";
$resource=mysql_query($sql) or die(mysql_error());
while($row=mysql_fetch_array($resource)) {
$zip=$row[0];
echo "<option value='$zip'>$zip</option>";
}
?>
</select>
...For the direction drop-down, all you need is this HTML:
Code: Select all
...
<select name='direction'>
<option value=1>To West</option>
<option value=2>To South</option>
</select>
..In your "action" PHP script, you will recover these selections like this:
Code: Select all
...
$zipcode = $_POST['zipcode'];
$direction = $_POST['direction'];Code: Select all
...
$sql="Select ZipCode, ToWest_Price, ToSouth_Price From prices Where ZipCode = '$zipcode' ";
$resource=mysql_query($sql) or die(mysql_error());
$row=mysql_fetch_array($resource);
$price=$row[$direction];
...Re: Get Data From MYSQL using dropdown list
Thank you califdon you gave me great help. I canceled the second drop down list. My final code is here:
and it worked great.
Code: Select all
<form action="lin.php" method="post">
<select name=zipcode>
<option value= "select you pick up zip">Zipcode
<option value=11111>11111
<option value=22222>22222
.................
</select>
<input type=submit name=submit value="Get Rate">
</form>
<?php
$localhost="XXXXXXX";
$username="root";
$password="";
$database="XXXXXXX";
$zipcode= $_POST['zipcode'];
$linkid=mysql_connect($localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
mysql_select_db("price",$linkid);
$resultid=mysql_query("SELECT * FROM price WHERE zipcode='$zipcode' ", $linkid);
echo"<table border=1><tr><th #999999>zipcode</th>";
echo"<th>to South price</th><th>To west price</th><th></tr>";
while ($row = mysql_fetch_row($resultid))
{
echo"<tr>";
foreach ($row as $field)
{
echo"<td>$field</td>";
}
echo"</tr>";
}
echo"</table>";
mysql_close($linkid);
?>
and it worked great.