Page 1 of 1
querying two linked tables in search
Posted: Sun Apr 04, 2010 4:39 pm
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?
Re: querying two linked tables in search
Posted: Sun Apr 04, 2010 4:59 pm
by Eran
operator precedence:
Code: Select all
WHERE `p`.`topic_id` = `t`.`id` AND (`p`.`formatted` LIKE '%$k%' OR `t`.`title` LIKE '%$k%')
Re: querying two linked tables in search
Posted: Sun Apr 04, 2010 5:01 pm
by VladSun

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

Re: querying two linked tables in search
Posted: Sun Apr 04, 2010 8:31 pm
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.
Re: querying two linked tables in search
Posted: Sun Apr 04, 2010 9:15 pm
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?
Re: querying two linked tables in search
Posted: Sun Apr 04, 2010 9:27 pm
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.
Re: querying two linked tables in search
Posted: Sun Apr 04, 2010 9:48 pm
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());
Re: querying two linked tables in search
Posted: Sun Apr 04, 2010 9:54 pm
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

This really is my first time working with joins and multi-table querying. This stuff looks fun. Good info here, thanks.
Re: querying two linked tables in search
Posted: Sun Apr 04, 2010 11:28 pm
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.