如何从一个下拉菜单的两个表中获取数据

Question

I have 2 database tables like so:

CREATE TABLE IF NOT EXISTS `banners` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `long_name` varchar(50) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

And:

CREATE TABLE IF NOT EXISTS `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `banner_id` int(2) NOT NULL,
  `district_id` int(2) NOT NULL,
  `number` int(5) NOT NULL,
  `location` varchar(50) NOT NULL,
  `visible` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=511 ;

I use the code below to pull a list of locations based on their district_id and create a drop-down menu:

$query = 'SELECT id, banner_id, location FROM locations WHERE district_id =' . $district_id;

$result = mysql_query($query, $connection);
if (!result) {
die("Database query failed: " . mysql_error());
}

while ($row = mysql_fetch_array($result)) {
echo '<option value="' . $row['id'] . '"';
if($row['id'] == $location) { echo ' selected';} ;
echo '>' . $row['location'] . '</option>';
}

echo '</select>';

But I'd also like to add to the list the name from the banners table that matches the banner_id for each location. So I can echo out $row['name'] with each $row['location']:

while ($row = mysql_fetch_array($result)) {
echo '<option value="' . $row['id'] . '"';
if($row['id'] == $location) { echo ' selected';} ;
echo '>' . $row['name'] . ' ' . $row['location'] . '</option>';
}

Answer 1

You need a JOIN query. Try following:

$query = 'SELECT l.id, l.banner_id, l.location, b.name
          FROM locations l
          INNER JOIN banners b ON (l.banner_id = b.id)
          WHERE district_id =' . $district_id;

Now you have both name and location in your resultset:

while ($row = mysql_fetch_array($result)) {
  echo '<option value="' . $row['id'] . '"';
  if($row['id'] == $location) { echo ' selected';} ;
  echo '>' . $row['name'] . ' ' . $row['location'] . '</option>';
}

Edit: sidenotes

• mysql_ extension is deprecated, you should use PDO or mysqli_
• In order to prevent SQL injection, you need to perform input filtering and/or use parameterized queries. Quickest way for your case is to cast $district_id to integer. For more information please see OWASP guide