I'm having trouble selecting multiple tables from the mysql SELECT command in PHP. All of the tables have the same columns but with different content. The code I'm trying to use is:

$query = "select * from blog JOIN news JOIN reviews WHERE categories LIKE '%Movies%' AND disclude_categories!=1 ORDER BY date DESC LIMIT 0,$movies_limit";

But the error I get is:

Column 'categories' in where clause is ambiguous

it means that a column specified in the where clause exists in more than ONE table.

specify the name of the table in your where clause:

select x,y,z
from tableA
inner join tableB on tableA.field=tableB.field
where tableA.AnotherField='z'

or use aliases on your joins:

select x,y,z
from tableA as A
inner join tableB as B on A.field=B.field
where A.AnotherField='z'

Do I have to manually tell it which fields that I want it to join? The tables are exactly the same (with the exception of the content of course).

For example this is my table structure:

+---------------------+------------+------+-----+---------------------+----------------+
| Field               | Type       | Null | Key | Default             | Extra          |
+---------------------+------------+------+-----+---------------------+----------------+
| id                  | int(11)    | NO   | PRI | NULL                | auto_increment | 
| date                | datetime   | NO   |     | 0000-00-00 00:00:00 |                | 
| title               | text       | NO   |     | NULL                |                | 
| author              | text       | NO   |     | NULL                |                | 
| content             | text       | NO   |     | NULL                |                | 
| categories          | text       | NO   | MUL | NULL                |                | 
| disclude_categories | tinyint(1) | NO   |     | 0                   |                | 
| theloop             | tinyint(1) | NO   |     | 0                   |                | 
| deleted             | tinyint(1) | NO   |     | 0                   |                | 
+---------------------+------------+------+-----+---------------------+----------------+

Do I have to manually tell it which fields that I want it to join?

Yes, you do.
Otherwise, how will the db engine know how to map the tables ?

Or you could use UNION queries

select x,y,z
from tblA
union 
select x,y,z
from tblB
union
select x,y,z
from tblC

The only thing using union is that you have to have the same number of fields, and the same type of data for each query in the union
http://dev.mysql.com/doc/refman/5.0/en/union.html

Hmm, well they do have the same number of fields and data types, so union should work. However now, my "where" statement is broken:

$query = "SELECT * FROM blog UNION SELECT * FROM news UNION SELECT * FROM reviews WHERE categories LIKE '%Something%' AND disclude_categories!=1 ORDER BY date DESC LIMIT 0,$music_limit"

It simply prints out everything, ignoring if categories contains "Something".

In described case WHERE condition will be applied to only last table. The correct way is
select * from table1 where $condition
union
select * from table2 where $condition
union
select* from table3 where $condition

Originally Posted by mtishetsky

In described case WHERE condition will be applied to only last table. The correct way is
select * from table1 where $condition
union
select * from table2 where $condition
union
select* from table3 where $condition

Ohhh, that makes perfect sense. Should I do the same for the "and" and "order by" statements as well?

select * from table1 where a = $a and b = $b order by a
union
select * from table2 where b = $b and c = $c order by b
union
select * from table3 where c = $c and d = $d order by c

Union unites data from result sets as if they were obtained with three separate queries. On DB level it is in fact three separate queries.

That's very interesting.

Now I'm curious, when I print the content of the query, is there a mysql/php function (or any way for that matter) to echo the current table that it's gathering the data from?

At the moment you display the data it is all already "gathered". Still if you need to know which row went from which table u can specify that in query:

select *, 't1' as source from table1 where a = $a and b = $b order by a
union
select *, 't2' as source from table2 where b = $b and c = $c order by b
union
select *, 't3' as source from table3 where c = $c and d = $d order by c

After that in your result set you will have field 'source' which contains the reference to corresponding table.

Edit:
or, you can use the solution mtishetsky gave you.
Sorry mtishetsky, I skipped your reply

Originally Posted by bswinnerton

That's very interesting.

Now I'm curious, when I print the content of the query, is there a mysql/php function (or any way for that matter) to echo the current table that it's gathering the data from?

