Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search multiple tables

    On my page I've got a search feature, where the user can search through the database. In mysql i've got different tables with the contents I want the search to go through. So I need to search through 2 queries. This is how I've done it.
    PHP Code:
    <?


    $records_per_page 
    10;


    //-query  the database table
    $sql1="select * from fagudtryk_muskler  where dansk like \"$trimmed%\" order by dansk";
    $sql2="select * from fagudtryk_led  where dansk like \"$trimmed%\" order by dansk";


    $result = array("$sql1""$sql2");
    foreach(
    $result as $value

    //-run  the query against the mysql query function
    $numro=mysql_query($value);

    //-count  results
    $numrows=mysql_num_rows($numro);

    $num_pages =  ceil($numrows/$records_per_page);
    //Determine the page to display
    $current_page = (isset($_GET['pg']))?$_GET['pg']:1;
    if(
    $current_page<|| $current_page>$num_pages) { $current_page 1; }
    //Define the limit start position for the current page of records (for current filter)
    $limitstart = (($current_page-1)*$records_per_page);


    // Build SQL Query  
    $query "select * from fagudtryk_muskler where dansk like \"$trimmed%\" order by dansk LIMIT $limitstart, $records_per_page "// EDIT HERE and specify your table and field names for the SQL query
    $query1 "select * from fagudtryk_led where dansk like \"$trimmed%\" order by dansk LIMIT $limitstart, $records_per_page "// EDIT HERE and specify your table and field names for the SQL query

    $numresults = array("$query""$query1");
    foreach(
    $numresults as $value1
    $value2=mysql_query($value1);

    // If we have no results, offer a google search as an alternative

    if ($numrows == 0)
      {

      echo 
    "<p>Din søgning: &quot;" $trimmed "&quot; retunerede ingen resultater</p>";

      }


    // get results

    if ($numrows 0)
      {
    // display what the person searched for

    echo "<p>Resultat for: &quot;" $var "&quot; </p>";



    // now you can display the results returned
     
    while($row=mysql_fetch_array($value2)){
    $dansk  =$row['dansk'];

    $ID  =$row['id'];            

     
    //-display  the result of the array

    echo  "<li>" "<a href='javascript:void()' onclick='window.location.href=\"search.php?id=" . ($ID) . "&dansk=". ($dansk) . "\"; parent.scrollTo(0,0); return false'><p> "   .$dansk " </a></li>\n</p>";


     
     
      }}
    The problem is that it only uses the last query, so I don't get any results from the first query. What is wrong?

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    Look in to UNION
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    New to the CF scene
    Join Date
    Nov 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Think I should use JOIN. Tried to do it like this
    PHP Code:
    $sql1="SELECT * FROM fagudtryk_knogler JOIN fagudtryk_muskler ON fagudtryk_knogler.latinsk = fagudtryk_muskler.latinsk where fagudtryk_knogler.latinsk = \"$trimmed%\" or fagudtryk_muskler.latinsk = \"$trimmed%\" 
    "

    But I can't get any results. Whatever I search for it just says 'No results' even though I know there should be a result


  •  

    Posting Permissions

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