[Mortal_Takeda]

I'm trying to make a search function which should get the results from 2 tables. I found more post of people with similar problems, but those were different from mine. Here's what I'm trying to do:

PHP Code:

$query = mysql_query("SELECT * FROM table1 LEFT JOIN table2 USING (post) WHERE post LIKE '$keywords'");



while ( $results = mysql_fetch_array($query) )

{

    echo $results['title']."<br>";

} 

This would return the following error: Column 'title' in field list is ambiguous

Why? Because both tables have a column with the same name (title). But I just want MySQL to treat those ambiguous columns as one column.

So I've read a few times through this page, but I just can't fix this. Any help appreciated.

[LordDaimos]

You have to specify the fieldnames as `table1`.`title`, `table2`.`title` so it can seperate them. You can't do select * if you are joining tables with same fieldnames.

/ Karl
http://www.crazybeavers.se/

[Mortal_Takeda]

But if I would specify the tables like you said, the above example would be useless. Is there something else I could do? I want the script to list the results of both tables.

[blaw]

Hello,

If the only field you want to retrieve from each table is "title", then I suggest that you explicitly say that, instead of retrieving every single field with * for performance and possibly other reasons.

Anyways, I think you will find SQL keyword UNION useful in your case, provided that these two fields have the same data type (and length). Check this out. In a word, I'd say JOIN joins horizontally, but UNION goes vertically, but I strongly recommend that you check out the following:

MySQL Doc
http://dev.mysql.com/doc/mysql/en/union.html

Or W3Schools (easier, but not MySQL official):
http://www.w3schools.com/sql/sql_union.asp

HTH.

[Mortal_Takeda]

Thanks a lot blaw, that was exatly what I was looking for. The search function now gives the results from both tables

But I still have one question. I also want the results to show from which table the result came from. Is there a way to do this?

[blaw]

Hi,

I'm glad that you got it.

To show (or let PHP know) which table that "title" field belongs to, what I would usually do is simply create a flag field. For instance,

PHP Code:

SELECT title, 'A' AS 'from_table'

FROM TableA

UNION (

    SELECT title, 'B'

    FROM TableB

) 

Now all of the records from TableA has a value "A" and the ones from TableB has a value "B" in the derived field named "from_table", so when you are looping through the results, check this flag to see which value is from which "title" table.

HTH.

[Mortal_Takeda]

Thanks blaw, it worked! Now I finally have my search function working for my website, thanks for your help guys!