PHP MySQL - How to connect them when using inner join select...
Results 1 to 3 of 3

Thread: PHP MySQL - How to connect them when using inner join select...

  1. #1
    Junior Member
    Join Date
    Nov 2007
    Posts
    18

    PHP MySQL - How to connect them when using inner join select...

    HI all,

    How do add this:


    PHP Code:
    $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 Code:
    <?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($rslt00);
    @
    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 $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 ;
      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

  2. #2
    Junior Member
    Join Date
    Aug 2006
    Posts
    14
    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...

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

    Andi

  3. #3
    Junior Member
    Join Date
    Nov 2007
    Posts
    18
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •