Welcome to WebmasterWorld Guest from 23.21.38.201

Forum Moderators: coopster & jatar k

Simple PHP SEARCH engine

based on prepared statements

   
10:31 am on Jul 8, 2012 (gmt 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 Jul 9, 2012 (gmt 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 Jul 10, 2012 (gmt 0)



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

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



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 Jul 11, 2012 (gmt 0)

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



or use regex, but the overhead is higher. "Like" should be case-insensitive.
4:10 pm on Jul 11, 2012 (gmt 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 Jul 13, 2012 (gmt 0)

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



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 Jul 13, 2012 (gmt 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 ?
 

Featured Threads

Hot Threads This Week

Hot Threads This Month