Please execute the following queries first to set up so that you can help me:-
CREATE TABLE IF NOT EXISTS `Tutor_Details` (
`id_tutor` int(10) NOT NULL auto_increment,
`firstname` varchar(100) NOT NULL default '',
`surname` varchar(155) NOT NULL default '',
PRIMARY KEY (`id_tutor`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=41 ;
INSERT INTO `Tutor_Details` (`id_tutor`,`firstname`, `surname`) VALUES
(1, 'Sandeepan', 'Nath'),
(2, 'Bob', 'Cratchit');
CREATE TABLE IF NOT EXISTS `Classes` (
`id_class` int(10) unsigned NOT NULL auto_increment,
`id_tutor` int(10) unsigned NOT NULL default '0',
`class_name` varchar(255) default NULL,
PRIMARY KEY (`id_class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=229 ;
INSERT INTO `Classes` (`id_class`,`class_name`, `id_tutor`) VALUES
(1, 'My Class', 1),
(2, 'Sandeepan Class', 2);
CREATE TABLE IF NOT EXISTS `Tags` (
`id_tag` int(10) unsigned NOT NULL auto_increment,
`tag` varchar(255) default NULL,
PRIMARY KEY (`id_tag`),
UNIQUE KEY `tag` (`tag`),
KEY `id_tag` (`id_tag`),
KEY `tag_2` (`tag`),
KEY `tag_3` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;
INSERT INTO `Tags` (`id_tag`, `tag`) VALUES
(1, 'Bob'),
(6, 'Class'),
(2, 'Cratchit'),
(4, 'Nath'),
(3, 'Sandeepan'),
(5, 'My');
CREATE TABLE IF NOT EXISTS `Tutors_Tag_Relations` (
`id_tag` int(10) unsigned NOT NULL default '0',
`id_tutor` int(10) default NULL,
KEY `Tutors_Tag_Relations` (`id_tag`),
KEY `id_tutor` (`id_tutor`),
KEY `id_tag` (`id_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `Tutors_Tag_Relations` (`id_tag`, `id_tutor`) VALUES
(3, 1),
(4, 1),
(1, 2),
(2, 2);
CREATE TABLE IF NOT EXISTS `Class_Tag_Relations` (
`id_tag` int(10) unsigned NOT NULL default '0',
`id_class` int(10) default NULL,
`id_tutor` int(10) NOT NULL,
KEY `Class_Tag_Relations` (`id_tag`),
KEY `id_class` (`id_class`),
KEY `id_tag` (`id_tag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `Class_Tag_Relations` (`id_tag`, `id_class`, `id_tutor`) VALUES
(5, 1, 1),
(6, 1, 1),
(3, 2, 2),
(6, 2, 2);
In the present system data which I have given , tutor named "Sandeepan Nath" has has created class named "My Class" and tutor named "Bob Cratchit" has created class named "Sandeepan Class".
Requirement -
To execute a single query with limit on the results to show search results as per AND logic on the search keywords like this:-
If "Sandeepan Class" is searched , Tutor Sandeepan Nath's record from Tutor Details table is returned(because "Sandeepan" is the firstname of Sandeepan Nath and Class is present in class name of Sandeepan's class)
If "Class" is searched Both the tutors from the Tutor_details table are fetched because Class is present in the name of the class created by both the tutors.
Following is what I have so far achieved (PHP Mysql):-
<?php
$searchTerm1 = "Sandeepan";
$searchTerm2 = "Class";
mysql_select_db("test");
$sql = "SELECT td.*
FROM Tutor_Details AS td
LEFT JOIN Tutors_Tag_Relations AS ttagrels ON td.id_tutor = ttagrels.id_tutor
LEFT JOIN Classes AS wc ON td.id_tutor = wc.id_tutor
LEFT JOIN Class_Tag_Relations AS wtagrels ON td.id_tutor = wtagrels.id_tutor
LEFT JOIN Tags as t1 on ((t1.id_tag = ttagrels.id_tag) OR (t1.id_tag = wtagrels.id_tag))
LEFT JOIN Tags as t2 on ((t2.id_tag = ttagrels.id_tag) OR (t2.id_tag = wtagrels.id_tag))
where t1.tag LIKE '%".$searchTerm1."%'
AND t2.tag LIKE '%".$searchTerm2."%'
GROUP BY td.id_tutor
LIMIT 10
";
$result = mysql_query($sql);
echo $sql;
if($result)
{
while($rec = mysql_fetch_object($result)) $recs[] = $rec;
//$rec = mysql_fetch_object($result);
echo "<br><br>";
if(is_array($recs))
{
foreach($recs as $each)
{
print_r($each);
echo "<br>";
}
}
}
?>
But the results are :-
If "Sandeepan Nath" is searched, it does not return any tutor (instead of only Sandeepan's row)
If "Sandeepan Class" is searched, it returns Sandeepan's row (instead of Both tutors )
If "Bob Class" is searched, it correctly returns Bob's row
If "Bob Cratchit" is searched, it does not return any tutor (instead of only
