[creativecoding]

Say I have a query like:

SELECT * FROM tickets AS ticket JOIN customers AS customer ON ticket.customer = customer.id LIMIT 10

How can I make it so that the keys in the selection are prepended with their respective table names? Like if tickets had the columns id, customer, and notes - and customers had id, name, and phone - in order to access those two I would have to use ticket.id, ticket.customer, ticket.notes, customer.id, and so on and so forth? Is there any way of doing this without specifying every column using SELECT column AS table.column?

[exiles.prx]

I am fairly certain there are only two ways of doing something similar to what your looking for:

SELECT ticket.id as tid, customer.id as cid, customer, phone, name, notes FROM tickets JOIN customers ON tid = cid;

SELECT * FROM tickets AS ticket JOIN customers AS customer ON ticket.customer = customer.id LIMIT 10

(same as what you have)

Other then the above, you can use the same query you have, but since notes, phone, name, and customer are not in both tables, you can use the columns names without the table names prepended. You only need to explicitly use the table name when both tables have the same column name other you will receive an ambiguous sql error.

This post has been edited by exiles.prx: 27 August 2012 - 05:35 PM

[creativecoding]

That only looked like one way... I understand that I can just use "SELECT (column) AS (table).(column)", but I'm asking for a way to do this while using "SELECT *", because my tables have many columns and typing all that out is time-consuming.

[Atli]

As far as I know, there is no way to automatically add a table name suffix to column names, like you are describing. A quick look at the docs for the SELECT query shows there are no options for that in the query syntax. - You'll have to type out the (column) AS `(suffix).(column)` definition for each column you want changed.

In any case, it's generally not a good idea to use the wild-card char in a SELECT query. At least not in a query used in production code. It will always return all the data, which is usually a lot more than is actually needed.


If the query is exceedingly long, then you may well want to consider creating a VIEW out of it, to save having to send the entire query string to MySQL to be parsed.

[exiles.prx]

creativecoding, on 27 August 2012 - 05:02 PM, said:

That only looked like one way... I understand that I can just use "SELECT (column) AS (table).(column)", but I'm asking for a way to do this while using "SELECT *", because my tables have many columns and typing all that out is time-consuming.

Well, I mentioned the second method being the SQL you stated, but I can see how my post can be confusing as I show one example in code and state the second in my paragraph (until edited).

[creativecoding]

Well, thanks. I guess short of creating a view, the way I'm going is the best.

Thanks for all the help.

[e_i_pi]

Looking at your problem from a different perspective, I'd say the issue you're really having is that identical column names are being clobbered once the data is fetched into an array/object by PHP. When ambiguous column-names are fetched into an array by PHP, if the key already exists, the data is overwritten. So, for exmaple, if you ran this query:

SELECT a.ID, b.ID, c.ID
FROM a, b, c

...you would get a result set with one column per row (due to clobbering), and the column would have the value of c.ID, as far as I know. Let's work on the assumption that this is the core of the problem that you are facing.

If you are using MySQL then you are most likely using PHP. If you are using PHP then you should be using PDOs^* instead of mysql functions in order to access the DB. If this is the case (i.e. you use PDOs) then there is a solution available. I don't have my dev kit in front of me (I'm at work), but I'll run you through the idea.

With PDOs, it is possible to retrieve meta information, so I believe this will work:

• Get the column count of the result set using PDOStatement::columnCount()
  
• Loop through the columns, and use PDOStatement::getColumnMeta(). You should be able to get the table and column names for each column then, and build a "name" array, like this - array(0 => 'a.id', 1 => 'b.id', 2=>'c.id')
  
• Retrieve the result set itself making sure you use PDO::FETCH_NUM as your fetch style, we'll call this the "data" array.

Since we have fetched the column meta-data and the rowset data in the same order, then there should exist a perfect relation between the numeric keys of your "name" and "data" arrays. From there, it shouldn't be too much of a hassle to create an associative array of the data, with fully-resolved table_name.column_name keys.

^* If you're using mysqli, you should be able to use mysqli_result::$field_count, mysqli_result::fetch_field_direct, and mysqli_result::fetch_row as alternatives.

This post has been edited by e_i_pi: 27 August 2012 - 07:01 PM

[creativecoding]

That is exactly what I needed e_i_pi! I should probably start learning PDO though, it's just getting better every day.

Here's what I wrote up (using mysqli) for what I needed:

/*
	 * fetchArray(mysqli_result $result)
	 * Fetches an associative array of $result. Array keys are prepended with their respective table names in the format of: (table).(column)
	*/
	public function fetchArray($result){
		$rows = array();
		$fieldcount = $result->field_count;
		$i = 0;
		while($row = $result->fetch_row()){
			for($n = 0; $n < $fieldcount; $n++){
				$meta = $result->fetch_field_direct($n);
				$rows[$i][$meta->table . '.' .  $meta->name] = $row[$n];
			}
			$i++;
		}
		return $rows;
	}