[mattrmclaren]

On a page in my site I have included a search bar where a user can search our database for jobs. The HTML of the search bar is as follows:

<form id="form1" name="form" method="get" action="search.php">
              <span>
              <input name="q" type="s" class="keywords" id="textfield" maxlength="75" value="Search..." />
              </span>
              <input name="b" type="image" src="images/search.gif" class="button" />
            </form>

I am having a number of issues, namely because I am an amateur. The mySQL database has data stored like this: company, title, description, industry. In this example, they are searching by company. I want the user to be able to type in facebook and all job listings at Facebook will show up. Also, the way it is now, if I capitalize Facebook it won't show up or if I include words other than Facebook no results will be returned. I'd like the user to be able to type in, get a job at Facebook and it will still show up. How can I correct this?

And here's the biggest issue. When the user clicks search, they're taken to the PHP page (which you'll see below) which displays some basic text like this:

You searched for: "facebook"

Results
1.) Work at Facebook
Showing results 1 to 1 of 1

How do I get this to show up in the results of the original page. Meaning, still use the PHP to process it, but show the results on the page the search bar is on. Even if this involves going to another page that looks exactly like it, I'm fine with it. It doesn't have to be AJAX.

The solution is probably very simple but I'm clueless. Any help would be sincerely appreciated.

And the PHP to process the request is as:

<?php

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

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

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

// Build SQL Query  
$query = "select link, company, title, industry from software where company like \"%$trimmed%\"  
  order by link"; // EDIT HERE and specify your table and field names for the SQL query

 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

// If we have no results, offer a google search as an alternative

// 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: &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["link"];

  echo "$count.)&nbsp;$title" ;
  $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&q=$var\">&lt;&lt; 
  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&q=$var\">Next 10 &gt;&gt;</a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<p>Showing results $b to $a of $numrows</p>";
  
?>

[CTphpnwb]

Here are two things that you might not think are important, but if you learn to use them will make your projects a lot easier:

• indenting.
  
• Functions.

[macosxnerd101]

A couple other things. First, simply trimming the element from $_GET is not enough to sanitize it to secure against SQL Injection. You should at least use mysql_real_escape_string(). Ideally, you should use Prepared Statements like PDO, which are immune to SQL Injection. Second, there is no $PHP_SELF variable. It is $_SERVER['PHP_SELF'], and that is insecure. Just type the name of the page you are getting or posting to.

[calebjonasson]

I'm just going to throw a little snippet out there that uses mysqli. This is going to give you a similar result but it is going to be safe and will no longer give you issues with case sensitive searching.

<php

//vars
$limit = 10;
$search = "%" . $_GET['q'] . "%";
$resultSet = null;

//set up your connection here.
$mysqli = new ...

$sql = "SELECT link, company, title, industry
    FROM software
    WHERE lower(company) LIKE LOWER(?)
    LIMIT ?";

if($stmt = $mysqli->prepare($sql))
{
    $stmt->bind_param('si', $search, $limit);
    if($stmt->execute())
    {
        $stmt->store_result();
        if($stmt->num_rows >= 1)
        {
            $resultSet = array();

            $stmt->bind_result($link, $company, $title, $industry);
            while($stmt->fetch())
            {
                $resultSet[] = array('link' => $link, 'company' => $company, 'title' => $title, 'industry' => $industry);
            }
        }
    }else{
        //else the stmt threw an error.
        echo $stmt->error;
    }
}else{
    //else the connection threw an error
    echo $mysqli->error;
}

//display results
if(is_array($resultSet))
{
    print_r($resultSet);
}
?>

You should read more on prepared data objects or the mysqli class to make sure that your code is sanitized and working without any problems.

mysqli: http://php.net/manua...book.mysqli.php

This post has been edited by calebjonasson: 28 October 2011 - 11:51 AM

[CapC]

macosxnerd101, on 27 October 2011 - 07:43 AM, said:

simply trimming the element from $_GET is not enough to sanitize it to secure against SQL Injection. Ideally, you should use Prepared Statements like PDO, which are immune to SQL Injection.

This is good advice. Following it will likely save you A LOT of woes.