show list based on which county has been clicked

jarv · January 13, 2011

hi, at the moment, my page only works with the County: Angus (2nd in the list)

List of Counties County List

The code for my Pubs page is below...,

I think I need to change the hard coded bit that reads:

echo "<ul title=\"Pubs in Angus\" id=\"Angu\">";

and the SQL string:

$query  = "SELECT * FROM pubs WHERE rsCounty = 'Angus' LIMIT $offset, $rowsPerPage";

pubs page

<?php
include_once("config.php");
include_once("functions.php");
// Check user logged in already:
checkLoggedIn("no");
//doCSS(); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>My Pub Space</title>
<link rel="stylesheet" type="text/css" href="stylesheets/style1.css" title="default" />
<meta name="viewport" content="width=device-width; initial-scale=1.0; minimum-scale=1.0; maximum-scale=1.0; user-scalable=0;"/>
	<link rel="apple-touch-icon" href="../iui/iui/mps-icon.png" />
	<style type="text/css" media="screen">@import "../iui/iui/iui.css";</style>
		<script type="application/x-javascript" src="../iui/iui/iui.js"></script>
	<meta name="apple-touch-fullscreen" content="YES" />
<script type="text/javascript" src="js/jva.js"></script>

</head>

<body>
<?
$offset = (isset($_GET['start'])) ? (int)$_GET["start"] : 0;
$rowsPerPage = (isset($_GET['count'])) ? (int)$_GET["count"] : 10; 
$query  = "SELECT * FROM pubs WHERE rsCounty = 'Angus' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die(mysql_error().'<br>SQL: ' . $query); 


//looping counties
$query1  = "SELECT rsCounty, COUNT(PUBID) AS County_Count FROM pubs GROUP BY rsCounty";
$result1 = mysql_query($query1) or die(mysql_error().'<br>SQL: ' . $query1);  
$County1 = $result1['rsCounty'];
$CountyCount = $result1['County_Count'];
?>
<div class="toolbar">
        <h1 id="pageTitle">Select County</h1>
        <a id="backButton" class="button" href="#"></a>
        <a class="button" href="logout.php" target="_self">Logout</a>
    </div>
 <ul title="Select County" id="county" selected="true">
<?php
while ($row = mysql_fetch_assoc($result1)){
$RSCOUNTY1 = $row['rsCounty'];
$RSCOUNTY1short = substr($row['rsCounty'],0,4);
$CountyCount = $row['County_Count'];
echo <<<EOF
  <li><a href="#$RSCOUNTY1" class="digg-count">$CountyCount</a>
  <a href="#$RSCOUNTY1short">$RSCOUNTY1</a></li>
EOF;
}
echo "</ul>";

// start East Sussex
echo "<ul title=\"Pubs in Angus\" id=\"Angu\">";
while($row = mysql_fetch_array($result)){
$PUBID = $row['PUBID'];
$rsPubName = $row['rsPubName'];
$rsAddress = $row['rsAddress']; 
$rsPostCode = $row['rsPostCode'];
$rsTel = $row['rsTel'];
$rsTown = $row['rsTown'];
$rsCounty = $row['rsCounty'];
// how many rows we have in database
// print the link to access each page

$self = $_SERVER['PHP_SELF'];
$next = "<li><a href=\"all.php?start=" . ($offset + $rowsPerPage) . "&count={$rowsPerPage}\" target=\"_replace\">View More</a></li>";
//div container of header and information
echo <<<EOF
  <li><a href="viewpub.php?PUBID=$PUBID">$rsPubName</a></li>
EOF;
if	($_SESSION["RSUSER"] == "admin") 
{
echo "<a href=\"edit.php?PUBID=$PUBID\" class=\"small\">edit this pub</a>";
}
}
echo $next;
echo "</ul>";
// End East Sussex


?>
</body>
</html>

Please help?!

jarv · January 14, 2011

I just changed some bits,

I put this bit in near the top:

$CountyName = $_REQUEST['CountyShort'];

changed my SQL string to:

$query  = "SELECT * FROM pubs WHERE rsCounty = $CountyName LIMIT $offset, $rowsPerPage";

changed my links to:

<a href="pubs.php?CountyShort=$RSCOUNTY1short&County=$RSCOUNTY1">$RSCOUNTY1</a>

and my UL ID to:

echo "<ul title=\"Pubs in Angus\" id=\"$CountyName\">";

now I get: 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 'LIMIT 0, 10' at line 1

SQL: SELECT * FROM pubs WHERE rsCounty = LIMIT 0, 10

Please help?!

jarv · January 14, 2011

I think it should just be a case of passing through the $RSCOUNTY1short and filtering it in the SQL and then showing it in the UL ID?!

denno020 · January 14, 2011

Your links don't work for starters,

As for the SQL syntax problem, you can only have one LIMIT value. Get rid of the comma and the extra value after that.

Post back with what that does.

Denno

jarv · January 14, 2011

