Trying to figure out search procedure

PHP
1

I’ve done my best not to bother you folks with this but am stumped. I’ve found some tutorials for searching a database and have managed to get a search happening. However, the list of names found (I searched on “Smith” because I knew there was more than one) just repeats the FIRST name in the db, instead of the supposedly found names (Ian Smith, Robert Smith, etc.). I’ve read and read and read the code but can’t find the problem. This is the code I’m using and I would love it if someone could please tell me what I’ve done wrong - thanks!:

<?php require_once(‘Connections/db9568.php’); ?>
<?php
if (!function_exists(“GetSQLValueString”)) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = “”, $theNotDefinedValue = “”)
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}

$theValue = function_exists(“mysql_real_escape_string”) ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {
case “text”:
$theValue = ($theValue != “”) ? “'” . $theValue . “'” : “NULL”;
break;
case “long”:
case “int”:
$theValue = ($theValue != “”) ? intval($theValue) : “NULL”;
break;
case “double”:
$theValue = ($theValue != “”) ? doubleval($theValue) : “NULL”;
break;
case “date”:
$theValue = ($theValue != “”) ? “'” . $theValue . “'” : “NULL”;
break;
case “defined”:
$theValue = ($theValue != “”) ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}

mysql_select_db($database_db9568, $db9568);
$query_rsJudgesSearch = “SELECT judgeID, firstName, lastName, city, province, country, emailaddy, ckcNumber, akcNumber, cdjaNumber, otherNumber, breedsOwned, kennelName, description, breedsJudged FROM judgegallery”;
$rsJudgesSearch = mysql_query($query_rsJudgesSearch, $db9568) or die(mysql_error());
$row_rsJudgesSearch = mysql_fetch_assoc($rsJudgesSearch);
$totalRows_rsJudgesSearch = mysql_num_rows($rsJudgesSearch);
?>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN” “http://www.w3.org/TR/html4/loose.dtd”>
<html>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=iso-8859-1”>
<title>Search Contacts</title>
</head>
<p><body>
<h3>Search Contacts Details</h3>
<p>You may search either by first or last name</p>
<form method=“post” action=“judgestest-search.php?go” id=“searchform”>
<input type=“text” name=“name”>
<input type=“submit” name=“submit” value=“Search”>
</form>
<?php
if(isset($_POST[‘submit’])){
if(isset($_GET[‘go’])){
if(preg_match(“/[A-Z | a-z]+/”, $_POST[‘name’])){
$name=$_POST[‘name’];

//-query the database table
$sql=“SELECT judgeID, firstName, lastName FROM judgegallery WHERE firstName LIKE '%” . $name . “%’ OR lastName LIKE '%” . $name .“%'”;

//-run the query against the mysql query function
$result=mysql_query($sql);

//-count results

$numrows=mysql_num_rows($result);

echo “<p>” .$numrows . " results found for " . stripslashes($name) . “</p>”;

//-create while loop and loop through result set
while($row=mysql_fetch_array($result)){

$FirstName =$row_rsJudgesSearch['firstName'];
$LastName=$row_rsJudgesSearch['lastName'];
$ID=$row_rsJudgesSearch['judgeID'];

//-display the result of the array

echo "<ul>
";
echo “<li>” . “<a href=\“judgestest-search.php?id=$ID\”>” .$FirstName . " " . $LastName . "</a></li>
";
echo “</ul>”;
}
}
else{
echo “<p>Please enter a search query</p>”;
}
}
}

?>
</body>
</html>
<?php
mysql_free_result($rsJudgesSearch);
?>
</p>

2

I did figure out a different way of doing the search and it works. Have to admit it’s nice to know that nobody else could figure this out either. ;>)

3

I had confidence you could figure it out on your own, thus left you to your own device.

(jk, I just check the top 20 posts or so for questions with 0 answers =P)

4

lol wonshikee (I always read that as wonkishee :goof:)
Must have missed this one Helen, just in case anyone else has a similar problem - how did you solve it?

5

I misread his name too - the same way you do, Mike. <LOL>

I found a tutorial at http://teamtutorials.com/web-development-tutorials/php-tutorials/creating-a-form-that-will-search-a-mysql-database#.T2FH43jPROc

I now have a rudimentary search form page:

&lt;form action="search.php" method="post"&gt;
 Search: &lt;input type="text" name="term" /&gt;&lt;br /&gt;
&lt;input type="submit" name="submit" value="Submit" /&gt;
&lt;/form&gt;

Then the page which displays the results:
<?php
mysql_connect (“localhost”, “testuser”,“password”) or die (mysql_error());
mysql_select_db (“test”);

$term = $_POST[‘term’];

$sql = mysql_query(“select * from testtable where FName like ‘%$term%’”);

