MYSQL Search

[k1ngscorp1o]

I am making an arcade script and I want to search for words within the all the descriptions in the table and Im unsure how I'd do this is mysql. I know its possible to search through a string in php how would I type 'action' and look for the word action in the description of all the games and return the results with action in it?

[Seker]

SELECT * FROM `table` WHERE `column` LIKE '% action %' OR `column` LIKE 'action %' OR `column` LIKE '% action'

[SRB]

FULLTEXT Search

SQL

ALTER TABLE `table` ADD FULLTEXT `ft_search` (`game_title`,`game_description`);

 

MySQL to call data (And weight results)

SELECT `game_title`, `game_description`, MATCH(`game_name`,`game_description`) AGAINST ('{$search}') AS `score` 
FROM `support_faq` WHERE MATCH(`game_name`, `game_description`) AGAINST ('{$search}') ORDER BY `score` DESC

 

Using $search to contain your search

$search = $_POST['search_term']; // Obviously with some type casting, data checking and whatnot.

 

Note

This does not work with the InnoDB Engine, but assuming you use the myISAM type, you'll be fine.

Edited August 20, 2013 by Guest

[k1ngscorp1o]

FULLTEXT Search

SQL

ALTER TABLE `table` ADD FULLTEXT `ft_search` (`game_title`,`game_description`);

 

MySQL to call data (And weight results)

SELECT `game_title`, `game_description`, MATCH(`game_name`,`game_description`) AGAINST ('{$search}') AS `score` 
FROM `support_faq` WHERE MATCH(`game_name`, `game_description`) AGAINST ('{$search}') ORDER BY `score` DESC

 

Using $search to contain your search

$search = $_POST['search_term']; // Obviously with some type casting, data checking and whatnot.

 

Note

This does not work with the InnoDB Engine, but assuming you use the myISAM type, you'll be fine.

