Selecting records from MySQL with a select, how to send title instead of id ?

C

Chvalov2014-11-03 18:18:08

PHP

Chvalov, 2014-11-03 18:18:08

hello i have a script

<?php
mysql_connect('127.0.0.1', 'root', '');
mysql_select_db('testselect');
mysql_query("SET NAMES 'UTF8'");
 
$action = isset($_REQUEST['action']) ? $_REQUEST['action'] : '';
 
// возвращаем список городов
if ($action == 'getCity')
{
  $result = mysql_query("SELECT id, town_name FROM city WHERE id = '" . (int)$_GET['id'] . "'");
  $result = mysql_query("SELECT id, street_name FROM street WHERE city_id = '" . (int)$_GET['id'] . "'");
    
    if ($result)
    {
                $city = array();
               
                while ($row = mysql_fetch_array($result))
                {
                        $city[$row['id']] = $row['name'];
            $city[$row['id']] = $row['street_name'];
                }
       
        echo json_encode($city);
    }
    else
    {
        echo json_encode(array('Выберите область'));
    }
 
    exit;
}
 
// выводим пришедшие данные
if ($action == 'postResult')
{
    echo '<pre>' . htmlspecialchars(print_r($_POST, true)) . '</pre>';
    exit;
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Зависимые списки</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
    <style>
        body {font-family:Verdana; font-size:12px;}
    </style>
    <script type="text/javascript">
        function loadCity(select)
        {
            var citySelect = $('select[name="city"]');
            citySelect.attr('disabled', 'disabled'); // делаем список городов не активным
           
            // послыаем AJAX запрос, который вернёт список городов для выбранной области
            $.getJSON('index.php', {action:'getCity', id:select.value}, function(cityList){
               
                citySelect.html(''); // очищаем список городов
               
                // заполняем список городов новыми пришедшими данными
                $.each(cityList, function(i){
                    citySelect.append('<option value="' + i + '">' + this + '</option>');
                });
               
                citySelect.removeAttr('disabled'); // делаем список городов активным
               
            });
        }
    </script>
</head>
<body>
    <form action="index.php" method="post">
        <select name="region" onchange="loadCity(this)">
            <option></option>
           
            <?php // 
            $result = mysql_query("SELECT id, street_name FROM street");
      $result = mysql_query("SELECT id, town_name FROM city");
      
            while ($row = mysql_fetch_array($result))
            {
                echo '<option value="' . $row['id'] . '">' . $row['town_name'] . '</option>' . "\n";
            }
            ?>
           
        </select>
       
        <select name="city" disabled="disabled">
            <option>Выберите область</option>
        </select>
 
        <input type="hidden" name="action" value="postResult" />
        <input type="submit" value="отправить" />
    </form>
</body>
</html>

And there is a db:

CREATE TABLE IF NOT EXISTS `city` (
  `id` int(2) unsigned NOT NULL AUTO_INCREMENT,
  `town_name` varchar(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

INSERT INTO `city` (`id`, `town_name`) VALUES
(1, 'Киев'),
(2, 'Винница'),
(3, 'Москва'),
(4, 'Львов'),
(5, 'Донбас'),
(6, 'Луганск');

CREATE TABLE IF NOT EXISTS `street` (
  `id` int(3) unsigned NOT NULL AUTO_INCREMENT,
  `street_name` varchar(250) NOT NULL,
  `city_id` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=100 ;

INSERT INTO `street` (`id`, `street_name`, `city_id`) VALUES
(1, 'Потреа ул.', 1),
(2, 'Прикольного ул.', 1),
(3, 'Тест ул.', 2),
(4, 'Пример  ул.', 2,
(5, 'Страшного ул.', 4),
(6, 'Asus ул.', 3),
(7, 'Рак ул.', 4),
(8, 'Тест2 ул.', 5),
(9, 'Шевченка ул.', 3),
(10, 'Прикол3 ул.', 1),
(11, 'Независимости', 5),
(12, 'Буденого ул.', 6),
(13, '3 космичная ул.', 6),
(14, 'пл. Шевченка', 5),
(15, 'Самарова ул.', 4),
(16, 'пер. Крутой', 1),
(17, 'Московскаяул.', 3),
(18, 'Пушкинская ул.', 2),
(19, 'пер. Водочный.', 6),
(20, 'Виноградная ул.', 6),
(21, 'Заболотного ул.', 5),
(22, 'Дорогого ул.', 5),
(23, 'Грушки ул.', 3),
(24, '8-Марта ул.', 2),
(25, 'Пченька ул.', 4),
(26, 'О. Пчилки ул.', 1),
(27, 'пер. О. Тест', 3);

The question is how to make it send the name of the City and the name of the Street, and not the ID.
And now it’s like this:

Reply

Answer the question

In order to leave comments, you need to log in

2 answer(s)

N

Nikita Lubchich, 2014-11-03
@Cybran

1. There is no such city as Donbas. This is a region.
2. For any non-HTML code in an HTML document, you need to take your fingers off. Seriously.
3. Learn SQL

K

klaxwork, 2014-11-14
@klaxwork

Well as far as I understood - it is necessary as a result to receive the data from other table on id from the first table.
Then you need to look towards join
As an example:

SELECT c.town_name 'город', st.street_name 'улица'
FROM street AS st
LEFT JOIN city c ON c.city_id = st.id
WHERE c.id = 1

Need more --

3. Learn SQL