Page 1 of 1

Using Info from two tables

Posted: Mon Dec 29, 2008 12:36 pm
by Da_Elf
Ive got two mysql tables. one is for user info like name, age etc the other one is for skills. Both mysql tables have UserID in common. Im trying to create a HTML table which will list out all people by first name last name and age who have Accepted = 1 (found in first mysql table) and also where Dance = 1 (from second table). there might be a cleaner way to do this but this is my code. However its not working. It doesn't list anyone even though ive got people who have dance = 1

Code: Select all

        <table border="1" cellpadding="0" cellspacing="2" width="192">
            <tr>
                <td align="center">First Name</td>
                <td align="center">Last Name</td>
                <td align="center">Age</td>
                <td></td>
            </tr>
            <?php   
    $sqluser = "SELECT * FROM User 
    Where Accepted='1'
    ORDER BY UserFirstName ";
    $queryuser = mysql_query($sqluser, $dbConn);
    while ($spu = mysql_fetch_array($queryuser, MYSQL_ASSOC)) {
            $idz = $spu['UserId'];
            $sqld = "SELECT * FROM skills   Where UserID='$idz' ";
            $qd = mysql_query($sqld, $dbConn);
            $spd = mysql_fetch_array($qd);
            $truefalse = $spd['dance'];
            if ($truefalse == 1){
        ?>
            <tr>
                <td align="center"><?=$spu['UserFirstName']?></td>
                <td align="center"><?=$spu['UserSurname']?></td>
                <td align="center"><?=$spu['UserAge']?></td>
                <td>VIEW</td>
            </tr>
<?php }} ?>
        </table>

Re: Using Info from two tables

Posted: Mon Dec 29, 2008 12:44 pm
by Mark Baker

Code: Select all

 
SELECT U.*
 FROM User U,
      Skills S
 WHERE U.Accepted=1
   AND S.UserID = U.UserID
   AND S.dance = 1
 ORDER BY U.UserFirstName
 

Re: Using Info from two tables

Posted: Mon Dec 29, 2008 12:49 pm
by Da_Elf
sigh. now i feel dumb. well i am a beginner after all and code more on what seems logical with what little code i know. but sometimes logic and syntax dont mesh well. Thanks a bunch mark