jmdweb Posted March 5, 2008 Share Posted March 5, 2008 Hello you beautiful people I have been converting an old script, and having a couple of problems in doing so - Its a surname database, people search for a surname etc and brings a list of people for the searched name, the script is displaying the list correctly and showing peoples information when clicked correctly, the problem lies when I click on to next results (page numbers) I click on another page of results and I get this sql error - SQL Error Occurred : 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 '20' at line 1 http://www.site.org/search/result.php?where=lcase(surname)%20like%20'smith'%20&startLimit=160&limitPerPage=20&sortby= I have viewed the coresponding page and cant see the problem - here is the page code; <!DOCTYPE HTML PUBLIC "-//SoftQuad//DTD HoTMetaL PRO 5.0::19981022::extensions to HTML 4.0//EN" "hmpro5.dtd"> <HTML> <HEAD> <TITLE>Family History: Surname DATABASE - SUMMARY SEARCH RESULTS</TITLE> <META NAME="description" CONTENT=""> <META NAME="keywords" CONTENT=""> <META NAME="COPYRIGHT" CONTENT="Copyright (C) 2005"> <META HTTP-EQUIV="CHARSET" CONTENT="ISO-8859-1"> <META NAME="ROBOTS" CONTENT="index,follow"> <META NAME="REVISIT-AFTER" CONTENT="4 weeks"> <META NAME="Content-Language" CONTENT="en-UK"> <META NAME="Coverage" CONTENT="UK"> <META NAME="rating" CONTENT="general"> <META NAME="page-type" CONTENT="Generalinfo"> <META NAME="audience" CONTENT="all"> <META NAME="ROBOTS" CONTENT="ALL"> <SCRIPT LANGUAGE="JavaScript"> defaultStatus=''; //--></SCRIPT> <LINK HREF="pagestylesheet.css" REL="stylesheet" TYPE="text/css"> </HEAD> <BODY BGCOLOR="#fff0d0"> <BR> <BR> <TABLE WIDTH="90%" CELLPADDING="0" CELLSPACING="0" HEIGHT="570" ALIGN="CENTER" BORDER="0" BGCOLOR="#fff0d0"> <TR> <TD VALIGN="MIDDLE" ALIGN="CENTER"> <TABLE CELLPADDING="0" CELLSPACING="0" BORDER="0" WIDTH="800"> <TR> <TD VALIGN="TOP"> <TABLE CELLPADDING="10" CELLSPACING="1" BORDER="0" BGCOLOR="#000000" WIDTH="100%"> <TR> <TD BGCOLOR="#FF0000" CLASS="whitehead" ALIGN="CENTER">DATABASE - SUMMARY SEARCH RESULTS</TD> </TR> </TABLE> <BR> <?php include("common/datacon.php"); $limitPerPage = 20; //Results per page $cut_off = 10; //Number of pages displayed at once if (!isset($_REQUEST['startLimit'])) $startLimit = 0; //Don't change! $querylimit = " limit $startLimit,$limitPerPage "; $nextStartLimit = $startLimit + $limitPerPage; $previousStartLimit = $startLimit - $limitPerPage; $sorted = " order by old_c_code,event_plac,year1,month1,day1,no_of_reco"; if (!isset($_REQUEST['where'])) { $surname = $_REQUEST['surname']; $surname = str_replace("*", "%", $surname); $forenames = $_REQUEST['forenames']; $forenames = str_replace("*", "%", $forenames); $no_of_reco = $_REQUEST['no_of_reco']; $old_c_code = $_REQUEST['old_c_code']; $old_c_code = str_replace("*", "%", $old_c_code); $day1 = $_REQUEST['day1']; $month1 = $_REQUEST['month1']; $year1 = $_REQUEST['year1']; $event_type = $_REQUEST['event_type']; $event_plac = $_REQUEST['event_plac']; $event_plac = str_replace("*", "%", $event_plac); $surname_fa = $_REQUEST['surname_fa']; $surname_fa = str_replace("*", "%", $surname_fa); $forename_f = $_REQUEST['forename_f']; $forename_f = str_replace("*", "%", $forename_f); $surname_mo = $_REQUEST['surname_mo']; $surname_mo = str_replace("*", "%", $surname_mo); $forename_m = $_REQUEST['forename_m']; $forename_m = str_replace("*", "%", $forename_m); $surname_sp = $_REQUEST['surname_sp']; $surname_sp = str_replace("*", "%", $surname_sp); $forename_s = $_REQUEST['forename_s']; $forename_s = str_replace("*", "%", $forename_s); $date_enter = $_REQUEST['date_enter']; $notes = $_REQUEST['notes']; $notes = str_replace("*", "%", $notes); if (($surname=="") && ($forenames=="") && ($no_of_reco=="") && ($old_c_code=="") && ($day1=="") && ($year1=="") && ($month1=="") && ($event_type=="") && ($event_plac=="") && ($surname_fa=="") && ($forename_f=="") && ($surname_mo=="") && ($forename_m=="") && ($surname_sp=="") && ($forename_s=="") && ($date_enter=="") && ($notes=="") && ($oldcode=="")) { echo "<tr><td class='box'>At least one search term must be selected! <a href='javascript:history.back()'>Go back</a> and enter a search term.</td></tr>"; exit; } if ($surname!=="") { $surname = strtolower($surname); $where = "lcase(surname) like '$surname' "; } else { $where = "surname like '%%' "; } if ($forenames!=="") { $forenames = strtolower($forenames); $where = $where . "and lcase(forenames) like '$forenames' "; } if ($year1!=="") { switch ($yearrange) { case 0: $where = $where . "and year1 like '$year1' "; break; case 2: case 5: case 10: case 20: $yearl = $year1-$yearrange; $yearm = $year1+$yearrange; $where = $where . "and year1 between $yearl and $yearm "; break; } } if ($month1!=="") { $where = $where . "and month1 like '$month1' "; } if ($day1!=="") { $where = $where . "and day1 like '$day1' "; } if ($event_type!=="") { $event_type = strtolower($event_type); $where = $where . "and lcase(event_type) like '$event_type' "; } if ($event_plac!=="") { $event_plac = strtolower($event_plac); $where = $where . "and lcase(event_plac) like '$event_plac' "; } if ($surname_fa!=="") { $surname_fa = strtolower($surname_fa); $where = "lcase(surname_fa) like '$surname_fa' "; } if ($forename_f!=="") { $forename_f = strtolower($forename_f); $where = $where . "and lcase(forename_f) like '$forename_f' "; } if ($surname_mo!=="") { $surname_mo = strtolower($surname_mo); $where = "lcase(surname_mo) like '$surname_mo' "; } if ($forename_m!=="") { $forename_m = strtolower($forename_m); $where = $where . "and lcase(forename_m) like '$forename_m' "; } if ($surname_sp!=="") { $surname_sp = strtolower($surname_sp); $where = "lcase(surname_sp) like '$surname_sp' "; } if ($forename_s!=="") { $forename_s = strtolower($forename_s); $where = $where . "and lcase(forename_s) like '$forename_s' "; } if ($no_of_reco==!"") { $where = $where . "and no_of_reco like '$no_of_reco'"; } if ($date_enter!=="") { switch ($daterange) { case "Exact": $where = $where . "and date_enter = '$date_enter' "; break; case "Before": $where = $where . "and date_enter between '0000-00-00' and '$date_enter' "; break; case "After": $today = date("Y-m-d"); $where = $where . "and date_enter between '$date_enter' and '$today' "; break; } } if ($notes==!"") { $notes = strtolower($notes); $where = $where . "and lcase(notes) like '$notes' "; } if ($old_c_code==!"") { $old_c_code = strtolower($old_c_code); $where = $where . "and lcase(old_c_code) like '$old_c_code'"; } } else { $where = stripslashes($_REQUEST['where']); } $cntquery = "select no_of_reco,surname,forenames,day1,year1,month1,event_type,event_plac,old_c_code from tyrrell_main where $where"; //echo $cntquery . "<p>"; $cntresult = mysql_query($cntquery) or die("<tr><td>SQL Error Occurred : " . mysql_error() . "</td></tr>"); $cntnumber = mysql_num_rows($cntresult); $queryall = $cntquery.$sorted.$querylimit; //echo $queryall . "<p>"; $resultall = mysql_query($queryall) or die("<tr><td>SQL Error Occurred : " . mysql_error() . "</td></tr>"); $numberall = mysql_num_rows($resultall); ?> <TABLE WIDTH="100%" CELLPADDING="2" CELLSPACING="2" BORDER="0"> <TR> <TD CLASS="mainbold">C. Code</TD> <TD CLASS="mainbold">Event Place</TD> <TD CLASS="mainbold" COLSPAN="3" width="30">Date</TD> <TD CLASS="mainbold">Surname</TD> <TD CLASS="mainbold">Forename</TD> <TD CLASS="mainbold">Event Type</TD> <TD CLASS="mainbold">R. No.</TD> <TD CLASS="mainbold"> </TD> </TR> <?php if ($numberall == 0) { echo "<tr><td>No Records Found !</td></tr>"; } else if ($numberall > 0) { $x = 0; while ($x < $numberall) { if (($x%2) == 0) { $bgcolor = "#FFFFFF"; } else { $bgcolor = "#C0C0C0"; } ?> <tr> <td colspan="9" valign="top"> <hr width="750"> </td> </tr> <tr height="25"> <?php $old_c_code1 = mysql_result($resultall,$x,"old_c_code"); ?> <td valign="top" height="25" class="box"><?php echo $old_c_code1; ?></td> <?php $event_plac1 = mysql_result($resultall,$x,"event_plac"); ?> <td valign="top" height="25" class="box"><?php echo $event_plac1; ?></td> <?php $day1a = mysql_result($resultall,$x,"day1"); ?> <td valign="top" height="25" class="box"><?php echo $day1a; ?></td> <?php $month1a = mysql_result($resultall,$x,"month1"); ?> <td valign="top" height="25" class="box"><?php echo $month1a; ?></td> <?php $year1a = mysql_result($resultall,$x,"year1"); ?> <td valign="top" height="25" class="box"><?php echo $year1a; ?></td> <?php $surname1 = mysql_result($resultall,$x,"surname"); ?> <td valign="top" height="25" class="box"><?php echo $surname1; ?></td> <?php $forenames1 = mysql_result($resultall,$x,"forenames"); ?> <td valign="top" height="25" class="box"><?php echo $forenames1; ?></td> <?php $event_type1 = mysql_result($resultall,$x,"event_type"); ?> <td valign="top" height="25" class="box"><?php echo $event_type1; ?></td> <?php $no_of_reco1 = mysql_result($resultall,$x,"no_of_reco"); ?> <td valign="top" height="25" class="box"><?php echo $no_of_reco1; ?></td> <td valign="top" class="box"> <form name="detail" method="post" action="tyrrellfull.php"> <input type="hidden" name="no_of_reco" value="<?php echo $no_of_reco1; ?>"> <input type="submit" name="submit" value="Full"> </form> </td> </tr> </tr><?php $x++; } // end while function pagination($page_count,$num,$start,$PHP_SELF,$cut_off,$sortby,$where) { $newnum = $num / $page_count; $newnum = ceil($newnum); if(!isset($page))$page = 1; if($newnum >= 2) { echo "<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" align=\"center\"> <tr> <td style=\"border: 1px solid #999999; font-family: Verdana; font-size: 11px;\" nowrap>"; if(isset($start) && $start != 0) { $new_start = $start - $page_count; echo "« <a href=\"$PHP_SELF?where=$where&startLimit=$new_start&limitPerPage=$page_count&sortby=$sortby\">back</a> "; } else { echo "« back "; } $total_pages = $newnum; if($newnum > $cut_off) $newnum = $cut_off; $cur_page = ($start + $page_count) / $page_count; if($cur_page > $cut_off) $page = $cur_page - $cut_off + 1; if($cur_page > $cut_off) { $start_page = $page * $page_count - $page_count; } else { $start_page = 0; } for($i=0; $i<$newnum;$i++) { if($start == ($page * $page_count) - $page_count) { echo "<b>$page</b> "; } else { echo "<a href=\"$PHP_SELF?where=$where&startLimit=$start_page&limitPerPage=$page_count&sortby=$sortby\">$page</a> "; } $page++; $start_page = $start_page + $page_count; } $new_start = $start + $page_count; if ($newnum >= 2 && $cur_page < $newnum && $cur_page <= $total_pages) { echo " <a href=\"$PHP_SELF?where=$where&startLimit=$new_start&limitPerPage=$page_count&sortby=$sortby\">next</a> »"; } elseif($cur_page >= $total_pages) { echo " next »"; } else { echo " <a href=\"$PHP_SELF?where=$where&startLimit=$new_start&limitPerPage=$page_count&sortby=$sortby\">next</a> »"; } echo "</td> </tr> </table> <br><div align=\"center\" style=\"font-family: Verdana; font-size: 11px;\">Page $cur_page of $total_pages<div>"; }//if any results at top }//function echo "<tr><td colspan=\"13\" align='center'>"; pagination($limitPerPage,$cntnumber,$startLimit,$PHP_SELF,$cut_off,$sortby,$where); echo "</td></tr></table></center>"; } // end if numberall > 0 ?></TABLE> <P><a href="searchTY.php" class="mainlink"><< BACK TO SEARCH SCREEN</A> </P> </TD> </TR> </TABLE> </TD> </TR> </TABLE> </BODY> </HTML> Quote Link to comment Share on other sites More sharing options...
shocker-z Posted March 5, 2008 Share Posted March 5, 2008 LIKE statements should be like this for everythign containing WHERE field LIKE '%$value%' By the looks of it you have WHERE filed LIKE %%'$value' You should definatly look at preventing injection attacks in your code as it looks very vunrable to SQL injection attacks. Regards Liam Quote Link to comment Share on other sites More sharing options...
jmdweb Posted March 5, 2008 Author Share Posted March 5, 2008 Im getting this error in sql query - Error SQL query: $limitPerPage =20; MySQL said: Documentation #1064 - 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 '$limitPerPage = 20' at line 1 Quote Link to comment Share on other sites More sharing options...
redarrow Posted March 5, 2008 Share Posted March 5, 2008 post the databse select statements only please.... should use LIKE but also need to use the '%varable%' wildcard syntex. look at this example page scroll down under title searching.... http://www.freewebmasterhelp.com/tutorials/phpmysql/8 Quote Link to comment Share on other sites More sharing options...
jmdweb Posted March 5, 2008 Author Share Posted March 5, 2008 right, Ive narrowed it down, the problem is the Pagination scrip not working correct, can anyone use the script above to sort the problem please? would be muchly appreciated!! kind regards Jonathan Quote Link to comment 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.