Database Searching Techniques with SQL
Wall Script
Wall Script
Thursday, July 15, 2010

Database Searching Techniques with SQL

Search box most important element on web pages specially contented management sites. In this post I want to explain very basic searching techniques and Unicode data searching using SQL LIKE statement. I hope you like this. Thanks

Search box techniques

Live Demo

Replace spaces
Before executing SQL query searching word replace spaces with %(symbol) using PHP str_replace function.

Here searching word jquery ajax spaces replaced with %(symbol)- jquery%ajax
SQL Code
SELECT * FROM messages WHERE msg LIKE '%jquery%ajax%' ;

Results
Search results

Sample Database
Contains two columns id and title.
CREATE TABLE `articles` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255),
PRIMARY KEY (`id`)
)

Search.php
PHP and HTML code. Seaching data from the articles table.
<?php
include("config.php");
if($_SERVER["REQUEST_METHOD"] == "POST")
{
$q=$_POST['q'];
$q=mysql_escape_string($q);
$q_fix=str_replace(" ","%",$q); // Space replacing with %
$sql=mysql_query("SELECT title FROM articles WHERE title LIKE N'%$q_fix%'");
}
?>
<html>
<body>
<form method="post" action="">
<input type="text" name="q" /> <input type="submit" value=" Search " />
</form>
<?php
while($row=mysql_fetch_array($sql))
{
$title=$row['title'];
echo '<div>'.$title.'</div>';
}
?>
</body>
</html>

How to search unicode or UTF-8 data.
If data stored in Unicode format. Take a look at following image.
Unicode Date

SQL ' LIKE N '
SELECT * FROM articles WHERE title LIKE N'%చిరంజీవి%' ;

Why prefix 'N'?
Unicode - The N stands for National language character set. Which means that you are passing an NCHAR, NVARCHAR or NTEXT value Read more.

Unicode Table Structure
CREATE TABLE `articles` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) character set utf8 collate utf8_general_ci default NULL,
PRIMARY KEY (`id`)
)

config.php
PHP database configuration file
<?php

$mysql_hostname = "Host name";
$mysql_user = "UserName";
$mysql_password = "Password";
$mysql_database = "Database Name";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die("Could not connect database");
mysql_select_db($mysql_database, $bd) or die("Could not select database");
?>

Postgre SQL - Relational Database Management System Professional - Version 7.2
web notification

36 comments:

  1. hi, its nice tutorial i have another one tutorial on this topic (www.99Points.info) which is really very awesome just try it

    ReplyDelete
  2. PL make sure that Demo link is correct

    ReplyDelete
  3. Kumar, demo is correct, and i've tested, everything was fine :D

    thanks for admin :)

    ReplyDelete
  4. Hey good post, but you forget to remove b tag from search results while loading.. for e.g. if load results with facebook , than because of b in url it's giving wrong results.

    ReplyDelete
  5. Sweet. Thanks dude!

    ReplyDelete
  6. Thank you

    Good topic

    ReplyDelete
  7. Very nice POST.

    Thanks a lot.

    ReplyDelete
  8. Please use codeigniter for your all jquery tutorial.
    i want to know jquery with codeigniter.

    waiting for that..thanksssssssssss

    ReplyDelete
  9. I got something for you all to try!!

    In the demo, search for the following, exactly as written:

    '; DELETE FROM articles; --

    OMG WHAT JUST HAPPENED?! It's called SQL injection. You should not use this code, it's very dangerous.

    ReplyDelete
  10. @hopeseekr

    Thank of tips but here mysql_escape_string() not allow this.
    $q="'; DELETE FROM articles; -- ";
    $q=mysql_escape_string($q);
    $q_fix=str_replace(" ","%",$q);

    $q_fix="\';%DELETE%FROM%articles;%--%";

    ReplyDelete
  11. how is if i wanna download the ajaxed version ?

    ReplyDelete
  12. Good tut.I think it's simple and quite helpful!!Nice work!! :)

    ReplyDelete
  13. Using "like" is one of the most horrible ways to "search", and will kill your performance on anything significant. Most DBs have a specific text searching/full text searching mechanism making better use of specific indexes, etc.

    MySql : match (col_1, col_2 ) against ( {text} )

    Oracle: http://www.oracle.com/technology/products/text/index.html

    ReplyDelete
  14. thank's for this post, is very important have a style for search botton and textbox

    ReplyDelete
  15. This code doesn't work ;). and there's no security, none even "htmlentities" or "mysql_escape_real_string"

    ReplyDelete
  16. thx to u for sharing us these resources!

    ReplyDelete
  17. very very simple .. thanx

    ReplyDelete
  18. how to search and print out in 1 row include the table format.

    ReplyDelete
  19. its just only an example no question for sql injection or something else...

    btw, thanks for this is could really help!


    ~aleks

    ReplyDelete
  20. i found this blog so interesting. keep it up...jquery mouse hover effects on image. try it here: http://goo.gl/ByWQ

    ReplyDelete
  21. haii good topic its very useful for my website thanks a lot

    ReplyDelete
  22. hey wheres the code? how can i download this..??

    please send me in my account. [email protected] tnx tnx =)

    ReplyDelete
  23. can some one give a example in mysql how to add an article?

    ReplyDelete
  24. hey really nice article.
    but tell me is there any way to search inside another website database.
    just like you are doing in yahoo, bing, google, youtube instant search
    i mean i want to make a local search enigne ( just dial ). so is there any possible way to search inside the data of just dial with the help of ajax, php.
    please review my request and try to make a local search engine instant search .

    thanks in advance

    ReplyDelete
  25. hey really..... is so good for program php

    ReplyDelete
  26. it doesn't found string with when 'ajax' occure befor 'jquery'

    it is slow, mysql does't use index on LIKE with % at start ('%LIKE') . use derrick solution^^.

    ReplyDelete
  27. $q=$_POST['q'];
    $q=mysql_escape_string($q);
    $q_fix=str_replace(" ","%",$q); // Space replacing with %
    $sql=mysql_query("SELECT title FROM articles WHERE title LIKE N'%$q_fix%'");


    I am using the same code as you posted.
    but the problem with your code is that it does not search if searching input is giving in reverse order. for example if I have something like this in DB 'php, mysql, ajax, wordpress'. If I search for ajax, php then your code will not return any results.
    can you suggest how to solve it.?

    ReplyDelete
  28. Do you really know how helpful your site has been to some of us?
    Would you please put an autocomplete with jquery, Ajax, PHP and MySQL please?

    ReplyDelete
  29. this is nice but it has no empty data error or no result not found error message


    thanks

    ReplyDelete
  30. plz provide testing related topics

    ReplyDelete
  31. I've been searching for 5 days now for the simplest search box with no luck until I found this...Thank you so so so much! I can finally proceed with my uni project..

    ReplyDelete
  32. i have proble two text box (ex text1 city search text2 name search ) in code for php plz fast responce

    ReplyDelete

mailxengine Youtueb channel
Make in India
X