Retrieve Data from different table for selected data

[marifard]

Hi,

I make a page to display all applications from table and few of these applications have an interview dates, location etc in another table. I use this code pasted in this article below. The problem is showing the applications with interviews only and the interviews without interviews are not displayed. Anyone help me pls to display the applications without interviews too?

<?php 
    require("../../headerloggedin.php"); 
?>
    <div id="content">
            <?php
                $userid = $_SESSION["userid"];
                $query = "SELECT * FROM applywork, locality, jobtype, interviews WHERE applywork.jobtype = jobtype.typeid AND applywork.locality = locality.localityid AND interviews.vacancyid = applywork.id AND applywork.userid = $userid";
                $result = $db->query($query);

                while ($row = mysql_fetch_array($result))
                {
            ?>
            <table>
                <tr>
                    <td>Vacancy:</td>                     
                    <td><?php echo $row["applyfor"];?></td>
                </tr>
                <tr>
                    <td>Seen on:</td>
                    <td><?php echo $row["vacancyseenon"]; ?></td>
                </tr>
                <tr>
                    <td>Company name:</td>
                    <td><?php echo $row["companyname"]; ?></td>
                <tr>
                <?php 
                    if($row["address2"] == "")
                    {
                ?>
                    <td>Address:</td>
                    <td><?php echo $row["address1"]; ?></td>
                <?php
                    }
                    else
                    {
                ?>
                    <td>Address 1:</td>
                    <td><?php echo $row["address1"]; ?></td>
                </tr>
                <tr>
                    <td>Address 2:</td>
                    <td><?php echo $row["address2"]; ?></td>
                </tr>
               <?php
                    }
               ?>
                <tr>
                    <td>Locality:</td>
                    <td><?php echo $row["locality"]; ?></td>
                <tr>
                    <?php
                        if($row["postcode"] == "")
                        {
                        }
                        else
                        {
                    ?>
                    <td>Postcode:</td>
                    <td><?php echo $row["postcode"]; ?></td>
                    <?php
                        }
                    ?>
                <tr>
                    <td>Telephone:</td>
                    <td><?php echo $row["telephone"]; ?></td>
                <tr>
                    <?php
                        if($row["website"] == "")
                        {

                        }
                        else
                        {
                    ?>
                    <td>Website:</td>
                    <td><?php echo $row["website"]; ?></td>
                    <?php
                        }
                    ?>
                <tr>
                    <?php
                        if($row["email"] == "")
                        {
                        }
                        else
                        {
                    ?>
                    <td>Email:</td>
                    <td><?php echo $row["email"]; ?></td>
                    <?php
                        }
                    ?>
                </tr>
                <tr>
                    <td>Interview date:</td>
                    <td><?php echo $row["date"]; ?></td>
                </tr>
                <tr>
                    <td>Interview time:</td>
                    <td><?php echo $row["time"]; ?></td>
                </tr>
                <tr>
                    <td>Interview in:</td>
                    <td><?php echo $row["interviewin"]; ?></td>
                </tr>
            </table>
            <br />
            <?php 
                } 
            ?>
        <br />   
        <a href="../../indexloggedin.php">Go to main menu.</a>
    </div>
<?php require("../../footerloggedin.php"); ?>

[broj1]

You will have to use the correct type of joins (LEFT JOIN, RIGHT JOIN) to retrieve records with empty values. Which type of JOIN depends on the structure of tables and where the empty values are. See this tutorial:

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

[diafol]

You are creating a multiple join. If you want to show all records whether they have interviews or not, this needs to be a LEFT or RIGHT JOIN, depending how you write your JOINS.

"SELECT it.*,jt.*,aw.*,loc.* FROM applywork AS aw INNER JOIN locations AS loc ON aw.locality = loc.locality_id INNER JOIN jobtype AS jt ON aw.jobtype = jt.typeid LEFT JOIN interviews AS it ON aw.id = it.vacancyid WHERE aw.userid= $userid" 

The only thing I'd add would be that this returns every field in every table. You don't need this. Strip out the fields that you're not going to need, e.g.

SELECT tbl1.field2, tbl1.field4, tbl2.field1, tbl2.field7... FROM ...