I have a basic search result page going right now, but i want the code to generate a html link on the search name so I can see all the data associated with the customer name. I have a simple DB set up with Customer Name, Computer type, Model, etc. The search does show the name and if there is multiple in there for the customer but I am stuck on how to make the name clickable and show all the results of the DB for that user.

Here is the search 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 to your database ** EDIT REQUIRED HERE **
mysql_connect("","",""); //(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 Client_Computer_Info where Customer_Name like \"%$trimmed%\"  
  order by ID"; // 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 "Results";
$count = 1 + $s ;

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

  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>";
  
?>
Member Avatar

diafol

Sorry the explanation is a little difficult to follow.
A user makes a search and you log the search keywords - is that right? Along with the user_id?

You want to get a list of users that have searched for a search term?

You want to get a list of search terms for the user?

Sorry, it is a lil confusing.

I want to run the query, and when it shows the results, i want it to have a clickable link on the name(Customer_Name) when it generates. When i click on the name i want it to show all results of the DB for that Customer, I have 8 Fields defined and i want to be able to see the results.

Member Avatar

diafol

OK I see a lookup search for users?

Search forms usually use the $_GET method, so the form:

<form id="fsearch" name="fsearch">
  <label for="search">Search user:</label>
  <input type = "text" name="search" id="search" placeholder="Enter the user..." />
  <input type = "submit" name="submit" id="submit" value="Search Users" />
</form>

Then to grab the search name:

//assume you include mysql connection details here
if(isset($_GET['search'])){
  $search = mysql_real_escape_string($_GET['search']);
  $r = mysql_query("SELECT cust_id, customer_name FROM customers WHERE customer_name LIKE '%{$search}%'");
  if(mysql_num_rows($r)>0){
     $output = "\n<ul>";
     while($d = mysql_fetch_array($r)){
        $output .= "\n\t<li><a href=\"profile.php?id={$d['cust_id']}\">{$d['customer_name']}</a></li>"
     }
     $output .= "\n</ul>";
  }else{
     $output = "<p>Sorry no results for that name.</p>";
  }
}

You then echo the $output in the relevant part of the page.
Then your profile page picks up the id from the url ($_GET).
This is used to extract info from the DB and then display this data in the profile page.

Like that?

Yeppers, but where do I include that code at? I have it set for the form to be this:

<div align="center">
<form name="form" action="search.php" method="get">
Please enter &quot;CLIENT NAME&quot; into search box<br>
<input type="text" name="q">
<input type="submit" name="Submit" value="Search">
</form>
</div>

Then i do have a DB connection string attached to the PHP code i supplied. just omitted it...But where do i insert that at for the code to work properly with the existing code i have.

Member Avatar

diafol

Depends - is the search form visible on all pages? Which pages do you have?

Upon logging into the DB, You come to a screen that has 2 buttons, One to add information and the other to search. I have the search on its own page and that's it. Its an internal DB for my company and I got this far but that's it. lol. I am not a php programmer or anything. I just want it to show the details for the information i have in there. The search box is only one page then it renders the other page with the results.

Member Avatar

diafol

OK - you seem to have a dedicated page for searching. There are a couple of ways to do this, but perhaps the easiest would be to combine the search and display details on the same page.

I used LIKE '%{$customer_name}%' , which could well give multiple results. If you change that to = '$customer_name' , you should just get the entry for which you specifically searched.

Anyway.

