Search results problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

Search results problem

Post 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>";
  }
?>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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=..."
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

Post 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?......
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Change $country to the integer value of $_GET['country']?
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

Post 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!
Post Reply