civey
05-03-2008, 03:15 PM
Any ideas? I am trying to read a database, user would input a city and state and get the listing for just the city and state input. I am been trying to use the code below:
<?
include("db.php");
if (!($limit)){$limit = 10;} // Default results per-page.
if (!($page)){$page = 0;} // Default page value.
$numresults = mysql_query("SELECT * FROM caribbean WHERE
city LIKE '%{$search}%' OR
state LIKE '%{$search1}%' OR
zip LIKE '%{$search2}%'"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if (!(search)&&(!(search1))){echo("No results found matching your query - $search"); // bah, modify the "Not Found" error for your needs.
exit();}
$pages = intval($numrows/$limit); // Number of results pages.
// $pages now contains int of pages, unless there is a remainder from division.
if ($numrows%$limit) {
$pages++;} // has remainder so add one page
$current = ($page/$limit) + 1; // Current page number.
if (($pages < 1) || ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.
else {
$total = $pages;} // Else total pages is $pages value.
$first = $page + 1; // The first result.
if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.
else{
$last = $numrows;} // If last results page, last result equals total number of results.
//escape from PHP mode.
?>
<html>
<head>
<title>Search Results for Business in <?=$search?></title>
</head>
<body>
<center><h2>Search Results for Business in <?=$search?></h2></center>
<table width="100%" border="0">
<tr>
<td width="50%" align="left">
Results <b><?=$first?></b> - <b><?=$last?></b> of <b><?=$numrows?></b>
</td>
<td width="50%" align="right">
Page <b><?=$current?></b> of <b><?=$total?></b>
</td>
</tr>
<tr>
<td colspan="2" align="right">
</td>
</tr>
<tr>
<td colspan="2" align="right">
Results per-page: <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=5">5</a>
| <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=10">10</a> |
<a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=20">20</a> |
<a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=50">50</a>
</td>
</tr>
</table>
<?
//Go back into PHP mode.
// Now we can display results.
$results = mysql_query("SELECT * FROM caribbean WHERE
city LIKE '%$search' OR
state LIKE '%$search1' OR
zip LIKE '%$search2' ORDER BY name ASC LIMIT $page, $limit");
while ($data = mysql_fetch_array($results))
{
?>
<p><?=$data["name"]?><br><?=$data["address"]?><br><?=$data["city"]?> <?=$data["state"]?> <?=$data["zip"]?><br>
<?=$data["country"]?><br><?=$data["acode"]?> <?=$data["numb"]?><br><?=$data["companytype"]?></p>
<?
}
?>
<p align="center">
<?
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a> \n");}
for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
$ppage = $limit*($i - 1);
if ($ppage == $page){
echo("<b>$i</b> \n");} // If current page don't give link, just text.
else{
echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> \n");}
}
if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo(" <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>\n");}
?>
The problem is when I submit the form with no input for city or state see a sample of the result I get.
Search Results for Business in
Results 1 - 10 of 22 Page 1 of 3
Results per-page: 5 | 10 | 20 | 50
Hibiscus Caribbean Restaurant
2105 Mastin Lake Road
Huntsville AL
USA
Restaurant
Bahama Breeze
101 Resource Center Parkway
Birmingham AL 35244
The problem is when i input a city and/or state I get the same result
Search Results for Business in Miami
Results 1 - 10 of 22 Page 1 of 3
Results per-page: 5 | 10 | 20 | 50
Hibiscus Caribbean Restaurant
2105 Mastin Lake Road
Huntsville AL
USA
Restaurant
Bahama Breeze
101 Resource Center Parkway
Birmingham AL 35244
I had a previous post on this and Syco suggest I use this
$q='SELECT * FROM caribbean WHERE ';
if(isset($search)){
$q.=" city = '$search' ";
}
if(isset($search1)){
if(isset($search)){
$q.=" OR ";
}
$q.=" state='$search1' ";
}
if(isset($search2)){
if(isset($search) || isset($search1)){
$q.=" OR ";
}
$q.=" zip ='$search2' ";
}
echo $q;
instead of this
$numresults = mysql_query("SELECT * FROM caribbean WHERE
city LIKE '%{$search}%' OR
state LIKE '%{$search1}%' OR
zip LIKE '%{$search2}%'"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
But i never figure out how to implement it.
<?
include("db.php");
if (!($limit)){$limit = 10;} // Default results per-page.
if (!($page)){$page = 0;} // Default page value.
$numresults = mysql_query("SELECT * FROM caribbean WHERE
city LIKE '%{$search}%' OR
state LIKE '%{$search1}%' OR
zip LIKE '%{$search2}%'"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
if (!(search)&&(!(search1))){echo("No results found matching your query - $search"); // bah, modify the "Not Found" error for your needs.
exit();}
$pages = intval($numrows/$limit); // Number of results pages.
// $pages now contains int of pages, unless there is a remainder from division.
if ($numrows%$limit) {
$pages++;} // has remainder so add one page
$current = ($page/$limit) + 1; // Current page number.
if (($pages < 1) || ($pages == 0)) {
$total = 1;} // If $pages is less than one or equal to 0, total pages is 1.
else {
$total = $pages;} // Else total pages is $pages value.
$first = $page + 1; // The first result.
if (!((($page + $limit) / $limit) >= $pages) && $pages != 1) {
$last = $page + $limit;} //If not last results page, last result equals $page plus $limit.
else{
$last = $numrows;} // If last results page, last result equals total number of results.
//escape from PHP mode.
?>
<html>
<head>
<title>Search Results for Business in <?=$search?></title>
</head>
<body>
<center><h2>Search Results for Business in <?=$search?></h2></center>
<table width="100%" border="0">
<tr>
<td width="50%" align="left">
Results <b><?=$first?></b> - <b><?=$last?></b> of <b><?=$numrows?></b>
</td>
<td width="50%" align="right">
Page <b><?=$current?></b> of <b><?=$total?></b>
</td>
</tr>
<tr>
<td colspan="2" align="right">
</td>
</tr>
<tr>
<td colspan="2" align="right">
Results per-page: <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=5">5</a>
| <a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=10">10</a> |
<a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=20">20</a> |
<a href="<?=$PHP_SELF?>?query=<?=$query?>&page=<?=$page?>&limit=50">50</a>
</td>
</tr>
</table>
<?
//Go back into PHP mode.
// Now we can display results.
$results = mysql_query("SELECT * FROM caribbean WHERE
city LIKE '%$search' OR
state LIKE '%$search1' OR
zip LIKE '%$search2' ORDER BY name ASC LIMIT $page, $limit");
while ($data = mysql_fetch_array($results))
{
?>
<p><?=$data["name"]?><br><?=$data["address"]?><br><?=$data["city"]?> <?=$data["state"]?> <?=$data["zip"]?><br>
<?=$data["country"]?><br><?=$data["acode"]?> <?=$data["numb"]?><br><?=$data["companytype"]?></p>
<?
}
?>
<p align="center">
<?
if ($page != 0) { // Don't show back link if current page is first page.
$back_page = $page - $limit;
echo("<a href=\"$PHP_SELF?query=$query&page=$back_page&limit=$limit\">back</a> \n");}
for ($i=1; $i <= $pages; $i++) // loop through each page and give link to it.
{
$ppage = $limit*($i - 1);
if ($ppage == $page){
echo("<b>$i</b> \n");} // If current page don't give link, just text.
else{
echo("<a href=\"$PHP_SELF?query=$query&page=$ppage&limit=$limit\">$i</a> \n");}
}
if (!((($page+$limit) / $limit) >= $pages) && $pages != 1) { // If last page don't give next link.
$next_page = $page + $limit;
echo(" <a href=\"$PHP_SELF?query=$query&page=$next_page&limit=$limit\">next</a>\n");}
?>
The problem is when I submit the form with no input for city or state see a sample of the result I get.
Search Results for Business in
Results 1 - 10 of 22 Page 1 of 3
Results per-page: 5 | 10 | 20 | 50
Hibiscus Caribbean Restaurant
2105 Mastin Lake Road
Huntsville AL
USA
Restaurant
Bahama Breeze
101 Resource Center Parkway
Birmingham AL 35244
The problem is when i input a city and/or state I get the same result
Search Results for Business in Miami
Results 1 - 10 of 22 Page 1 of 3
Results per-page: 5 | 10 | 20 | 50
Hibiscus Caribbean Restaurant
2105 Mastin Lake Road
Huntsville AL
USA
Restaurant
Bahama Breeze
101 Resource Center Parkway
Birmingham AL 35244
I had a previous post on this and Syco suggest I use this
$q='SELECT * FROM caribbean WHERE ';
if(isset($search)){
$q.=" city = '$search' ";
}
if(isset($search1)){
if(isset($search)){
$q.=" OR ";
}
$q.=" state='$search1' ";
}
if(isset($search2)){
if(isset($search) || isset($search1)){
$q.=" OR ";
}
$q.=" zip ='$search2' ";
}
echo $q;
instead of this
$numresults = mysql_query("SELECT * FROM caribbean WHERE
city LIKE '%{$search}%' OR
state LIKE '%{$search1}%' OR
zip LIKE '%{$search2}%'"); // the query.
$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
But i never figure out how to implement it.