Grant Holmes Posted January 24, 2008 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/ Share on other sites More sharing options...
pocobueno1388 Posted January 24, 2008 Share Posted 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"; Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447824 Share on other sites More sharing options...
Grant Holmes Posted January 24, 2008 Author Share Posted 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 HolmesGrant 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/ Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447829 Share on other sites More sharing options...
pocobueno1388 Posted January 24, 2008 Share Posted January 24, 2008 How do you want it to print out? Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447843 Share on other sites More sharing options...
Grant Holmes Posted January 24, 2008 Author Share Posted 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 Grant Holmes 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. Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447848 Share on other sites More sharing options...
The Little Guy Posted January 24, 2008 Share Posted January 24, 2008 echo ''.$row_sql['Contact_Info_LastName'].' '.$row_sql['Contact_Info_FirstName'].''; Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447849 Share on other sites More sharing options...
Grant Holmes Posted January 24, 2008 Author Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447858 Share on other sites More sharing options...
blocker Posted January 24, 2008 Share Posted 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++; } } Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447859 Share on other sites More sharing options...
The Little Guy Posted January 24, 2008 Share Posted January 24, 2008 Like so? echo $row_sql['Contact_Info_FirstName'].' '.$row_sql['Contact_Info_LastName'].' '.$row_sql['Contact_Info_State'].'<br />'; Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447862 Share on other sites More sharing options...
Grant Holmes Posted January 24, 2008 Author Share Posted January 24, 2008 LG: Thanks! That fixes the formatting!! Blocker: I'll look that over and report back...Thanks! Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447864 Share on other sites More sharing options...
Grant Holmes Posted January 24, 2008 Author Share Posted 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?? Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-447869 Share on other sites More sharing options...
Grant Holmes Posted January 24, 2008 Author Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-448082 Share on other sites More sharing options...
pocobueno1388 Posted January 24, 2008 Share Posted 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>"; Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-448115 Share on other sites More sharing options...
Grant Holmes Posted January 24, 2008 Author Share Posted 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>"; Quote Link to comment https://forums.phpfreaks.com/topic/87559-solved-building-a-search-function-for-my-db/#findComment-448121 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.