[SOLVED] Building a search function for my db

Grant Holmes · January 24, 2008

I'm going through this tutorial to learn how to build a search.

The first part works:

$sql = "SELECT * FROM birthdays WHERE Contact_Info_FirstName LIKE '$string' or Contact_Info_LastName LIKE '$string' or Contact_Info_State LIKE '$string' ORDER BY id DESC";;

But on the next step, you add "%" for better results:

$sql = "SELECT * FROM birthdays WHERE Contact_Info_FirstName LIKE '%'.$string.'%' or Contact_Info_LastName LIKE '%'.$string.'%' or Contact_Info_State LIKE '%'.$string.'%' ORDER BY id DESC";

When I use this, my page returns this:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.grant.'%' or Contact_Info_LastName LIKE '%'.grant.'%' or Contac"

I'm sure this is a PHP version issue, but have no clue how to resolve it.

pocobueno1388 · January 24, 2008

Change it to

$sql = "SELECT * FROM birthdays WHERE Contact_Info_FirstName LIKE '%$string%' or Contact_Info_LastName LIKE '%$string%' or Contact_Info_State LIKE '%$string%' ORDER BY id DESC";

Grant Holmes · January 24, 2008

Thanks for the quick answer! Now my next piece of cornfusion. Here's the testing code as is:

<?php $string = "grant"; ?>
<?php 
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$sql = "SELECT * FROM birthdays WHERE Contact_Info_FirstName LIKE '%$string%' or Contact_Info_LastName LIKE '%$string%' or Contact_Info_State LIKE '%$string%' ORDER BY id DESC";
$query = mysql_query($sql) or die(mysql_error());
$row_sql = mysql_fetch_assoc($query);
$total = mysql_num_rows($query);

if($total>0) {
while ($row_sql = mysql_fetch_assoc($query)) {//echo out the results
echo ''.$row_sql['Contact_Info_FirstName'].'<br />'.$row_sql['Contact_Info_LastName'].'';
}
} else
{
echo "No results to display";
}

?>

My results look like this:

Grant

HolmesGrant

hGrant

HolmesGrant

From the above, I'd assume the First_name-Last_name stuff controlled how it prints out. How is the query returning it like this and how can I fix the output/

pocobueno1388 · January 24, 2008

How do you want it to print out?

Grant Holmes · January 24, 2008

Well I want to get to the point where I can click on the record to edit it, but (using the same list from below) I was looking for:

Grant

Grant Holmes

Grant h

Grant Holmes

