Welcome Guest, Not a member yet? Register   Sign In
Need help with search engine
#1

[eluser]M4rc0[/eluser]
Hi!

I'm trying to implement a search in my CI app using mysql's fulltext index and searching

I think it's a very good way to perform a search in website (can't rely on google custom search so far as this is a localhost website), if anybody has another approach, please tell me! I'd be interested to hear.

So once i make this work i think it's good to have the code documented here in the forum if anybody looks for this (I didn't find anything like it in the forum).

So far is not working :-P

Here's my code:
Code:
function search_movie()
     {
         //SELECT * FROM table WHERE MATCH (field1,field2) AGAINST ('keyword' WITH QUERY EXPANSION);*/
        $keywords = "'".$this->input->post('keywords')."'";        
        $this->db->query('SELECT * FROM movies WHERE MATCH (name,director,actors,country,year,comment) AGAINST ('.$keywords.' WITH QUERY EXPANSION);');        
        $query = $this->db->get('movies');
        return $query->result();
     }

The sql is performing correctly, already did an echo to see the output of the query.

But it's showing all rows i have in the table instead of just those containing the keywords.

Anybody has any clue where the problem might be?
#2

[eluser]M4rc0[/eluser]
Ok, let me try again.

Is anybody there that has a search box on your CI application?
How would you do it?

Please share! I'd like to try something different from %LIKE% as I think It's not accurate.

Suggestions?
#3

[eluser]manilodisan[/eluser]
Try:
Code:
$this->db->query('SELECT * FROM movies WHERE MATCH (name,director,actors,country,year,comment) AGAINST ('.$keywords.' WITH QUERY EXPANSION IN BOOLEAN MODE);');

Or:
Code:
$this->db->query('SELECT * FROM movies WHERE MATCH (name,director,actors,country,year,comment) AGAINST ('.$keywords.' IN BOOLEAN MODE);');
#4

[eluser]M4rc0[/eluser]
Thanks for your reply manilodisan!

With "QUERY EXPANSION IN BOOLEAN MODE":
Code:
A Database Error Occurred

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN BOOLEAN MODE)' at line 1

SELECT * FROM movies WHERE MATCH (name,opinion) AGAINST ('test' WITH QUERY EXPANSION IN BOOLEAN MODE);

And with just "BOOLEAN MODE" it still brings all the rows.

Now I'm matching just name and opinion fields to avoid the query expansion to bring up too much, but it still brings it all.
#5

[eluser]manilodisan[/eluser]
You can check the Full text guides/docs as It's a long time since I did this and I'm not sure what the solution was. Here's a link: http://dev.mysql.com/doc/refman/5.0/en/f...earch.html
#6

[eluser]M4rc0[/eluser]
That's exactly where i took it, just tought that the first link explains more clearly.

manilodisan, do you have any other suggestions do to a search engine? How would you do it?

I will give up on fulltext for now, not a big deal since i read it can be slow as well. I just want something more accurate then %like% but I don't know what.
#7

[eluser]manilodisan[/eluser]
Here's a tutorial I wrote some while ago: http://www.roscripts.com/PHP_search_engine-119.html
I'm no expert in searching. I'm building the same thing as you right now as a module for Webber and I'm in the search for a solution myself. Will keep you posted ...
#8

[eluser]Randy Casburn[/eluser]
@M4rc0 - you did fine! The only mistake you made was running the query twice. The second time you ran it, you queried every record. Check the docs. Here is the mistake:

[quote author="M4rc0" date="1222474337"]Here's my code:
Code:
QUERY 1)        $this->db->query('SELECT * FROM movies WHERE MATCH (name,director,actors,country,year,comment) AGAINST ('.$keywords.' WITH QUERY EXPANSION);');        
QUERY 2)        $query = $this->db->get('movies');
[/quote]

The only thing you should have to do to correct this is remove:

Code:
QUERY 2)        $query = $this->db->get('movies');

and then you should be able to find your results thus:

Code:
foreach ($query->result() as $row)
{
   echo $row->title;
   echo $row->actor
   echo $row->yada;
}

Hope this helps,

Randy
#9

[eluser]M4rc0[/eluser]
Hi Randy! Is that simple? :ohh:

I tried doing that and i get a blank page, when i view the code it stops right after ul, so i suppose there's something wrong with foreach, could you check the code for me?

model
Code:
function search_movie()
     {
        $keywords = "'".$this->input->post('keywords')."'";        
        $this->db->query('SELECT * FROM movies WHERE MATCH (name,opinion) AGAINST ('.$keywords.' WITH QUERY EXPANSION);');    
     }

controller
Code:
function search()
    {    
        $data['query'] = $this->Movie_model->search_movie();
        $data['header'] = "Search Results";
        $this->template->write_view('content','movie/results',$data,TRUE);
        $this->template->render();                
    }

view
Code:
<h2><img src="public/images/search.png" />&lt;?=$header;?&gt;</h2>
<ul id="movies">
&lt;?php foreach($query->result() as $movie):?&gt;
    <li><a >id;?&gt;" title="&lt;?=$movie->name;?&gt;"><img class="tip">cover_thumb;?&gt;" title="&lt;?=$movie->name;?&gt;" /></a></li>
&lt;?php endforeach;?&gt;
</ul>

Nevermind the extra stuff i put like ul, img tag and etc to lazy to clean the code to make "forum readable" %-P

Are you sure i do the result in the view and not model?
#10

[eluser]Randy Casburn[/eluser]
[quote author="M4rc0" date="1222564807"]Are you sure i do the result in the view and not model?[/quote]

I think after all of that, you answered your own question about the spaghetti, so the only question left is the one above...

The answer to that question is: it is a personal preference. There is certainly nothing wrong with looping through your query results in your view the way you've done here. It's quite efficient actually.

The counter argument is that there is too much mixture of logic and visualization. Or, put another way, not enough separation between the two.

The discussion becomes somewhat academic so if you're attempting to solve an academic problem (for homework) or produce the perfect design patterned solution (now we're really becoming academic!), then by all means worry about these things.

If you're concerned about producing output from a query that isn't likely to change too much over time, your solution is quite solid. That is, perhaps, why the example directly from the CodeIgniter documentation matches your solution as well.

Hope this is helpful,

Randy




Theme © iAndrew 2016 - Forum software by © MyBB