thanks, not sure why the links were not working?! maybe I needed the '#' at the start?!

I have changed it back so that now it works but you can only click on 'East Sussex', Also, the Limit has 2 values after it and works.

<?php
include_once("config.php");
include_once("functions.php");
$ebits = ini_get('error_reporting');
error_reporting($ebits ^ E_NOTICE);
// Check user logged in already:
checkLoggedIn("no");
//doCSS(); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>My Pub Space</title>
<meta name="viewport" content="width=device-width; initial-scale=1.0; minimum-scale=1.0; maximum-scale=1.0; user-scalable=0;"/>
	<link rel="apple-touch-icon" href="../iui/iui/mps-icon.png" />
	<style type="text/css" media="screen">@import "../iui/iui/iui.css";</style>
		<script type="application/x-javascript" src="../iui/iui/iui.js"></script>
	<meta name="apple-touch-fullscreen" content="YES" />
<script type="text/javascript" src="js/jva.js"></script>
<style type="text/css">

body > ul > li {
    font-size: 14px;
}

body > ul > li > a {
    padding-left: 54px;
    padding-right: 40px;
    min-height: 34px;
}

li .digg-count {
    display: block;
    position: absolute;
    margin: 0;
    left: 6px;
    top: 7px;
    text-align: center;
    font-size: 110%;
    letter-spacing: -0.07em;
    color: #93883F;
    font-weight: bold;
    text-decoration: none;
    width: 36px;
    height: 30px;
    padding: 7px 0 0 0;
    background: url(shade-compact.gif) no-repeat;
}

h2 {
    margin: 10px;
    color: slateblue;
}

p {
    margin: 10px;
}
</style>
</head>

<body>
<?
$CountyName = $_REQUEST['CountyShort'];


$offset = (isset($_GET['start'])) ? (int)$_GET["start"] : 0;
$rowsPerPage = (isset($_GET['count'])) ? (int)$_GET["count"] : 10; 
$query  = "SELECT * FROM pubs WHERE rsCounty = 'East Sussex' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die(mysql_error().'<br>SQL: ' . $query); 


//looping counties
$query1  = "SELECT rsCounty, COUNT(PUBID) AS County_Count FROM pubs GROUP BY rsCounty";
$result1 = mysql_query($query1) or die(mysql_error().'<br>SQL: ' . $query1);  
$County1 = $result1['rsCounty'];
$CountyCount = $result1['County_Count'];
?>
<div class="toolbar">
        <h1 id="pageTitle">Select County</h1>
        <a id="backButton" class="button" href="#"></a>
        <a class="button" href="logout.php" target="_self">Logout</a>
    </div>
 <ul title="Select County" id="county" selected="true">
<?php
while ($row = mysql_fetch_assoc($result1)){
$RSCOUNTY1 = $row['rsCounty'];
$RSCOUNTY1short = substr($row['rsCounty'],0,4);
$CountyCount = $row['County_Count'];
echo <<<EOF
  <li><a href="#$RSCOUNTY1short" class="digg-count">$CountyCount</a>
  <a href="#$RSCOUNTY1short">$RSCOUNTY1</a></li>
EOF;
}
echo "</ul>";

// start East Sussex
echo "<ul title=\"Pubs in Angus\" id=\"$CountyName\">";
while($row = mysql_fetch_array($result)){
$PUBID = $row['PUBID'];
$rsPubName = $row['rsPubName'];
$rsAddress = $row['rsAddress']; 
$rsPostCode = $row['rsPostCode'];
$rsTel = $row['rsTel'];
$rsTown = $row['rsTown'];
$rsCounty = $row['rsCounty'];
// how many rows we have in database
// print the link to access each page

$self = $_SERVER['PHP_SELF'];
$next = "<li><a href=\"all.php?start=" . ($offset + $rowsPerPage) . "&count={$rowsPerPage}\" target=\"_replace\">View More</a></li>";
//div container of header and information
echo <<<EOF
  <li><a href="viewpub.php?PUBID=$PUBID">$rsPubName - $rsTown, $rsCounty</a>
EOF;
if	($_SESSION["RSUSER"] == "admin") 
{
echo "<a href=\"edit.php?PUBID=$PUBID\" class=\"small\">edit this pub</a>";
}
}
echo "</li>";
echo $next;
echo "</ul>";
// End East Sussex


?>
</body>
</html>

jarv · January 16, 2011

can anyone please help me here?

jarv · January 16, 2011

here is the link

http://www.mypubspace.com/iphone/pubs/default.php#___1__

user: test

password: test

if you click on Aberdeenshire this is the only one that works, I have set it up manually

here is my current code with Aberdeenshire working

