querying two linked tables in search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

querying two linked tables in search

Post by s.dot »

I'm implementing a forum search and I'm having trouble with the SQL. In particular, when searching for "bread", I'd like to search the `topics` table where the `title` is LIKE '%bread%' and also search the `replies` table where the `reply` is like "%bread%". I'd like to gather the topic id from the query (`topics`.`id` and `replies`.`topic_id`).

I have tried this:

Code: Select all

"SELECT `p`.`topic_id`, `t`.`id` FROM `forum_replies` p, `forum_topics` t WHERE `p`.`topic_id` = `t`.`id` AND `p`.`formatted` LIKE '%$k%' OR `t`.`title` LIKE '%$k%'
This gives me a bunch of bogus results that aren't correct. What am I doing wrong?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: querying two linked tables in search

Post by Eran »

operator precedence:

Code: Select all

WHERE `p`.`topic_id` = `t`.`id` AND (`p`.`formatted` LIKE '%$k%' OR `t`.`title` LIKE '%$k%')
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: querying two linked tables in search

Post by VladSun »

:evil: Formatted:

Code: Select all

SELECT 
	`forum_replies`.`topic_id`, 
	`forum_topics`.`id` 
FROM 
	`forum_replies` 
INNER JOIN
	`forum_topics` on 
		`forum_replies`.`topic_id` = `forum_topics`.`id` 
		AND 
		`forum_replies`.`formatted` LIKE '%$k%' 
		OR 
		`forum_topics`.`title` LIKE '%$k%
OR has lower precedence than AND ;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: querying two linked tables in search

Post by s.dot »

Thanks guys.. Amazing it was something so simple. ;) I usually do use parenthesis, too.

OK, so to take this query one step further so I don't have to do two queries...

Code: Select all

$sql = "SELECT * FROM `forum_topics` WHERE `id` IN(SELECT `p`.`topic_id`, `t`.`id` FROM `forum_replies` p, `forum_topics` t WHERE `p`.`topic_id` = `t`.`id` AND (`p`.`formatted` LIKE '%$k%' OR `t`.`title` LIKE '%$k%'))";

$res = mysql_query($sql)
    or die(mysql_error());
If i can get this working I can get the results all in one query!

Comes up with this error:

Code: Select all

Operand should contain 1 column(s)
Amazing how I've survived all these years with basic CRUD usage. It's doable, just gotta use a lot more queries and a lot more variable storage in php.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: querying two linked tables in search

Post by Eran »

I'm not sure why you are trying to convert the join to a dependent subquery (that would be really inefficient), but when you use it in the WHERE clause it can only return one column (as the error indicates). When you write id = ... something ... , how can it tell what to equate it to if you return multiple columns?
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: querying two linked tables in search

Post by s.dot »

Hmm I guess that's a bit above my head.

If I run this query:

Code: Select all

SELECT `p`.`topic_id`, `t`.`id` FROM `forum_replies` p, `forum_topics` t WHERE `p`.`topic_id` = `t`.`id` AND (`p`.`formatted` LIKE '%$k%' OR `t`.`title` LIKE '%$k%')
And then fetch the results, it'll give me topic ids like 18, 22, 33

So, what I want to do is select * from forum_topics where id in(18, 22, 33)

Will I have to do this?

Code: Select all

$res = mysql_query("SELECT `p`.`topic_id`, `t`.`id` FROM `forum_replies` p, `forum_topics` t WHERE `p`.`topic_id` = `t`.`id` AND (`p`.`formatted` LIKE '%$k%' OR `t`.`title` LIKE '%$k%')")

$topic_ids = array();
while ($arr = mysql_fetch_assoc($res))
{
    $topic_ids[] = $arr['topic_id'];
}

$topic_ids = implode(', ', $topic_ids);

$topicres = mysql_query("SELECT * FROM `forum_topics` WHERE `id` IN($topic_ids)");
I'm looking for a way to do that in a single query.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: querying two linked tables in search

Post by Weirdan »

Why wouldn't you fetch the data you need directly from the first query? You don't need that extra step with 'where id in (...)':

Code: Select all

$sql = "
   SELECT t.* 
   FROM `forum_replies` p 
   INNER JOIN `forum_topics` t 
   ON `p`.`topic_id` = `t`.`id` 
   WHERE 
      `p`.`formatted` LIKE '%$k%' 
      OR `t`.`title` LIKE '%$k%'
";

$res = mysql_query($sql)
    or die(mysql_error());
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: querying two linked tables in search

Post by s.dot »

Weirdan wrote:Why wouldn't you fetch the data you need directly from the first query? You don't need that extra step with 'where id in (...)':
That's why ;) My logical thinking was I'd have to select the topic ids first and then query the topics table for information about those specific topic ids.
Weirdan wrote:

Code: Select all

$sql = "
   SELECT t.* 
   FROM `forum_replies` p 
   INNER JOIN `forum_topics` t 
   ON `p`.`topic_id` = `t`.`id` 
   WHERE 
      `p`.`formatted` LIKE '%$k%' 
      OR `t`.`title` LIKE '%$k%'
";

$res = mysql_query($sql)
    or die(mysql_error());
Beautiful :-D This really is my first time working with joins and multi-table querying. This stuff looks fun. Good info here, thanks.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: querying two linked tables in search

Post by josh »

s.dot wrote:OK, so to take this query one step further so I don't have to do two queries..
Just be careful, sometimes that can be a step backward. Sometimes multiple queries are faster than joins. I've seen 1 query take hours. I've also seen 1,000s of queries happen within a second.

Also, relying on operator precedence is sloppy practice. Always use the parenthesis if you want others to be able to understand it.

Some people cannot even remember the order of operations for our numeral system, let alone the operator precedences in various programming languages.
Post Reply