Hello
I am new to all of this and have the following script which should search a database and then display 10 results per page with links at the bottom of the results to all the other individual pages.
When I run the script the first time the first 10 results are displayed and the links at the bottom are in place.
However, when I click on a link for example page 2 I get the following error message:
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 'LIKE 'y' AND town LIKE '%%' AND checked LIKE '%1%' ORDER BY nam
I think this may be coming from the following line which produces the link:
echo " <a href=\"$PHP_SELF?page=$i&$category='y'&town='$category2'&checked='1'\">$i</a> ";
Can anyone suggest what the syntax should be please and also how to put a Previous/Next link on?
As you might gather I have "cobbled" the script together from a script which displayed all the results and a few extra bits to limit the display to 10 results per page.
Thanking you all in anticipation
Mark
UPDATE - It has been suggested that I try to use:
echo " <a href=\"".$_SERVER['PHP_SELF']."?page=$i&$category='y'&town='$category2'&checked='1'\">$i</a> ";
but this produces the same error message
Apologies for reproducing all the code but I am not sure which parts are relevant.
<?php require_once('Connections/colleges.php'); ?>
<?php
// Number of entries per page
$per_page = 10;
// You will need to change this. This should be your normal SQL query that
// you use to get the info to be displayed from the db.
$maxRows_Recordset1 = 10000;
$pageNum_Recordset1 = 0;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
mysql_select_db($database_stemel, $stemel);
$query_Recordset1 = "SELECT name, add1, add2, town, county, postcode, website, aat, acca, att, cat, cima, ciot, cipfa, degree, icaew, icas FROM colleges WHERE $category LIKE 'y' AND town LIKE '%$category2%' AND checked LIKE '%1%' ORDER BY name ASC";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $stemel) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
// Set page #, if no page is specified, assume page 1
if (!$page) {
$page = 1;
}
$prev_page = $page - 1;
$next_page = $page + 1;
// Set up specified page
$page_start = ($per_page * $page) - $per_page;
$num_rows = $totalRows_Recordset1;
if ($num_rows <= $per_page) {
$num_pages = 1;
} else if (($num_rows % $per_page) == 0) {
$num_pages = ($num_rows / $per_page);
} else {
$num_pages = ($num_rows / $per_page) + 1;
}
$num_pages = (int) $num_pages;
if (($page > $num_pages) || ($page < 0)) {
error("You have specified an invalid page number");
}
//
// Now the pages are set right, we can
// perform the actual displaying...
$maxRows_Recordset1 = $per_page;
$pageNum_Recordset1 = $page;
if (isset($_GET['pageNum_Recordset1'])) {
$pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
}
$startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
mysql_select_db($database_stemel, $stemel);
$query_Recordset1 = "SELECT name, add1, add2, town, county, postcode, website, aat, acca, att, cat, cima, ciot, cipfa, degree, icaew, icas FROM colleges WHERE $category LIKE 'y' AND town LIKE '%$category2%' AND checked LIKE '%1%' ORDER BY name ASC";
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $stemel) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
if (isset($_GET['totalRows_Recordset1'])) {
$totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
} else {
$all_Recordset1 = mysql_query($query_Recordset1);
$totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
}
$totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<?php
require ('header.php');
?>
<style type="text/css">
<!--
.style1 {
font-family: Arial, Helvetica, sans-serif;
font-size: 13px;
}
-->
</style>
<form method="post" action="sdfsd.php">
<?php
/* This section passes on received values as hidden fields after validating null entries. */
print ("<input type=hidden name=\"search_name\" value=\"$search_name\">\n");
?>
</form>
<span class="style1"><?php echo "<div align='center'>" ?>
<?php echo "<u>" ?>
<?php echo "AccountancyStudents Tuition Provider Database". "<BR><BR>" ?>
<?php echo "</u>" ?>
<?php echo $totalRows_Recordset1; ?>
<?php echo " result(s) found for" ?>
<?php echo "<strong>" ?>
<?php echo $category; ?>
<?php echo "</strong>" ?>
<?php if ($category2 <>'') echo " and"; else echo ' and '."<strong>".'Anywhere'."</strong>"?>
<?php echo "<strong>" ?>
<?php echo $category2; ?>
<?php echo "</strong>" ?>
<BR>
<BR>
<?php do { ?>
<?php echo "<table width='454' border='1'><td width='348' valign='top' bgcolor='#CCFFCC'><div align='left'>" ?>
<?php echo "<strong>" ?>
<?php if ($row_Recordset1['name'] <>'') echo ($row_Recordset1['name'])."<BR>"; else echo ''?>
<?php echo "</strong>" ?>
<?php if ($row_Recordset1['add1'] <>'') echo ($row_Recordset1['add1'])."<BR>"; else echo ''?>
<?php if ($row_Recordset1['add2'] <>'') echo ($row_Recordset1['add2'])."<BR>"; else echo ''?>
<?php if ($row_Recordset1['town'] <>'') echo ($row_Recordset1['town'])."<BR>"; else echo ''?>
<?php if ($row_Recordset1['county'] <>'') echo ($row_Recordset1['county'])."<BR>"; else echo ''?>
<?php if ($row_Recordset1['postcode'] <>'') echo ($row_Recordset1['postcode'])."<BR>"; else echo ''?>
<?php if ($row_Recordset1['website'] <>'') echo "<a href=http://".$row_Recordset1['website']." target='_blank'> Website </a><BR>"; else echo ''?>
<?php echo "</td>" ?>
<?php echo "<td width='90' valign='top' bgcolor='#CCFFCC'><div align='left'>" ?>
<?php if ($row_Recordset1['aat'] <>'') echo "AAT"."<BR>"; else echo ''?>
<?php if ($row_Recordset1['acca'] <>'') echo "ACCA"."<BR>"; else echo ''?>
<?php if ($row_Recordset1['att'] <>'') echo "ATT"."<BR>"; else echo ''?>
<?php if ($row_Recordset1['cat'] <>'') echo "CAT"."<BR>"; else echo ''?>
<?php if ($row_Recordset1['cima'] <>'') echo "CIMA"."<BR>"; else echo ''?>
<?php if ($row_Recordset1['ciot'] <>'') echo "CIOT"."<BR>"; else echo ''?>
<?php if ($row_Recordset1['cipfa'] <>'') echo "CIPFA"."<BR>"; else echo ''?>
<?php if ($row_Recordset1['degree'] <>'') echo "DEGREE"."<BR>"; else echo ''?>
<?php if ($row_Recordset1['icaew'] <>'') echo "ICAEW"."<BR>"; else echo ''?>
<?php if ($row_Recordset1['icas'] <>'') echo "ICAS"."<BR>"; else echo ''?>
<?php echo "</td>" ?>
</table>
<BR>
<BR>
<?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
</span>
<?php
// Page # direct links
// If you don't want direct links to each page, you should be able to
// safely remove this chunk.
for ($i = 1; $i <= $num_pages; $i++) {
if ($i != $page) {
echo " <a href=\"$PHP_SELF?page=$i&$category='y'&town='$category2'&checked='1'\">$i</a> ";
} else {
echo " $i ";
}
}
?>
</div>
<br>
<?php
require ('footer.php');
?>
<?php
mysql_free_result($Recordset1);
?>