<?php
include_once("config.php");
include_once("functions.php");
// Check user logged in already:
checkLoggedIn("yes");
//doCSS(); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>My Pub Space</title>
<link rel="stylesheet" type="text/css" href="stylesheets/style1.css" title="default" />
<meta name="viewport" content="width=device-width; initial-scale=1.0; minimum-scale=1.0; maximum-scale=1.0; user-scalable=0;"/>
	<link rel="apple-touch-icon" href="../iui/iui/mps-icon.png" />
	<style type="text/css" media="screen">@import "../iui/iui/iui.css";</style>
		<script type="application/x-javascript" src="../iui/iui/iui.js"></script>
	<meta name="apple-touch-fullscreen" content="YES" />
<script type="text/javascript" src="js/jva.js"></script>

</head>

<body>
<?
$CountyName = filter_input(INPUT_GET, 'CountyName', FILTER_SANITIZE_STRING);

$offset = (isset($_GET['start'])) ? (int)$_GET["start"] : 0;
$rowsPerPage = (isset($_GET['count'])) ? (int)$_GET["count"] : 10; 
$query  = "SELECT * FROM pubs WHERE rsCounty = 'Aberdeenshire' LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die(mysql_error().'<br>SQL: ' . $query); 


//looping counties
$query1  = "SELECT rsCounty, COUNT(PUBID) AS County_Count FROM pubs GROUP BY rsCounty";
$result1 = mysql_query($query1) or die(mysql_error().'<br>SQL: ' . $query1);  
$County1 = $result1['rsCounty'];
$CountyCount = $result1['County_Count'];
?>
<div class="toolbar">
        <h1 id="pageTitle">Select County</h1>
        <a id="backButton" class="button" href="#"></a>
        <a class="button" href="logout.php" target="_self">Logout</a>
    </div>
 <ul title="Select County" id="county" selected="true">
<?php
while ($row = mysql_fetch_assoc($result1)){
$RSCOUNTY1 = $row['rsCounty'];
$RSCOUNTY1short = substr($row['rsCounty'],0,4);
$CountyCount = $row['County_Count'];
echo <<<EOF
  <li><a href="#$RSCOUNTY1short"  class="digg-count">$CountyCount</a>
  <a href="#$RSCOUNTY1short">$RSCOUNTY1</a></li>
EOF;
}
echo "</ul>";

// start East Sussex
echo "<ul title=\"Aberdeenshire\" id=\"Aber\">";
while($row = mysql_fetch_array($result)){
$PUBID = $row['PUBID'];
$rsPubName = $row['rsPubName'];
$rsAddress = $row['rsAddress']; 
$rsPostCode = $row['rsPostCode'];
$rsTel = $row['rsTel'];
$rsTown = $row['rsTown'];
$rsCounty = $row['rsCounty'];
// how many rows we have in database
// print the link to access each page

$self = $_SERVER['PHP_SELF'];
$next = "<li><a href=\"all.php?start=" . ($offset + $rowsPerPage) . "&count={$rowsPerPage}\" target=\"_replace\">View More</a></li>";
//div container of header and information
echo <<<EOF
  <li><a href="viewpub.php?PUBID=$PUBID">$rsPubName</a></li>
EOF;
if	($_SESSION["RSUSER"] == "admin") 
{
echo "<a href=\"edit.php?PUBID=$PUBID\" class=\"small\">edit this pub</a>";
}
}
echo $next;
echo "</ul>";
// End East Sussex



// start London
echo "<ul title=\"Pubs in London\" id=\"Lond\">";
while($row = mysql_fetch_array($result2)){
$PUBID = $row['PUBID'];
$RSPUBNAME = $row['RSPUBNAME'];
$RSADDRESS = $row['RSADDRESS']; 
$RSPOSTCODE = $row['RSPOSTCODE'];
$RSTEL = $row['RSTEL'];
$RSTOWN = $row['RSTOWN'];
$RSCOUNTY = $row['RSCOUNTY'];
// how many rows we have in database
// print the link to access each page

$self = $_SERVER['PHP_SELF'];
$next = "<li><a href=\"all2.php?start=" . ($offset + $rowsPerPage) . "&count={$rowsPerPage}\" target=\"_replace\">View More</a></li>";
//div container of header and information
echo <<<EOF
  <li><a href="viewpub.php?PUBID=$PUBID">$RSPUBNAME</a></li>
EOF;
if	($_SESSION["RSUSER"] == "admin") 
{
echo "<a href=\"edit.php?PUBID=$PUBID\" class=\"small\">edit this pub</a>";
}
}
echo $next;
echo "</ul>";
// End London


?>
</body>
</html>

jarv · January 17, 2011

please help

jarv · January 17, 2011

can anyone help me here?

Sign In

show list based on which county has been clicked

Recommended Posts

jarv

Link to comment

Share on other sites

jarv

Link to comment

Share on other sites

jarv

Link to comment

Share on other sites

denno020

Link to comment

Share on other sites

jarv

Link to comment

Share on other sites

jarv

Link to comment

Share on other sites

jarv

Link to comment

Share on other sites

jarv

Link to comment

Share on other sites

jarv

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information