joining tables for search results

[rodhow]

hello, I am trying to join multiple tables to print out links as search results on a page. So far one table is fine but when I try to add the second table, i get an error message. I have two tables (qa and mlb) and I get search results from keywords that match tags in qa table, but the error message comes when I add tags2 from mlb table. heres the query

 

$sql = "SELECT qa.link, mlb.link2 FROM qa, mlb WHERE qa.tags, mlb.tags2 LIKE '%$trimmed%'";

$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

 

There is another query as well which gets me my rows from search results that is probably having the same problems...

 

$sql = "SELECT COUNT(*) FROM qa, mlb WHERE qa.tags, mlb.tags2 LIKE '%$trimmed%'";

$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

 

Here is a query with one table that works...

 

$sql = "SELECT COUNT(*) FROM qa WHERE tags LIKE '%$trimmed%'";

$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

[Barand]

You need to specify the matching columns in the tables for the join

 

SELECT qa.link, mlb.link2 
FROM qa
INNER JOIN mlb ON qa.??? = mlb.???
WHERE qa.tags LIKE '%$trimmed%'

[mikosiko]

If the tables are not related you need to use UNION

 

[DavidAM]

AND you need to break up the WHERE clause into separate conditions

WHERE qa.tags, mlb.tags2 LIKE '%$trimmed%'";

 

should be

WHERE qa.tags LIKE '%$trimmed%'" AND mlb.tags2 LIKE '%$trimmed%'";

(or maybe OR instead of AND, depending on what you want)

[rodhow]

Hello, I combined both of the suggestions and keep getting a fatal error...this is one of several queries I tried but didn't work.

 

$sql = "SELECT qa.link, mlb.link2 FROM qa INNER JOIN mlb ON qa.id = mlb.id (<--- don't really understand this part) WHERE qa.tags LIKE '%$trimmed%' LIMIT $offset, $rowsperpage AND mlb.tags2 LIKE '%$trimmed%' LIMIT $offset, $rowsperpage";

 

tried it without the INNER JOIN, ON qa.id=mlb.id and also tried qa.tags AND mlb.tags2 LIKE '%$trimmed%' LIMIT $offset, $rowsperpage . I also tried the UNION but was unsuccessful. Help lol

[Barand]

If you gave keyboards to an infinite number of monkeys they would come up with a working SQL query. I think you are a few monkeys short.

 

Tell us your table structures and what, exactly, you are trying to do. Then we can help.

[rodhow]

the data (links) are displayed as search results. from one table (qa) I am getting what i want to be displayed...but I have another table (mlb), basically set up the same way that I'm having problems combining into the query that already works. The differences is the data i.e. qa link is questions for one set of athletes (who (nlf) was born on this day) and in the mlb table under link2 is for another set (who (mlb) was born on this day)...I want it all to flush out on my search results page..Thanks for the help

[rodhow]

basically when someone enters a search word, if it matches whats in the tag column, the query kicks out the link on a search results page. For the qa table, it is set up with a id, q#, question, link, answer and tags. In the mlb table, it is id2,q#2,question2,link2, answer2 and tags2. Field names tags(qa) and tags2(mlb) has search terms corresponding to the link they are search for. For instance, if someone puts in NFL, then all the links with the tag nfl will print on the search results page. Again, this works for the qa table but I have other tables im now filling up and want to join it on one search page.

 

qa table

id

q#

question

linkans

answer

tags

 

mlb table

id2

q#2

question2

link2an

answer2

tags 2

 

Hope this illustrates it more effectively...thanks

[Barand]

If the tables are not related you need to use UNION

 

I think you need to follow this advice