Page 1 of 2
PHP search MySQL
Posted: Tue Jul 02, 2002 7:45 pm
by gmitra
I have a website filled with editorials and articles. My articles are all listed in my MySQL database by name, date, and category. I wanted to create a simple search box using php where people can type in the supposed name of the article they are looking for and get a listing of relevant article titles from my database. Problem, of course, is that I don't know how to do such a thing. I've seen more complicated searches where you index the entire site and so on, but all I want to do is take what the user typed, say "Impact of Cancer on the United States", and pull out any similar titles from my database. Does anyone know how to do this?
Re: PHP search MySQL
Posted: Tue Jul 02, 2002 9:43 pm
by Peter
gmitra wrote:I have a website filled with editorials and articles. My articles are all listed in my MySQL database by name, date, and category. I wanted to create a simple search box using php where people can type in the supposed name of the article they are looking for and get a listing of relevant article titles from my database. Problem, of course, is that I don't know how to do such a thing. I've seen more complicated searches where you index the entire site and so on, but all I want to do is take what the user typed, say "Impact of Cancer on the United States", and pull out any similar titles from my database. Does anyone know how to do this?
Let me introduce you to LIKE.
The SQL Query you're looking for is...
SELECT * FROM table WHERE name LIKE '%$search_keyword%';
% is LIKE wildcard. Either % or *.
Posted: Tue Jul 02, 2002 10:36 pm
by gmitra
Yes I know that. But I am unsure as to how to incorporate that into a form where someone can type a search word into a text box, hit submit and get the results.
Also just for reference this is a portion of my the PHP that I use just to display my article listings. Now I want to be able to search these listings and present them in the same layout and table structure.
Code: Select all
<?php include('../georgedb.php');?>
<center><table border="0" cellspacing="1">
<tr>
<td width="10"></td>
<td width="100"><b><font face="Verdana" size="2">Date</font></b></td>
<td width="315"><b>
<font face="Verdana" size="2">Title</font></b></td>
<td width="163"><b><font face="Verdana" size="2">Category</font></b></td>
</tr>
<?
$sqlquery = "SELECT * FROM articles ORDER BY date DESC";
$result = mysql_query($sqlquery);
$number = mysql_numrows($result);
$i = 0;
if ($number < 1) {
print "<CENTER><P>There Were No Results for Your Search</CENTER>";
}
else {
while ($number > $i) {
$date = mysql_result($result,$i,"date");
$name = mysql_result($result,$i,"name");
$category = mysql_result($result,$i,"category");
$link = mysql_result($result,$i,"link");
$catlink = mysql_result($result,$i,"catlink");
?>
<tr>
<td width="10"></td>
<td width="100"><font face="verdana" size="2"><?php print "$date";?></font></td>
<td width="315"><font face="verdana" size="2"><a href="<?php print "$link";?>" style="color:#000000"><?php print "$name";?></a></font></td>
<td width="163"><font face="verdana" size="2"><a href="<?php print "$catlink";?>" style="color:#000000"><?php print "$category";?></a></font></td>
</tr>
<?php
$i++;
}
}
?>
</table></center>
Any help on how to properly set up a simple search of the database and how to present it using the same layout would be much appreciated.
Posted: Wed Jul 03, 2002 3:07 am
by mikeq
Word of warning on LIKE '%something%'
having % at the start of your search criteria will produce a really slow query (dependent on number of records) as it cannot use an index and must scan every record in the table to find what you want.
Mike
Posted: Wed Jul 03, 2002 10:55 am
by gmitra
Alright, thanks to the people that gave me any sort of information thus far. I got the simple search working on my site. However now I wish to be able to sort my listings in specific orders.
I know how to use the "ORDER BY whatever" and that's fine for displaying everything because I can make a specific page to show everything in my table and order it however I want. But when someone searches for something and gets several results I want them to still be able to choose how they want to see it, once the search returns results.
Posted: Wed Jul 03, 2002 3:34 pm
by josa
If you have MySQL version 3.23.23 or later you can make use of the full-text indexing which makes full-text searches much more efficient than using LIKE in combination with wildcards. You have to alter your tables to include full-text index and use MATCH/AGAINST instead of LIKE. As a solution to your sorting problems (if I understand them correctly) I suggest building your queries dynamically, i.e. start of with a base query like:
and if the user selects a sorting option you simply add the "ORDER BY" part based on the users choice:
You can extend this to build as complex queries as you like. I use this approach a lot in my scripts. A final suggestion on fetching the data; I see that you are using
mysql_result() to fetch all rows in the result. Consider using one of the fetch functions instead since they are much faster:
Code: Select all
...
while($data = mysql_fetch_assoc($result)) {
?>
<tr>
<td width="10"></td>
<td width="100"><font face="verdana" size="2"><?=dataї'date']?></font></td>
<td width="315"><font face="verdana" size="2"><a href="<?=dataї'link']?>" style="color:#000000"><?=dataї'name']?></a></font></td>
<td width="163"><font face="verdana" size="2"><a href="<?=dataї'catlink']?>" style="color:#000000"><?=dataї'category']?></a></font></td>
</tr>
<?php
}
?>
</table></center>
The variables $date, $name and so on, are replaced by references to an associative array where the keys are assigned the same name as the table columns. I also replaced the
<?php print ...?> with short tags. This is just a matter of taste.
/josa
Posted: Wed Jul 03, 2002 3:54 pm
by EvilWalrus
While rewriting my Code Search on EvilWalrus.com today, newbienetwork pointed out the MATCH() and AGAINST() functions in mysql. They're awesome for searching results, but keep in mind, the searched fields must be fulltext enabled...
Posted: Wed Jul 03, 2002 5:42 pm
by gmitra
josa wrote:
Code: Select all
...
while($data = mysql_fetch_assoc($result)) {
?>
<tr>
<td width="10"></td>
<td width="100"><font face="verdana" size="2"><?=dataї'date']?></font></td>
<td width="315"><font face="verdana" size="2"><a href="<?=dataї'link']?>" style="color:#000000"><?=dataї'name']?></a></font></td>
<td width="163"><font face="verdana" size="2"><a href="<?=dataї'catlink']?>" style="color:#000000"><?=dataї'category']?></a></font></td>
</tr>
<?php
}
?>
</table></center>
The variables $date, $name and so on, are replaced by references to an associative array where the keys are assigned the same name as the table columns. I also replaced the
<?php print ...?> with short tags. This is just a matter of taste.
/josa
Thanks, your advice sounds really good, so I'm trying it out. Right now though I'm having trouble with the code you gave me. I replaced my old script with the piece you gave me, but I keep getting this error message....
"Parse error: parse error, expecting `','' or `';'' in h:\html\george\articles\includes\article_index2.php on line 27"
This is line 27
Code: Select all
<td width="100"><font face="verdana" size="2"><?=dataї'date']?></font></td>
So I added fiddled around adding quotes and even changing back to php print, but then when I do stuff like that my tables just read =data['date'], =data['link'] and so on without displaying the results in my database.
Also I am unsure as to what you mean about the sorting ORDER BY query. See what I have right now is a drop down menu where the user can select what order they want the listing to be in. However, I don't know how to make it automatic so that they can sort the results from a search.
Posted: Thu Jul 04, 2002 3:38 am
by josa
I'm sorry; sometimes I forget the dollar sign in front of the variable names. Most of my programming experience is with other languages that don't use this kind of variable declaration (like C++, Java, and so on). If you add a
$ in front of every
data it should run:
Code: Select all
<td width="100"><font face="verdana" size="2"><?=$dataї'date']?></font></td>
I'll get back to you shortly about the searching technique I was talking about.
/josa
Posted: Thu Jul 04, 2002 12:44 pm
by gmitra
Ahhh, works like a charm =) I look forward to your next response, thanks!
Posted: Thu Jul 04, 2002 2:31 pm
by josa
Here is an example, as promised, of how to build a query based on a user's choice. This time I've actually tested the script before I post it :-). The script is just an example of how it can be done. Btw I'm not sure if this is what you are looking for, but you may find it useful anyhow.
/josa
file: search_form.html
Code: Select all
<html>
<head><title>Search form</title></head>
<body>
<form action="search_result.php" method="post" name="search_form">
<table border="1" cellpadding="5">
<tr>
<td>search for</td>
<td><input name="search_text" type="text"></td>
<td align="center">in column</td>
<td>
<select name="search_column">
<option value="category">Category</option>
<option value="date">Date</option>
<option value="name">Title</option>
</select>
</td>
</tr>
<tr>
<td>order by </td>
<td>
<select name="order_by">
<option value="category">Category</option>
<option value="date">Date</option>
<option value="name">Title</option>
</select>
</td>
<td><input type="radio" name="sort_order" value="asc" checked>ascending</td>
<td><input type="radio" name="sort_order" value="desc">descending</td>
</tr>
<tr>
<td colspan="4" align="center">
<input type="reset" name="reset" value="Reset">
<input type="submit" name="submit" value="Submit">
</td>
</tr>
</table>
</form>
</body>
</html>
file: search_result.php
Code: Select all
<?php
include('../georgedb.php');
//Make a list of column names that are legal. This list is used
//to check if a column is valid. It's a good practice not to trust
//variables passed from a form and this approach makes it harder
//to forge post variables in order to run for example a malicious
//SQL command.
$legal_columns = array('category' => 1,
'date' => 1,
'name' => 1);
//This is the base query we are building on.
$query = "SELECT * FROM articles";
//-------------------- 1. Add WHERE clause
//We check against the list of legal columns to make sure nobody
//tampered with the form data.
if($legal_columnsї"{$_POSTї'search_column']}"] == 1) {
$query .= " WHERE {$_POSTї'search_column']} LIKE";
}
//-------------------- 2. Add search text ('%text%')
//Note the use of mysql_escape_string(). If your server has magic
//quotes enabled you should change this to just add the variable
//without escaping it. Use phpinfo() and look for magic_quotes_gpc.
$query .= " '%" . mysql_escape_string($_POSTї'search_text']) . "%'";
//-------------------- 3. Add ORDER BY clause
if($legal_columnsї"{$_POSTї'order_by']}"] == 1) {
$query .= " ORDER BY {$_POSTї'order_by']}";
}
//-------------------- 4. Add ASC/DESC
//Choose ascending or descending sort order. I use a ternary operator
//which can look a bit strange if you're not used to them.
$query .= $sort_order == "asc" ? " ASC" : " DESC";
$result = mysql_query($query);
?>
<html>
<head><title>Search result</title></head>
<body>
<center>
<table border="0" cellspacing="1">
<tr>
<td width="10"></td>
<td width="100"><b><font face="Verdana" size="2">Date</font></b></td>
<td width="315"><b><font face="Verdana" size="2">Title</font></b></td>
<td width="163"><b><font face="Verdana" size="2">Category</font></b></td>
</tr>
<?php
while($data = mysql_fetch_assoc($result)) {
?>
<tr>
<td width="10"></td>
<td width="100"><font face="verdana" size="2"><?=$dataї'date']?></font></td>
<td width="315"><font face="verdana" size="2"><a href="<?=$dataї'link']?>" style="color:#000000"><?=$dataї'name']?></a></font></td>
<td width="163"><font face="verdana" size="2"><a href="<?=$dataї'catlink']?>" style="color:#000000"><?=$dataї'category']?></a></font></td>
</tr>
<?php
}
?>
</table>
</center>
</body>
</html>
Posted: Thu Jul 04, 2002 4:45 pm
by gmitra
Thanks alot Josa. You gave me a lot more than what I was expecting and hoping for, though it's not exactly what I was looking for. But I could definitely incorporate that into my layout -- the script works fine =)
What I was trying to learn was more along the lines of sorting after the search. For instance my main article page consists of...
Line 1 --> Sort By (different options here) and Search For (text box here)
Second Part --> Current list of all articles in the database ordered by date DESC.
I know how to list the stuff using ORDER BY, but that's only if I have a specific thing already planned out. What I was wondering was what happens if someone uses the search box and gets several listings. After the search returns the results could they use the sort by to order the results? Because at the moment the sort by links to different pages which call for everything in the database listed in different orders. Is there a way to sort what's already there, not predefined or sorted before the search.
Perhaps it something simple that I just haven't seen, like when search and are forwarded to the search result page, the variable is "$search_name". Maybe getting a variable that stands for all the results that could be used to sort what is there. It's hard to explain so I'm not sure if I'm being understood or just confusing everyone. Either way though, I appreciate all the help given so far.
Posted: Fri Jul 05, 2002 7:23 am
by mikeq
One way I have done this in the past is to have a link above the column
which calls the same page passing a column name as a variable. The page wuold then need to re-execute the query with the column passed in the ORDER BY clause.
Posted: Fri Jul 05, 2002 8:02 pm
by gmitra
mikeq, I'm not sure I understand how you did it, but what I am asking, if possible, is something like this.
SEARCH PAGE
Code: Select all
<form method=post action="results2.php">
Search For:
<p>
Title: <input type="text" name="title" size="25" maxlength="25">
<p>
<input type=submit>
</form>
RESULT PAGE
Code: Select all
<?
$sqlquery = "SELECT * FROM articles WHERE name LIKE '%$title%'";
$result = mysql_query($sqlquery);
$number = mysql_numrows($result);
....
ORDER PAGE
Code: Select all
<?
$sqlquery = "SELECT * FROM articles WHERE name LIKE '%$title%' ORDER BY name desc";
$result = mysql_query($sqlquery);
$number = mysql_numrows($result);
....
Of course the $title doesn't last into the order page and therefore when the order page is called up everything in the articles table is listed in the specified order instead of just the original query results. Is there a specific way sort the results without having to go around it in the ways talked about in this thread thus far?
Posted: Mon Jul 08, 2002 3:57 am
by mikeq
Hi,
What I meant was, say each column that you output to the page has a title, this title would be a contstructed as a hyperlink something like
<a href="
http://www.myserver.com/orderpage.php?o ... ">Title</a>
so when it is clicked the page calls itself passing the variable $order which can then be used in your query
$sqlquery = "SELECT * FROM articles WHERE name LIKE '%$title%' ORDER BY $order desc";
By the way, the result page and order page can be the same page, you could maybe define a default order for when the page is first called.
Code: Select all
if (!$order){
$order = 'name';
}
$sqlquery = "SELECT * FROM articles WHERE name LIKE '%$title%' ORDER BY $order desc";
$result = mysql_query($sqlquery);
$number = mysql_numrows($result);
...
Hope this is clearer.
Mike