Show full table.column on join results possible?

[DssTrainer]

Using an example like:

 

SELECT * FROM apple a1 LEFT JOIN color c1 ON (a1.color_id = c1.color_id)

 

will result in fields being returned from both tables but with no identifier on which table they came from

 

name = FUJI

color_id = 10

code = RED

 

 

I could do:

SELECT a1.name as a1_name, a1.color_id as a1_color, c1.type as c1_type FROM apple a1 LEFT JOIN color c1 ON (a1.color_id = c1.color_id)

 

And get actual names to return, but that is very static. I will be dealing with dynamic information

 

Is there a way to auto-prefix the returned results with the table name?

Like:

SELECT a1.*, c1.* AS apple.*, color.* FROM apple a1 LEFT JOIN color c1 ON (a1.color_id = c1.color_id)

 

So that it returns the full table.column names:

apple.name = FUJI

apple.color_id = 10

color.color_id = 10

color.code = RED

 

?

 

[DssTrainer]

For lack of a better way, I do a bit more round about with php..

 

First I parse the query to get only the tables from the query:

 

$sql_query = "SELECT * FROM product LEFT JOIN product_data ON (product.id = product_data.id)";

 

//Get all tables into an array
$tables = array();
$sqlarr = explode(' ', $sql_query);
foreach ($sqlarr as $k => $value) {
if (in_array($value, array('FROM','JOIN'))) {
	$tables[] = $sqlarr[$k+1];
}
}

 

Then I look up each table to get all the columns and create a string of them

"table.column AS table___column"

 

$str = "";
foreach ($tables as $table) {
$sql = "DESC `" . DB_PREFIX . "$table`";
$query = $this->db->query($sql);
if ($query->num_rows) {
	foreach($query->rows as $row) {
		$str .= DB_PREFIX . $table . '.' . $row['Field'] . ' AS `' . $table . '___' . $row['Field'] . '`, ';
	}
}
}
$str = trim($str, ', ');

 

Then I str_replace the "*" from the select * xxxx with the new string

$sql_query = str_replace("*", $str, $sql_query);

 

Resulting in:

 

"SELECT product.id AS product___id, product.weight AS product___weight, product_data.name AS product_data___name FROM product LEFT JOIN product_data ON (product.id = product_data.id)"

 

There are caveats of course:

- Can't use aliases

- Must use JOINs (tho the parsing could be improved to remove this need)

- Can't use embedded selects (again parsing could be improved)

 

But it works...