0

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"); ?>
3
Contributors
2
Replies
10
Views
5 Years
Discussion Span
Last Post by diafol
0

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 ...

Edited by diafol

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.