genista Posted July 11, 2006 Share Posted July 11, 2006 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]<?phpinclude_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> </p> <input type="submit" name="Submit" value="Search" /></form><html><?phpcheckLoggedIn("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 parameterif (!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: "" . $trimmed . "" 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 forecho "<p>You searched for: "" . $var . ""</p>";// begin to show results setecho "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.) $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 " <a href=\"$PHP_SELF?s=$prevs&q=$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&q=$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>"; ?>[/code]Any ideas.ThanksG Quote Link to comment https://forums.phpfreaks.com/topic/14309-searching-a-different-table/ Share on other sites More sharing options...
GingerRobot Posted July 11, 2006 Share Posted July 11, 2006 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> Quote Link to comment https://forums.phpfreaks.com/topic/14309-searching-a-different-table/#findComment-56304 Share on other sites More sharing options...
genista Posted July 11, 2006 Author Share Posted July 11, 2006 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 Quote Link to comment https://forums.phpfreaks.com/topic/14309-searching-a-different-table/#findComment-56377 Share on other sites More sharing options...
genista Posted July 12, 2006 Author Share Posted July 12, 2006 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]<?phpinclude_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> </p> <input type="submit" name="Submit" value="Search" /></form><html><?phpcheckLoggedIn("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 parameterif (!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: "" . $trimmed . "" 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 forecho "<p>You searched for: "" . $var . ""</p>";// begin to show results setecho "Results: ";$count = 1 + $s ;// now you can display the results returned while ($row= mysql_fetch_array ($result)) { $title = $row ["username"]; echo "<p>$count.) <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 " <a href=\"$PHP_SELF?s=$prevs&q=$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&q=$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>"; ?>[/code] Quote Link to comment https://forums.phpfreaks.com/topic/14309-searching-a-different-table/#findComment-56807 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.