When you select everything from MySQL, I can rename certain fields and still select everything else, as is the case with *

Question

Hey guys. So, I'm trying to figure out how to select each field in several tables in the mysql database and output the resulting table to a CSV file for Excel. I found the infamous stackoverflow question related to .csv ouput and having all the code. So, I think I'm asking:

Is there a way to make a mysql query that looks something like this?

SELECT * prof.id AS youth_id FROM time time, profiles prof, WHERE foo='bar' 

and does it select everything from the tables (or like 4), but select id as youth_id, or do I need to specify EVERY value that I want if I need to select certain values ​​as another name?

I do not need to specify each field, since I have 50+ for output.

I tried to search, but I can not find what I need. I think if you could point me in the right direction, that would be great.

Answer 1

You do not need to specify each field. Instead, you can do the following:

 SELECT *, prof.id AS youth_id FROM time time, profiles prof 

However, the above will return all columns from two tables plus the renamed column. Therefore, if the original two tables contain only 4 columns and you rename them, you will get 5 columns in the result set.

Answer 2

Ryan, you can do what you want. The only catch - the results will include both youth_id and id with the same values ​​for each. If you want only youth_id and not id, you will need to specify all the columns you want, instead of using '*'. By the way, if you have phpmyadmin, you can just enter sql and see the result right away.

Answer 3

Well, because no other solution seems perfect, I will think outside the box for a second. If you do not mind the two challenges. You could use the table schema (where the column names are stored), and then you could programmatically change the names as much as needed (without entering them). After that, make the second call with the array mounted as part of the SELECT.

Example:

 <?php $tables = array ('table1','table2','table3'); $NameAliases = array('table1.id'=>'profid'); foreach ($tables as $table) { $sql = "SHOW COLUMNS FROM $table;"; $result = mysql_query($sql); $columns = array(); while ($row = mysql_fetch_array($result)) { $search = array_search($table.'.'.$row["Field"],$NameAliases); if ($search === true) { $parts = explode('.',$NameAliases[$search]); $columns[] = "$table.{$row["Field"]} AS {$parts[1]}"; //get the side of the dot } else { $columns[] = "$table.{$row["Field"]}"; } } } $columnstring = implode(', ',$columns); $sql = "SELECT $columnstring FROM ".implode(', ',$tables)." WHERE 1=1"; $result = mysql_query($sql); while ($row = mysql_fetch_array($result)) { } ?> 

(No clue if this works at all)

Answer 4

If you really don't want this duplication of columns (i.e. getting id and youth_id) from

 SELECT *, prof.id AS youth_id FROM time INNER JOIN profiles prof ON time.profile_id = prof.id WHERE foo = 'bar' 

You can SELECT * from each table, except for the table with the column that you want to add to the alias:

 SELECT time.*, prof.id AS youth_id, prof.forename, prof.surname FROM time INNER JOIN profiles prof ON time.profile_id = prof.id WHERE foo = 'bar' 

This will give you all the columns from time to time and listed from profiles. You still have to list all the columns in the table in which you are looking for the column alias, but at least you do not need to list each of them.

Of course, there is also an argument that you should list all the columns anyway if your layout changes at some point, but this is another story.