I'm really struggling to get the code below to work, it brings bacl all the results ($display = 1; so I can test it as I dont have a full database yet).
Can anyone shed any light?
<?php // This is the home page for the site.
// set the title for the html header
$page_title = 'Results';
// require the config file
require_once ('../includes/config.inc.php');
// include the header filde
// add header file once created
// require the database settings file using information from config.inc.php
require_once (MYSQL);
// Create a variable and set the number of record to display per page
$display = 1;
// get the variables posted from the form in the url and clean before using it in a query
$ward = mysqli_real_escape_string ($dbc, trim($_GET['ward']));
$party = mysqli_real_escape_string ($dbc, trim($_GET['party']));
$name = mysqli_real_escape_string ($dbc, trim($_GET['name']));
// trim any whitespace from the free text field
$name= trim($name);
// query the address, party and ward tables using the vaules from the last query
// $q="SELECT name.*, ward.*, party.* FROM name, address, party, ward ";
$q="SELECT * FROM name LEFT JOIN address ON name.address_id = address.address_id LEFT JOIN party ON name.party_id = party.party_id LEFT JOIN ward ON name.ward_id = ward.ward_id ";
if ($ward <> "*") {
$q = $q . "WHERE name.ward_id = '" . $ward . "' AND ward.ward_id = '" . $ward . "' ";
} if ($party <> "*" && $ward <> "*") {
$q = $q . "AND name.party_id = '" . $party . "' AND party.party_id = '" . $party . "' ";
} if ($party <> "*" && $ward == "*") {
$q = $q . "WHERE name.party_id = '" . $party . "' AND party.party_id = '" . $party . "' ";
} if ($name <> "NULL" && $ward <> "*" || $party <> "*") {
$q = $q . "AND name.surname LIKE '%" . $name . "%' ";
} if ($name <> "NULL" && $ward == "*" && $party == "*") {
$q = $q . "WHERE name.surname LIKE '%" . $name . "%' ";
}
// determine how many pages there are
if (isset($_GET['p']) && is_numeric($_GET['p'])){
$pages = $_GET['p'];
} else { // need to determine how many pages are needed
$q2 = $q . ";";
$r2 = mysqli_query ($dbc, $q2) or trigger_error("Query: $q2\n<br />MySQL Error: " . mysqli_error($dbc));
$row = mysqli_num_rows($r2);
$records = $row[0];
}
// calculate the number of pages needed
if ($records > $display) { // more than one page
$pages = ceil ($records/$display);
} else {
$pages = 1;
}
// determine hwere in the databasse to start returning results
if (isset($_GET['s']) && is_numeric ($_GET['s'])) {
$start = $_GET['s'];
} else {
$start =0;
}
$q = $q . " LIMIT $start, $display " ;
// For debugging return any errors or run the query
$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
// check the query returnbed any results of not we need to tell the user so they understand why the page is blank
if (mysqli_affected_rows($dbc) == 0) {
// if no new topics have been created tell the user
echo '<p><h1>Please try again, we didnt find any results</h1></p>';
} else{
echo '<p>Your search returned' .mysqli_affected_rows.' records</p>';
}
?>
<!-- Create the table for the loop -->
<p><h1>Results</h1></p>
<table width="90%" border="0" align="center" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">
<table summary="Table showing forum posts">
<tr>
<th align="center" bgcolor="#E6E6E6"><strong>First Name</strong></th>
<th align="center" bgcolor="#E6E6E6"><strong>Surname</strong></th>
<th align="center" bgcolor="#E6E6E6"><strong>Ward</strong></th>
<th align="center" bgcolor="#E6E6E6"><strong>Party</strong></th>
<th align="center" bgcolor="#E6E6E6"><strong>Email</strong></th>
<th align="center" bgcolor="#E6E6E6"><strong>Phone</strong></th>
<th align="center" bgcolor="#E6E6E6"><strong>Address</strong></th>
</tr>
<?php
// Start looping table row
while ($rows=mysqli_fetch_array($r, MYSQLI_ASSOC)){
// make the background colour alternate for each row.
$bg = ($bg=='#eeeeee' ? '#FFCCCC' : '#eeeeee');
// close the php
?>
<tr>
<td align="center" bgcolor="<?php echo $bg; ?>"><?php echo $rows['first_name']; ?></td>
<td align="center" bgcolor="<?php echo $bg; ?>"><?php echo $rows['surname']; ?></td>
<td align="center" bgcolor="<?php echo $bg; ?>"><?php echo $rows['ward']; ?></td>
<td align="center" bgcolor="<?php echo $bg; ?>"><?php echo $rows['party']; ?></td>
<td align="center" bgcolor="<?php echo $bg; ?>"><?php echo $rows['email']; ?></td>
<td align="center" bgcolor="<?php echo $bg; ?>"><?php echo $rows['phone']; ?></td>
<td align="center" bgcolor="<?php echo $bg; ?>"><?php echo $rows['address']; ?></td>
<?php
}
// close connection
mysqli_close($dbc);
echo '</table>';
// make the links to the other pages of results if necessary
if ($pages > 1) {
//add some spacing and start a new paragraph
echo '<br /><p>';
// determine what page the script is on
$current_page = ($start/$display) +1;
// if its not on the first page make a previous button
if ($current_page != 1) {
echo '<a href="results.php?ward=' . $ward . '&party=' .$party. '&name=' .$name. '&s=' .($start - $display) . '&p=' . $pages . '">Previous</a> ';
}
// Make all the numbered pages
for ($i = 1; $i <= $pages; $i++) {
if ($i != $current_page) {
echo '<a href="results.php?ward=' . $ward . '&party=' .$party. '&name=' .$name. '&s=' . (($display * ($i - 1))) . '&p=' . $pages . '">' . $i. '</a> ';
} else {
echo $i . ' ';
}
} // end the loop
// if its not the past lage generate a next button
if ($current_page != $pages) {
echo '<a href="results.php?ward=' . $ward . '&party=' .$party. '&name=' .$name. '&s=' . ($start + $display) . '&p=' . $pages . '">Next</a>';
}
echo '</p>';
} // end of links section
// will include footer here
?>
