If you have a MySQL database containing records like a songs information database, then you might probably not be able to add a good search page to your website which will search records in the database. I faced the same problem, but now I'm able to create one sophisticated database search. I would like to share my knowledge with you. But it's not possible for me to start with a certain point here. I would like you people (interested in creating a search page) to post your problems here & I'll definitely help you.
You can see the search which I created on my past website as a sample of what I'm talking about. Click here to visit that. One thing I used to look for was a search script which could also bold the search terms in the search results, but couldn't find that. Now you can see on my past website that it also bolds the search terms in the search results.
As a demo, put the keyword The in the search box & you'll find out how cool search results page you'll experience there.
I would welcome any queries & suggestions regarding this discussion.
I think nobody's getting time to initiate. Ok then! I start it my self.
Well! of course the search will be implemented on an existing database, so the first thing you need is a database. It may contain any sort of data, but lemme make it a music database.
You must be familiar with MySQL & its implementation to understand what I am going to say below. And if you aren't, then it's better to jump to any other topic. (You may ask any question as well...)
Designing database is not any task. It would become easy if you mess up all the data in a single table, but of course, it would heavily increase the size of your database. Try to divide your data in different tables for optimum performance. For instance, if you are creating a database which would be used for displaying songs available to be downloaded, you need at least 5 tables in my point of view; artists, album, genre, songs, links.
The tables must be related to each other by ID keys, like albums table would contain a column of artistID whose value will be the value in the ID column of artists table, which will show that a particular album belongs to a particular artist.. And same applies to other tables likewise.
Uptil this point, it was an overview of how the database should be sorted out. Now the records should be created. In the next reply to this post, I'll post a sample code to let you understand how the things should go.
Below is a sample MySQL querying performed in PHP to a pre-defined MySQL database. Just look at the code below to get an idea for fetching the results independently from the tables.
CODE
[font=Courier] // SQL Query
// ***Songs***
// Any keyword Songs (Songs) using $originquery
mysql_select_db($database_connMembers, $connMembers);
$query_rsSongs = sprintf("SELECT * FROM songs WHERE title LIKE '%%%s%%' ORDER BY title", $titlevalue);
$rsSongs = mysql_query($query_rsSongs, $connMembers) or die(mysql_error());
$row_rsSongs = mysql_fetch_assoc($rsSongs);
$totalRows_rsSongs = mysql_num_rows($rsSongs);
// ***Artists***
// Any keyword Artists (Artists) using $originquery
mysql_select_db($database_connMembers, $connMembers);
$query_rsArtists = sprintf("SELECT * FROM artists WHERE name LIKE '%%%s%%' ORDER BY name", $titlevalue);
$rsArtists = mysql_query($query_rsArtists, $connMembers) or die(mysql_error());
$row_rsArtists = mysql_fetch_assoc($rsArtists);
$totalRows_rsArtists = mysql_num_rows($rsArtists);
// ***Albums***
// Any keyword Albums (Albums) using $originquery
mysql_select_db($database_connMembers, $connMembers);
$query_rsAlbums = sprintf("SELECT * FROM albums WHERE title LIKE '%%%s%%' ORDER BY title", $titlevalue);
$rsAlbums = mysql_query($query_rsAlbums, $connMembers) or die(mysql_error());
$row_rsAlbums = mysql_fetch_assoc($rsAlbums);
$totalRows_rsAlbums = mysql_num_rows($rsAlbums);
$totalResults = $totalRows_rsSongs + $totalRows_rsArtists + $totalRows_rsAlbums;
$m = 0;
?>
[/font]
// ***Songs***
// Any keyword Songs (Songs) using $originquery
mysql_select_db($database_connMembers, $connMembers);
$query_rsSongs = sprintf("SELECT * FROM songs WHERE title LIKE '%%%s%%' ORDER BY title", $titlevalue);
$rsSongs = mysql_query($query_rsSongs, $connMembers) or die(mysql_error());
$row_rsSongs = mysql_fetch_assoc($rsSongs);
$totalRows_rsSongs = mysql_num_rows($rsSongs);
// ***Artists***
// Any keyword Artists (Artists) using $originquery
mysql_select_db($database_connMembers, $connMembers);
$query_rsArtists = sprintf("SELECT * FROM artists WHERE name LIKE '%%%s%%' ORDER BY name", $titlevalue);
$rsArtists = mysql_query($query_rsArtists, $connMembers) or die(mysql_error());
$row_rsArtists = mysql_fetch_assoc($rsArtists);
$totalRows_rsArtists = mysql_num_rows($rsArtists);
// ***Albums***
// Any keyword Albums (Albums) using $originquery
mysql_select_db($database_connMembers, $connMembers);
$query_rsAlbums = sprintf("SELECT * FROM albums WHERE title LIKE '%%%s%%' ORDER BY title", $titlevalue);
$rsAlbums = mysql_query($query_rsAlbums, $connMembers) or die(mysql_error());
$row_rsAlbums = mysql_fetch_assoc($rsAlbums);
$totalRows_rsAlbums = mysql_num_rows($rsAlbums);
$totalResults = $totalRows_rsSongs + $totalRows_rsArtists + $totalRows_rsAlbums;
$m = 0;
?>
[/font]
Now, the art goes with you, i mean how you place your code in your page to look good is totally upto you.
I've left almost every thing after that code in this discussion which would have to be used to display the search results, because it depends on how you want it to look like.
Finally below is the code to BOLD the search keywords in the search results. You have to somehow integrate this code in to your page, so that each result is processed through this code.
If you are comfortable with PHP, you will surely understand the simple logic which I myself created using a bit of built-in PHP functions.
The if-else statements ensures that all the matches in the record whether they be in capital letters or small letter must be processed for Bold.
The variable $keywords below in the code is for that search terms which user entered in the search box. The rest will be explained by the code itself.
CODE
[font=Courier]<?php // Bold
$cnt = substr_count($keywords, " "); // $cnt is the number of words in the query
$val = explode(" ", $keywords); // $val is an array to store all the words separately
for($j=0; $j<=$cnt; $j++) {
if(substr_count($resultvalue, $val[$j]) > 0) {
$search[$j] = $val[$j];
$replace[$j] = "<b>".$val[$j]."</b>";
} else
if(substr_count($resultvalue, ucfirst($val[$j])) > 0) {
$search[$j] = ucfirst($val[$j]);
$replace[$j] = "<b>". ucfirst($val[$j]) ."</b>";
} else
if(substr_count($resultvalue, strtoupper($val[$j])) > 0) {
$search[$j] = strtoupper($val[$j]);
$replace[$j] = "<b>". strtoupper($val[$j]) ."</b>";
} else
if(substr_count($resultvalue, strtolower($val[$j])) > 0) {
$search[$j] = strtolower($val[$j]);
$replace[$j] = "<b>". strtolower($val[$j]) ."</b>";
}
}
$search = implode(" ", $search);
$replace = implode(" ", $replace);
str_replace($search, $replace, $subject);
// $search is the text to be replaced
// $replace is the text which replaces the $search text.
// $subject is the text which contains $search text.
}
?>[/font]
$cnt = substr_count($keywords, " "); // $cnt is the number of words in the query
$val = explode(" ", $keywords); // $val is an array to store all the words separately
for($j=0; $j<=$cnt; $j++) {
if(substr_count($resultvalue, $val[$j]) > 0) {
$search[$j] = $val[$j];
$replace[$j] = "<b>".$val[$j]."</b>";
} else
if(substr_count($resultvalue, ucfirst($val[$j])) > 0) {
$search[$j] = ucfirst($val[$j]);
$replace[$j] = "<b>". ucfirst($val[$j]) ."</b>";
} else
if(substr_count($resultvalue, strtoupper($val[$j])) > 0) {
$search[$j] = strtoupper($val[$j]);
$replace[$j] = "<b>". strtoupper($val[$j]) ."</b>";
} else
if(substr_count($resultvalue, strtolower($val[$j])) > 0) {
$search[$j] = strtolower($val[$j]);
$replace[$j] = "<b>". strtolower($val[$j]) ."</b>";
}
}
$search = implode(" ", $search);
$replace = implode(" ", $replace);
str_replace($search, $replace, $subject);
// $search is the text to be replaced
// $replace is the text which replaces the $search text.
// $subject is the text which contains $search text.
}
?>[/font]
I hope it helped you a lot. But if you feel uncomfortable with any statement, feel free to post your problem. I'll try my best to answer you. That's it for this reply to my own topic.
Well, you should keep this thing in mind that your user is going to enter any value in the search terms & your code should be able to process or handle all such requests. For example when you are querying the MySQL server, and if you use the values in the MySQL command from a variable, so you must ensure that the variable doesn't contain any apostrophies in it, else the result could be very unexpected & the next results page will seriously show off that you're a poor programmer.
Then your search form should not make a user get in to trouble, like there's a lot of options like, search in artists or search in albums or search in song titles or like that.. Rather if the user enter any keywords, your code should automatically search for all the possible matches in artists tables & in albums table & in songs tables & then display the results separably without effecting the look of your page.
Another best method by which your website visitor will be able to save time is that if he/she founds some pre-defined searches in a corner, and if the keywords are the same he/she was going to enter in the search box, then a direct click to that keywords link will surely solve the problem. Keep track of the entered keywords by adding some code to your page & then display them on the main page of your website as a separate source.
Now, the point raises which form method to be used. Of course, you must use GET method, because this is how you'll be able to provide the direct search links for the most used keywords.
And, I think, all these things would perfectly make up a complex & effective database search system. I used songs information database as an example here, but the tips n tricks may be followed for any type of database. Hope, you get my point.
