Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2006
    Location
    Wolverhampton, West Midlands
    Posts
    10

    Question Unanswered: Multiple table select, ambiguous

    Hi, I have setup a search script which searches one table and this works fine. Now, i want the script to search two tables, the tables have the same structure and i want the script to select from the "Name" column in both tables. I have tried using a select statement for two tables but receive this error: Column 'Name' in where clause is ambiguous. I heard that this means the script doesnt know which table to search as they both have the same column name. I want it to search both. Here is my select script:

    select * from downloads_anti ,downloads_multi where Name like \"%$trimmed%\"

    any suggestions would help me greatly thanks.
    Last edited by Maddocks; 04-18-06 at 16:46.
    http://pchelpworld.awardspace.com < Free PSP/PC Help Now!

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Use a UNION (ALL)

    Code:
    select * from downloads_multi where Name like \"%$trimmed%\"
    union
    select * from downloads_anti where Name like \"%$trimmed%\"
    Give a man a fish and you feed him for a day. Teach a man to fish and you lose a steady customer.
    -----------
    Eclectic Web Development (under heavy construction)

  3. #3
    Join Date
    Apr 2006
    Location
    Wolverhampton, West Midlands
    Posts
    10
    my web host is currently down, i will finish off my site and post the results back to you, thanks for your help
    http://pchelpworld.awardspace.com < Free PSP/PC Help Now!

  4. #4
    Join Date
    Apr 2006
    Location
    Wolverhampton, West Midlands
    Posts
    10
    right i have another problem, when i searhc for something which is in the table it displays couldnt execute query, and when i search for something that doesnt exist it shosws the message zero results. So, its finding the keywords but not displaying them, here is my complete script:


    <!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>PCHelpWorld :: Search Results</title>
    <style type="text/css">
    <!--
    body {
    background-image: url(bg.jpg);
    background-repeat: no-repeat;
    }
    #Layer1 {
    position:absolute;
    width:425px;
    height:341px;
    z-index:1;
    left: 181px;
    top: 140px;
    overflow: scroll;
    }
    -->
    </style></head>

    <body>
    <div id="Layer1">
    <form name="form" action="search.php" method="get">
    <input type="text" name="q" />
    <input type="submit" name="Submit" value="Search" />
    </form>
    <?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("db2.awardspace.com","pchelpworld_sq l","************"); //(host, username, password)

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

    // Build SQL Query

    // EDIT HERE and specify your table and field names for the SQL query

    $numresults=mysql_query("select * from downloads_multi where Name like \"%$trimmed%\"
    union
    select * from downloads_anti where Name like \"%$trimmed%\"")
    OR die (mysql_error());
    $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 No 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
    echo "<table border='0' CELLPADDING=5 STYLE='font-size:13px'>";
    echo "<tr> <td><H3>Name</h3></td> <td><H3>Size</H3></td><td><H3>Download</H3></td></tr>";

    while ($row= mysql_fetch_array($result)) {

    echo "<tr><td>";
    echo $row['Name'];
    echo "</td><td>";
    echo $row['Size'];
    echo "</td><td>";
    echo $row['Link'];
    echo "</td></tr>";
    }
    echo "</table>";



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

    ?>

    </div>
    </body>
    </html>
    Last edited by Maddocks; 04-19-06 at 09:39.
    http://pchelpworld.awardspace.com < Free PSP/PC Help Now!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    run your query directly in mysql, i.e. outside of php, with a hardcoded value for $trimmed, so that you are sure that the query is working correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2006
    Location
    Wolverhampton, West Midlands
    Posts
    10
    well it executes ok, bt it does not reply any results when there are results in one of my tables under the name "Avast" which i searched for,

    this is the whole code i entered:

    select * from downloads_anti where Name like "avast" order by Name
    Last edited by Maddocks; 04-19-06 at 09:13.
    http://pchelpworld.awardspace.com < Free PSP/PC Help Now!

  7. #7
    Join Date
    Apr 2006
    Location
    Wolverhampton, West Midlands
    Posts
    10
    this works when searching for avast, removing the / and keeping the %

    select * from downloads_multi where Name like "%avast%"
    union
    select * from downloads_anti where Name like "%avast%"

    doesn't work in php, "couldn't execute query":

    $query .= " limit $s,$limit";
    $result = mysql_query($query) or die("Couldn't execute query");

    my full scriptis above, it recognises when there is something in and when there isnt something in the table but wont display the appropiate results

    think i know what i did, i removed a variable and put the actual select script, then i found a missing line which i looking for this variable
    Last edited by Maddocks; 04-19-06 at 09:30.
    http://pchelpworld.awardspace.com < Free PSP/PC Help Now!

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, so the query is working correctly?

    then the problem is in your php

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    For troubleshooting purposes, echo your query string to make sure that it contains the expected contents. This will expose many types of errors. The variables might not contain what you expect, the parsing of the variables in the string might not be happening due to the usage of and type of quotes being used or the query string might have the wrong syntax.

    Always include error checking and reporting to get mysql to tell you about any problems it had executing -
    PHP Code:
    $result mysql_query($query) or die("Couldn't execute query" mysql_error()); 

Posting Permissions

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