Jump to content

Archived

This topic is now archived and is closed to further replies.

genista

Searching a different table

Recommended Posts

Hi all,

The following script is causing me some issues. First of all the user logs in and then goes to this page to search for suppliers in their area. The suppliers are stored in a seperate table - 'suppliers.' So the problem I have is that the search is not searching the suppliers table.

The second problem is that I have made the values for each county a number and the results return the number, I need it to display the county name, have tried different ways but nothing is working, here is the script:

[code=php:0]
<?php

include_once ("config.php");

?>

<html>
<form name="form" action="suppliersearch.php" method="get">
<tr><td>Search here for suppliers near you:</td><td><select size="1" name="q"
<option value="1">Aberdeenshire
<option value="1">Aberdeenshire
<option value="2">Anglesey
<option value="3">Angus
<option value="4">Argyll
<option value="5">Avon
<option value="6">Ayrshire
<option value="7">Banffshire
<option value="8">Bedfordshire
<option value="9">Berkshire
<option value="10">Berwickshire
<option value="11">Borders
<option value="12">Buckinghamshire   
<option value="13">Bute
<option value="14">Caithness   
<option value="15">Cambridgeshire
<option value="16">Central Scotland 
<option value="17">Cheshire
<option value="18">Clackmananshire
<option value="19">Cleveland
<option value="20">Clwyd
<option value="21">Cornwall
<option value="22">County Antrim
<option value="23">County Down
<option value="24">County Durham
<option value="25">County Fermanagh
<option value="26">County Londonderry
<option value="27">County Tyrone
<option value="28">Cumbria
<option value="29">Denbighshire
<option value="30">Derbyshire
<option value="31">Devon
<option value="32">Dorset
<option value="33">Dumfries & Galloway
<option value="35">Dunbartonshire
<option value="36">Durham
<option value="37">Dyfed
<option value="38">East Ayrshire
<option value="39">East Lothian
<option value="40">East Sussex
<option value="41">East Yorkshire
<option value="42">Edinburgh
<option value="43">Essex
<option value="44">Fife
<option value="45">Glamorgan
<option value="46">Gloucestershire
<option value="47">Grampian
<option value="48">Greater London
<option value="49">Greater Manchester
<option value="50">Guernsey
<option value="51">Gwent
<option value="52">Gwynedd
<option value="53">Hampshire   
<option value="54">Herefordshire
<option value="55">Hertfordshire   
<option value="56">Highlands & Islands
<option value="57">Humberside
<option value="58">Inverness-shire
<option value="59">Isle of Arran
<option value="60">Isle of Man
<option value="61">Isle of Skye
<option value="62">Isle of Wight
<option value="63">Jersey
<option value="64">Kent
<option value="65">Lanarkshire
<option value="66">Lancashire
<option value="67">Leicestershire
<option value="68">Lincolnshire
<option value="69">Lochabar
<option value="70">London
<option value="71">Londonderry
<option value="72">Lothian
<option value="73">Merseyside
<option value="74">Middlesex
<option value="75">Moray
<option value="76">Nottinghamshire
<option value="77">Orkneys
<option value="78">Outer Hebrides
<option value="79">Oxfordshire
<option value="80">Peebleshire
<option value="81">Perthshire
<option value="82">Powys
<option value="83">Shropshire
<option value="84">Somerset
<option value="85">South Yorkshire
<option value="86">Staffordshire
<option value="87">Stirlingshire
<option value="88">Strathclyde
<option value="89">Suffolk
<option value="90">Surrey
<option value="91">Sutherland
<option value="92">Swansea
<option value="93">Tayside
<option value="94">Tyne & Wear
<option value="95">Warwickshire
<option value="96">West Lothian
<option value="97">West Midlands
<option value="98">West Sussex
<option value="99">West Yorkshire
<option value="100">Wester Ross
<option value="101">Wiltshire
<option value="102">Worcestershire
</select></p>
<p>&nbsp;</p>
  <input type="submit" name="Submit" value="Search" />
</form>
<html>

<?php


checkLoggedIn("yes");
doCSS();
print("Welcome to the supplier search page <b>".$_SESSION["username"]."</b><br>");
print("<a href=\"logout.php?".session_name()."=".session_id()."\">Logout</a>");


  // Get the search variable from URL
  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10;

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>Please enter a search</p>";
  exit;
  }

//connect to your database ** EDIT REQUIRED HERE **
//mysql_connect("localhost","username","password"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
//mysql_select_db("databasename") or die("Unable to select database"); //select which database we're using

// Build SQL Query 
$query = "select * from suppliers where county = \"$trimmed\" 
  order by username"; // EDIT HERE and specify your table and field names for the SQL query

$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: &quot;" . $trimmed . "&quot; 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: &quot;" . $var . "&quot;</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 [print "<a href=\"supplier_info_page.php?id=$username\">$username</a><br>"];

  echo "$count.)&nbsp;$title" ;
  $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&q=$var\">&lt;&lt;
  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&q=$var\">Next 10 &gt;&gt;</a>";
  }
//line 109
$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
 
?>

[/code]
Any ideas.


Thanks

G

Share this post


Link to post
Share on other sites
I dont understand what the problem is relating to your tables and the field, suppliers, could you explain that some more?

As for the other problem, just change the value to the county name:
<option value="Aberdeenshire">Aberdeenshire</option>

