HI all,

How do add this:

$sql = "SELECT a.Address, t.Name
        FROM address a
            INNER JOIN linktable lt ON a.AddId = lt.FK_AddId
            INNER JOIN tablesearch t ON lt.FK_NameId = t.NameId
        WHERE a.AddId = $selectedAddressId";

TO THIS:

<?php
// Set up some vars to use:
$tablename = 'tablesearch'; // Change to the table to search
$searchcolumn = 'Name'; // Change to the column to search
$searchcolumna = 'Address';
$searchcolumnb = 'Suburb';
$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;
  }

// Build SQL Query  

$query = "SELECT COUNT(*)
          FROM `{$tablename}`
          WHERE `{$searchcolumn}` LIKE '%$trimmed%' or `{$searchcolumna}` LIKE '%$trimmed%' or `{$searchcolumnb}` 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>";
}

// get results
  $query = "SELECT *
            FROM `{$tablename}`
            WHERE `{$searchcolumn}` LIKE '%{$trimmed}%' or `{$searchcolumna}` LIKE '%{$trimmed}%' or `{$searchcolumnb}` 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><br />';
  $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>";

?>

At the moment my search works fine, but i need to be able to type in an address which may be linked to more than 1 person and have all the names show up. i have to tables added, and the code i just dont know how to add it to my php to make it work without stuffing up my already working search.

any help would be great.

Thanks

    Hi there,

    instead of using inner join on the SQL, use Left or Right Join... IE one to many relationship... Then if there is more than one record in your sub tables it should list all of them where you ID matches... As long as you have a foreign key in the subtable too.

    try something like this...

    
$sql = "SELECT a.Address, t.Name 
        FROM address a 
            [COLOR="Red"]LEFT[/COLOR] JOIN linktable lt ON a.AddId = lt.FK_AddId 
            [COLOR="Red"]LEFT[/COLOR] JOIN tablesearch t ON lt.FK_NameId = t.NameId 
        WHERE a.AddId = $selectedAddressId";

    Thanks

    Andi

      Thanks, I will try that and see how I go.

      I am new to php so I am learning so it may sound like a silly question but where do i put that code in my script? Do i put it under the mysql query i already have? Or do i have to somehow add it to the query?

      Thanks 😃

        Write a Reply...