SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Bah, I'll just hack it DoobyWho's Avatar
    Join Date
    Jul 2002
    Posts
    476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching more then one table

    My current SQL statement is
    PHP Code:
    $sql "SELECT * FROM Registrant WHERE last LIKE \"%$lastname%\" AND state=\"$state\""
    I need that statement to search 2 tables , so it will return results from both tables (Registrant and CoRegistrant).

    I know its probably some sort of join statement, I just don't know which one! Help!

  2. #2
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $sql "SELECT * FROM Registrant as R, CoRegistrant as C WHERE R.last LIKE '%$lastname%' AND C.last LIKE '%$lastname%' AND R.state = '$state' AND C.state = '$state'"
    This is just off the cuff, and not guarenteed to work. I'm sure there is a better way as I am not well versed in the "JOIN"s.
    John

  3. #3
    Bah, I'll just hack it DoobyWho's Avatar
    Join Date
    Jul 2002
    Posts
    476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so then how would i use the results? after i do the whole $row = mysql_fetch_array etc etc thing

    if i normally would have used $row=['name']; to grab the name from Registrant.

  4. #4
    if($awake){code();} PHP John's Avatar
    Join Date
    Jul 2002
    Location
    Along the Wasatch Fault line.
    Posts
    1,771
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A most excellent question, and a consequence I had not considered.

    I see enough of these types of questions, I guess I'm going to have to break out the mySQL manual and do some studying on JOIN's.
    John

  5. #5
    Bah, I'll just hack it DoobyWho's Avatar
    Join Date
    Jul 2002
    Posts
    476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Basically here is how it works..

    Someone enters a last name and state , then it should search both the registrant and coregistrant tables for records containing that info. If it contains that info, then it should look grab the 'regID' for each one. With each regID, it should search table 'events' and grab each record in there that has that regID. AFter that ill be echoing the info. I've got it to work for one table. but not searching both registrant and coregistrant

  6. #6
    Bah, I'll just hack it DoobyWho's Avatar
    Join Date
    Jul 2002
    Posts
    476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This works to search one table:
    PHP Code:
    <?php
    if((!$lastname) && (!$state)){
    header('location: find_registry.php');
    }else{
    include(
    '../library/lib.php');
    connect_db();
    $sql "SELECT * FROM Registrant WHERE last LIKE '%$lastname%' AND state='$state'";
    $sql_result mysql_query($sql) or die(mysql_error());
    if(!
    $sql_result)
    {
    echo(
    mysql_error());
    }
    while(
    $result1=mysql_fetch_array($sql_result)){
    $regID $result1["regID"];
    $sql2 "SELECT * FROM CoRegistrant WHERE regID='$regID'";
    $sql_result2 mysql_query($sql2) or die(mysql_error());
    if(!
    $sql_result2)
    {
    echo(
    mysql_error());
    }
    while(
    $result2=mysql_fetch_array($sql_result2)){
    $sql3 "SELECT * FROM Events WHERE regID='$regID'";
    $sql_result3 mysql_query($sql3) or die(mysql_error());
    if(!
    $sql_result3)
    {
    echo(
    mysql_error());
    }
    while(
    $result3=mysql_fetch_array($sql_result3)){
    $lastname $result1["last"];
    $firstname $result1["first"];
    $colastname $result2["last"];
    $cofirstname $result2["first"];
    $eventtype $result3["type"];
    $eventdate $result3["date"];
    echo (
    "<a href=""\"shop_view.php?id=$regID\"><b>$firstname $lastname and $cofirstname $colastname</b></a> - $eventtype on $eventdate</b>" );
    }
    }
    }
    }
    ?>
    Last edited by DoobyWho; May 12, 2003 at 09:58.

  7. #7
    SitePoint Enthusiast Donut's Avatar
    Join Date
    Nov 2001
    Location
    Brisbane, Australia
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You will a JOIN for a query to search two tables, if you don't you will get output of number of rows in table A multiplied by number of rows in table B.

    It has been about three years since I have done any complex SQL, so this should be close


    SELECT * FROM TABLE_A, TABLE_B WHERE TABLE_A.PRIMARY_KEY = TABLE_B.FOREIGN_KEY AND (other selection criterea)

    Okay that should work where:
    TABLE_A = name of first table
    TABLE_B = name of second key
    TABLE_A.PRIMARY_KEY = the primary key of TABLE_A
    TABLE_B.FOREIGN_KEY = The foreign key of TABLE_A relates to the primary key of TABLE_A

    To use this properly, you need to make sure that you have proper referential integrity rules in place or you'll get crap results.

  8. #8
    Bah, I'll just hack it DoobyWho's Avatar
    Join Date
    Jul 2002
    Posts
    476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont think that kind will work. see, people might not know the last name of the registrant. so it needs to search the lastname of Registrant and CoRegistrant, only pulling up one record for each regID. Then it needs to search Events for a matching regID. so basically. Either it finds a match in either table, for each regID, but not both. We dont want repeating records.

    So if say..

    John Bobby is a registrant
    and Bob Bobby is the co registrant.

    someone searches for 'Bobby'
    It needs to pull all the info from both tables.
    and it will only display once "John Johny and Bob Bobby - Basketball game on 5/5/2003" for example. Instead of displaying it once for the result from Registrant and once from the CoRegistrant.

  9. #9
    Bah, I'll just hack it DoobyWho's Avatar
    Join Date
    Jul 2002
    Posts
    476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just did this, in hopes it would work, however it doesn't.

    PHP Code:
    <?php
    if((!$lastname) && (!$state)){
    header('location: find_registry.php');
    }else{
    include(
    '../library/lib.php');
    connect_db();
    $sql "SELECT reg.last,reg.regID,reg.first,coreg.last,coreg.first,coreg.regID,reg.state,coreg.state
       FROM Registrant as reg, CoRegistrant as coreg
       WHERE ( (reg.regID = coreg.regID && reg.last LIKE \"%
    $lastname%\" && reg.state=\"$state\")
       || (reg.regID = coreg.regID && coreg.last LIKE \"%
    $lastname%\" && reg.state=\"$state\") )";
    $sql_result mysql_query($sql) or die(mysql_error());
    if(!
    $sql_result)
    {
     echo(
    mysql_error());
    }
    while(
    $result1=mysql_fetch_array($sql_result)){
     
    $regID $result1["reg.regID"];
      
            
    $sql3 "SELECT * FROM Events WHERE regID='$regID'";
      
    $sql_result3 mysql_query($sql3) or die(mysql_error());
      if(!
    $sql_result3)
      {
       echo(
    mysql_error());
      }
            while(
    $result3=mysql_fetch_array($sql_result3)){
                
    $lastname $result1["reg.last"];
                
    $firstname $result1["reg.first"];
                
    $colastname $result1["coreg.last"];
                
    $cofirstname $result1["coreg.first"];
                
    $eventtype $result3["type"];
                
    $eventdate $result3["date"];
       
       if( (
    $lastname == $lastname) && ($firstname == $firstname) ) {
                 echo (
    "<a href=""\"shop_view.php?id=$regID\"><b>$firstname $lastname - $eventtype on $eventdate</b>");}else{   
                echo (
    "<a href=""\"shop_view.php?id=$regID\"><b>$firstname $lastname and $cofirstname $colastname</b></a> - $eventtype on $eventdate</b>");}
                }
            }
        }
    ?>
    what's wrong with it?

  10. #10
    Bah, I'll just hack it DoobyWho's Avatar
    Join Date
    Jul 2002
    Posts
    476
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no one?


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
  •