Mysql + php как получить значение третьего столбца сравнивая два других в двух разных таблицах?
Всем доброго времени суток. Столкнулся с такой проблемой. У меня в БД 2 таблицы, в одной сохраняются данные о посетителе сайта (ip, ISO-код страны...). Во второй таблице есть столбец с ISO-кодом страны и столбец с расшифровкой ISO-кода. Мне нужно что бы ISO-код страны из первой таблицы находил такое же значение во второй, но на экран выводилась бы расшифровка кода. Если кто то сталкивался с подобным и нашел решение подскажите или киньте ссылку где можно почитать про это.
RU: table1 с полями id, date, visitor, ip, country, citie host, table2 с полями id, iso, continent, name_ru, name_en, lat, lon, timezone, для визуализации используется table1, так вот надо что бы country нашло такое же значение в iso, но при визуализации выдало значение name_ru в столбце country. Вот как то так. Потом я хочу сделать тоже самое с cities но с другой таблицы (т.е. будут table1 и table3) может это как то через пхп сделать?
Николай Громов: select t1.id, t1.date, t1visitor, t1.ip, t1.country, t1.citie, t1.host, t2.name_ru from table1 as t1 inner join table2 as t2 on t1.iso=t2.iso
в join указываете с какой таблицей объединить, и по какому полю. В Select указывайте из каких таблиц брать поля. Последнее не обязательно, если поле есть только в одной таблице.
Вот часть кода которая отвечает за выборку и вывод на экран:
$query = "SELECT * FROM `pre_visitors` WHERE (visitor NOT RLIKE 'bot') GROUP BY ip ORDER BY 'id' DESC LIMIT $lastid";
$res = mysqli_query($dbh, $query);
$num_rows = mysqli_num_rows($res);
?>
№ п/п
Время и дата
Данные о посетителе
IP/прокси
Страна
Город
Посещенный URL
'.$row['id'].'
'.$row['date'].'
'.$row['visitor'].'
'.$row['ip'].'
'.$row['country'].'
'.$row['cities'].'
'.$row['host'].'
Общее количество посищений без поисковых ботов
'.$num_rows.'
';
Получается у меня запрос должен будет выглядеть так:
$query = select t1.id, t1.date, t1visitor, t1.ip, t1.country, t1.citie, t1.host, t2.name_ru from table1 as t1 inner join table2 as t2 on t1.iso=t2.iso WHERE (visitor NOT RLIKE 'bot') GROUP BY ip ORDER BY 'id' DESC LIMIT $lastid";?
И какое мне значение теперь подставлять в $row['country?'] что бы выдало name_ru?
На основании вашей подсказки написал вот такой запрос :
$query = "SELECT t1.id, t1.date, t1visitor, t1.ip, t2.name_ru, t1.cities, t1.host, FROM `pre_visitors` as t1 INNER JOIN `pre_country` as t2 on t1.country=t2.iso WHERE (visitor NOT RLIKE 'bot') GROUP BY ip ORDER BY 'id' DESC LIMIT $lastid";
Но он возвращает false, судя по журналу ошибок, т.к. последующие mysqli_* не могут отработать.
$query = "SELECT t1.id, t1.date, t1.visitor, t1.ip, t1.country, t2.name_en, t1.cities, t1.host FROM `pre_visitors` as t1 INNER JOIN `pre_country` as t2 on t1.country=t2.iso WHERE (visitor NOT RLIKE 'bot') GROUP BY ip ORDER BY 'id' DESC LIMIT $lastid";
Данный запрос отрабатывает. Но теперь нет сортировки по id и нет удаления повторяющихся ip. Что нужно изменить? Должно отрабатывать t1.id и t1.ip. Я уже их подставлял в GROUP BY и ORDER BY но они не работают.
Я уже пробовал, так не работает. Кстати дописал третью таблицу можешь посмотреть синтаксис правильный?
SELECT t1.id, t1.date, t1.visitor, t1.ip, t2.name_en, t3.name_en_region, t1.host FROM `pre_visitors` as t1
INNER JOIN `pre_country` AS t2 ON t1.country=t2.iso
INNER JOIN `pre_regions` AS t3 ON t2.iso=t3.country
WHERE (visitor NOT RLIKE 'bot') GROUP BY t1.ip ORDER BY 't1.id' DESC LIMIT $lastid
Николай Громов: с JOIN все правильно. косяк в GROUP. по какому признаку сервер должен выкинуть все записи и оставить только 1(а группировка работает именно так)? выбирайте минимальное/максимальное/среднее значение чего-то, считайте сумму/количество/что-то еще.
Получается до всех этих приключений с JOIN, порядок был по id, по ip он выбирал не повторяющиеся, т.е. в таблице которая выводилась на экран не было повторяющихся ip адресов.
особенность INNER JOIN в том, что поле должно быть и в первой и во второй таблице (в вашем случае - и в третьей) т.е. если t1.x=t2.y , а такой строки в t2 нет - в выборке-результате строки не будет. Можно пропробовать LEFT JOIN, тогда при отсутствии строки в t2 строки будут, просто с NULL в полях, где не получилось свзять таблицы. Но что будет в Вашем случае - знать только Вам.
с LEFT JOIN все заработало, большое спасибо! В visitor пока особо важной информации нет, так что проводить группировку еще и по нему нет необходимости, но данные по нему все равно хранятся в базе так что не потеряются. Но в дальнейшем я думаю что мне придется сделать такую группировку.
Тем более когда заходят с одного ip это в основном одна фирма которая выходит в инет через один роутер, или с телефона заходят где примерно 100 внутренних ip адресов отправляют через один шлюз.
