Syntax error for search db

jmdweb · 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>

shocker-z · 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

jmdweb · 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

redarrow · 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

jmdweb · 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

Sign In

Syntax error for search db

Recommended Posts

jmdweb

Link to comment

Share on other sites

shocker-z

Link to comment

Share on other sites

jmdweb

Link to comment

Share on other sites

redarrow

Link to comment

Share on other sites

jmdweb

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information