Php & Mysql Table Linking question

[rach99]

Hi I am trying to create a table link in my database so when a user searches for an address my results bring up all the names who live at that address, but also i need it setup so that a person can be associated with more than one address if needed. Heres what i have so far:

 

CREATE TABLE tablesearch (

NameId INT AUTO_INCREMENT NOT NULL,

Name TEXT NOT NULL,

Suburb TEXT NOT NULL,

Link TEXT NOT NULL,

PRIMARY KEY (NameId));

 

CREATE TABLE address (

AddId INT AUTO_INCREMENT NOT NULL,

Address TEXT NOT NULL,

PRIMARY KEY (AddId));

 

 

CREATE TABLE linktable (

FK_NameId INT NOT NULL,

FK_AddId INT NOT NULL,

PRIMARY KEY (FK_NameId, FK_AddId));

 

 

INSERT INTO linktable (FK_NameId, FK_AddId)

VALUES (1,1), (2,1), (3,1), (3,3), (4,1), (4,3), (4,4), (6,1);

 

At the moment i have a database which i need to allow for a quick search.

 

What i would like is for a visitor to type in an address and for all the names of the people who live at that address to come up in my results. Keeping in mind that a person may need to show up for 2 addresses or more i created the database above.

 

BUT i have no idea how to add a query to my php to find this when my php already works well with the search i have. So what do i add to my code below to get it to bring up the above results? (and are the above entries correct?)

 

 

                

<?php

  // Connect to the database server  
  $dbcnx = @mysql_connect("localhost", "root", "password");  
  if (!$dbcnx) {    
  echo( "<P>Unable to connect to the " .          
  "database server at this time.</P>" );    
  exit();  
  }

  // Select the table database  
  if (! @mysql_select_db("table") ) {    
  echo( "<P>Unable to locate the tasbite " .          
  "database at this time.</P>" );    
  exit();  
  }

?>

<?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: "" . $trimmed . "" 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: "" . $var . ""</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 " <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>";
  
?> 

 

any direction would be greatly appreciated.

 

Thanks

 

[marcus]

You could create another table, called like census.

 

Match Person's name with an Address ID, select from the address, get the ID, search census table, bam you got peeps.

[rach99]

Havn't I already done that by adding my linktable? the numbers are the ids of name and address? I dont know how to add it to my php code though. do i just do 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';
$searchcolumnc = 'NameId'
$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}` or `{$linktable}`
          WHERE `{$searchcolumn}` LIKE '%$trimmed%' or `{$searchcolumna}` LIKE '%$trimmed%' or `{$searchcolumnb}` LIKE '%$trimmed%' or `{$searchcolumnc}` LIKE '%$trimmed%'
          ORDER BY `{$ordercolumn}`"; 

sorry if i am way off track i just have no idea with this linking stuff.

[Barand]

try

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

 

And use

...

tags around your code.

[rach99]

Hi,

 

Thanks so far for all your help i really do appreciate it.

 

I added the code in but i am getting this error:

 

MySQL Error: Unknown column 'Address' in 'where clause'

<?php
// Build SQL Query  
$query = "SELECT COUNT(*)
          FROM `{$tablename}`
          WHERE `{$searchcolumn}` LIKE '%$trimmed%' or `{$searchcolumnb}` LIKE '%$trimmed%'
          ORDER BY `{$ordercolumn}`"; // EDIT HERE and specify your table and field names for the SQL query

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


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

 

Does that mean the last line in the sql query is wrong?