SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast haryoh's Avatar
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question searching multiple Database tables....

    Hi everyone, I'm trying to make this search script i developed be able search 2 or more tables. i'm calling the categories from within the tables I'm searching... i got the codes below:



    PHP Code:
    <!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 http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Search Companies</title>

    <link rel="stylesheet" type="text/css" href="inc/styletab.css" />
    <link rel="stylesheet" type="text/css" href="inc/style.css" />

    </head>

    <body>
    <div id="sitelogoBG">
    <table width="779">
      <!--DWLayoutTable-->
      <tr>
    <td width="327">
      <div id="leftLogo">GrapeVine Admin Control</div>
    </td> 
    <td valign="middle"><div>something stays here</div></td>
    <td valign="middle"><div>Logout information stays here</div></td>
    </tr>
    </table>
    </div>

    <?php
    include 'inc/conn.php';
    ?>

    <div id="content">

    <div id="leftlogo">
    <dt style="font: bold 16px Verdana, Arial, Helvetica, sans-serif;">Welcome to Company Search</dt> 
    </div><br />

    <div class="subnav">Search by Zipcode/state</div>

    <div class="content-searchbar">
    <dl class="sidebox" style="    border: 1px solid #CECECE; background: #FAFAFA;">
        <dt>
        <form action="<?php $_SERVER['PHP_SELF'?>" method="post"><div>
        <label for="company" style="font-weight: bold;">&nbsp;Choose Company:</label>
        <br />
    &nbsp;<select name="searchcomp" id="company" onFocus="style.backgroundColor='#E6E6E6'; style.color='#3246A0'" onBlur="style.backgroundColor='#FAFAFA'; style.color='#FGFGFG'" />
    <option value=''>Choose Country from list..</option>
    <option value=''>..................................</option>
    <option value='company1'>Company 1</option>
    <option value='company2'>Company 2</option>
    <option value='company3'>Company 3</option>
    <option value='company4'>Company 4</option>
    <option value='company5'>Company 5</option>
    <option value='company6'>Company 6</option>
    </select>

        <label for="searchComp" style="font-weight: bold;">&nbsp;Enter keyword: </label>
        <br />
        &nbsp;<input type="text" name="searchtext" id="searchComp" onFocus="style.backgroundColor='#E6E6E6'; style.color='#3246A0'" onBlur="style.backgroundColor='#FAFAFA'; style.color='#FGFGFG'" /> <input type="submit" name="submit" value="search" id="searchbutton"/>
    </div>
    </form>
        </dt>
    </dl>
    </div>
    </div>

    <br />
    <div id="content">
    <dl class="subnav">
    <?
    // The basic SELECT statement

    $select 'SELECT DISTINCT *';
    $from   ' FROM companytable1, companytable2';
    $where  ' WHERE 1 = 1';

    $company $_POST['searchcomp'];
    if (
    $company != '') { // Some search text was specified
      
    $where .= " AND compstatus = '$company'";
    }
            
    $searchtext $_POST['searchtext'];
    if (
    $searchtext != '' || $searchtext '') {
      
    $where .= " AND zipcode LIKE '%$searchtext%'";
      
    $where .= " OR state LIKE '%$searchtext%' ORDER BY '$searchtext'";
      
    }else{
            
    // demacating my search fields and result into two
       
    die("<p>
            No Search preference was entered. Please, specify one.<br />" 
    .
           
    "Click to go <a href=javascript:history.go(-1);>Back Search Page</a>");
    //require_once 'inc/footer.inc';
         
    }

    ?>

    <?php

    $search 
    mysql_query($select $from $where);
    if (!
    $search) {
      die(
    '<p>Error retrieving details from database!<br />'.
          
    'Error: ' mysql_error() . '</p>');
    }

    //if no word found give an error message
    $row mysql_num_rows($search);
            if (
    $row 0) {
              echo(
    '<b>No Keyword seems to have been entered.</b>');
    } else {
    echo 
    "<br /><div><b>Number(s) of Match found: <font color='orange'>[$row]</font></b></div><br />";

      echo(
    "<div class='redText'><dt><font size='2'><b>Result</b></font></div></dt>");
      echo(
    "<dt><font size='2' color='red'><i>Searched keywords:</i></font> " .
             
    " <font color='blue'><b><u>$company</u> &nbsp; <u>$searchtext</u></b></font> " .
            
    "|&nbsp;&nbsp;&nbsp; Click to go <a href=javascript:history.go(-1);>Back - Search Page</a></dt>");
      echo(
    "<div style='border-bottom: 1px dotted #EDEDED;'>&nbsp;</div><br />");
        }
        
    while (
    $user mysql_fetch_array($search)) {

      
    $email $user['email'];
      
    $fname $user['Fname'];
      
    $lname $user['Lname'];
      
    $phone $user['phone'];
      
    $zip   $user['zipcode'];
      
    $comment $user['note'];

        echo 
    "<dt>";
        echo 
    "<b>FullName:</b> $fname $lname<br />";
        echo 
    "<b>Email:</b> $email<br />";
        echo 
    "<b>Zipcode:</b> $zip<br />";
              
    $comment htmlspecialchars($comment);
        echo 
    "<b>Comment:</b> $comment <br />";
        echo(
    "<div style='border-bottom: 1px dotted #FCFCFC;'>&nbsp;</div></dt>");

    }

    ?>
    </dl>
    </div>

    <br /><br />
    <div id="navigationtabsfooter">&nbsp;</div>

    <div id="siteinfo">
    <p>Designed by  Scofield Associates<br>
    <a href="http://www.scofieldassociates.com">Visit Us</a></p>
    <ul>
        <li><a href="http://www.reorganize.ca/pages/about-us" title="">About Us</a></li>
        <li><a href="http://www.reorganize.ca/pages/shipping" title="">Hosting Info</a></li>
        <li><a href="http://www.reorganize.ca/pages/privacy" title="">Privacy</a></li>
    </ul>
    </div><!-- end id:siteinfo -->

    </body>
    </html>

    it gives me a "where clause ambigous" error when i run this script. but i use one table, it works fine.

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    What are the structures of the two tables? One or more fieldnames the same in each table?
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    You get the error because your tables have columns with the same names. Include table name in column references to avoid ambiguities.
    Saul

  4. #4
    SitePoint Enthusiast haryoh's Avatar
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do you mean where it says companytable1, companytable2 should be changed entirely?

  5. #5
    SitePoint Enthusiast haryoh's Avatar
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    71
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by php_daemon
    You get the error because your tables have columns with the same names. Include table name in column references to avoid ambiguities.
    do you mean where it says companytable1, companytable2 should be changed entirely?

  6. #6
    ✯✯✯ silver trophybronze trophy php_daemon's Avatar
    Join Date
    Mar 2006
    Posts
    5,284
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    if ($company != '') { // Some search text was specified
      
    $where .= " AND (companytable1.compstatus = '$company' OR companytable2.compstatus = '$company')";
    }
            
    $searchtext $_POST['searchtext'];
    if (
    $searchtext != '' || $searchtext '') {
      
    $where .= " AND (companytable1.zipcode LIKE '%$searchtext%' OR companytable2.zipcode LIKE '%$searchtext%')";
      
    $where .= " OR (companytable1.state LIKE '%$searchtext%' OR companytable2.state LIKE '%$searchtext%' )ORDER BY '$searchtext'";
      
    }else{ 
    Saul


Bookmarks

Posting Permissions

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