get database table name as a column name

Question

Here I'm trying to get the datas from four tables. I got the results successfully. But, I need to get the table name of filtered results. For example I got 10 results. 3 results from TBL_CAR, 3 results from TBL_BIKE, 2 results from TBL_TRUCK, 2 results from TBL_BUS. I want to apply the different url links to these results.

How do I get the database table name as a column name to apply different url links to these results?

$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));

This code is a little bit terrifying, and I'd encourage you to consider moving to PDO instead of mysql_*; while it's not a magic bullet to make your code automatically better, I think it might help you see better ways to structure things if you start reading code that is using PDO, and comparing how it does things. Here's a quick tutorial to help you get started. — TML
– TML, Commented Oct 9, 2014 at 4:44

Trent · Accepted Answer · 2014-10-09 04:40:52Z

3

A simple way would be to add the table name as a constant to your query, eg:

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)

that way you just check the table_name column to determine which table it came from.

answered Oct 9, 2014 at 4:40

Trent

865 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TML Over a year ago

In addition to @Trent's suggestion, you could also drop the constant right in as a string: "(SELECT model_name, maker_url, model_url, '".TBL_CAR_ADD_MODELS"' as tbl_name FROM ".TBL_CAR_ADD_MODELS." ..

Karuppiah RK Over a year ago

@TML it doesn't return the table name as column name

TML Over a year ago

"... '".TBL_CAR_ADD_MODELS."'..." should give you the value of the constant TBL_CAR_ADD_MODELS, wrapped in single-quotes; adding " as tbl_name" after it will cause your database to return a pseudo-column named "tbl_name" with the value being the string constant that was inserted (properly quoted) into the string. If you're struggling with it, maybe update the question so we can see what you've tried so far.

TML · Accepted Answer · 2014-10-09 05:18:40Z

Another approach might be to look into creating a VIEW in your database, perhaps something like:

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;

This then greatly simplifies your PHP code to something like:

$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;
        }
    }

We could then continue to simplify the code by switching those case statements for an array lookup, and create a wrapper for the *_image_path functions which calls the correct fa_FOO_model_image_path based on the same $row['tbl_name'] value, perhaps eventually getting us to something like:

$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']));
    }

We could then continue to simply the code by switching those case statements to set a variable, such as $sort_key = (CAR_HOMEPAGE|BIKE_HOMEPAGE|TRUCK_HOMEPAGE|BUS_HOMEPAGE);, and create a similar wrapper for the *_image_path functions which calls the correct fa_FOO_model_image_path based on the same $sort_key value.

harvey · Accepted Answer · 2014-10-09 04:44:10Z

0

Just use the column name as a static column value;

( 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 ) { ... }
...

answered Oct 9, 2014 at 4:44

harvey

2,95312 silver badges10 bronze badges

3 Comments

TML Over a year ago

These quotes will mess him up a bit, might be a good idea to either match his code style a little more closely, or provide a more comprehensive re-write so he can see it in the larger context.

Karuppiah RK Over a year ago

it doesn't return the table name as column name. Unknown column 'fa_car_add_models' in 'field list'

TML Over a year ago

Yes, because he forgot to show you how to quote it properly; that's exactly what my comment to him was meant to call out.

Collectives™ on Stack Overflow

get database table name as a column name

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related