while ($row = mysql_fetch_array($sql)){
echo 'ID: '.$row[‘ID’];
echo '<br/> First Name: '.$row[‘FName’];
echo '<br/> Last Name: '.$row[‘LName’];
echo '<br/> Phone: '.$row[‘Phone’];
echo ‘<br/><br/>’;
}

?>

The above is copied right from the tutorial. I, of course, have changed it drastically as I need multiple search options but I’m just beside myself that I figured it out!! I can’t believe that my creaky old brain has picked up as much of this stuff as it has in such a relatively short time.

Sorry - I’m pretty excited that this is coming together, can you tell? <LOL>

6

Ouch, that came from a tutorial?

Please read up on mysql_real_escape_string, it’s about as important as remembering to lock your door.

7

I thought mysql_real_escape_string was only needed on the forms where people enter their own data. Don’t tell me I have to figure out how to use it for displaying the data too???!!! Oh no - I’ll never get this stuff figured out. So much for feeling like I’m getting somewhere. :>( Back to the drawing board, I guess.

8 
$term = $_POST['term'];

$sql = mysql_query("select * from testtable where FName like '%$term%'");
9

Yes, I see that in the stuff I got from the tutorial. Are you saying that that is the part which needs mysql_real_escape_string? I’m confused.

10

Whenever something is entered into a form and it goes to your database - nuke it to within an inch of its life before it gets to your data.
ANYTHING coming from a form and being used needs to be cleaned and sanitized.

Also one tiny thing, I would advise you to use $_GET on your search form so that the data is passed in the url and you dont get the form resubmission request everytime you hit the back button!

11

I do understand that, Mike and I do use the mysql_real_escape_string thingy when I insert the data from the form. What I don’t understand, though, is why I’d need it to display the data once it’s already in the database. That really doesn’t make sense to me. It was escaped when it went into the database so why does it need to be escaped again when it’s displayed?

Do you mean

&lt;form action="judges-search_LastName.php" method="get"&gt;
 Search Last Name: &lt;input type="text" name="lastnamesearch" /&gt;&lt;br /&gt;
&lt;input type="submit" name="submit" value="Submit" /&gt;
&lt;/form&gt;

instead of

&lt;form action="judges-search_LastName.php" method="post"&gt;
 Search Last Name: &lt;input type="text" name="lastnamesearch" /&gt;&lt;br /&gt;
&lt;input type="submit" name="submit" value="Submit" /&gt;
&lt;/form&gt;

?

12

You dont need it when its displayed. What Wonshikee means is that when the variable has gone from your form and is being used in your query, it needs the mysql_real_escape_string()



$term = mysql_real_escape_string($_GET['term']); // note the change to GET!
$sql = mysql_query("select * from testtable where FName like '%$term%'");

Because it is being used directly in your SQL query it is prone to SQL injection.
If you didnt have any safeguards in place and I entered in the search box something like:

’ and 1=1

the query would in effect become select * from testtable where FName like ‘’ and 1=1

which is a pretty basic example but it could be manipulated further to drop your database entirely or compromise your table and pinch all your data.

Have a read of: http://www.unixwiz.net/techtips/sql-injection.html for more useful examples :slight_smile:

Do you mean

&lt;form action="judges-search_LastName.php" method="get"&gt;
 Search Last Name: &lt;input type="text" name="lastnamesearch" /&gt;&lt;br /&gt;
&lt;input type="submit" name="submit" value="Submit" /&gt;
&lt;/form&gt;

instead of

&lt;form action="judges-search_LastName.php" method="post"&gt;
 Search Last Name: &lt;input type="text" name="lastnamesearch" /&gt;&lt;br /&gt;
&lt;input type="submit" name="submit" value="Submit" /&gt;
&lt;/form&gt;

?

Yes - and then change the instance of $_POST to $_GET

13

OK - thanks Mike. Now I get it. I’m cursed with a VERY literal (to a frustrating fault) brain and it took this post to bump it over to realizing that people are also entering data on the search form, even though it’s not actually going into the database. You would not believe (or perhaps you would) the amount of time I spend because my brain won’t move out of the track its on. Thanks so much for taking the time to explain it in detail.

14

You are welcome Helen :slight_smile:

15

I’ve done this and have the form working (thanks again!) except for one thing. I need to make it case insensitive, in case someone types “anderson” instead of “Anderson”. I think that is achieved by using preg-match applied to the first line ($term = mysql…) but I can’t find an example demonstrating it, so can’t figure it out. I’ve tried putting if(preg_match(“/[A-Z | a-z]+/”, $_GET[‘term’])) immediately before the $term = mysql… line (as I saw in the example I found) but it doesn’t work so obviously that’s not the correct place. Where should it go - or should I be using something else instead?