Hi all,
I have a search script whereby a user can search for suppliers in a given county, the query looks for suppliers in that county along with suppliers that might cover that county. The problem I have at the moment is that my counties WERE stored as a numeric value in the database. Now though I have them stored as their actual value, ie: 'Surrey.' I have tried just changing the selection values but have got myself in a complete mess, so I am going back to square one and seeing how you think I can modify this script:
//Session stuff
//connect to database
<html>
<title><?php print $title ?></title>
<form name="form" action="<?=$_SERVER["PHP_SELF"]?>" method="get">
<p>County:
<name="county">
<?php
//$currentvalue0=$row['county'];
$counties = array('Aberdeenshire', 'Anglesey', 'Angus', 'Argyll', 'Avon', 'Ayrshire', 'Banffshire', 'Bedfordshire', 'Berkshire', 'Berwickshire', 'Borders', 'Buckinghamshire', 'Bute', 'Caithness', 'Cambridgeshire', 'Central Scotland', 'Cheshire', 'Clackmananshire', 'Cleveland', 'Clwyd', 'Cornwall', 'County Antrim', 'County Down', 'County Durham', 'County Fermanagh', 'County Londonderry', 'County Tyrone', 'Cumbria', 'Denbighshire', 'Derbyshire', 'Devon', 'Dorset', 'Dumfries and Galloway', 'Dunbartonshire', 'Durham', 'Dyfed', 'East Ayrshire', 'East Lothian', 'East Sussex', 'East Yorkshire', 'Edinburgh', 'Essex', 'Fife', 'Glamorgan', 'Gloucestershire', 'Grampian', 'Greater London', 'Greater Manchester', 'Guernsey', 'Gwent', 'Gwynedd', 'Hampshire', 'Herefordshire', 'Hertfordshire', 'Highlands and Islands', 'Humberside', 'Inverness-shire', 'Isle of Arran', 'Isle of Man', 'Isle of Skye', 'Isle of Wight', 'Jersey', 'Kent', 'Lanarkshire', 'Lancashire', 'Leicestershire', 'Lincolnshire', 'Lochaber', 'London', 'Londonderry', 'Lothian', 'Merseyside', 'Middlesex', 'Moray', 'Nottinghamshire', 'Orkneys', 'Outer Hebrides', 'Oxfordshire', 'Peeblesshire', 'Perthshire', 'Powys', 'Shropshire', 'Somerset', 'South Yorkshire', 'Staffordshire', 'Stirlingshire', 'Strathclyde', 'Suffolk', 'Surrey', 'Sutherland', 'Swansea', 'Tayside', 'Tyne and Wear', 'Warwickshire', 'West Lothian', 'West Midlands', 'West Sussex', 'West Yorkshire', 'Wester Ross', 'Wiltshire', 'Worcestershire');
echo '<select name="county"><option value="01" />Please Select a County';
for ($i=1; $i <= sizeof($counties); $i++)
{
$lz = strlen($i) == 1 ? '0'.$i : $i;
echo '<option value="'.$lz.'" selected />'.$counties[$i-1];
}
echo '</select>';
?>
</p>
<input type="submit" name="Submit" value="Search" />
</form>
<html>
<?php
$var = @$_GET['county'] ;
$county = trim($var); //trim whitespace from the stored variable
// rows to return
$limit=10;
// check for an empty string and display a message.
if ($county == "")
{
echo "<p>Please enter a search...</p>";
exit;
}
// check for a search parameter
if (!isset($var))
{
echo "<p>Please enter a search</p>";
exit;
}
/*$query = "select county, area_covered1, area_covered2, area_covered3, supplierid, username from suppliers where county =\"$county\" order by username"; */
$query = "select county
, area_covered1
, area_covered2
, area_covered3
, supplierid
, username
from suppliers
where '$county' in
( county
, area_covered1
, area_covered2
, area_covered3 )
order
by username";
$numresults=mysql_query($query) or die(mysql_error());
$numrows=mysql_num_rows($numresults);
if ($numrows == 0)
{
echo "<h4>Results</h4>";
echo "<p>Sorry, your search: "" . $county . "" returned zero results</p>";
}
// next determine if s has been passed to script, if not use 0
if (empty($s)) {
$s=0;
}
// get results
// line 60
$query .= " limit $s,$limit";
$result = mysql_query($query) or die("Couldn't execute query");
// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";
// begin to show results set
echo "Results: ";
$count = 1 + $s ;
// now you can display the results returned
while ($row= mysql_fetch_array ($result)) {
$title = $row ["username"];
$supplier = $row ["supplierid"];
echo "<p>$count.) <a href=\"supplierinfo.php?id=$supplier\">$title</a></p>" ;
$count++ ;
}
$currPage = (($s/$limit) + 1);
//80
//break before paging
echo "<br />";
// next we need to do the links to other results
if ($s>=1) { // bypass PREV link if s is 0
$prevs=($s-$limit);
print " <a href=\"$PHP_SELF?s=$prevs&county=$var\"><<
Prev 10</a>  ";
}
//line 90
// calculate number of pages needing links
$pages=intval($numrows/$limit);
// $pages now contains int of pages needed unless there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}
//line 100
// check to see if last page
if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$s+$limit;
echo " <a href=\"$PHP_SELF?s=$news&county=$var\">Next 10 >></a>";
}
//line 109
$a = $s + ($limit) ;
if ($a > $numrows) { $a = $numrows ; }
$b = $s + 1 ;
echo "<p>Showing results $b to $a of $numrows</p>";
?>
The array for counties will change into this:
<html>
<title><?php print $title ?></title>
<form name="form" action="<?=$_SERVER["PHP_SELF"]?>" method="get">
<tr><td>County:
<select size="1" name="county"
<option value="Aberdeenshire" <?php if (isset($_POST['county']) and $_POST['county'] == "Aberdeenshire") echo "selected"?>>Aberdeenshire</option>
<option value="Anglesey" <?php if (isset($_POST['county']) and $_POST['county'] == "Anglesey") echo "selected"?>>Anglesey</option>
//etc, etc
ANy ideas as I am really stuck and help would be much appreciated,
G