Your profile/search page (let's call it search.php):

//assume you include mysql connection details here
$output = "";
if(isset($_GET['search'])){
  $search = mysql_real_escape_string($_GET['search']);
  $r = mysql_query("SELECT cust_id, customer_name FROM customers WHERE customer_name LIKE '%{$search}%'");
  if(mysql_num_rows($r)>0){
     $output .= "\n<ul>";
     while($d = mysql_fetch_array($r)){
        $output .= "\n\t<li><a href=\"search.php?id={$d['cust_id']}\">{$d['customer_name']}</a></li>"
     }
     $output .= "\n</ul>";
  }else{
     $output = "<p>Sorry no results for that name.</p>";
  }
}elseif(isset($_GET['id'])){
   $id = intval($_GET['id']);
   $r = mysql_query("SELECT cust_id, customer_name ...(all fields you want)... FROM customers WHERE customer_id = $id LIMIT 1");
   if(mysql_num_rows($r) == 1){
      $d = mysql_fetch_array($r);
      $output .= "\n<p>ID: {$d['customer_id']}</p>
                  \n<p>Name: {$d['customer_name']}</p>
                  \n<p>Address: {$d['addr1']}, {$d['addr2']}, {$d['postcode']}</p>
                  \n<p>Tel: {$d['tel']}</p>
                  \n<p>Email: {$d['email']}</p>";
  }else{
     $output = "<p>Sorry the ID supplied for the customer does not exist.</p>";
  }
}

That goes above the Doctype declaration and html/head area

The form goes into the body.

The below the form:

<?php echo $output;?>

OK so i amended the PHP code for what you had, i inserted the Connection string that i have and it doesn't work. to connect to the db. Like i said i am kinda a noob at this. Can you affix the connection string to the code as well? so i know where it goes...

Member Avatar

diafol

That goes above the first bit of php code. As for being a noob - it's not a crime, but you can get this info from a million different websites and the php.net site.

Parse error: syntax error, unexpected T_STRING in /homepages/36/d301143136/htdocs/MyDB/search.php on line 3
Member Avatar

diafol

That's all well and good, but what's on line 3?

My SQL connect string

Here is the code like you said to put it in with my connect string. But i am getting the error described above. Any ideas?

<? PHP
    session_start(); 
    //connect to your database ** EDIT REQUIRED HERE **
	mysql_connect("localhost","omit","omit"); //(host, username, password)

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

    $output = "";
    if(isset($_GET['search'])){
    $search = mysql_real_escape_string($_GET['search']);
    $r = mysql_query("SELECT ID, Customer_Name FROM customers WHERE Customer_Name LIKE '%{$search}%'");
    if(mysql_num_rows($r)>0){
    $output .= "\n<ul>";
    while($d = mysql_fetch_array($r)){
    $output .= "\n\t<li><a href=\"search.php?id={$d['cust_id']}\">{$d['Customer_Name']}</a></li>"
    }
    $output .= "\n</ul>";
    }else{
    $output = "<p>Sorry no results for that name.</p>";
    }
    }elseif(isset($_GET['Customer_Name'])){
    $id = intval($_GET['ID']);
    $r = mysql_query("SELECT Customer_Name, Computer_Type, Computer_Make, Computer_Model, Serial_Number, Product_Number, OS, `Key` FROM customers WHERE customer_id = $id LIMIT 1");
    if(mysql_num_rows($r) == 1){
    $d = mysql_fetch_array($r);
    $output .= "\n<p>Name: {$d['Customer_Name']}</p>
    \n<p>Computer Type {$d['Computer_Type']}</p>
    \n<p>Computer Make {$d['Computer_Make']}</p>
    \n<p>Computer Model {$d['Computer_Model']}</p>
    \n<p>Serial Number {$d['Serial_Number']}</p>
    \n<p>Product Number {$d['Product_Number']}</p>
    \n<p>OS {$d['OS']}</p>
    \n<p>Key {$d['Key']}</p>";
    }else{
    $output = "<p>Sorry the ID supplied for the customer does not exist.</p>";
    }
    }
    ?>
    
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Search</title>
</head>

<body>
    <form id="fsearch" name="fsearch">
    <label for="search">Search user:</label>
    <input type = "text" name="search" id="search" placeholder="Enter the user..." />
    <input type = "submit" name="submit" id="submit" value="Search Users" />
    </form>
    
    <?php echo $output;?>
</body>

</html>

1) remove space between <? PHP, it should read <?PHP
2) add semicolon to line number 17 in above code in the end

OK Guys, one last error and it should work like a charm. I am now getting error: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /homepages/36/d301143136/htdocs/MyDB/search.php on line 12

I used the code above, Ill repost it with changes:

