PDA

View Full Version : Search mysql problems

pha3dr0n
02-18-2004, 07:00 PM
I'm still having problems trying to get a search script working the way I want it. Basically, I have a form where users enter the search type, enter the text to be searched for, and this in turn is passed to the php script, which should return the results, but also have hyperlinks at the top of selected fields, whereby if you click on them it will re-order the results (say alphabetical order of surname).

At the moment, I am able to do a search with no problems, but cannot work out how to allow for wildcards in the search text (say, entering "A%" in the type "Surname" will return all results where the surname begins with A). Nor can I get the re-order links to work - if I click on them I get a "No results".

I'm fairly new to php, so am really struggling at the moment. Heres the form (searchdb.htm) and the php script (test.php):

Form.htm
<html>
<head>
<title>Database search</title>
</head>

<body>

<h1><font face="Arial, Helvetica, sans-serif" color="#333333">Database Search</font></h1>

<form action="test.php" method="post">
<font face="Arial, Helvetica, sans-serif" color="#333333" size="2">Choose
Search Type:</font><br>
<select name="searchtype">
<option value="forename">Forename
<option value="surname">Surname
<option value="category">Category
<option value="title">Title
<option value="shows">Shows
<select>
<br>
<font face="Arial, Helvetica, sans-serif" size="2" color="#333333">Enter
Search Term:</font><br>
<input name="searchterm" type=text>
<br>
<input type=submit value="Search">
</form>

</body>
</html>


Test.php
<?php
$default_sort = 'surname';
$allowed_order = array ('sex', 'surname','forename');

if($_GET['order']) $order = $_GET['order'];
else $order = $default_sort;

mysql_connect ('xxxx','xxxx','xxxx');
mysql_select_db ('contacts');

$column = $_POST['searchtype'];
$term = $_POST['searchterm'];

$query = "SELECT * FROM admin_contacts WHERE $column LIKE '%$term%' ORDER BY $order";
$result = mysql_query ($query);

$numrows = mysql_num_rows($result);
if ($numrows == 0) {
echo "No data to display!";
exit;
}

$row = mysql_fetch_assoc ($result);
echo "<TABLE border=1>\n";
echo "<TR>\n";
foreach ($row as $heading=>$column) {
echo "<TD><b>";
if (in_array ($heading, $allowed_order)) {
echo "<a href=\"{$_SERVER['PHP_SELF']}?order=$heading\">$heading</a>";
} else {
echo $heading;
}
echo "</b></TD>\n";
}
echo "</TR>\n";

mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
echo "<TR>\n";
foreach ($row as $column) {
echo "<TD>$column</TD>\n";
}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>

Thanks in advance.

Pha3dr0n
sad69
02-18-2004, 10:44 PM
As far as getting your columns to be sortable, I think you're missing something in the <a href=... tag. You've got to include the column and term in that link, along with your order.

The fact that you're using the $_POST variable, and that you'll be including it in the url may pose a problem.. I'm not sure. You may want to have it using the $_GET, and your form's method should perhaps be changed to GET as well.

For the wildcard issue, I thought the % sign was a wildcard. It looks to me as though if 'a' was the search term, 'cat' could be returned in the resultset, since you're looking for %a%

If your question is if the user enters 'a%', you want this to be treated as anything starting with an 'a', then you're going to have to make some changes to your select statement.

I'm not quite sure why you're currently including the wildcards, if you want the user to specify them. Give that one some thought.

Good luck,
Sadiq.
ConfusedOfLife
02-19-2004, 08:42 AM
Even though it's not quite relevant to your case, but are you aware of MySql function MATCH().....AGAINST?? I think for such searches it gives a better result (as it's used in this forum too) than simply asking your client to learn the wildcards.

Nontheless if you still wana use your wildcars, as sad69 mentioned you have to remove the wildcards you're using yourself in the query and only let the client enters it himself.
pha3dr0n
02-19-2004, 12:05 PM
I see what you mean about the wildcards - its sorted :)

Still havent a clue about getting the results to sort into order though.
ConfusedOfLife
02-20-2004, 08:10 AM
I don't understand that sort thing, you mean for example if you have to records containing your search word, but the 2nd record has 3 samples of the search word and the 1st record only has one sample, then you want the 2nd record comes first? If it's so then MATCH() AGAINST() already do it for you.