Welcome to WebmasterWorld Guest from 54.160.254.203

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Simple PHP SEARCH engine

based on prepared statements

     
10:31 am on Jul 8, 2012 (gmt 0)

Junior Member

joined:Nov 5, 2011
posts: 55
votes: 0


Hello again everybody,

today i try to set up my first and simple SEARCH ENGINE based on prepared statements.

Code:

<?php
if(isset($_REQUEST['word'])) {
$word = stripslashes($_REQUEST['word']);
// Prepared Statement
$db = new mysqli('#*$!', '#*$!', '#*$!', '#*$!');
$db -> query("SET NAMES 'latin2'");
$stmt = $db->stmt_init();
if($stmt->prepare("SELECT `user_id`, `model`, `vendor`, `registration_date` FROM `my_database` WHERE `model` = ? OR `vendor` = ? ORDER BY registration_date DESC")) {
$stmt->bind_param('ss', $word,$word);
$stmt->execute();
$stmt->bind_result($id_var, $model_var, $vendor_var, $reg_var);
while($stmt->fetch()) {
echo '<table>';
echo '<td><b>ID:</b> '.$id_var.'</td><tr />';
echo '<td><b>Model:</b> '.$model_var.'</td><tr />';
echo '<td><b>Wprowadzono:</b> '.$reg_var.'</td><tr />';
echo '<td><b>Odnośnik:</b> <a href="http://www.mypage.com/catalog/detail.php?id='.$id_var.'">Go there</a></td><br />';
echo '</table>';
}}

else {
echo 'There is no word in database<br />';
echo $word;
}
}
?>


Plain and simple. It works wery well.
Now i want to try complicate a little and add to the query condition LIKE. [u]Something like this[/u]:


$query = 'SELECT user_id, model, vendor FROM my_database WHERE model LIKE '%' . $word . '%' OR vendor LIKE '%' . $word . '%' ORDER BY registration_date DESC';


Can you help me with it ?
Thanks in advence.
9:22 am on July 9, 2012 (gmt 0)

Junior Member

joined:Nov 5, 2011
posts: 55
votes: 0


Finally i get it work by change to:


if($stmt->prepare("SELECT `user_id`, `model`, `vendor`, `registration_date` FROM `my_database` WHERE `model` LIKE CONCAT('%',?,'%') OR `vendor` LIKE CONCAT('%',?,'%') ORDER BY registration_date DESC")) {


Now I need to make a change that allows to search for words regardless of whether in to the search box someone type uppercase or lowercase.

PHP currently recognizes large and small signs.
If you have a device at the base of the model JFS200 and someone will want to type in search "jfs" - nothing found.
6:47 pm on July 10, 2012 (gmt 0)

Junior Member

joined:Nov 5, 2011
posts: 55
votes: 0


Anyone ?
2:04 pm on July 11, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 12, 2005
posts:5966
votes: 0


concat('%',?,'%')
is ugly. You should probably pull these percent symbol out into your substituted value.

Also, if it is necessary to you normalize the search term and the field. In the extreme case, doing something like this should suffice:
where upper(model) like ?
and then you convert the user input to uppercase before substitution.
3:49 pm on July 11, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


or use regex, but the overhead is higher. "Like" should be case-insensitive.
4:10 pm on July 11, 2012 (gmt 0)

Junior Member

joined:Nov 5, 2011
posts: 55
votes: 0


The problem was solved.
It turned out that the database "model" and "vendor" have the encoding (utf8bin).
Now, simple search engine works.

eelixduppy - why do you mean by -> ugly? (Dangerous?)
6:18 pm on July 13, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 12, 2005
posts:5966
votes: 0


Ugly as in unnecessary and not flexible -- you have separated the query value into two different locations. If you wanted to change the "like" behavior you couldn't.

What if you wanted to make a more advanced search that allowed options of searching terms that "begin with" ,"end with", "contain", etc.

It should just be
where model like ?
and then in the substituted parameter you can add wildcards as necessary.


$contains = '%'.$term.'%';
$startsWith = $term.'%';
....
7:55 pm on July 13, 2012 (gmt 0)

Junior Member

joined:Nov 5, 2011
posts: 55
votes: 0


Can you show me that in a simple example ?
Sorry but my English is kinda limited :-/


you have separated the query value into two different locations


You mean:

if($stmt->prepare("SELECT `user_id`, `model`, `registration_date` FROM `my_database` WHERE `model` = ? OR `vendor` = ? ORDER BY registration_date DESC"))


and


if($stmt->prepare("SELECT `user_id`, `vendor`, `registration_date` FROM `my_database` WHERE `model` = ? OR `vendor` = ? ORDER BY registration_date DESC"))


where "model" and "vendor" are separated ?