Hi,

I made a page to show the information of the applications I apply and the interviews I made for selected applications and I use the inner join and left join to display data from different tables.

This week I add another table to enter in it the comments. I use it because if I wish add a comment for application or for the interiew. In mysql add left join to show the comments but I have some problems because the interview details doesn't display.

the problem is in line 10 in mysql query.

Anyone help me to correct it please?

Thanks.

<?php 
    require("../headerloggedin.php"); 
?>
    <div id="content">
        <p id="smalltitle">Vacancies applied for:</p>
            <?php
                $userid = $_SESSION["userid"];
                $allvacancies = 0;

                $query = "SELECT it.*,jt.*,aw.*,loc.*,son.*,com.* FROM applywork AS aw INNER JOIN locality AS loc ON aw.locality = loc.localityid INNER JOIN jobtype AS jt ON aw.jobtype = jt.typeid INNER JOIN seenon AS son ON aw.seenon = son.seenid LEFT JOIN interviews AS it ON aw.id = it.vacancyid LEFT JOIN comments AS com ON aw.id = com.appid WHERE aw.userid= $userid ORDER BY aw.id" ;
                $result = $db->query($query);

                while ($row = mysql_fetch_array($result))
                {
                    $allvacancies++;
            ?>
                    <table id="applicationheader">
                        <tr id="applicationid">
                            <td id="appid">Vacancy Number <?php echo $allvacancies;?></td>
                            <td>
                                <ul id="changeapp">
                                    <li><a href="editform.php?editid=<?php echo $row["id"];?>&edituserid=<?php echo $row["userid"];?>&editjobtype=<?php echo $row["jobtype"];?>&editlocality=<?php echo $row["locality"];?>">Edit Application</a></li>
                                    <li><a href="deleteapplication.php?id=<?php echo $row["id"]; ?>">Delete Application</a></li>
                                </ul>
                            </td>
                        </tr>
                    </table>
                    <table id="appdetails">
                        <tr id="details">
                            <td id="detailname">Apply For:</td>
                            <td id="detail" colspan="2"><?php echo $row["applyfor"];?></td>
                        </tr>
                        <?php
                            if($row["refnumber"] == "")
                            {
                                /*show nothing*/
                            }
                            else
                            {
                        ?>
                        <tr id="details">
                            <td id="detailname">Reference Number:</td>
                            <td id="detail" colspan="2"><?php echo $row["refnumber"];?></td>
                        </tr>
                        <?php
                            }
                        ?>
                        <tr id="details">
                            <td id="detailname">Job Type:</td>
                            <td id="detail" colspan="2"><?php echo $row["type"];?></td>
                        </tr>
                        <tr id="details">
                            <td id="detailname">Seen On:</td>
                            <td id="detail" colspan="2"><?php echo $row["seen"];?></td>
                        </tr>
                        <tr id="details">
                            <td id="detailname">Application Sent:</td>
                            <td id="detail" colspan="2"><?php echo $row["applicationsent"];?></td>
                        </tr>
                        <tr id="details">
                            <td id="detailname">Company Name:</td>
                            <td id="detail" colspan="2"><?php echo $row["companyname"];?></td>
                        </tr>
                        <tr id="details">
                            <td id="detailname">Address 1:</td>
                            <td id="detail" colspan="2"><?php echo $row["address1"];?></td>
                        </tr>
                        <?php
                            if($row["address2"] == "")
                            {
                                /*show nothing*/
                            }
                            else
                            {
                        ?>
                        <tr id="details">
                            <td id="detailname">Address 2:</td>
                            <td id="detail" colspan="2"><?php echo $row["address2"];?></td>
                        </tr>
                        <?php
                            }
                        ?>
                        <tr id="details">
                            <td id="detailname">Locality:</td>
                            <td id="detail" colspan="2"><?php echo $row["locality"];?></td>
                        </tr>
                        <?php
                            if($row["postcode"] == "")
                            {
                                /*show nothing*/
                            }
                            else
                            {
                        ?>
                        <tr id="details">
                            <td id="detailname">Postcode:</td>
                            <td id="detail" colspan="2"><?php echo $row["postcode"];?></td>
                        </tr>
                        <?php
                            }
                        ?>
                        <?php
                            if($row["telephone"] == "0")
                            {
                                /*show nothing*/
                            }
                            else
                            {
                        ?>
                        <tr id="details">
                            <td id="detailname">Telephone:</td>
                            <td id="detail" colspan="2"><?php echo $row["telephone"];?></td>
                        </tr>
                        <?php
                            }
                        ?>
                        <?php
                            if($row["website"] == "")
                            {
                                /*show nothing*/
                            }
                            else
                            {
                        ?>
                        <tr id="details">
                            <td id="detailname">Website:</td>
                            <td id="detail" colspan="2"><?php echo $row["website"];?></td>
                        </tr>
                        <?php
                            }
                        ?>
                        <?php
                            if($row["email"] == "")
                            {
                                /*show nothing*/
                            }
                            else
                            {
                        ?>
                        <tr id="details">
                            <td id="detailname">Email:</td>
                            <td id="detail" colspan="2"><?php echo $row["email"];?></td>
                        </tr>
                        <?php
                            }
                        ?>
                    </table>
                    <?php
                        if($row["comments.interviewid"] == "")
                        {
                            echo "hello";
                        }
                        else
                        {
                            echo "mario";
                        }
                    ?>
                    <table>
                        <tr>
                        </tr>
                    </table>
                    <table id="interviews">
                        <?php
                            $applyworkid = $row["id"];
                            $interviewid = $row["vacancyid"];

                            if($applyworkid == $interviewid)
                            {
                        ?>
                        <tr id="intdetails">
                            <td id="interviewname">Date:</td>
                            <td id="interviewdetail"><?php echo $row["date"];?></td>
                        </tr>
                        <tr id="intdetails">
                            <td id="interviewname">Time:</td>
                            <td id="interviewdetail"><?php echo $row["time"];?></td>
                        </tr>
                        <tr id="intdetails">
                            <td id="interviewname">Interview held in:</td>
                            <td id="interviewdetail"><?php echo $row["interviewin"];?></td>
                        </tr>
                        <?php
                            if($row["askfor"] == "")
                            {
                                /*show nothing*/
                            }
                            else
                            {
                        ?>
                        <tr id="intdetails">
                            <td id="interviewname">Ask for:</td>
                            <td id="interviewdetail"><?php echo $row["askfor"];?></td>
                        </tr>
                        <?php
                            }
                        ?>
                    </table>
                    <table id="intchange">
                        <tr id="intchanges">
                            <td>
                                <ul id="intchangebtn">
                                    <li><a href="editinterviewform.php?editid=<?php echo $row["id"];?>&edituserid=<?php echo $row["userid"];?>">Edit Interview Details</a></li>
                                    <li><a href="deleteinterview.php?id=<?php echo $row["interviewid"]; ?>">Delete Interview</a></li>
                                </ul>
                            </td>
                        </tr>
                    </table>
                    <table>
                        <?php
                            }
                            else
                            {
                        ?>
                        <tr id="intchange">
                            <td><a href="addcomment.php?editid=<?php echo $row["id"];?>&edituserid=<?php echo $row["userid"];?>">Add Comment</a></td>
                            <td id="newintext"><a href="interviewform.php?editid=<?php echo $row["id"];?>&edituserid=<?php echo $row["userid"];?>" id="intlink">Add Interview</a></td>
                        </tr>
                        <?php
                            }
                        ?>
                    </table>
                    <br />
            <?php 
                } 
            ?>
        <br />   
        <a href="../indexloggedin.php">Go to the homepage.</a>
    </div>
<?php require("../footerloggedin.php"); ?>

Debugging a query without table structures and sample data is next to impossible.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.