根据下拉选择显示特定的MySQL行

Question

I created a form that includes a dropdown field

<select name="locationselect" id="locationselect" tabindex="7">
  <option value="Location1">Location 1</option>
  <option value="Location2">Location 2</option>
  <option value="Location3">Location 3</option>
  <option value="Location4">Location 4</option>
</select>

Upon submission I want to pull the location they selected from the dropdown and print a specific row from my MySQL database that would show them an address. So if they select Location 1 it would show:

Company Name 
1234 ABC Street
New York, NY 12345

But if they select Location 2 it would show:

Other Company
5678 XYZ Street 
San Francisco, CA 12345 

And so on for 99 different locations.

Here's what I started with but I'm missing a variable defining the array $fulladdress - I am new to MySQL so I'm not even sure what to put after Select? Is there a row number or can I put the contents of the first column or what type of ID?

switch($_GET['locationselect']){

case 'Location1':
   mysql_query("SELECT ____ FROM locations");
   break;
case 'Location2':
   mysql_query("SELECT ____ FROM locations");
   break;
  }
  while($row = mysql_fetch_array($fulladdress))
  {
  echo ($row['PlaceName']." Office Building<br>".$row['Address']."<br>".$row['City'].", CA      
  ".$row['Zip']."<br><br>");
  }

Any help for how to solve this problem would be greatly appreciated. I know my code is messy but I'm hoping you can get the idea of what I'm trying to do.

Thank you!!

Answer 1

I'm not too sure about using the case statement, what you can do is a parameterised query. So it would be:

mysql_query("Select fulladdress from Location where location ='" . $location . "'");

Using the dropdown value, you can pass that into the $location variable. But if you're displaying so many values in a search box. You might want to look into something like jQuery Autocomplete. Of course after you've escaped the input.

Edit:

The above method isn't very secure, you should really use mysqli. And use something like prepared statements:

$stmt = $dbConnection->prepare('SELECT * FROM locations WHERE name = ?');
$stmt->bind_param('s', $name);

For more information check this post on SQL Injection