Page 2 of 2

Posted: Mon Jul 08, 2002 6:00 am
by josa
Okay, I've made some changes to the search_result.php script that I think you will find useful. I've added an option at the bottom to re-order the fetched data. I accomplish this by copying part of the form from search_form.php and adding two hidden variables containing the search column and text. The form is posted to the same page and the query is rebuilt and sent to the database. I've also fixed a couple of minor bugs. Try it out!

/josa

search_result.php

Code: Select all

<?php
    
    include('../georgedb.php');
    
    //Make a list of column names that are 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.
    $valid_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($valid_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%')
    //This small hack checks if magic quotes are enabled and escapes
    //the string based on that. If magic quotes are enabled all form
    //data is automatically escaped. By doing this check we avoid
    //escaping the string twice.
    $query .= " '%" . ((get_magic_quotes_gpc() == 1) ? 
        $_POST&#1111;'search_text'] : addslashes($_POST&#1111;'search_text'])) . "%'";
    
    //-------------------- 3. Add ORDER BY clause
    if($valid_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 .= $_POST&#1111;'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
    if(mysql_num_rows($result) == 0) &#123;
?>
        <tr>
            <td>&nbsp;</td>
            <td colspan="3"><font face="verdana" size="2">No articles found...</font></td>
        </tr>
    </table>
<?php
    &#125; else &#123;
        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>
    <form action="search_result.php" method="post" name="sort_form">
        <table border="0" cellspacing="1">
            <tr>
                <td><font face="verdana" size="2">order by&nbsp;</font></td>
                <td>
                    <font face="verdana" size="2">
                        <select name="order_by">
                            <option value="category">Category</option>
                            <option value="date">Date</option>
                            <option value="name">Title</option>
                        </select>
                    </font>
                </td>
                <td><font face="verdana" size="2"><input type="radio" name="sort_order" value="asc" checked>ascending</font></td>
                <td><font face="verdana" size="2"><input type="radio" name="sort_order" value="desc">descending</font></td>
                <td><font face="verdana" size="2"><input type="submit" name="submit" value="Sort"></font></td>
            </tr>
        </table>
        <input type="hidden" name="search_column" value="<?=$_POST&#1111;'search_column']?>">
        <input type="hidden" name="search_text" value="<?=$_POST&#1111;'search_text']?>">
    </form>
<?php
    &#125;
?>
</center>
</body>
</html>

Posted: Thu Jul 25, 2002 6:38 pm
by gmitra
I haven't checked by in awhile because I was visiting some friends for the past couple of weeks. But I've taken a look at what you both have written and tried to encorporate both into my layout. Here is the included order page on my site that contains the search information.

Code: Select all

<p align="center"><font face="Verdana" size="2">
        <img src="/george/images/articles.jpg" align="left"></font> <br>
        <br>
        <br>
        <br>
        <br>

<table border="0" width="100%">

  <tr>
	<td align="right" width="200">

<!--begin order-->

<form name="order">

<font face="verdana" size="2">Sort By </font>

	<select size="1" name="category" style="font-family: Verdana; font-size: 8pt; border-style: solid; border-width: 1; padding-left: 4; padding-right: 4; padding-top: 1; padding-bottom: 1" onChange="location=document.order.category.options&#1111;document.order.category.selectedIndex].value;" value="GO">

	<option>---------------</option>
        <option value="?order=date&how=desc">Date (DESC)</option>
	<option value="?order=date&how=asc">Date (ASC)</option>
        <option value="?order=name&how=asc">Title (ASC)</option>
	<option value="?order=name&how=desc">Title (DESC)</option>
        <option value="?order=category&how=asc">Category (ASC)</option>
	<option value="?order=category&how=desc">Category (DESC)</option>

&#1111;b]<input type="hidden" name="search" value="<?=$_POST&#1111;'search']?>">&#1111;/b]

        </select>

</form>

<!--end order-->

	</td>
	<td align="left" width="408">

<!--begin search-->

<FORM METHOD="post" ACTION="http://localhost/george/articles/index.php">

<font size="2" face="Verdana">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
                <input type="submit" value="Title Search" name="Title Search" style="font-size: 7pt; font-family: Verdana; border-style: solid; border-width: 1">&nbsp; </font>

   
<input type="text" name="search" size="30" style="font-family: Verdana; font-size: 8pt; border-style: solid; border-width: 1">



</form>

<!--end search-->

	</td>
  </tr>
</table>
Now the order drop down box works, however once again when I make a search it does not reorder the results, but instead calls up everything in the table. From what Josa wrote I bolded the part in which I try to throw back the original query, but it's not working. Any suggestions?

P.S. I eliminated the results page like mikeq suggested so the search just refreshes the page with the query results.