获取数据库表名作为列名 - get database table name as a column name

这里我试图从四个表中获取数据。我成功地得到了结果。但是，我需要得到过滤结果的表名。例如，我得到了10个结果。3个来自TBL_CAR的结果，3个来自TBL_BIKE的结果，2个来自TBL_TRUCK的结果，以及2个来自TBL_BUS的结果。我想将不同的url链接应用于这些结果。

如何将数据库表名作为列名，以将不同的url链接应用于这些结果？

$sql = "(SELECT model_name, maker_url, model_url FROM ".TBL_CAR_ADD_MODELS." WHERE model_status = '1' AND model_url != '".$model_url."' AND model_name LIKE '%$q%' LIMIT 3)
    UNION
    (SELECT model_name, maker_url, model_url FROM ".TBL_BIKE_MODELS." WHERE model_status = '1' AND model_url != '".$model_url."' AND model_name LIKE '%$q%' LIMIT 3)
    UNION
    (SELECT model_name, maker_url, model_url FROM ".TBL_TRUCK_MODELS." WHERE model_status = '1' AND model_url != '".$model_url."' AND model_name LIKE '%$q%' LIMIT 3)
    UNION
    (SELECT model_name, maker_url, model_url FROM ".TBL_BUS_MODELS." WHERE model_status = '1' AND model_url != '".$model_url."' AND model_name LIKE '%$q%' LIMIT 3)";
    $res = mysql_query($sql, $CN);
    $rows = array();
    while($row = mysql_fetch_array($res))
    {   
        if(resutls FROM TBL_CAR)
        {
        $rows[] = array('url' => asort_get_url(CAR_HOMEPAGE, $row['maker_url'], $row['model_url']), 'label' => $row['model_name'], 'image' => fa_model_image_path("thumb", $row['maker_url'],$row['model_url']));
        }
        elseif(results FROM TBL_BIKE)
        {
            $rows[] = array('url' => asort_get_url(BIKE_HOMEPAGE, $row['maker_url'], $row['model_url']), 'label' => $row['model_name'], 'image' => fa_bike_model_image_path("thumb", $row['maker_url'],$row['model_url']));
        }
        elseif(results FROM TBL_TRUCK)
        {
            $rows[] = array('url' => asort_get_url(TRUCK_HOMEPAGE, $row['maker_url'], $row['model_url']), 'label' => $row['model_name'], 'image' => fa_truck_model_image_path("thumb", $row['maker_url'],$row['model_url']));
        }
        elseif(resutls FROM TBL_BUS)
        {
            $rows[] = array('url' => asort_get_url(BUS_HOMEPAGE, $row['maker_url'], $row['model_url']), 'label' => $row['model_name'], 'image' => fa_bus_model_image_path("thumb", $row['maker_url'],$row['model_url']));
        }
    }
    echo stripslashes(json_encode($rows,JSON_PRETTY_PRINT));

一个简单的方法是将表名作为常量添加到查询中，例如：

SELECT 'TBL_CAR_ADD_MODELS' table_name, model_name, maker_url, model_url FROM ".TBL_CAR_ADD_MODELS." WHERE model_status = '1' AND       model_url != '".$model_url."' AND model_name LIKE '%$q%' LIMIT 3)
UNION
(SELECT 'TBL_BIKE_MODELS',model_name, maker_url, model_url FROM ".TBL_BIKE_MODELS." WHERE model_status = '1' AND model_url != '".$model_url."' AND model_name LIKE '%$q%' LIMIT 3)
UNION
(SELECT 'TBL_TRUCK_MODELS', model_name, maker_url, model_url FROM ".TBL_TRUCK_MODELS." WHERE model_status = '1' AND model_url != '".$model_url."' AND model_name LIKE '%$q%' LIMIT 3)
UNION
(SELECT 'TBL_BUS_MODELS', model_name, maker_url, model_url FROM ".TBL_BUS_MODELS." WHERE model_status = '1' AND model_url != '".$model_url."' AND model_name LIKE '%$q%' LIMIT 3)

这样，您只需检查tablename列来确定它来自哪个表。

另一种方法可能是在数据库中创建一个VIEW，可能类似于：

CREATE VIEW ITEM_MODELS AS
    SELECT *, 'cars' as tbl_name TBL_CARS
    UNION
    SELECT *, 'bikes' as tbl_name TBL_BIKE
    UNION
    SELECT *, 'trucks' as tbl_name TBL_TRUCK
    UNION
    SELECT *, 'buses'  as TBL_BUS;

这将极大地简化您的PHP代码，例如：

$sql = "SELECT model_name, maker_url, model_url, tbl_name FROM ITEM_MODELS  WHERE model_status = '1' AND model_url != '${model_url}' AND model_name LIKE '%${q}%' LIMIT 3)";
    while($row = mysql_fetch_array($res))
    {   
        switch($row['tbl_name']) {
            case TBL_CAR_ADD_MODELS:
                $rows[] = array('url' => asort_get_url(CAR_HOMEPAGE, $row['maker_url'], $row['model_url']), 'label' => $row['model_name'], 'image' => fa_model_image_path("thumb", $row['maker_url'],$row['model_url']));
                break;
            case TBL_BIKE_MODELS:
                $rows[] = array('url' => asort_get_url(BIKE_HOMEPAGE, $row['maker_url'], $row['model_url']), 'label' => $row['model_name'], 'image' => fa_bike_model_image_path("thumb", $row['maker_url'],$row['model_url']));
                break;
            case TBL_TRUCK_MODELS:
                $rows[] = array('url' => asort_get_url(TRUCK_HOMEPAGE, $row['maker_url'], $row['model_url']), 'label' => $row['model_name'], 'image' => fa_truck_model_image_path("thumb", $row['maker_url'],$row['model_url']));
                break;
            case TBL_BUS_MODELS:
                $rows[] = array('url' => asort_get_url(BUS_HOMEPAGE, $row['maker_url'], $row['model_url']), 'label' => $row['model_name'], 'image' => fa_bus_model_image_path("thumb", $row['maker_url'],$row['model_url']));
                break;
            default:
                // nothing sensible to do here
                break;
        }
    }

然后，我们可以通过将这些case语句切换为数组查找来继续简化代码，并为*_image_path函数创建一个包装器，该包装器基于相同的$row['tbl_name']值调用正确的fa_FOO_model_image_path，也许最终会使我们达到以下效果：

$sql = "SELECT model_name, maker_url, model_url, tbl_name FROM ITEM_MODELS  WHERE model_status = '1' AND model_url != '${model_url}' AND model_name LIKE '%${q}%' LIMIT 3)";
$sort_keys = [
    TBL_CAR_ADD_MODELS => CAR_HOMEPAGE,
    TBL_BIKE_MODELS => BIKE_HOMEPAGE,
    TBL_TRUCK_MODELS => TRUCK_HOMEPAGE,
    TBL_BUS_MODELS => BUS_HOMEPAGE
];
    while($row = mysql_fetch_array($res))
    {   
        $rows[] = array('url' => asort_get_url($sort_keys[$row['tbl_name']], $row['maker_url'], $row['model_url']), 'label' => $row['model_name'], 'image' => model_image_path($row['tbl_name'], "thumb", $row['maker_url'],$row['model_url']));
    }

只需将列名用作静态列值；

( SELECT model_name, maker_url, model_url, ".TBL_CAR_ADD_MODELS." AS table_name 
  FROM ".TBL_CAR_ADD_MODELS." WHERE model_status = '1' .....
...
if ( $rows[3] eq TBL_CAR_ADD_MODELS ) { ... }
...