2

Possible Duplicate:
How do I create a PDO parameterized query with a LIKE statement in PHP?

At the moment I have a very simple search engine which uses the old mysql_* queries and also uses "like" in the query, i understand this is now outdated and using like queries can get slow as the database grows?

I am looking for some ideas on how I can create a search engine using PDO as it offers more protection than i currently have, I have researched about using MATCH and AGAINST in a query,to search the "title" and "description" columns in my database, but as i am a beginner i am unsure of where to start.

I get stuck on trying to figure out how to implement a search query, and getting the search to work if the user enters multiple keywords

any help is much appreciated, i understand this might be a big ask of someone to explain this to me but i am doing as much as i can to learn as i am a student and working on a project

Thankyou for anyones help!

THIS IS PART OF MY CURRENT CODE WHICH I WOULD LIKE TO CHANGE AND UPDATE TO SOMETHING MORE SECURE AND WITH BETTER SEARCH FACILITIES:

$query = "SELECT * FROM people WHERE ";

foreach ($terms as $each){
$i++;

if ($i == 1)
    $query .= "lname LIKE '%$each%' ";
if($i == 1 && $_GET['category'] != '') {
    $query .= "AND category = '$chosencategory' ";
}
else
    $query .= "AND lname LIKE '%$each%' ";
if($_GET['price'] != '') {
    $query .= " ORDER BY price $price";
}
echo $query;
}
Community
  • 1
  • 1
neeko
  • 1,930
  • 8
  • 44
  • 67
  • 1
    I think this is what you're looking for: [How do I create a PDO parameterized query with a LIKE statement in PHP?](http://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement-in-php) – John Carter Sep 13 '12 at 20:45
  • thankyou! i have taken a look at the code, do you have any idea what effect using a LIKE statement has on performance in the long term? and how protected are you with the PDO LIKE statement? – neeko Sep 13 '12 at 20:51
  • 1
    If i'm making a search, I'm indexing the table (temp) using triggers to update and delete, using myisam on it and match against which is much more powerful than LIKE. – wesside Sep 13 '12 at 20:52
  • 2
    There is no difference between a prepared query with/without LIKE in it. Its just as safe. As long as you use `prepare()` you should be fine. – mishmash Sep 13 '12 at 20:52
  • thankyou for everyones feedback has been very helpful! @wes regarding indexing the table and using match/again, could you give my a few starting pointers on how i could go about implementing a system like that? – neeko Sep 13 '12 at 20:55
  • Google could explain it better than myself. – wesside Sep 13 '12 at 20:56

1 Answers1

3

Now that's a very complicated topic. So I'm just going to point you to a few starting points. If someone else is able to formulate a good answer to this hugely complicated topic here, I'd be glad to give them an upvote..

  • Doctrine 1 has a decent "search engine". [1] (If you're not already using an ORM, I highly recommend you give it a try.)
  • Lucene[2] is also worth a try.
  • Roll your own search engine. I also used to think it's about some MySQL features or something, but it's really not. It's all about building good indexes and using them well. (Building them yourself, that is. Not just using database indexes.) It's actually a pretty interesting topic to get into, if you have the time.
  • Buy Nine Algorithms That Changed the Future by John MacCormick.[3] It's got an awesome chapter about how search engines work.

[1] http://docs.doctrine-project.org/projects/doctrine1/en/latest/en/manual/searching.html

[2] http://en.wikipedia.org/wiki/Lucene

[3] http://www.amazon.com/Nine-Algorithms-That-Changed-Future/dp/0691147140

Rudolph Gottesheim
  • 1,671
  • 1
  • 17
  • 30
  • Dont forget http://sphinxsearch.com/ – wesside Sep 13 '12 at 21:08
  • thankyou this was very useful, looks like i have a lot of research to do! Is it possible you could explain a little about indexes and how they work? – neeko Sep 13 '12 at 21:17
  • 1
    Really, really big topic. But I'd bet you'll find a lot about it on the web (Maybe google "custom mysql index table" or something). But at its *very* basis it's about creating a two-column table that's got searched-for words in the first column and corresponding matches (URLs, IDs,...) in the second. – Rudolph Gottesheim Sep 13 '12 at 22:54