Searching

genista

Hi all,

I think I may have posted on searching before, but I have a new problem with searching my database and am stumped on it.

The following code is searching a table for a list of suppliers in the given county, I have it executing without any errors, but it does not return any search results, despite there being records in the database which should come up. I know it is something to do with the 'trimmed' part in bold as I can run successful queries in the database.

Any ideas?

<?php

include_once ("config.php");

?>

<html>
<form name="form" action="suppliersearch.php" method="get">
  <input type="text" name="q" />
  <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>We dont seem to have a search parameter!</p>";
  exit;
  }

// Build SQL Query  

$query = "select * from suppliers where county =[B] \"%$trimmed%\"[/B]  

  order by county"; // 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["county"];

  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>";

?>

etully

Here's how to troubleshoot it. Right after you build $query, write:

print "$query";

This way, you can see what PHP is trying to run at MySQL. Compare the query to the one you are running manually and see how they are different.

On a different topic, your code has a serious security flaw. It's vulnerable to a SQL injection.

Think about it this way. Imagine that you stood on the street with a plate and a sign that said, "Place Food On Plate". Would you blindly eat whatever was on the plate without looking at it? No, anyone could put anything they want on the plate - including non-foods.

SQL injections work by passing in code that you will blindly execute at mysql. For example, Imagine if I searched on the following string:

nothing%"; delete from suppliers; select * from suppliers where county="%nothing

Read it carefully. That line is what gets set as $trimmed so when you build $query, the query string will look like this:

select from suppliers where county="%nothing%"; delete from suppliers; select from suppliers where county="%nothing%\" order by county";

So to prevent SQL injections, you need to make sure that $q adheres to whatever you expect the person to be submitting.

genista

Ok thanks for that, once I get this working the search on county will be defined by a drop down rather than free text, which should hopefully help with the security?

As for the query, the one I run in the database is on a specific county, ie select * from suppliers where county = 'countyname'

However, in the form I am searching on the trimmed value and I believe this is the problem...

etully

And what are the results of the print "$query"; test so that we can see what is actually being sent to the database?

As for the security problem, the odds are very low that you will be hacked. I only mentioned the problem because I wanted to help you develop good habits. Technically, a drop down will not solve the problem. If a hacker was determined to hack you, all they would have to do is save the HTML, edit the values in the drop down menu, and then Post the altered form to your site.

From a security perspective, the best way to handle the problem is to number your countries from 1-115. Then your drop down menu has the countries and the values 1-115. Then, in your PHP script, you check to see if $q >0 and also that $q < 116. You could even check to see if $q has any text in it. If so, then exit();

That way, if I inject some SQL, I get nowhere.

Again, the odds are one in a million that you'll get hacked. Until you write for some high profile web site. Then the hackers are going to be scouring through your code trying to find ways to bring the big site to its knees so it's better to learn the right way to do it now, when the stakes are lower.

genista

Ok, I found the problem, it was the two % marks, I have taken them out and now I get the search results.

Now I get the results that I want, but I am curious now as to how to be able to click on the results and have information displayed in another page about the supplier.

I also know I have to get into security as the site I am working on will be big as you say and I can't ignore the threat of security!

I know its another question, but how could I get numbers into the form relating to the name of the county?

genista

Just re-reading your post are you saying I should put a number next to each county in the drop down?

etully

Since you say you are going to use a drop down box for county, that tells me that you have a fixed number of counties. Maybe 1-100 or something like that. So in your database change the "county" field to int. Then change the names from things like, "Dade County" and "Miller County" to numbers like 17 and 35.

Then your drop down list should look like this:
<select name="q">
<option value="17">Dade County
<option value="35">Miller County
etc...
</select>

This way, when they select "Miller County", they will be selecting "35" and that's all that gets sent to your PHP search script. Then the query looks like this:

select * from suppliers where county=35.

This way, it's easy to see if $q >0 and $q<116. If it's in that range and there are no text characters, then you KNOW FOR CERTAIN that there is no SQL injection.

genista

Ok thanks, I'll give it a try...

genista

