Jump to content

search form to search two columns from mssql table


bickyz

Recommended Posts

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.

hgkv4.png

 

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';
?>
Link to comment
Share on other sites

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);
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.