www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: selecting from multiple tables

  1. #1
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197

    selecting from multiple tables

    Hi

    I'm trying to use information in may tables, each table has a common column 'username'. I want to use the data in each table that matches the user name.

    the colde below gives this error message
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' username = 'proctk', username = 'proctk', username = 'proctk', username = 'proc' at line 1

    PHP Code:
    $username $_SESSION['username'];

    $query= ("SELECT * FROM children, spouse, sibling, aboutMe, users WHERE username = '$username', username = '$username', username = '$username', username = '$username', username = '$username'");

    $result mysql_query($query) or die(mysql_error());

    $num mysql_numrows($result);

    mysql_close();


    $i=0;

    while (
    $i $num) { 
    Last edited by kproc; 07-29-2006 at 09:35 AM.

  2. #2
    for selecting from tables which match ALL the conditions
    Code:
    $query= ("SELECT * FROM children c, spouse sp, sibling sb, aboutMe am, users u WHERE c.username = '$username' AND sp.username = '$username' AND sb.username = '$username' AND  am.username = '$username' AND u.username = '$username'");
    for checking each table use seperate queries

  3. #3
    Join Date
    Jun 2006
    Posts
    472
    Well firstly it's not a good way to design a database...
    Secondly you have 5 tables so you need four joins to connect those tables to start with..
    Code:
    SELECT * FROM children, spouse, sibling, aboutMe, users 
    WHERE tablename.username = anothertablename.username
    AND tablename.username = anothertablename.username
    AND tablename.username = anothertablename.username
    AND tablename.username = anothertablename.username
    AND tablename.username ='$username';

  4. #4
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    thanks for the help you just taugh me something new.
    Kevin

  5. #5
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    I ran into a little snag. It looks like its looping throuhg twice as it doubles each entery.

    PHP Code:
    include 'db.php'


    $username $_SESSION['username'];

    $query= ("SELECT * FROM children c, spouse sp, sibling sb, aboutMe am, users u WHERE c.username = '$username' AND sp.username = '$username' AND sb.username = '$username' AND  am.username = '$username' AND u.username = '$username'");

    $result=mysql_query($query) or die(mysql_error());

    $num=mysql_numrows($result);

    mysql_close();


    $i=0;

    while (
    $i $num) {

    $fistname mysql_result($result,$i,"childfirstname");
    $lastname mysql_result($result,$i,"childlastname");
    $dob mysql_result($result,$i,"childdob");
    $sex mysql_result($result,$i,"childsex");

    $momFirstName mysql_result($result,$i,"momFirstName");
    $momLastName mysql_result($result,$i,"momLastName");
    $momDob mysql_result($result,$i,"momDob");
    $dadFirstName mysql_result($result,$i,"dadFirstName");
    $dadLastName mysql_result($result,$i,"dadLastName");
    $dadDob mysql_result($result,$i,"dadDob");
    $anniversaryDate mysql_result($result,$i,"anniversaryDate");
    $maddenName mysql_result($result,$i,"maddenName");

    $siblingfistname mysql_result($result,$i,"siblingfirstname");
    $siblinglastname mysql_result($result,$i,"siblinglastname");
    $siblingdob mysql_result($result,$i,"siblingdob");
    $siblingsex mysql_result($result,$i,"siblingsex");

    $spousefistname mysql_result($result,$i,"spousefirstname");
    $spouselastname mysql_result($result,$i,"spouselastname");
    $spousedob mysql_result($result,$i,"spousedob");
    $spousesex mysql_result($result,$i,"spousesex");


    echo 
    "<p class='$sex'><b>First Name:</b> $fistname<br>
    <b>Last Name:</b> 
    $lastname<br>
    <b>DOB:</b> 
    $dob<br>
    <b>Age:</b> 
    $age<br>
    <a href='UpdateChildren.php?id=
    $userid'>Edit</a>
    <a href='deleteChild.php?id=
    $userid'>Delete</a>
    </p><br>"
    ;

    $i++;


    Kevin

  6. #6
    Join Date
    Jun 2006
    Posts
    472
    PHP Code:
    include 'db.php';


    $username $_SESSION['username'];

    $query= ("SELECT * FROM children c, spouse sp, sibling sb, aboutMe am, users u WHERE c.username = '$username' AND sp.username = '$username' AND sb.username = '$username' AND  am.username = '$username' AND u.username = '$username'");

    $result=mysql_query($query) or die(mysql_error()); 

    //use the mysql_fetch_array function and test if variables are getting assigned
    // keep using the $row variable for any other column info you need printed
    while ($row mysql_fetch_array($resultMYSQL_ASSOC))
    {
     echo 
    $row['fistname'];
     echo 
    $row['lastname'];
     echo 
    $row['dob'];
     echo 
    $row['age'];
     
    }


    mysql_close(); 

  7. #7
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    I tried the code that you provided but nothing is printed on one screen. I get no error messages, just a blank screen.

    In some tables there could be multiple enteries that will be printed as the username could be found more then once in the table.

    In my example where I assign everything to variables it works, but creates double enteries for each item that matches the username.


    The code that posted above works great when i reference on one table.
    Last edited by kproc; 07-29-2006 at 03:57 PM.
    Kevin

  8. #8
    Join Date
    Jun 2006
    Posts
    472
    Quote Originally Posted by kproc
    I tried the code that you provided but nothing is printed on one screen. I get no error messages, just a blank screen.

    In some tables there could be multiple enteries that will be printed as the username could be found more then once in the table.

    In my example where I assign everything to variables it works, but creates double enteries for each item that matches the username.


    The code that posted above works great when i reference on one table.
    These should be the name of your columns..change them if you have to
    PHP Code:
    while ($row mysql_fetch_array($resultMYSQL_ASSOC))
    {
    echo 
    $row['childfistname'];
    echo 
    $row['childlastname'];
    echo 
    $row['childdob'];
    echo 
    $row['childage']; 


  9. #9
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    Thanks for the help,

    this displays the information but it repeats it 6 times. there is only one cloumn in table 'spouse' that should be printed.

    its doing the same that that was happening when I first started this post

    any ideas why

    thankyou for your help

    PHP Code:
    <?
    include 'db.php'

    $username $_SESSION['username'];

    $query = ("SELECT * FROM children, spouse, sibling, aboutMe, users WHERE children.username = '$username' AND spouse.username = '$username' AND sibling.username = '$username' AND  aboutMe.username = '$username' AND users.username = '$username'");

    $result mysql_query($query) or die(mysql_error());

    $num mysql_numrows($result);

    while (
    $row mysql_fetch_array($resultMYSQL_ASSOC) or die(mysql_error())) {
        echo 
    '<p class='.$row['spousesex'].'><b>First Name:</b> '.$row['spousefirstname'].'<br>
        <b>Last Name:</b> '
    .$row['spouselastname'].'<br>
        <b>DOB:</b> '
    .$row['spousedob'].'<br>
        </p><br>'
    ;
        
        }

    ?>
    Kevin

  10. #10
    Join Date
    Jun 2006
    Posts
    472
    Without really knowing how your tables are set up and how they relate to each other is hard to say. I can only suggest that it's your database design and having the username in all of the tables is giving duplicate values. Have you tried testing that SQL in MySQL just by itself, does it give you the same results?

  11. #11
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    Each table has a column called 'username' I did this so that I could link each table as needed. Each table has a various number of columns all set up the same way. There was a comment that my database design was not great.

    would there be a web site or tutorial to sho me a better way of doing it.

    What I'm trying to do is create a summary page for all the users information from all the tables.
    Kevin

  12. #12
    Join Date
    Jun 2006
    Posts
    472
    Quote Originally Posted by kproc
    Each table has a column called 'username' I did this so that I could link each table as needed. Each table has a various number of columns all set up the same way. There was a comment that my database design was not great.

    would there be a web site or tutorial to sho me a better way of doing it.

    What I'm trying to do is create a summary page for all the users information from all the tables.
    Well if you do a search on entity relationships and normalisation you should get some websites..

    But here's a quick tutorial...If we take four(4) of you tables and only put the information about that entity (table) and nothing else
    Code:
    children
    child_id(pk)
    childfirstname
    childlastname
    childdob
    
    
    sibling
    sibling_id(pk)
    sibfirstname
    siblastname
    etc
    etc
    
    spouse
    spouse_id(pk)
    spousefirstname
    spouselastname
    etc
    etc
    
    users
    user_id(pk)
    userfirstname
    userlastname
    username
    etc
    etc
    These tables should not have any information from any other table in them only information about them...
    Then there is the last table..this is the assocate table that links all the other tables using the column _id's

    Code:
    aboutMe
    aboutme_id(pk)
    child_id(fk)
    spouse_id(fk)
    sibling_id(fk)
    any
    other
    info
    Can you see how there is no dulicated data only the primary key(pk) and foreign key(fk) and that;s how you establish the relationships.. but there are more detailed websites out there I'm sure

  13. #13
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    Thank you for the help. I'm working at fixing up my table. I hit a snag and not shure how to create the assoiate table. did some google seaching but not getting anything that explains in simple terms.

    thank you for the help
    Kevin

  14. #14
    Join Date
    Jun 2006
    Posts
    472
    Quote Originally Posted by kproc
    Thank you for the help. I'm working at fixing up my table. I hit a snag and not shure how to create the assoiate table. did some google seaching but not getting anything that explains in simple terms.

    thank you for the help
    What kind of snag? An associate table is just a table that has the links to the other tables like the aboutMe table above..There's are also called relationship tables..http://www.umsl.edu/~sauter/analysis/er/er_intro.html
    You also need to make sure that the foreign key columns that you set up have to have the same data type and size as the primary key in the original table. Looking at those tables I would get rid of the spouse table that information can be added to the users table since people wouldn't have multiple spouses..
    Last edited by aussie girl; 07-30-2006 at 02:56 AM.

  15. #15
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    I have a basic understanding of the concept. writeing the code to make sure that the PK is add when data is entered into each table I cannot figure out
    Kevin

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
  •  
HTML5 Development Center



Recent Articles