homepage Welcome to WebmasterWorld Guest from 107.22.70.215
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Simple PHP SEARCH engine
based on prepared statements
bethesda




msg:4473624
 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.

 

bethesda




msg:4473767
 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.

bethesda




msg:4474328
 6:47 pm on Jul 10, 2012 (gmt 0)

Anyone ?

eelixduppy




msg:4474612
 2:04 pm on Jul 11, 2012 (gmt 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.
rocknbil




msg:4474652
 3:49 pm on Jul 11, 2012 (gmt 0)

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

bethesda




msg:4474669
 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?)

eelixduppy




msg:4475453
 6:18 pm on Jul 13, 2012 (gmt 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.'%';
....

bethesda




msg:4475484
 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 ?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved