MySQL Search - Resolved

[eluser]georgerobbo[/eluser]
I am starting to develop a search form, however I need to search two different tables with the same query.

The two tables I wish to query are post, and tag. You can guess what the tag table does. The post table contains all the uploads/posts.

Post

`ID` int(11) NOT NULL auto_increment,
`title` varchar(25) NOT NULL,
`permalink` varchar(50) NOT NULL,
`author` varchar(25) NOT NULL,
`username` varchar(25) NOT NULL,
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`filename` varchar(25) NOT NULL,
`description` varchar(200) NOT NULL,
`category` varchar(25) NOT NULL,
`comments` int(11) NOT NULL,
`favourites` int(11) NOT NULL,
`views` int(11) NOT NULL,
PRIMARY KEY (`ID`)
ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Tag

`ID` int(11) NOT NULL auto_increment,
`post` int(11) NOT NULL,
`tag` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

I want to select results from the two tables based on the one keyword. However I do not know how to do this in my model function.

Code:

function search($query)
    {
        $this->db->select('*');
        $this->db->where('title', $query);
        $this->db->or_where('author', $query);
        $this->db->or_where('description', $query);
        $this->db->or_where('category', $query);
        
                // How do I select a row from another table within the function?

        $query = $this->db->get('post');  // How do I search both post and tag?
        return $query->result_array();
    }

[eluser]@rno[/eluser]
You could try using a left join for this one:
$this->db->join('Tag', 'LEFT')
and than use a regular OR_WHERE

But I think you will run into trouble when searchnig for multiple keywords.

Maybe someone else has a better solutions, but for single keywords it should work.

Regards,
Arno

[eluser]imn.codeartist[/eluser]
Try this code it joins both table

Code:

function search($query)
    {
        $this->db->select('*');
        $this->db->from('Post p');
        $this->db->join('Tag t', 'p.ID = t.post');
        $this->db->where('p.title', $query);
        $this->db->or_where('p.author', $query);
        $this->db->or_where('p.description', $query);
        $this->db->or_where('p.category', $query);
        
                // How do I select a row from another table within the function?

        //$query = $this->db->get('post');  // How do I search both post and tag?
        $query = $this->db->get();
        return $query->result_array();
    }

[eluser]georgerobbo[/eluser]
Thanks. I am able to search for a tag, however whenever I search for a name in the post table it outputs the result multiple times for every tag there is that relates to it.

[eluser]CroNiX[/eluser]
Maybe you need to use "distinct" in the select.

[eluser]bluepicaso[/eluser]
thank you. I Love codeigniter. It has solution for almost every massive query. I was successfull to join three tables using join and now this one. Its been nearly a year I'm using CI.