Share this post


Link to post
Share on other sites
The code above doesnt search on the suppliers table, instead I get results from the users table.

The numeric values in the county are for security, but want I want is a way to get the details of the county name rather than the number.

Hope this explains...G

Share this post


Link to post
Share on other sites
Ok, so I can now get the first search result to be 'clickable' and passed onto supplierinfo.php page, however the second and subsequent search results don't even appear anymore off the following code. At the bottom of the page it says 'Displaying search results 1-2 of 2, but the 2nd isnt there:


[code=php:0]
<?php

include_once ("config.php");

?>

<html>
<form name="form" action="suppliersearch.php" method="get">
<tr><td>Search here for suppliers near you:</td><td><select size="1" name="q"
<option value="1">Aberdeenshire
<option value="1">Aberdeenshire
<option value="2">Anglesey
<option value="3">Angus
<option value="4">Argyll
<option value="5">Avon
<option value="6">Ayrshire
<option value="7">Banffshire
<option value="8">Bedfordshire
<option value="9">Berkshire
<option value="10">Berwickshire
<option value="11">Borders
<option value="12">Buckinghamshire   
<option value="13">Bute
<option value="14">Caithness   
<option value="15">Cambridgeshire
<option value="16">Central Scotland 
<option value="17">Cheshire
<option value="18">Clackmananshire
<option value="19">Cleveland
<option value="20">Clwyd
<option value="21">Cornwall
<option value="22">County Antrim
<option value="23">County Down
<option value="24">County Durham
<option value="25">County Fermanagh
<option value="26">County Londonderry
<option value="27">County Tyrone
<option value="28">Cumbria
<option value="29">Denbighshire
<option value="30">Derbyshire
<option value="31">Devon
<option value="32">Dorset
<option value="33">Dumfries & Galloway
<option value="35">Dunbartonshire
<option value="36">Durham
<option value="37">Dyfed
<option value="38">East Ayrshire
<option value="39">East Lothian
<option value="40">East Sussex
<option value="41">East Yorkshire
<option value="42">Edinburgh
<option value="43">Essex
<option value="44">Fife
<option value="45">Glamorgan
<option value="46">Gloucestershire
<option value="47">Grampian
<option value="48">Greater London
<option value="49">Greater Manchester
<option value="50">Guernsey
<option value="51">Gwent
<option value="52">Gwynedd
<option value="53">Hampshire   
<option value="54">Herefordshire
<option value="55">Hertfordshire   
<option value="56">Highlands & Islands
<option value="57">Humberside
<option value="58">Inverness-shire
<option value="59">Isle of Arran
<option value="60">Isle of Man
<option value="61">Isle of Skye
<option value="62">Isle of Wight
<option value="63">Jersey
<option value="64">Kent
<option value="65">Lanarkshire
<option value="66">Lancashire
<option value="67">Leicestershire
<option value="68">Lincolnshire
<option value="69">Lochabar
<option value="70">London
<option value="71">Londonderry
<option value="72">Lothian
<option value="73">Merseyside
<option value="74">Middlesex
<option value="75">Moray
<option value="76">Nottinghamshire
<option value="77">Orkneys
<option value="78">Outer Hebrides
<option value="79">Oxfordshire
<option value="80">Peebleshire
<option value="81">Perthshire
<option value="82">Powys
<option value="83">Shropshire
<option value="84">Somerset
<option value="85">South Yorkshire
<option value="86">Staffordshire
<option value="87">Stirlingshire
<option value="88">Strathclyde
<option value="89">Suffolk
<option value="90">Surrey
<option value="91">Sutherland
<option value="92">Swansea
<option value="93">Tayside
<option value="94">Tyne & Wear
<option value="95">Warwickshire
<option value="96">West Lothian
<option value="97">West Midlands
<option value="98">West Sussex
<option value="99">West Yorkshire
<option value="100">Wester Ross
<option value="101">Wiltshire
<option value="102">Worcestershire
</select></p>
<p>&nbsp;</p>
  <input type="submit" name="Submit" value="Search" />
</form>
<html>

<?php


checkLoggedIn("yes");
doCSS();
print("Welcome to the supplier search page <b>".$_SESSION["username"]."</b><br>");
print("<a href=\"logout.php?".session_name()."=".session_id()."\">Logout</a>");


  // Get the search variable from URL
  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable

// rows to return
$limit=10;

// check for an empty string and display a message.
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

// check for a search parameter
if (!isset($var))
  {
  echo "<p>Please enter a search</p>";
  exit;
  }

//connect to your database ** EDIT REQUIRED HERE **
//mysql_connect("localhost","username","password"); //(host, username, password)

//specify database ** EDIT REQUIRED HERE **
//mysql_select_db("databasename") or die("Unable to select database"); //select which database we're using

// Build SQL Query 
$query = "select * from suppliers where county = \"$trimmed\" 
  order by username"; // EDIT HERE and specify your table and field names for the SQL query

$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: &quot;" . $trimmed . "&quot; 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: &quot;" . $var . "&quot;</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"];

  echo "<p>$count.)&nbsp; <a href=\"supplierinfo.php?id=$title\>$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&q=$var\">&lt;&lt;
  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&q=$var\">Next 10 &gt;&gt;</a>";
  }
//line 109
$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
 
?>

[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.