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

Вы можете установить псевдонимы для выбранных столбцов:

$query = 'SELECT news.id AS newsId, user.id AS userId, [OTHER FIELDS HERE] FROM news JOIN users ON news.user = user.id'

Вы можете использовать числовые индексы ($row[0]) или лучше использовать AS в MySQL:

SELECT *, user.id AS user_id FROM ...

Вы можете сделать что-то вроде

SELECT news.id as news_id, user.id as user_id ....

И тогда $row['news_id'] будет идентификатором новостей, а $row['user_id'] будет идентификатором пользователя.

Если вы не хотите использовать псевдонимы, вы можете просто добавить префикс к именам таблиц.

Таким образом, вы можете лучше автоматизировать создание ваших запросов. Кроме того, рекомендуется не использовать select * (это, очевидно, медленнее, чем просто выбор полей, которые вы нужно Кроме того, явно указывайте только те поля, которые вы хотите иметь.

SELECT
    news.id, news.title, news.author, news.posted, 
    users.id, users.name, users.registered 
FROM 
    news 
LEFT JOIN 
    users 
ON 
    news.user = user.id

Я только что понял это. Вероятно, это плохая практика, но в данном случае у меня она сработала.

Я один из ленивых людей, которые не хотят использовать псевдонимы или записывать каждое имя столбца с префиксом таблицы.

Вы можете выбрать все столбцы из определенной таблицы, используя table_name.* в своем операторе выбора.

Когда вы дублируете имена столбцов, mysql будет перезаписывать их от первого до последнего. Данные из первого дублированного имени столбца будут перезаписаны, когда он снова встретит это имя столбца. Таким образом, побеждает повторяющееся имя столбца, которое идет последним.

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

Пример:

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

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

Что, если я хочу, чтобы dup был значением table1?

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

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

Теперь table1 идет последним, поэтому значением dup будет значение из table1.

Я получил значение, которое хотел для dup, без необходимости записывать каждый чертов столбец, и у меня все еще есть все столбцы, с которыми можно работать. Ура!

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

У меня была такая же проблема с динамическими таблицами. (Таблицы, которые, как предполагается, имеют идентификатор для возможности присоединения, но без каких-либо предположений для остальных полей.) В этом случае вы заранее не знаете псевдонимы.

В таких случаях вы можете сначала получить имена столбцов таблицы для всех динамических таблиц:

$tblFields = array_keys($zendDbInstance->describeTable($tableName));

Где $ zendDbInstance - это экземпляр Zend_Db, или вы можете использовать одну из функций здесь, чтобы не полагаться на Zend php pdo: получить имя столбца таблицы

Затем для всех динамических таблиц вы можете получить псевдонимы и использовать $ tableName. * Для тех, которые вам не нужны псевдонимы:

$aliases = "";
foreach($tblKeys as $field)
    $aliases .= $tableName . '.' . $field . ' AS ' . $tableName . '_' . $field . ',' ;
$aliases = trim($aliases, ',');

Вы можете обернуть весь этот процесс в одну общую функцию и просто иметь более чистый код или, если хотите, стать более ленивым :)

@ Джейсон. Вы правы, за исключением того, что виноват php, а не mysql. Если вы поместите свой JOIN в Mysql Workbench, вы получите три столбца с одинаковым именем (по одному для каждой таблицы), но не с одинаковыми данными (некоторые будут null, если эта таблица не соответствует JOIN).

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

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

и позже ...

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

...

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

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

Еще один совет: если вы хотите иметь более чистый PHP-код, вы можете создать VIEW в базе данных, например

Например:

CREATE VIEW view_news AS
SELECT
  news.id news_id,
  user.id user_id,
  user.name user_name,
  [ OTHER FIELDS ]
FROM news, users
WHERE news.user_id = user.id;

В PHP:

$sql = "SELECT * FROM view_news";

Есть два подхода:

1. Использование псевдонимов; в этом методе вы присваиваете новые уникальные имена (ALIAS) различным столбцам, а затем используете их при извлечении PHP. например

   SELECT student_id AS FEES_LINK, student_class AS CLASS_LINK 
   FROM students_fee_tbl 
   LEFT JOIN student_class_tbl ON students_fee_tbl.student_id = student_class_tbl.student_id
   

   а затем получить результаты в PHP:

   $query = $PDO_stmt->fetchAll();
   
   foreach($query as $q) {
       echo $q['FEES_LINK'];
   }
   

2. Использование позиции позиции или индекса столбца набора результатов; в этом случае позиции массива используются для ссылки на повторяющиеся имена столбцов. Поскольку они появляются в разных позициях, используемые порядковые номера всегда уникальны. Однако номера позиций индекса начинаются с 0. например

   SELECT student_id, student_class 
   FROM students_fee_tbl 
   LEFT JOIN student_class_tbl ON students_fee_tbl.student_id = student_class_tbl.student_id
   

   а затем получить результаты в PHP:

   $query = $PDO_stmt->fetchAll();
   
   foreach($query as $q) {
       echo $q[0];
   }
   

Вот ответ на вышеизложенное, он прост и работает с возвращаемыми результатами 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, но после него вы можете включить столько именованных столбцов и столбцов с псевдонимами, сколько хотите, поскольку повторный выбор столбца не вызывает проблем.

При использовании PDO для взаимодействия с базой данных можно использовать режим выборки PDO :: FETCH_NAMED, который может помочь решить проблему:

$sql = "SELECT * FROM news JOIN users ON news.user = user.id";
$data = $pdo->query($sql)->fetchAll(PDO::FETCH_NAMED);
foreach ($data as $row) {
    echo $row['column-name'][0]; // from the news table
    echo $row['column-name'][1]; // from the users table
    echo $row['other-column']; // any unique column name
}