Page 1 of 1

Get Data From MYSQL using dropdown list

Posted: Thu Jan 20, 2011 1:43 am
by sonata
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

Re: Get Data From MYSQL using dropdown list

Posted: Thu Jan 20, 2011 2:13 am
by Peter Kelly
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

Posted: Thu Jan 20, 2011 9:13 am
by sonata
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

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

Posted: Fri Jan 21, 2011 10:44 pm
by sonata
Hi, I am still looking for any hint.

Thanks

Re: Get Data From MYSQL using dropdown list

Posted: Sat Jan 22, 2011 1:15 am
by califdon
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:

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>
...
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:

Code: Select all

...
<select name='direction'>
   <option value=1>To West</option>
   <option value=2>To South</option>
</select>
..
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:

Code: Select all

...
$zipcode = $_POST['zipcode'];
$direction = $_POST['direction'];
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:

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

Posted: Mon Jan 24, 2011 10:38 am
by sonata
Thank you califdon you gave me great help. I canceled the second drop down list. My final code is here:

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.