select x,y,z,src='tblA'
from tblA
union 
select x,y,z,src='tblB'
from tblB
union
select x,y,z,src='tblC'
from tblC

And now, you have a field "src" with the table the row come from.

Perfect. But how would I actually echo the src in php?

echo 'src'; I assume wouldn't work. What's the correct way?

How do u echo all other fields from DB result set?

Aha, you haven't understood the power of union yet...
When you send an union'ed query to the db, it returns you 1 dataset, like any other query.
You cannot by itself get the distinction from which select the row comes.

By adding a column with that information, you simply extend the result set by 1 column, that gives you this information.

To get them, you do the same thing that you do to get other columns:

PHP Code:

$res=mysql_query("the union query");
while($o=mysql_fetch_object($res)){
  print 'field x:'.$o->x;
  print 'field y:'.$o->y;
  print 'field z:'.$o->z;
  print 'field src:'.$o->src;
  print '<hr/>';
} 


I see. Well I was just wondering if there was a simple MySQL or PHP variable that already knows the table that it's working in, I thought that might be a little bit easier.

Anyways, I tried the ",src='table1'" that you recommended, but when I do that I get the error: "Unknown column 'src' in 'field list'". I don't have to actually create a new column in the MySQL database do I?

I'm starting to doubt whether or not this is even the best way of going about it. Is there another way that you would recommend besides union? If I do decide to change some of the columns around in each table it will break the union statement, is there perhaps any way that I can use multiple tables no matter their column structure?

Why not just run three seperate queries then?

Originally Posted by lizciz

Why not just run three seperate queries then?

Excuse my ignorance I'm very new to PHP. That sounds like a great idea, how can I organize the sum of the three queries by date?

EDIT: I have a date column in the columns of my MySQL data.

I don't have to actually create a new column in the MySQL database do I?

No, you can dynamically create columns in a select by giving "column name"="value"

select msg='hello world';

Is perfectly valid.
I use the same principle in the union query.

is there perhaps any way that I can use multiple tables no matter their column structure?

Yes, by using joins and giving in the select the fields you want to output and the tables they come from, like in you first query

how can I organize the sum of the three queries by date?

You cannot easily. If you go that road, you will need to parse with PHP each resultset, and put them in another one in the order you want to.
I'd say you better stick with either the union or the join query.

Anyways, I tried the ",src='table1'" that you recommended, but when I do that I get the error: "Unknown column 'src' in 'field list'".

In that case, you have a syntax error.
Paste your query here, and we'll tell you where the problem is.

Okay, then in that case (I'm sorry!), I think that I want to go back to the join statement.

Here is my current UNION statement, how would I go about converting it to join?

$query = "SELECT * FROM blog WHERE categories LIKE '%Music%' AND disclude_categories!=1 UNION SELECT * FROM news WHERE categories LIKE '%Music%' AND disclude_categories!=1 UNION SELECT * FROM reviews WHERE categories LIKE '%Music%' AND disclude_categories!=1 ORDER BY date DESC LIMIT 0,$music_limit";

(and just for clarification):
I have three tables: blog, news, and reviews

and the table structure of each reads:

+---------------------+------------+------+-----+---------------------+----------------+
| Field               | Type       | Null | Key | Default             | Extra          |
+---------------------+------------+------+-----+---------------------+----------------+
| id                  | int(11)    | NO   | PRI | NULL                | auto_increment | 
| date                | datetime   | NO   |     | 0000-00-00 00:00:00 |                | 
| title               | text       | NO   |     | NULL                |                | 
| author              | text       | NO   |     | NULL                |                | 
| content             | text       | NO   |     | NULL                |                | 
| categories          | text       | NO   | MUL | NULL                |                | 
| disclude_categories | tinyint(1) | NO   |     | 0                   |                | 
| theloop             | tinyint(1) | NO   |     | 0                   |                | 
| deleted             | tinyint(1) | NO   |     | 0                   |                | 
+---------------------+------------+------+-----+---------------------+----------------+