Modification to search script headache

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

Modification to search script headache

Post by genista »

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:

Code: Select all

<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.)&nbsp; <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 "&nbsp;<a href=\"$PHP_SELF?s=$prevs&county=$var\"><< 
  Prev 10</a>&nbsp&nbsp;";
  }
//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 "&nbsp;<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 to look like this:

Code: Select all

<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

If you need anything else, please let me know.


Thanks,


G
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

You definitly want to go back and change your db so you have a counties table with the counties in it and an integer primary key (probably an autonumber). Then in your suppliers table you want the county and the area covered fields to be integer foreign keys that link back to the counties table.

For your dropdown you would do a SELECT * FROM counties, then loop through the results displaying the county name with the value being your primary key.

For the results you want to left join suppliers against counties to get the county names (if you even need them) then do an integer comparison to find the matches.
genista
Forum Commoner
Posts: 57
Joined: Fri Aug 18, 2006 3:56 pm

Post by genista »

Thanks for the reply, however the database structure I can look at after I have solved this problem, I am still unable to change the script to change the array for counties into a simple drop down upon which you can search. At the moment if I simply put the drop down list in it works fine until it finds a macth in the database, then the page returns nothing, not even an error. When you run a search against a county that is not in the database it comes back with, "Sorry found nothing" which is what I would expect....


G
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

post your new code
Post Reply