[RESOLVED] Display PHP Search Results On New Line/Searching More then One Table Field

[rach99]

Hi All,

I have a search script which works fine. I just have a problem when a search is made that displays more than one result as all the results appear on 1 line with out spaces between them.

I would like to know how to display each result if there is more than one, on seperate lines. At the moment it looks like this:

1) resultoneresulttworesult3 - (for example if i had searched for 'result' as keyword.

I would like to know how to display them like this.

1) result 1
2) result 2
3) result 3

There are 4 rows in my table, ID, name, address, suburb, link

I only want to display name, but when i type in a suburb for example it lists all the names of the people in the suburb. so the results always only show the name. I just would like them to appear on seperate lines.

Also at the moment all i get results from is by searching by name, how would i make is so if a visitor typed in a suburb or address it would show those results too? I tried duplicating this:

// Build SQL Query  

$query = "SELECT * FROM searchtable where name like \"%$trimmed%\"  

  ORDER BY name"; // EDIT HERE and specify your table and field names for the SQL query

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

which worked, but i figure there must be a way to make this all one line using 'like'.

Copy of the code below:

<?php

  // Connect to the database server  

  $dbcnx = @mysql_connect("localhost", "username", "password");  

  if (!$dbcnx) {    

  echo( "<P>Unable to connect to the " .          

  "database server at this time.</P>" );    

  exit();  

  }

  // Select the database  

  if (! @mysql_select_db("databasename") ) {    

  echo( "<P>Unable to locate the databasename " .          

  "database at this time.</P>" );    

  exit();  

  }

?>

<P>Welcome to example</P>

<?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","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 searchtable where name like \"%$trimmed%\"  

  ORDER BY name"; // 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

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

// google
 echo "<p><a href=\"http://www.google.com/search?q=" 
  . $trimmed . "\" target=\"_blank\" title=\"Look up 
  " . $trimmed . " on Google\">Click here</a> to try the 
  search on google</p>";
  }

// 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 "<p>Results</p>";
$count = 1 + $s ;

// now you can display the results returned
    echo "$count.)&nbsp;$title " ;
  $count++ ;

    while ($row = mysql_fetch_array
	($result)) {
                   $link = $row['link'];
                   $link_title = $row['name'];

            echo "<a href=\"".$link."\">".$link_title."</a>";



  }

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

?>

Any help or direction towards tutorials would be greatly appreciated. I don't expect just code as i would like to understand what the code is doing 😃

Cheers.

[ ModEdit - bpat1434 ] Please use the php bbcode to post PHP code.

[bpat1434]

1.) Why are you connecting twice, first at the top and then later just before the search?

2.) Your first query can be shortened to be:

SELECT COUNT(*) FROM `searchtable` WHERE `name` LIKE '%{$trimmed}%' ORDER BY `name`

Then just use the returned value as the number of rows 😉

$num_results = mysql_result(mysql_query($query), 0, 0);

3.) When you want to have the next result on the next line, be sure to add a <br /> tag after the closing </a> tag of the link 😉

[rach99]

Hey Bpat,

Thank you for your fast reply.

I have no idea why I was connecting twice, lol. I am pretty new to this stuff and i guess i didnt want to delete it in case it messed up all the code i had working. 😃 lol. its gone now.

I added in the code you showed me but it says it can not execute query.

Is there something i need to add or change in the code or delete once i have added the above?

Even a hint would be good, then i can focus on and area and work it out.

Thanks again 😃

[rach99]

😃

So it now displays but it doesn't show any results? It says your search returned zero results?

All the details around it comes up just not the actual result. Says nothing there when there is.

[bpat1434]

Okay, try this code. I cleaned it up quite a bit, added in some error checking for the 's' parameter and also escaped the search variable (which should be done). So without further ado:

<?php 
// Set up some vars to use:
$tablename = 'searchtable'; // Change to the table to search
$searchcolumn = 'name'; // Change to the column to search
$ordercolumn = 'name'; // Change to the column to order by
?>
<P>Welcome to example</P>

<?php

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

// If s is sent, then grab it.  If it's less than 0, make it 0 ;)
  $s = 0;
  if(isset(@$_GET['s']))
  {
	  $s = $_GET['s'];
	  if($s < 0)
		  $s = 0;
  }

// rows to return
$limit=10;

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

// secure the "trimmed" value
$trimmed = mysql_real_escape_string($trimmed);

// 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","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 COUNT(*) 
		  FROM `{$tablename}` 
		  WHERE `{$searchcolumn}` LIKE '%$trimmed%'  

		  ORDER BY `{$ordercolumn}`"; // EDIT HERE and specify your table and field names for the SQL query

$rslt=mysql_query($query) or die('MySQL Error: ' . mysql_error());
$numrows = mysql_result($rslt, 0, 0);
@mysql_free_result($rslt);

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

if ($numrows == 0)
{
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

  // google
  echo "<p><a href=\"http://www.google.com/search?q="
	  . $trimmed . "\" target=\"_blank\" title=\"Look up
	  " . $trimmed . " on Google\">Click here</a> to try the
	  search on google</p>";
}

// get results
  $query = "SELECT *
			FROM `{$tablename}`
			WHERE `{$searchcolumn}` LIKE '%{$trimmed}%'
			ORDER BY `{$ordercolumn}`
			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 "<p>Results</p>";
$count = 1 + $s ;

