Simple PHP SEARCH engine

based on prepared statements

[bethesda]

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.

[bethesda]

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.

[bethesda]

Anyone ?

[eelixduppy]

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.

[rocknbil]

or use regex, but the overhead is higher. "Like" should be case-insensitive.

[bethesda]

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?)

[eelixduppy]

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.'%';
....

[bethesda]

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 ?