Wenn man zum Beispiel bei einem komplexen Join automatisch alle Spalten mit Prefixen für eine Tabelle versehen will kann man folgenden Code verwenden:
function fetchAssoc(&$db, $strQuery)
{
$result = $db->query($strQuery);
$arReturn = array();
while ($row = $result->fetch_assoc())
{
$arReturn[] = $row;
}
return $arReturn;
}
function prefixed_table(&$db, $table_alias, $table)
{
$columns = fetchAssoc($db, "SHOW COLUMNS FROM `".$table."`");
$field_names = array();
foreach ($columns as $column)
{
$field_names[] = $column["Field"];
}
$prefixed = array();
foreach ($field_names as $field_name)
{
$prefixed[] = "`".$table_alias."`.`".$field_name."` AS `".$table.".".$field_name."`";
}
return implode(", ", $prefixed);
}
Beispielhafte Anwendung für einen xtCommerce Export:
define(TBL_PRODUCTS, ‚xt_products‘);
define(TBL_PRODUCTS_DESCRIPTION, ‚xt_products_description‘);
define(TBL_PRODUCTS_CATEGORIE, ‚xt_products_to_categories‘);
$db = mysqli_connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
$strQuery = "
SELECT
".prefixed_table($db, ‚P‘, TBL_PRODUCTS).",
".prefixed_table($db, ‚PD‘, TBL_PRODUCTS_DESCRIPTION).",
GROUP_CONCAT(PC.`categories_id`) AS `".TBL_PRODUCTS_CATEGORIE.".categories_id`,
GROUP_CONCAT(PC.`master_link`) AS `".TBL_PRODUCTS_CATEGORIE.".master_link`
FROM
`".TBL_PRODUCTS."` AS P
LEFT JOIN `".TBL_PRODUCTS_DESCRIPTION."` AS PD ON (P.`products_id` = PD.`products_id`)
LEFT JOIN `".TBL_PRODUCTS_CATEGORIE."` AS PC ON (P.`products_id` = PC.`products_id`)
GROUP BY
P.`products_id`
";
$product_data = fetchAssoc($db, $strQuery);