Как разрешить неоднозначные имена столбцов при получении результатов?

В моей базе данных есть две таблицы: 

Таблица NEWS с колонками:

1. id — id новости

2. user — идентификатор пользователя

Таблица USERS со столбцами:

1. id — id пользователя

Я хочу выполнить следующий SQL запрос:

SELECT * FROM news JOIN users ON news.user = user.id 

Когда я получаю результаты в PHP, я хочу получить ассоциативный массив и имена столбцов по $row['column-name']. Как я могу получить ID новости и ID пользователя, имея одинаковое имя столбца в разных таблицах?

Ответ 1

Возможно, это плохое решение, но в данном случае оно мне помогло. Я один из тех ленивых людей, которые не хотят создавать псевдонимы или писать каждое имя столбца с префиксом таблицы. Вы можете выбрать все столбцы из определенной таблицы, используя имя_таблицы.»*» в операторе select. Если имена столбцов дублируются, mysql перезаписывает их от первого к последнему. Данные из первого дублирующегося имени столбца будут перезаписаны, когда он снова встретит это имя столбца. Поэтому дублирующееся имя столбца, которое встречается последним, остается в выборке.

Если я соединяю 3 таблицы, каждая из которых содержит дублирующееся имя столбца, порядок таблиц в операторе select определит, какие данные я получу для дублирующегося столбца. Пример:

SELECT table1.* , table2.* , table3.* FROM table1 LEFT JOIN table2 ON table1.dup = table2.dup LEFT JOIN table3 ON table2.dup = table3.dup;

В приведенном выше примере значение «dup», которое я получу, будет из таблицы 3.

А если я хочу, чтобы «dup» было значением из таблицы 1?

Тогда мне нужно сделать следующее:

SELECT table3.* , table2.* , table1.* FROM table1 LEFT JOIN table2 ON table1.dup = table2.dup LEFT JOIN table3 ON table2.dup = table3.dup;

Теперь таблица 1 идет последней, поэтому значение «dup» будет значением из таблицы 1. Я получил нужное мне значение для «dup» без необходимости выписывать каждый отдельный столбец, и у меня все еще есть все столбцы для работы. Я знаю, что значение «dup» должно быть одинаковым во всех трех таблицах, но что если в таблице 3 нет подходящего значения для «dup»? Тогда «dup» будет пустым для первого примера.

Ответ 2

Если вы поместите JOIN в Mysql Workbench, вы получите три столбца с одинаковыми именами (по одному для каждой таблицы), но не с одинаковыми данными (некоторые будут нулевыми, если в таблице нет совпадений для JOIN). В php, если вы используете MYSQL_NUM в mysql_fetch_array(), вы получите все столбцы. Проблема возникает, когда вы используете mysql_fetch_array() с MYSQL_ASSOC. Тогда внутри этой функции php формирует возвращаемое значение следующим образом:

$row['dup'] = [value from table1]

 а потом...

$row['dup'] = [значение из table2]

...

$row['dup'] = [значение из table3]

 Таким образом, вы получите только значение из таблицы 3. Проблема в том, что набор результатов из mysql может содержать столбцы с одинаковыми именами, но ассоциативные массивы в php не позволяют дублировать ключи в массивах. Когда данные сохраняются в ассоциативных массивах, в php часть информации теряется...

Ответ 3

Вот ответ на этот вопрос, который одновременно прост и работает с возвращаемыми результатами JSON. В то время как SQL-запрос автоматически добавляет префикс имени таблицы к каждому экземпляру идентичного имени поля при использовании SELECT *, JSON-кодирование результата для отправки обратно на веб-страницу игнорирует значения полей с дублирующимся именем и вместо этого возвращает значение NULL.

Точнее, это включает первый экземпляр дублирующегося имени поля, но делает его значение NULL. А второй экземпляр имени поля (в другой таблице) опускается полностью, как имя поля, так и значение. Но когда вы проверяете запрос непосредственно на базе данных (например, с помощью Navicat), все поля возвращаются в наборе результатов. И только при следующем кодировании результата в JSON они имеют значения NULL, а последующие дубликаты имен полностью опущены. Итак, простой способ решить эту проблему — сначала выполнить SELECT *, а затем следовать за ним с псевдонимом полей для дубликатов. Вот пример, где обе таблицы имеют одинаково названные поля site_name.

SELECT *, w.site_name AS wo_site_name FROM ws_work_orders w JOIN ws_inspections i WHERE w.hma_num NOT IN(SELECT hma_number FROM ws_inspections) ORDER BY CAST(w.hma_num AS UNSIGNED);

Теперь в декодированном JSON вы можете использовать поле wo_site_name, и оно имеет значение. В данном случае имена сайтов содержат специальные символы, такие как апострофы и одинарные кавычки, поэтому кодировка при первоначальном сохранении и декодирование при использовании результата из базы данных будет:

...decHTMLifEnc(decodeURIComponent( jsArrInspections[x]["wo_site_name"]))

Вы всегда должны ставить «*» первым в операторе SELECT, но после него вы можете включать столько именованных и псевдоименованных столбцов, сколько захотите, поскольку повторный выбор столбца не вызывает проблем.