PHP search MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
gmitra
Forum Newbie
Posts: 15
Joined: Tue Jul 02, 2002 7:45 pm
Location: New York

PHP search MySQL

Post 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?
Peter
Forum Commoner
Posts: 28
Joined: Mon Jun 10, 2002 12:40 am
Location: Brisbane, Australia

Re: PHP search MySQL

Post 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 *.
User avatar
gmitra
Forum Newbie
Posts: 15
Joined: Tue Jul 02, 2002 7:45 pm
Location: New York

Post 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) &#123;
print "<CENTER><P>There Were No Results for Your Search</CENTER>";
&#125;
else &#123;
while ($number > $i) &#123;
$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++;
&#125;
&#125;
?>
          </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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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
User avatar
gmitra
Forum Newbie
Posts: 15
Joined: Tue Jul 02, 2002 7:45 pm
Location: New York

Post 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.
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post 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:

Code: Select all

$query = "SELECT * FROM articles"
and if the user selects a sorting option you simply add the "ORDER BY" part based on the users choice:

Code: Select all

$query .= " ORDER BY date"
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)) &#123;
?>
    <tr>
        <td width="10"></td> 
        <td width="100"><font face="verdana" size="2"><?=data&#1111;'date']?></font></td> 
        <td width="315"><font face="verdana" size="2"><a href="<?=data&#1111;'link']?>" style="color:#000000"><?=data&#1111;'name']?></a></font></td> 
        <td width="163"><font face="verdana" size="2"><a href="<?=data&#1111;'catlink']?>" style="color:#000000"><?=data&#1111;'category']?></a></font></td> 
    </tr> 
<?php 
&#125;
?> 
</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
User avatar
EvilWalrus
Site Admin
Posts: 209
Joined: Thu Apr 18, 2002 3:21 pm
Location: Springmont, PA USA

Post 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...
User avatar
gmitra
Forum Newbie
Posts: 15
Joined: Tue Jul 02, 2002 7:45 pm
Location: New York

Post by gmitra »

josa wrote:

Code: Select all

...
while($data = mysql_fetch_assoc($result)) &#123;
?>
    <tr>
        <td width="10"></td> 
        <td width="100"><font face="verdana" size="2"><?=data&#1111;'date']?></font></td> 
        <td width="315"><font face="verdana" size="2"><a href="<?=data&#1111;'link']?>" style="color:#000000"><?=data&#1111;'name']?></a></font></td> 
        <td width="163"><font face="verdana" size="2"><a href="<?=data&#1111;'catlink']?>" style="color:#000000"><?=data&#1111;'category']?></a></font></td> 
    </tr> 
<?php 
&#125;
?> 
</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&#1111;'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.
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post 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&#1111;'date']?></font></td>
I'll get back to you shortly about the searching technique I was talking about.

/josa
User avatar
gmitra
Forum Newbie
Posts: 15
Joined: Tue Jul 02, 2002 7:45 pm
Location: New York

Post by gmitra »

Ahhh, works like a charm =) I look forward to your next response, thanks!
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post 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&nbsp;</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">
            &nbsp;&nbsp;&nbsp;
            <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&#1111;"&#123;$_POST&#1111;'search_column']&#125;"] == 1) &#123;
        $query .= " WHERE &#123;$_POST&#1111;'search_column']&#125; LIKE";
                  
    &#125;
    
    //-------------------- 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&#1111;'search_text']) . "%'";
    
    //-------------------- 3. Add ORDER BY clause
    if($legal_columns&#1111;"&#123;$_POST&#1111;'order_by']&#125;"] == 1) &#123;
        $query .= " ORDER BY &#123;$_POST&#1111;'order_by']&#125;";
    &#125;
    
    //-------------------- 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)) &#123;
?>
        <tr> 
            <td width="10"></td> 
            <td width="100"><font face="verdana" size="2"><?=$data&#1111;'date']?></font></td> 
            <td width="315"><font face="verdana" size="2"><a href="<?=$data&#1111;'link']?>" style="color:#000000"><?=$data&#1111;'name']?></a></font></td> 
            <td width="163"><font face="verdana" size="2"><a href="<?=$data&#1111;'catlink']?>" style="color:#000000"><?=$data&#1111;'category']?></a></font></td> 
        </tr> 
<?php
    &#125;
?>
    </table>
</center>
</body>
</html>
User avatar
gmitra
Forum Newbie
Posts: 15
Joined: Tue Jul 02, 2002 7:45 pm
Location: New York

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
User avatar
gmitra
Forum Newbie
Posts: 15
Joined: Tue Jul 02, 2002 7:45 pm
Location: New York

Post 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?
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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)&#123;
  $order = 'name';
&#125;

$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
Post Reply