Mysql + php как получить значение третьего столбца сравнивая два других в двух разных таблицах?

Question

[Николай Громов]

Всем доброго времени суток. Столкнулся с такой проблемой. У меня в БД 2 таблицы, в одной сохраняются данные о посетителе сайта (ip, ISO-код страны...). Во второй таблице есть столбец с ISO-кодом страны и столбец с расшифровкой ISO-кода. Мне нужно что бы ISO-код страны из первой таблицы находил такое же значение во второй, но на экран выводилась бы расшифровка кода. Если кто то сталкивался с подобным и нашел решение подскажите или киньте ссылку где можно почитать про это.

Answer 1

[Макс]

настала пора открыть для себя оператор JOIN

Comments

[Николай Громов]

Читал я про него. Я не могу понять как сформулировать запрос!

[Николай Громов]

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_* не могут отработать.

[Макс]

Николай Громов: попробуйте выполнить запрос напрямую в mysql. Для начала в "t1.host, FROM" запятая лишняя.

[Николай Громов]

$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 но они не работают.

[Макс]

Николай Громов: попробуйте явно указать поля GROUP BY t1.ip ORDER BY 't1.id'

[Николай Громов]

Я уже пробовал, так не работает. Кстати дописал третью таблицу можешь посмотреть синтаксис правильный?
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 адресов.

[Николай Громов]

Теперь он выдает мне id с 240 какого-то и то не по порядку.

[Николай Громов]

запрос должен выдать мне все поля указанные в селекте с возрастанием по id и не повторяющимися ip адресами.

[Макс]

особенность INNER JOIN в том, что поле должно быть и в первой и во второй таблице (в вашем случае - и в третьей) т.е. если t1.x=t2.y , а такой строки в t2 нет - в выборке-результате строки не будет. Можно пропробовать LEFT JOIN, тогда при отсутствии строки в t2 строки будут, просто с NULL в полях, где не получилось свзять таблицы. Но что будет в Вашем случае - знать только Вам.

[Макс]

Николай Громов: ну так выбирайте MAX(t1.id) - тогда группировка должна работать. Получите последние входы

[Макс]

Макс: но если с 1 ip заходило несколько посетителей - эту информацию Вы потеряете. Мне кажется, более правильным будет GROUP BY t1.ip, t1.visitor

[Николай Громов]

с LEFT JOIN все заработало, большое спасибо! В visitor пока особо важной информации нет, так что проводить группировку еще и по нему нет необходимости, но данные по нему все равно хранятся в базе так что не потеряются. Но в дальнейшем я думаю что мне придется сделать такую группировку.
Тем более когда заходят с одного ip это в основном одна фирма которая выходит в инет через один роутер, или с телефона заходят где примерно 100 внутренних ip адресов отправляют через один шлюз.