mysql search help, cannot get this to work

[jaylam13]

Hi everyone,

 

Would someone be able to help me out here, I cannot get this to work.

Basically I have 3 pages, I with a search box which then run's a search script to display the results.  Then the results are clickable, the idea is when the user clicks the result it takes them to another page which I want to display the info from the database.....if that makes sense, but when the link is clicked nothing is displaying.

 

So here is what I have done so far:

 

index.php - This is the page with the search box, code is :

<?php
$dbhost = 'localhost';
$dbuser = '*********';
$dbpass = '********';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

$dbname = '***********';
mysql_select_db($dbname);
?> 
<form name="form" action="search.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>

 

When they hit submit it takes them to the results page, code is:

<?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","********","********"); //(host, username, password)

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

// Build SQL Query  
$query = "select * from Fish where Commonn like \"%$trimmed%\"  
  order by Commonn"; // 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: "" . $trimmed . "" 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: "" . $var . ""</p>";

// begin to show results set
echo "Results<br><br>";
$count = 1 + $s ;

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

  echo "$count.) <a href='info.php?ID=$ID'>$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 " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev 10</a>&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 " <a href=\"$PHP_SELF?s=$news&q=$var\">Next 10 >></a>";
  }

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

 

When they click the results from this page it takes them to another page which should (but doesnt) display the info from the mysql database, code is:

 

<?php


// now you can display the results returned


  echo "$title" ;



?>

 

Im really sorry if this is obvious or if I have dont something completely wrong but im very new to php and trying to learn on the job.

 

Many Thanks in advance

 

Jay

[Pawn]

$title is a local variable available only in the script that defined it. Standard method is to create links that look like

 

http://yoursite.com/somepage.php?id=123

 

Where "id" is the unique identifier of the result you want to display. In somepage.php, you would then select on the basis of the identifier provided.

 

if(isset($_GET['id'])) {
$sql = "SELECT title FROM table WHERE item_id = ".$_GET['id']." LIMIT 1";
$query = mysql_query($sql);
$row = mysql_fetch_assoc($query);
echo $row['title'];
}

[jaylam13]

Thanks for the reply, im still a bit confused. Would you be able to show me where and what I need to edit in my code to get it to work?

[BlueSkyIS]

$numresults=mysql_query($query) or die(mysql_error() . " IN $query");

[jaylam13]

$numresults=mysql_query($query) or die(mysql_error() . " IN $query");

Where would this go and would this then let me do what im trying to do?

[BlueSkyIS]

I suggest that you update your existing code to include

 

or die(mysql_error())

 

after all mysql_query()'s. otherwise, you don't know whether the query actually worked or not.

[jaylam13]

I suggest that you update your existing code to include

 

or die(mysql_error())

 

after all mysql_query()'s

 

I have now do that, thanks but still when I click the search result I get a blank page

[BlueSkyIS]

it sounds like you have error_reporting turned off. try putting this as the first line in your PHP code:

 

// Report simple running errors
error_reporting(E_ERROR | E_WARNING | E_PARSE);

 

what i'm getting at is your SQL is probably invalid. but you wouldn't know that as no errors are being checked for (or displayed, apparently).

[jaylam13]

it sounds like you have error_reporting turned off. try putting this as the first line in your PHP code:

 

// Report simple running errors
error_reporting(E_ERROR | E_WARNING | E_PARSE);

 

what i'm getting at is your SQL is probably invalid. but you wouldn't know that as no errors are being checked for (or displayed, apparently).

 

I added that but still a blank page, I think its not carrying over the variables but I dont know enough about php to be 100%

[BlueSkyIS]

oh wait. i wasn't paying attention. so, you're looking at the third page, the one with echo "$title" ; on it?

 

if so, $title won't automatically be set on that page. each page is different. if you must send the user to a new page and you expect $title to be available, you'll need to either use sessions to pass the information from page to page, or store the value in a database and retrieve it on pages as needed.

 

I think Pawn was on the problem from the get-go. my bad...

[jaylam13]

oh wait. i wasn't paying attention. so, you're looking at the third page, the one with echo "$title" ; on it?

 

if so, $title won't automatically be set on that page. each page is different. if you must send the user to a new page and you expect $title to be available, you'll need to either use sessions to pass the information from page to page, or store the value in a database and retrieve it on pages as needed.

 

I think Pawn was on the problem from the get-go. my bad...

 

Sorry I havent been to clear, so the first bit of code is the search box on its own page (index.php), that then when you hit the submit button runs search.php which does the search and displays the links then I want the users to be able to click the link which will take them to info.php where I can echo the data in the mysql database for the search result.  So for example one field in the table I would like to display is "Commonn".

 

I have the first 2 bits working but cannot work out how to get the info.php to display the information from the database for the search result clicked.

 

[jammesz]

Also might like to point out that there's no reason for a mysql connection in index.php as you dont use the database at all on that page.

<?php
$dbhost = 'localhost';
$dbuser = '*********';
$dbpass = '********';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql');

$dbname = '***********';
mysql_select_db($dbname);
?>
<form name="form" action="search.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>

 

should be this:

<form name="form" action="search.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>

[Pawn]

Replace info.php with

 

 

if(isset($_GET['ID']) && is_numeric($_GET['ID'])) {    $dbhost = 'localhost';    $dbuser = '*********';    $dbpass = '********';    $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');    $dbname = '***********';    mysql_select_db($dbname);    $sql = "SELECT * FROM Fish WHERE fish_id = ".$_GET['ID']." LIMIT 1";    $query = mysql_query($sql);    $row = mysql_fetch_assoc($query);    echo $row['title'];}

 

After "$sql =", replace "fish_id" with whatever the name of the ID column in your table is. Now, modify this line in index.php

 

 

echo "$count.) <a href='info.php?ID=$ID'>$title</a>" ;

 

The value of $ID should be the value of the ID of that table row. Do this instead

 

 

$title = $row["Commonn"];$ID = $row['fish_id'];echo "$count.) <a href='info.php?ID=$ID'>$title</a>" ;

 

Again, replace fish_id with the name of your table's ID column.