Searching MySQL with PHP. Coming across some issues..

n3rd

I found a script online for searching a database and have molded it into what I need.
For some reason when I try to display the results it's not showing any of the $row['$field'] results.

Here is the code:

<?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 =D
include('connect.php');

// Build SQL Query  

$query = "select * from contacts where $field like \"%$trimmed%\"  

  order by $field"; // 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 "<h2>Results</h2>";
$count = 1 + $s ;

// build the table
	echo "<table>";

echo "<tr>";
	echo "<td>Name</td>";
	echo "<td>Email</td>";
	echo "<td>Phone</td>";
	echo "<td>Location</td>";
	echo "<td>Industry</td>";
	echo "<td>Company</td>";
	echo "<td>Title</td>";
echo "</tr>";


// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {

echo "<tr>";
	echo "<td>";
		echo "<p>";
			echo $row["$firstname"];
				echo "&nbsp;";
			echo $row["$lastname"];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo "<a href=\"";
				echo $row["$email"];
			echo "\">";
			echo $row["'$email"];
			echo "</a>";
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["$phone"];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["$postal"];
			   echo "&nbsp;";
			echo $row["$city"];
			   echo ",&nbsp;";
			echo $row["$state"];
			   echo "&nbsp;";
			echo $row["$state"];
			   echo "&nbsp;";
			echo $row["$zip"];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["$industry"];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["$company"];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["$title"];
		echo "</p>";
	echo "</td>";
echo "</tr>";



  $count++ ;
  }

// end the table
echo "</table>";

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

?>

NogDog

I don't think you want to use variables as the array keys, just string literals, e.g.:

echo $row["firstname"];  // no "$" before "firstname"

n3rd

I tried that, and stripped out all of the logic in it. down to just this:

<?php
// connect =D
include('connect.php');

// Build SQL Query  

$query = "select * from contacts where '".$field."' like '".$q."' order by '".$field."'";

// begin to show results set
echo "<h2>Results</h2>";

// build the table
	echo "<table>";

echo "<tr>";
	echo "<td>Name</td>";
	echo "<td>Email</td>";
	echo "<td>Phone</td>";
	echo "<td>Location</td>";
	echo "<td>Industry</td>";
	echo "<td>Company</td>";
	echo "<td>Title</td>";
echo "</tr>";



$result = mysql_query($query) or die("Couldn't execute query");

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {

echo "<tr>";
	echo "<td>";
		echo "<p>";
			echo $row["firstname"];
				echo "&nbsp;";
			echo $row["lastname"];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo "<a href=\"";
				echo $row["email"];
			echo "\">";
			echo $row["email"];
			echo "</a>";
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["phone"];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["postal"];
			   echo "&nbsp;";
			echo $row["city"];
			   echo ",&nbsp;";
			echo $row["state"];
			   echo "&nbsp;";
			echo $row['zip'];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["industry"];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["company"];
		echo "</p>";
	echo "</td>";
	echo "<td>";
		echo "<p>";
			echo $row["title"];
		echo "</p>";
	echo "</td>";
echo "</tr>";

  }

// end the table
echo "</table>";



mysql_close($con);
?>

And I'm still not showing any results

Blulagoon

Might I suggest:

echo $row['firstname'];

Let us know if that works for you

Blu

liquorvicar

In my experience

echo $row['firstname'];

and

echo $row["firstname"];

will both work. The only difference being PHP will parse "firstname" which will take a few extra CPU cycles.

Have you tried echo-ing your query and running it directly against the DB to check to are getting results?
Also,

SELECT * FROM table WHERE firstname LIKE 'And'

will not match where firstname is 'Andrew', whereas

 SELECT * FROM table WHERE firstname LIKE 'And%'

will match.

n3rd

I've tried both versions of ['firstname'] and ["firstname"] and that didnt seem to make a difference.

Also, I tried the SQL directly in php admin as you said and generated results there.

What's strange is when I run the full version of this with the result counting and such, it will actually show the counted results; but still not display the table information.

I'm thinking it might have to do with the while statement in this case, but I've checked this and it seems ok. Can anyone verify?

liquorvicar

The while loop looks fine to me.
Is it echo-ing any of the HTML inside the loop? Or is it skipping the loop altogether?

n3rd

It does the comma in the address part and the " "

liquorvicar

Just that? None of the tags?

Have you tried using var_dump() or print_r() inside the loop to work out what's happening? i.e.

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
      echo('<pre>');
      print_r($row);
      die();

should give you nicely formatted output of the first row retrieved from your table.