Hi,
This is my first time here and only my second week using PHP. I am happy to know there are people who actually want to help. I have a piece of code which i will paste below that I want to use for my website. It's supposed to look through the database and find records that match the search and print them to different pages. For example 5 to a page. I got it working when I dont use a form, but when I use a form with conditionals only the first page works and when I click next it says "empty query". I need help in figuring out what is wrong, I've been stuck now for 2 days trying to solve this problem. I just want to be pointed in the right direction. Thank you!
.<---code above---->
<?php
// This script retrieves all the records from the users table.
$page_title = 'View the Current Records';
include('templates/header.inc');
require_once ('./mysql_connect.php'); // Connect to the db
// how many rows to show per page
$rowsPerPage =5;
// by default we show first page
$pageNum = 1;
// if $GET['page'] defined, use it as page number
if(isset($GET['page']))
{
$pageNum = $_GET['page'];
}
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
if (isset($_POST['submit']))
{ // Handle the form.
// Make the query.
if (strlen($_POST['business_name']) > 0) {
$business_name = TRUE;
}
// Check for a category.
if (strlen($_POST['category']) > 0) {
$category = TRUE;
}
if (strlen($_POST['state']) > 0) {
$state = TRUE;
}
if ($business_name && $category && $state)
{
$query = "SELECT Business_Name, Street, City, State, Zip_Code, Phone FROM Users WHERE (Business_Name LIKE \"{$_POST['business_name']}%\" && State =\"{$_POST['state']}\" && Category =\"{$_POST['category']}\") ORDER BY Business_Name, City LIMIT $offset, $rowsPerPage";
}
elseif ($business_name && $category)
{
$query = "SELECT Business_Name, Street, City, State, Zip_Code, Phone FROM Users WHERE (Business_Name LIKE \"{$_POST['business_name']}%\" && Category =\"{$_POST['category']}\") ORDER BY Business_Name, City LIMIT $offset, $rowsPerPage";
}
elseif ($business_name && $state)
{
$query = "SELECT Business_Name, Street, City, State, Zip_Code, Phone FROM Users WHERE (Business_Name LIKE \"{$_POST['business_name']}%\" && State =\"{$_POST['state']}\") ORDER BY Business_Name, City LIMIT $offset, $rowsPerPage";
}
elseif ($category && $state)
{
$query = "SELECT Business_Name, Street, City, State, Zip_Code, Phone FROM Users WHERE (Category = \"{$_POST['category']}\" && State =\"{$_POST['state']}\") ORDER BY Business_Name, City LIMIT $offset, $rowsPerPage";
}
elseif ($business_name)
{
$query = "SELECT Business_Name, Street, City, State, Zip_Code, Phone FROM Users WHERE Business_Name LIKE \"{$_POST['business_name']}%\" ORDER BY Business_Name, City LIMIT $offset, $rowsPerPage";
}
elseif ($category)
{
$query = "SELECT Business_Name, Street, City, State, Zip_Code, Phone FROM Users WHERE Category = \"{$_POST['category']}\" ORDER BY Business_Name, City LIMIT $offset, $rowsPerPage";
}
elseif ($state)
{
$query = "SELECT Business_Name, Street, City, State, Zip_Code, Phone FROM Users WHERE State = \"{$_POST['state']}\" ORDER BY Business_Name, City LIMIT $offset, $rowsPerPage";
}
else
{
echo 'Please enter at least one search item: Business Name, Category and/or State.<br /><br />';
}
$result = mysql_query($query);
//$query = "SELECT Business_Name, City, State, Phone FROM Users ORDER BY Business_Name, City LIMIT $offset, $rowsPerPage";
//$result = @($query); // Rund the query.
if ($result){ // If it ran OK, display the records.
echo '<table align="center" cellspacing="2" cellpadding="2">
<tr><td align="left"><b>Name</b></td><td align="left"><b>Street</b><td align="left"><b>City</b></td>
<td align="left"><b>State</b><td align="left"><b>Zip Code</b></td><td align="left"><b>Phone</b></td></tr>';
// Fetch and print all the records.
while ($row = mysql_fetch_array($result, MYSQL_NUM))
{
echo "<tr><td align=\"left\">$row[0]</td><td align=\"left\">$row[1]</td><td align=\"left\">$row[2]</td><td align=\"left\">$row[3]</td><td align=\"left\">$row[4]</td><td align=\"left\">$row[5]</td></tr>\n";
}
echo '</table>';
}
if ($business_name && $category && $state)
{
$query = "SELECT COUNT(Business_Name) AS numrows FROM Users WHERE (Business_Name LIKE \"{$_POST['business_name']}%\" && State =\"{$_POST['state']}\" && Category =\"{$_POST['category']}\") ";
}
elseif ($business_name && $category)
{
$query = "SELECT COUNT(Business_Name) AS numrows FROM Users WHERE (Business_Name LIKE \"{$_POST['business_name']}%\" && Category =\"{$_POST['category']}\") ";
}
elseif ($business_name && $state)
{
$query = "SELECT COUNT(Business_Name) AS numrows FROM Users WHERE (Business_Name LIKE \"{$_POST['business_name']}%\" && State =\"{$_POST['state']}\")";
}
elseif ($category && $state)
{
$query = "SELECT COUNT(Business_Name) AS numrows FROM Users WHERE (Category = \"{$_POST['category']}\" && State =\"{$_POST['state']}\") ";
}
elseif ($business_name)
{
$query = "SELECT COUNT(Business_Name) AS numrows FROM Users WHERE Business_Name LIKE \"{$_POST['business_name']}%\" ";
}
elseif ($category)
{
$query = "SELECT COUNT(Business_Name) AS numrows FROM Users WHERE Category = \"{$_POST['category']}\" ";
}
elseif ($state)
{
$query = "SELECT COUNT(Business_Name) AS numrows FROM Users WHERE State = \"{$_POST['state']}\"";
}
else
{
echo "Didnt work!";
}
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current page
}
else
{
$nav .= " <a href=\"$self?page=$page\">$page</a> ";
}
}
// creating previous and next link // plus the link to go straight to// the first and last page
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = ' '; // we're on page one, don't print previous link
$first = ' '; // nor the first page link
}
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";
$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}
// print the navigation link
echo $first . $prev . $nav . $next . $last;
//mysql_free_result($result); // Free up the resources.
}
else
{
echo '<p>The users could not be displayed due to a system error. We apologize for any inconvenience.</p><p>'.
mysql_error(). '</p>';
}
mysql_close();
//include('templates/footer.inc');
?>