(If you can suggest how to make them "click to edit" or add an "EDIT"(this record) link, that would be great. I already have an edit record page I could use.

The Little Guy · January 24, 2008

echo ''.$row_sql['Contact_Info_LastName'].' '.$row_sql['Contact_Info_FirstName'].'';

Grant Holmes · January 24, 2008

Close, but that puts "Last-First" ... I need "First-Last"

Here's my line now:

	echo ''.$row_sql['Contact_Info_FirstName'].' '.$row_sql['Contact_Info_LastName'].' '.$row_sql['Contact_Info_State'].'<br />' ';

But it breaks the page. I'm trying to do a line break after each name.

blocker · January 24, 2008

I did something similar to what you're doing. I needed to have a database to search for employees located in any of 4 different locations and upon finding them, I needed to be able to click on their name to view ALL their information, or click "Edit" to edit, "Delete" to delete... I also had a feature to Add a new record...

Here's my function that printed the results of the query. If you want to see EVERYTHING, check out my website and click on "development," all the php docs are there

// $SearchString is what you are searching for
// $Order is what you want to order by (last name, etc)
// $Location is an optional extra string, such as "AND Location =  'Tulsa'"
// $Admin is set if you are in "admin" mode (meaning you can edit people's information)
function PeopleSearch($SearchString, $Order, $Location, $Admin)
{
include 'php_db_login.php';

$query = "SELECT * FROM people WHERE (FirstName LIKE '$SearchString%' || LastName LIKE '$SearchString%') $Location ORDER BY $Order;";

$conn = mysql_connect($dbhost, $dbuser, $dbpassword) or die(mysql_error());
mysql_select_db('life');
$result = mysql_query($query) or die(mysql_error());
$num = mysql_numrows($result);
mysql_close($conn);

echo '<tr>'."\n";
echo '<td colspan="5" valign="top" align="left">'."\n";
echo '</td>'."\n";
echo '</tr>'."\n";
echo '<tr>'."\n";
echo '<td colspan="4" valign="top" align="center">'."\n";
echo '<font class="red"><b>First Responders in red</b></font>'."\n";
echo '</td>'."\n";
echo '</tr>'."\n";
echo '<tr>'."\n";
echo '<td nowrap class="PAD_7px" valign="top" align="left">'."\n";
echo '<b>Name</b>'."\n";
echo '</td>'."\n";
echo '<td nowrap class="PAD_7px" valign="top" align="left">'."\n";
echo '<b>Extension</b>'."\n";
echo '</td>'."\n";
echo '<td nowrap class="PAD_7px" valign="top" align="left">'."\n";
echo '<b>Location</b>'."\n";
echo '</td>'."\n";
echo '<td nowrap class="PAD_7px" valign="top" align="left">'."\n";
echo '<b>Department</b>'."\n";
echo '</td>'."\n";
echo '<td nowrap class="PAD_7px" valign="top" align="left" width="100%">'."\n";

 if ($Admin == '?ADMIN')
 {
 echo '<font class="RED">|_______ADMIN_______|</font>'."\n"; ;
 }

echo '</td>'."\n";
echo '</tr>'."\n";

$i = 0;
while ($i < $num)
{
 $FirstName = mysql_result($result, $i, "FirstName");
 $LastName = mysql_result($result, $i, "LastName");
 $Phone = mysql_result($result, $i, "Phone1");
 $Location = mysql_result($result, $i, "Location");
 $FirstResponder = mysql_result($result, $i, "FirstResponder");
 $Department = mysql_result($result, $i, "Department");
 $IDNum = mysql_result($result, $i, "IDNum");

 echo '<TR>'."\n";
 echo '<TD class="PAD_7px" valign="top" align="left" nowrap>'."\n";
 if ($FirstResponder == "checked")
 {
   echo '<b><font class="RED">+ ';
 }
 echo '<a href="peopledata.php?IDNum='.$IDNum.'" target="_blank">'.$LastName.', '.$FirstName.'</a>';
 if ($FirstResponder == "checked")
 {
   echo '</b></font>';
 }
 echo "\n";
 echo '</TD>'."\n";
 echo '<TD class="PAD_7px" valign="top" align="left" nowrap>'."\n";
 echo $Phone."\n";
 echo '</TD>'."\n";
 echo '<TD class="PAD_7px" valign="top" align="left" nowrap>'."\n";
 echo $Location."\n";
 echo '</TD>'."\n";
 echo '<TD class="PAD_7px" valign="top" align="left" nowrap>'."\n";
 echo $Department."\n";
 echo '</TD>'."\n";
 echo '<TD class="PAD_7px" valign="top" align="left" width="100%">'."\n";

 if ($Admin == '?ADMIN')
 {
 $AdminTag = 'ADMIN';
 echo '<a href="peopledata.php?IDNum='.$IDNum.'&'.$AdminTag.'" target="_blank">[ EDIT ]</a>   ←→   <a href="php_people_delete.php?IDNum='.$IDNum.'" target="_blank">[ DELETE ]</a>'."\n"; ;
 }

 echo '</TD>'."\n";
 echo '</TR>'."\n";
 $i++;
}
}

The Little Guy · January 24, 2008

Like so?

echo $row_sql['Contact_Info_FirstName'].' '.$row_sql['Contact_Info_LastName'].' '.$row_sql['Contact_Info_State'].'<br />';

Grant Holmes · January 24, 2008

LG: Thanks! That fixes the formatting!!

Blocker: I'll look that over and report back...Thanks!

Grant Holmes · January 24, 2008

Brian, looking at your code above, and at the tutorial, I don't see a FORM to input the original search string. How is that included? or am I missing it??

Grant Holmes · January 24, 2008

I found a great script here.

I am very close to beating this so a tad more help and I'll be good. I am getting the results I expect and am printing the rows in a table and I have an "edit" record link at the end of the row, but when I click it, I get:

".../requests_edit.php?id=%3C?%20echo%20%22%22;%20?%3E" in the address bar, so I know I'm not passing the row number (id) correctly.

My current Page:

<?php include_once("security/SECsecurity.php"); ?>
<?php $DOCROOT = $_SERVER['DOCUMENT_ROOT'] ; ?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
<TITLE>private data management- your IP has been recorded.</TITLE>
    <META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
   <META NAME="GOOGLEBOT" CONTENT="NOARCHIVE">
    <META NAME="ROBOTS" CONTENT="NONE">
    <LINK REL="stylesheet" TYPE="text/css" HREF="martini.css">
<script src="../JS/sorttable.js"; ?></script>
</HEAD>

<BODY><H1>Please choose...</H1><P>

<?php
if (!empty($_REQUEST["id"])) {
   echo "<center><FONT color=\"#ff0000\"><B>Record Updated</B></FONT></center>";
}
if (!empty($_REQUEST["del"])) {
   echo "<center><FONT color=\"#ff0000\"><B>Record Deleted</B></FONT></center>";
}
?>
<center><div style='width:100%; background-color:silver; text-align:right'>
  <?php SECShowAdminLink(); ?>
   
  <?php SECShowLogoutLink(); ?>    
</div></center>
<center><div style='width:100%; background-color:#ffe4c4; text-align:center'><BR>note: On most pages, clicking column titles will re-sort list<BR>
<IMG src="images/uparrow.gif" border="0" hspace="2" vspace="4" /></A><A href="#bottom"><IMG src="images/downarrow.gif" border="0" hspace="4" vspace="4" /></A> Up/Down arrows on pages will take you to the top of bottom of the page.<BR><BR></div></center>
<!-- *********************************************************** -->
<form name="search" method="post" action="<?=$PHP_SELF?>">
Seach for: <input type="text" name="find" /> in
<Select NAME="field">
<Option VALUE="Contact_Info_FirstName">First Name</option>
<Option VALUE="Contact_Info_LastName">Last Name</option>
<Option VALUE="Contact_Info_State">State</option>
<Option VALUE="Contact_Info_Country">Country</option>
</Select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>
<!-- *********************************************************** -->
<?php

//This is only displayed if they have submitted the form
if ($searching =="yes")
{
echo "<h2>Results</h2><p>";

//If they did not enter a search term we give them an error
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}

// Otherwise we connect to our Database
include("dbinfo.inc.php");
mysql_connect(mysql,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// We preform a bit of filtering
$find = strtoupper($find);
$find = strip_tags($find);
$find = trim ($find);

//Now we search for our search term, in the field the user specified
$data = mysql_query("SELECT * FROM birthdays WHERE upper($field) LIKE'%$find%'");
//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find; echo "<br><table border=\"1\" cellspacing=\"2\" cellpadding=\"3\">";
//And we display the results
while($result = mysql_fetch_array( $data ))
{
$id=$row["id"];
echo "<TR><TD valign=\"top\">";
echo $result['id'];
echo "</TD><TD valign=\"top\">";
echo $result['Contact_Info_FirstName'];
echo " ";
echo $result['Contact_Info_LastName'];
echo "</TD><TD valign=\"top\">";
echo $result['Contact_Info_State'];
echo "</TD><TD valign=\"top\">";
echo $result['Contact_Info_Country'];
echo "</TD><TD valign=\"top\">";
echo "<a href='requests_edit.php?id=<? echo \"$id\"; ?>'>edit</a></TD>";
echo "<TR>";
}

//This counts the number or results - and if there wasn't any it gives them a little message explaining that
$anymatches=mysql_num_rows($data);
if ($anymatches == 0)
{
echo "Sorry, but we can not find an entry to match your query<br><br>";
}
echo "</TABLE>";
//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;
}
?>

<!-- *********************************************************** -->
<?php include "include/footer.php"; ?>	
</BODY>
</HTML>

pocobueno1388 · January 24, 2008

Your trying to echo inside an echo.

Change this

echo "<a href='requests_edit.php?id=<? echo \"$id\"; ?>'>edit</a></TD>";

To

echo "<a href='requests_edit.php?id=$id'>edit</a></TD>";

Grant Holmes · January 24, 2008

Thanks to all for your help. This is what did it...

echo "<a href='search_edit.php?id=".$result['id']."''>edit</a></TD>";

Sign In

[SOLVED] Building a search function for my db

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information