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`).
"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.
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
$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!
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.
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?
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)
$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.
$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());
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.
$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.
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.
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.