while ($row = mysql_fetch_array($result)) {
  $link = $row['link'];
  $link_title = $row['name'];

  echo $count . '.) <a href="' . $link . '">' . $link_title . '</a>'; 
  $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>";

?>

[rach99]

Thank you so much for all your help, but it i have filled in the code below and it wont even load the search page now after i click search. I can not work out what i am doing wrong!

<?php

  // Connect to the database server  

  $dbcnx = @mysql_connect("localhost", "username", "password");  

  if (!$dbcnx) {    

  echo( "<P>Unable to connect to the " .          

  "database server at this time.</P>" );    

  exit();  

  }

  // Select the tasbite database  

  if (! @mysql_select_db("putmynameinhere") ) {    

  echo( "<P>Unable to locate the databasename " .          

  "database at this time.</P>" );    

  exit();  

  }

?>

<?php
// Set up some vars to use:
$tablename = 'addedmytabletosearch'; // Change to the table to search
$searchcolumn = 'addedmynames'; // Change to the column to search
$ordercolumn = 'addedmynames'; // Change to the column to order by
?> 

<P>Welcome to example: </P>

<?php

  // Get the search variable from URL
  $var = @$_GET['q'] ;
  $trimmed = trim($var); //trim whitespace from the stored variable
// If s is sent, then grab it.  If it's less than 0, make it 0 ;)
  $s = 0;
  if(isset(@$_GET['s']))
  {
      $s = $_GET['s'];
      if($s < 0)
          $s = 0;
  }

// rows to return
$limit=10;

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

// secure the "trimmed" value
$trimmed = mysql_real_escape_string($trimmed);

// 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 COUNT(*)
          FROM `{$addedmysearchtable}`
          WHERE `{$addedmynames}` LIKE '%$trimmed%'  

          ORDER BY `{$addedmynames}`"; // EDIT HERE and specify your table and field names for the SQL query

$rslt=mysql_query($query) or die('MySQL Error: ' . mysql_error());
$numrows = mysql_result($rslt, 0, 0);
@mysql_free_result($rslt);

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

if ($numrows == 0)
{
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

  // google
  echo "<p><a href=\"http://www.google.com/search?q="
      . $trimmed . "\" target=\"_blank\" title=\"Look up
      " . $trimmed . " on Google\">Click here</a> to try the
      search on google</p>";
}

// get results
  $query = "SELECT *
            FROM `{$addedmysearchtable}`
            WHERE `{$addedmynames}` LIKE '%{$trimmed}%'
            ORDER BY `{$addedmynames}`
            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 "<p>Results</p>";
$count = 1 + $s ;

while ($row = mysql_fetch_array($result)) {
  $link = $row['Link'];
  $link_title = $row['Name'];

  echo $count . '.) <a href="' . $link . '">' . $link_title . '</a>';
  $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>";

?>

Please let me know if i am changing things wrong or missing something.

Thanks again for all your help 😃

[bpat1434]

Yes, in the queries, dont' change any of the variable names as they correspond to the names at the top ($tablename, $columnsearch, $columnorder). That was there to make it easier for you to maintain. Rather than changing code in 2 or more different spots, you change it in one.

I don't see any syntax errors (though I did a quick skim), so is there anything in the error logs?

[rach99]

This is the error in the log:

PHP Parse error: syntax error, unexpected '@', expecting T_STRING or T_VARIABLE or '$' in search.php line 96.

[bradgrafelman]

Really? The error message I got was:

Parse error: syntax error, unexpected '@', expecting T_STRING or T_VARIABLE or '$' in search.php on line 36

Looking on line 36, it's obvious why; you don't use the "@" error-suppressor in an [man]isset/man function! The whole reason of using isset() is that you can test if variables exist before simply assuming they do and warnings/notices are generated.

EDIT: Also, why do you even use '@' in the script at all? In a production script, there's no reason to use the '@' error suppressor, and when debugging a script you'd want to see as many errors as possible...

[rach99]

Thanks, its working now.

I didnt even know what any of that meant until you just explained it so thanks heaps for all the help guys.

Its hard to know what is wrong with something if you don't know what each thing means.

Now i know i can look it up and learn it.

All the help is much appreciated. 😃

[rach99]

...Me again. 😃

So now i have it all working etc it still doesn't search all the columns in my database, just the 'name' one. I would like it to search the suburb one and the address one also. At the moment on my site it has a quick search so if visitors wanted to search by a suburb, or name or address they just type it in the one box click search then it brings up the names of the people that are associated with the address or suburb.

Just a hint to where in the code i would need start or add something would be great then i will look into it!

Thanks

[rach99]

...A miracle has occured. I worked it out myself! 😮

Will add it below in case anyone else is needs to know 😃

<?php
// Set up some vars to use:
$tablename = 'tablename'; // Change to the table to search
$searchcolumn = 'suburb'; // Change to the column to search
$searchcolumna = 'name';
$ordercolumn = 'name'; // Change to the column to order by
?>

[code=php]

Then I changed this:

[code=php]

$query = "SELECT COUNT(*)
          FROM `{$tablename}`
          WHERE `{$searchcolumn}` LIKE '%$trimmed%' or `{$searchcolumna}` LIKE '%$trimmed%'
          ORDER BY `{$ordercolumn}`"; 
[code=php]

Hope thats the correct way to do it!

Thanks again for all your help it was much appreciated! :D