Page 1 of 1

Search results problem

Posted: Mon Oct 16, 2006 2:45 pm
by genista
Hi all,

I have a script that allows you to search for suppliers in a given county. I have the counties stored as numeric values in the database. The problem comes with trying to change the numeric value into the name of the county I can get the county name returned, but no search results, even though there are county values in the database:

Code: Select all

<?php

<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(1=>'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++) 
{ */
 foreach($counties as $_key => $_county)
  {
  echo '<option value="'.sprintf("%03d",$_key).'"/>'.$_county.'</option>';
  //echo '<option value="'.($_key < 0 ? '0'.$_key : $_key).'"/>'.$_county.'</option>';
}  
//}
 /* $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>


  if (isset($_GET['county'])) { 
$var = $counties[(int)$_GET['county']] ;

$county = trim( $counties[ intval($_GET['county']) ] );
//$var= $county;
// rows to return
$limit=10; 

// check for an empty string and display a message.
if ($var == "")
  {
  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: "" . /*$counties[$_GET['county']]*/$var . "" returned zero results</p>";

 }

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results

  $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.)&nbsp; <a href=\"supplierinfo.php?id=$supplier\">$title</a></p>" ;

  $count++ ;
  }

$currPage = (($s/$limit) + 1);

//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 "&nbsp;<a href=\"$PHP_SELF?s=$prevs&county=$var\"><< 
  Prev 10</a>&nbsp&nbsp;";
  }

// 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++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo "&nbsp;<a href=\"$PHP_SELF?s=$news&county=$var\">Next 10 >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  }
?>

Posted: Mon Oct 16, 2006 2:57 pm
by feyd
Have you echoed out $query? Did you check it against the database directly?

Be aware that $PHP_SELF and its synonyms contain user input and therefore can lead to compromizes in your site's security and theft of user data. Form submissions to the form's own page can be sent to "#"; the latter usage of the variable can be removed to only be "?s=..."

Posted: Mon Oct 16, 2006 4:21 pm
by genista
Here is the result:

select county , area_covered1 , area_covered2 , area_covered3 , supplierid , username from suppliers where 'Angus' in ( county , area_covered1 , area_covered2 , area_covered3 ) order by username

as you can see it is searching for angus, as opposed to its numeric value, it needs to search on numeric value then display the county name. In this instance it wont find anything.

Great! I think we have a result, now how to fix it?......

Posted: Mon Oct 16, 2006 4:37 pm
by feyd
Change $country to the integer value of $_GET['country']?

Posted: Mon Oct 16, 2006 4:46 pm
by genista
Do you mean in this query?

Code: Select all

$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); 
  
echo "$query";
if ($numrows == 0)
Thanks for the reply!