Page 1 of 1

mysql table name

Posted: Fri Jul 21, 2006 5:45 pm
by yshaf13
i'm planning on joining a bunch of tables and using a loop to display the html for every row, but in every row it has to say which table it came from. how do i get the name of the table?

Posted: Fri Jul 21, 2006 5:48 pm
by feyd
If you're using a UNION, add it to each of the queries.

re

Posted: Fri Jul 21, 2006 6:01 pm
by yshaf13
i wasnt planniing on using union just plain old join (select col1, col2 from table1, table2...) since i'm selecting the same colums from each table i don't see how union would help. and even if i did use union how would i get the table name?

Posted: Fri Jul 21, 2006 6:16 pm
by feyd
you write it into the query as a "fake" column returned by supplying a string literal. If the tables have the same structure, joining them will only return massive duplicate records or no records, depending on how you write the query. It sounds like a UNION would be best, unless you can provide more information.

re

Posted: Fri Jul 21, 2006 6:34 pm
by yshaf13
i have 5-6 tables that each have a number of colums that are the same and a few that are distinct to that table. on the page i'm talking about i want to output all the colums that are the same in each table.because of the way it's set up there shouldn't be any duplicates. are you suggesting adding a colum to each table with the table name? is that the only way?

Posted: Fri Jul 21, 2006 6:41 pm
by feyd
Not adding a column to the tables, but instead adding a string literal to the query as I've already said now.

re

Posted: Fri Jul 21, 2006 6:46 pm
by yshaf13
you write it into the query as a "fake" column returned by supplying a string literal.
i'm sorry but i'm kinda new to php/mysql and i have no idea what that means.

Posted: Fri Jul 21, 2006 7:24 pm
by Weirdan
i'm sorry but i'm kinda new to php/mysql and i have no idea what that means.

Code: Select all

select
    'table1', *
from table1
union
select
    'table2', *
from table2
........

re

Posted: Sat Jul 22, 2006 8:46 pm
by yshaf13
thanks for the help, i tried it but using "select 'table1', * from..." didnt work i had to say "select 'table1', col1, col2..." is there any way i could use the "*"?

Posted: Sat Jul 22, 2006 8:50 pm
by kbrown3074
If you are using tables with fields of the same names you will have problems using '*'. You will need to explicitly state 'table.field' in the select statement. You will have to do the same in the WHERE clause of the statement as well.

Code: Select all

SELECT tbl1.field1,tbl2.field2 FROM tbl1,tbl2 WHERE tbl1.field1 = tbl2.field2