<?PHP
    session_start(); 
    //connect to your database ** EDIT REQUIRED HERE **
	mysql_connect("localhost","omit","omit"); //(host, username, password)

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

    $output = "";
    if(isset($_GET['search'])){
    $search = mysql_real_escape_string($_GET['search']);
    $r = mysql_query("SELECT ID, Customer_Name FROM customers WHERE Customer_Name LIKE '%{$search}%'");
    if(mysql_num_rows($r)>0){
    $output .= "\n<ul>";
    while($d = mysql_fetch_array($r)){
    $output .= "\n\t<li><a href=\"search.php?id={$d['cust_id']}\">;{$d['Customer_Name']}</a></li>"
    }
    $output .= "\n</ul>";
    }else{
    $output = "<p>Sorry no results for that name.</p>";
    }
    }elseif(isset($_GET['Customer_Name'])){
    $id = intval($_GET['ID']);
    $r = mysql_query("SELECT Customer_Name, Computer_Type, Computer_Make, Computer_Model, Serial_Number, Product_Number, OS, `Key` FROM customers WHERE customer_id = $id LIMIT 1");
    if(mysql_num_rows($r) == 1){
    $d = mysql_fetch_array($r);
    $output .= "\n<p>Name: {$d['Customer_Name']}</p>
    \n<p>Computer Type {$d['Computer_Type']}</p>
    \n<p>Computer Make {$d['Computer_Make']}</p>
    \n<p>Computer Model {$d['Computer_Model']}</p>
    \n<p>Serial Number {$d['Serial_Number']}</p>
    \n<p>Product Number {$d['Product_Number']}</p>
    \n<p>OS {$d['OS']}</p>
    \n<p>Key {$d['Key']}</p>";
    }else{
    $output = "<p>Sorry the ID supplied for the customer does not exist.</p>";
    }
    }
    ?>
    
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Search</title>
</head>

<body>
    <form id="fsearch" name="fsearch">
    <label for="search">Search user:</label>
    <input type = "text" name="search" id="search" placeholder="Enter the user..." />
    <input type = "submit" name="submit" id="submit" value="Search Users" />
    </form>
    
    <?php echo $output;?>
</body>

</html>

Any ideas?

OK so i solved the issue i was getting. But this new thing is presenting its self. When I click on the name i want...it doesnt render any information on the page, its just blank. I am going to upload my code as it is in my DB and see if there is an issue that i am overlooking....Hope it is small....

<?PHP
    
	mysql_connect("omit","omit","omit"); 
	
	mysql_select_db("omit") or die("Unable to select database"); 

    $output = "";
    if(isset($_GET['search'])){
    $search = mysql_real_escape_string($_GET['search']);
    $r = mysql_query("SELECT ID, Customer_Name FROM Client_Computer_Info WHERE Customer_Name LIKE '%{$search}%'");
    if(mysql_num_rows($r)>0){
    $output .= "\n<ul>";
    while($d = mysql_fetch_array($r)){
    $output .= "\n\t<li><a href=\"search.php?id={$d['ID']}\">{$d['Customer_Name']}</a></li>";
    }
    $output .= "\n</ul>";
    }else{
    $output = "<p>Sorry no results for that name.</p>";
    }
    }elseif(isset($_GET['Customer_Name'])){
    $id = intval($_GET['ID']);
    $r = mysql_query("SELECT Customer_Name, Computer_Type, Computer_Make, Computer_Model, Serial_Number, Product_Number, OS, `Key` FROM Client_Computer_Info WHERE Customer_Name = $id LIMIT 1");
    if(mysql_num_rows($r) == 1){
    $d = mysql_fetch_array($r);
    $output .= "\n<p>Name: {$d['Customer_Name']}</p>
    \n<p>Computer Type {$d['Computer_Type']}</p>
    \n<p>Computer Make {$d['Computer_Make']}</p>
    \n<p>Computer Model {$d['Computer_Model']}</p>
    \n<p>Serial Number {$d['Serial_Number']}</p>
    \n<p>Product Number {$d['Product_Number']}</p>
    \n<p>OS {$d['OS']}</p>
    \n<p>Key {$d['`Key`']}</p>";
    }else{
    $output = "<p>Sorry the ID supplied for the customer does not exist.</p>";
    }
    }
    ?>
    
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Search</title>
</head>

<body>
    <form id="fsearch" name="fsearch">
    <label for="search">Search user:</label>
    <input type = "text" name="search" id="search" placeholder="Enter the user..." />
    <input type = "submit" name="submit" id="submit" value="Search Users" />
    </form>
    
    <?php echo $output;?>
</body>

</html>

Most likely your code crashes somewhere. Either echo some messages to see where you get, or enable error reporting while testing.