Show a specific MySQL row based on dropdown selection

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!!

Are you confusing 'rows' and 'columns'? This is very, very wrong. Suggest you start with a tutorial. :-( — Strawberry, Jul 27 '14 at 18:04

score 1 · Answer 1 · edited May 23 '17 at 12:20

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

Show a specific MySQL row based on dropdown selection

1 Answers1