search form to search two columns from mssql table

[bickyz]

Hi we have a inhouse Win32 application that saves data to MSSQL. I am trying to create a php search form that will search a table which has following data.

 

my search form

<form name="myform" method="post" action="searchresults.php" id="myform">
<input type="text" name="search" size=30>
<Select NAME="field">
<Option VALUE="contents">Contents</option>
<Option VALUE="item_id">Item Number</option>
</Select>
<input name="Search" type="Submit" id="Search" value="Search">
</form>

my search results page

<?php 
include 'includes/opendb.php';

$search = $_POST['search'];
$field = $_POST['field'];
if ($search == ""){ 
    echo "<p>You forgot to enter a search term"; 
    exit; 
}

$result = mssql_query("SELECT * FROM dbo.userAnswers WHERE {$field} LIKE '%{$search}%'"); 

echo '<ul class="SearchResults">';
while($r = mssql_fetch_array($result)){
    $item_id = $r['item_id'];
    $contents = $r['contents'];
    echo <<<LIST
        <li>
            <ul class="Result">
				<li>{$item_id}</li>
				<li>{$contents}</li>
            </ul>
        </li>
LIST;
}
echo '</ul>';
?>
<?php
include 'includes/closedb.php'; 
?>

I now have changed my search form to be like this. When someone select Firstname from dropdown list I want the search to be done for all the 101 item id and its contents.

<form name="myform" method="post" action="searchresults.php" id="myform">
<input type="text" name="search" size=30>
<Select NAME="field">
<Option VALUE="101">Firstname</option>
<Option VALUE="102">Start Date</option>
<Option VALUE="103">Introduction</option>
<Option VALUE="104">Question 1</option>
<Option VALUE="105">Question 2</option>
</Select>
<input name="Search" type="Submit" id="Search" value="Search">
</form>

What changes I will be looking to do in my search results page script. I have tried changing few bits like this but nothing is displayed in the search results page.

 

Your help will be much appreciated thank you.

<?php
include 'includes/opendb.php';

$search = $_POST['search'];
$field = $_POST['field'];
if ($search == ""){
    echo "<p>You forgot to enter a search term";
    exit;
}

$result = mssql_query("SELECT * FROM dbo.userAnswers
WHERE item_id = $field
AND contents LIKE '%{$search}%'
");


echo '<ul class="SearchResults">';
while($r = mssql_fetch_array($result)){
    $item_id = $r['item_id'];
    $contents = $r['contents'];
    echo <<<LIST
        <li>
            <ul class="Result">
                <li>{$item_id}</li>
                <li>{$contents}</li>
            </ul>
        </li>
LIST;
}
echo '</ul>';
?>
<?php
include 'includes/closedb.php';
?>

[lemmin]

The code looks like it should work. Are you getting an error message? You should at least get an undefined index notice before submitting the form. Try showing errors if they are turned off:

ini_set('display_errors', '1');
error_reporting(E_ALL);

[Psycho]

You don't have any error handling for the query - it is probably failing. My best guess, on the limited information provided, is that the item_id field may not be a numeric type (e.g. it may be a text type). Using the query

 

$result = mssql_query("SELECT * FROM dbo.userAnswers
WHERE item_id = $field
AND contents LIKE '%{$search}%'
");

 

would fail because the value for item_id would need to be in quotes. But, you should definitely be sanitizing any data from the user before using in a query (or use a method that handles it natively). I always suggest creating your queries as a string variable so you can echo it to the page for debugging purposes. In this case, you could do that and then run the query directly in the DB manager to see if that is the problem.