Using Info from two tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Da_Elf
Forum Commoner
Posts: 81
Joined: Mon Dec 29, 2008 12:31 pm

Using Info from two tables

Post 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>
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Using Info from two tables

Post 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
 
Da_Elf
Forum Commoner
Posts: 81
Joined: Mon Dec 29, 2008 12:31 pm

Re: Using Info from two tables

Post 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
Post Reply