Ah :( all I use is InnoDB

[SRB]

Ah :( all I use is InnoDB

I do too - I have 1 table that I use this on, that I felt was worth the change to be able to use full text, since fulltext searching is pretty much **** hot :D

[k1ngscorp1o]

I do too - I have 1 table that I use this on, that I felt was worth the change to be able to use full text, since fulltext searching is pretty much **** hot :D

haha yea, no reason not to make the change here! I'll go with this. Thanks!

[Alan]

This does not work with the InnoDB Engine...

MySQL 5.6 supports full-text searching under the InnoDB engine - amongst some other seriously impressive updates that make it well worth your time finding a host that has it installed - or in your case, me I believe! :D

[SRB]

MySQL 5.6 supports full-text searching under the InnoDB engine - amongst some other seriously impressive updates that make it well worth your time finding a host that has it installed - or in your case, me I believe! :D

Didn't know they had updated. Guess I should venture into change logs more often... ok, I should just go into them, just this once! :P

EDIT: When they let me define words to keep, from the stop lists, I'll be happy once more.

[k1ngscorp1o]

Didn't know they had updated. Guess I should venture into change logs more often... ok, I should just go into them, just this once! :P

EDIT: When they let me define words to keep, from the stop lists, I'll be happy once more.

Would this be correct, well its not working so I'd guess I did something wrong, but this is the function and then the function in use:

 

   public function searchGames($term) {
       $search = $this->dbh->prepare("SELECT `name`, `desc`, MATCH(`name`,`desc`) AGAINST (:term) AS `score` FROM `game_list` WHERE MATCH(`name`, `desc`) AGAINST (:term) ORDER BY `score` DESC");
       $search->execute(array(':term' => $term));

       return $search->fetchAll(\PDO::FETCH_OBJ);
   }

 

Then in use:

 

$term = $_GET['term'];

       foreach($mainModel->searchGames($term) as $game) {
           $mainTPL->newBlock('search_results');
           $mainTPL->assign(array('name' => $game->name));
       }

[k1ngscorp1o]

guess no one knows...

[rockwood]

FULLTEXT searches are absolutely going to be faster

select COUNT(columnname) FROM table WHERE INSTR(columnname,'keyword') > 0;

or follow this

http://www.joedolson.com/boolean-query-in-mysql.php

Edited August 22, 2013 by rockwood

[k1ngscorp1o]

Im wondering what I'm doing wrong, I've read through the tutorials and I'm not even getting errors back but its just not pulling my results. I hard coded the term in to make sure it was passed and its just not returning results...anyone see a problem...

 

public function searchGames($term) {
       $search = $this->dbh->query("SELECT * FROM game_list WHERE MATCH (`name`,`desc`) AGAINST ('test')");

       return $search->fetch(\PDO::FETCH_ASSOC);
}

 

Here is it in use:

 

var_dump($mainModel->searchGames($term)); die;

 

That keeps returning:

boolean false

or array empty

[k1ngscorp1o]

never mind it is working, I just didnt know about the 50% rule so I had to put it in bool mode so it would return.

[SRB]

Why the slash before PDO?

 

Im wondering what I'm doing wrong, I've read through the tutorials and I'm not even getting errors back but its just not pulling my results. I hard coded the term in to make sure it was passed and its just not returning results...anyone see a problem...

 

public function searchGames($term) {
       $search = $this->dbh->query("SELECT * FROM game_list WHERE MATCH (`name`,`desc`) AGAINST ('test')");

       return $search->fetch(\PDO::FETCH_ASSOC);
}

 

Here is it in use:

 

var_dump($mainModel->searchGames($term)); die;

 

That keeps returning:

boolean false

or array empty

[HauntedDawg]

Rule of thumb: When ever having issues with a query (such as you specified not returning results). It's a lot quicker testing the query directly into phpmyadmin (or any other db tool you use) until you get what you want, then you put that query into php!

PS: finally climbed off your high horse now that you require help? Glad to see that! Thank you for finally asking something that is simple!

[sniko]

Why the slash before PDO?

Saving it for the namespace?

Also, check this test run and you will see why it's bool(false). Take note of what the above post says.

Rule of thumb: When ever having issues with a query (such as you specified not returning results). It's a lot quicker testing the query directly into phpmyadmin (or any other db tool you use) until you get what you want, then you put that query into php!

As you've claimed to be one of the best around here on these forums multiple times, I'd of thought you would have done that.

Edited August 22, 2013 by sniko

[k1ngscorp1o]

@guest - sniko is correct.

@sniko / HD - I did that previously. I didnt get an error because the query was doing what it was set by default to do. FullText Search ignores words that occur in 50% of your rows which is why I did every debugging tactic in the book and it didnt work. Its hard to debug something when there is no error. It was a matter of me not fully understanding Full Text Search and not reading up on it and just attempting

to use it without realizing { IN BOOLEAN MODE | WITH QUERY EXPANSION } were important. Thanks for your attempts at lessons on debugging but wouldnt have helped any...though because thats simple im sure you 2 knew that.

[HauntedDawg]

As you've claimed to be one of the best around here on these forums multiple times, I'd of thought you would have done that.

Sorry, but I have never claimed to be the best around here, all i have claimed is that I am efficient at programming, in essence meaning that I can program at a fast pace along with neat and tidyness. And 60% of the time, the script alway's work's withy minor faults.

I don't have to do huge amount's of research. Maybe once in awhile. I don't have to be asking questions.

I've seen NWE code when it came out, ever since have not seen it. Haven't coded for it, but the point I'm getting at is that, if someone approached me and asked me for a module for NWE, I wouldn't need to tell them "give me 2 days to learn the engine" type of thing.

But... meh.

[a_bertrand]

Well a guy asked for a pet module for NWE. So you could try to make it ;)

[Seker]

And 60% of the time, the script alway's work's withy minor faults.

To be certain, you've claimed a much higher percentage of success in the past.

Just saying.

/popcorn

[HauntedDawg]

To be honest, Majority (95%) of the time, when ever i modify a script, or write a script. I never have parse errors. Never have missing curly brackets. So, your page still displays, but the SQL query might be wrong, or a div tag is not closing, or lets say an attribute without the closing quote (EG: class="something).

I do have to say, it's not about success, but productivity. Not having to go and find the little issue in your script is nice, make's development faster.

When there is an issue (as stated above), usually it's quicker to find, as it's part of something being echo'ed out.

Jus sayin!

[a_bertrand]

well with any good IDE, parse errors should be solved while writting the code, however, parse errors should be the easiest to fix. Personally logic errors take me a lot more time, both to detect and fix.