I also wanted to ask again, how I could have a user click on the search results and then be presented the relevant information in a new page, I have searched and searched on this (so I am not just trying to get you guys to do all the work) but I am stuck as to how to attempt this. Any scripts out there that might help or ideas?

etully

When you perform a search and you get multiple responses back, there will be something unique about each line. For example, if you search on "county" and you get 20 suppliers who operate in that county, you will probably have each supplier's Name, Phone, Email, and their ID#.

In the while loop where you print the list of suppliers in that county, your print statement might look something like this:

print "$name, $phone, $email ";

You know the ID number of each supplier (for example, maybe it's in a variable called $id), then you can make the name a hot link like this:

print "<a href=\"supplier_info_page.php?id=$id\">$name</a>, $phone, $email ";

So you can see that the name is wrapped in an <a href> tag that links to a file called "supplier_info_page.php" but also, the ID# gets passed to that page.

If you have 20 suppliers who match for a certain county, you will get a list of 20 suppliers, names, phones, and emails. But each name will be hyperlinked with a different value for $id

So then you'll have to make a page called supplier_info_page.php that displays the details about a supplier. And on that page, you will have a query that looks like this:

$id = $_GET['id'];
$query = "select * from suppliers where id=$id";

genista

Thank you for your help, I will try it and let you know!

genista

Ok, I have tried it and manage to get the results to print (see code below in bold), however the problem is that the user is searching a suppliers table. The user needs to be logged in to use this search and of course when they search currently they are searching the user table, not the suppliers table.

As you have suggested I have changed the values to numbers as suggested, but of course the search results display the number instead of the county name, this I will need to sort as well..

<?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)) {
[B]  $title = $row [print "<a href=\"supplier_info_page.php?id=$username\">$username</a><br>"];[/B]

  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>";

?>

Thanks

genista

Do I need to provide details of the table, ie create a connection or should this run fine providing the sql query is done properly?

genista

On closer inspection the search results from this script are actually displaying the username and password of the user that is logged in to do the search, when the query is:

$query = "select * from suppliers where county = \"$trimmed\"

order by username";

etully

Make sure that you are passing in the desired ID and make sure that you are selecting from the desired table.

genista

Ok I have gone back to the beginning and have it now searching on the right table, but am back to square one in terms of trying to click on the search results and then go to a page to review each supplier.

etully suggested entering something like the following:

print "<a href=\"supplier_info_page.php?id=$id\">$name</a>, $phone, $email ";

I somehow have to get this type of code into the following code relating to the display of the search results:

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

etully

Please re-read my post from 07-10-2006, 02:04 PM

It explains as clearly as I can what you need to do.

genista

Ok nearly there!
I can get the results to display with the href, the first result displays properly, but the second one is 'mixed up' with the line of code that displays the 'showing results 1 to 2 of 2,' see below:

Results:
1.) test4 - This is shown as a link

Showing results 1 to 2 of 2 - This links to the second result from the query, ie instead of linking to a 'next page' it is linking to the next supplier from the query and the next result should be displayed underneath number 1, ie 2.) usernamex

Here is the code from the results down, the two problem parts are in Bold:

// now you can display the results returned
  while ($row= mysql_fetch_array ($result)) {
  $title = $row ["username"];

[B]  echo "<p>$count.)&nbsp; <a href=\"supplierinfo.php?id=$title\>$title</p>" ;[/B]
  $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 ;
[B]  echo "<p>Showing results $b to $a of $numrows</p>";[/B]

?>

I have tried so many changes on here, but just can't fix it!

thanks,

etully

The very last line of your code reads: Showing results $b to $a of $numrows
and it doesn't have <a> </a> around it.... so it can't possibly be linking to anything (right or wrong). There's no way.

I don't understand what's broken.

genista

That last line and the text "showing results... have become a link to the second supplier that is returned. What it should do instead is show the second supplier like so:

2.) username2

Before I added:

echo "$count.)  <a href=\"supplierinfo.php?id=$title>$title" ;

This worked fine, it would display each result in a list, but the results are not 'clickable'