(PHP, MYSQL) How to check the conditions from different database table?

Question

I'm new to PHP and now I have some problems about the logic to query the data from my database.

First if I have 2 databases which are Application file and Position file.

Application file includes

Application_ID, 2.Applicant_ID, 3.Position_ID

and Position file includes

1.Position_ID, 2.Position_Name, 3.Department_Name

Let me show the unfinished codes...

Link (I copy everything in HTML provided field) -> https://jsfiddle.net/2e50wvzq/

I would like to focus here...

$result4 = mysql_query("SELECT * FROM position"); 
while ($rows4 = mysql_fetch_array($result4, MYSQL_ASSOC)){ 

    $pos_dept = $rows4['Position_Department'];

    $result5 =  mysql_query("SELECT * FROM position WHERE Position_Department = '".$pos_dept."' "); 

    while($rows5 = mysql_fetch_array($result5, MYSQL_ASSOC)){

            $pos_id = $rows5['Position_ID'];

    }

    for($i = 0; $i < $counter; $i++){       
            if($dept_array[$i] == $pos_dept){

            }else{

                        $result6 = mysql_query("SELECT App_Data_ID FROM application_data_file WHERE Position_ID = '".$pos_id."' "); 


                        $pos_app = 0;


                        while ($rows6= mysql_fetch_array($result6, MYSQL_ASSOC)){ 

                            $pos_a = $rows6['App_Data_ID'];

                            $pos_app++;

                        }

                        array_push($dept_array," '".$pos_dept."' ");


                        ?>
                        <TR>

                        <TD> <?php echo $rows4['Position_Department']; ?> </TD>
                        <TD> <?php echo $rows4['Recruitment_Seat'];    ?> </TD>

                        <TD> <?php echo $pos_app; ?>                    </TD>
                        <TD> <?php echo $recruited;   ?>                    </TD>
                        <TD> <?php echo $Vacancy;  ?>                   </TD>



                        </TR>

<?php 
                } 
        } 
}
?>

Assume that the data in databases are state as...

Application File

00001, 00001, 00001
00002, 00003, 00001
00003, 00002, 00002
00004, 00004, 00001
00005, 00006, 00002

and Position File

00001, Programmer, Department of IT
00002, Accountant, Department of Accounting

As my codes, the result in the table should be as this picture (focus in "Total Applicants" tab in the first table)

->

From my thinking so far, if I use the codes as I posted above the result will turn to be like table 2 (or maybe similar).

How can I provide the result like table 1 result? Please help.

If it doesn't work or works not as expected, please inform me, otherwise, you should mark as accepted so that in future others would notice. — Gynteniuxas
– Gynteniuxas, Commented May 29, 2016 at 8:32
@EdvinTenovim sorry for late checking, i'm going through your code now — gznero
– gznero, Commented May 29, 2016 at 13:42

Gynteniuxas · Accepted Answer · 2016-05-29 06:14:33Z

1

Just double check names in my query but code works in overall (because I tested it):

$sql = 'SELECT Department_Name, COUNT(*) AS Total FROM `application_file` LEFT JOIN `position_file` ON (application_file.Position_ID = position_file.Position_ID) GROUP BY Department_Name ORDER BY application_file.Position_ID ';
$result = $db->query($sql);
$total = 0;

if ($result->num_rows > 0)
{
    echo '<table class="table">';
    echo '<tr><td>Department</td><td>Total Recruitments</td><td>Total Applicant</td><td>Making Contract</td><td>Slot left</td></tr>';

    while($row = $result->fetch_assoc())
    {
        echo '<tr><td>'.$row['Department_Name'].'</td><td>x</td><td>'.$row['Total'].'</td><td>x</td><td>x</td></tr>';
        $total += $row['Total'];
    }

    echo '<tr><td>Total</td><td>x</td><td>'.$total.'</td><td>x</td><td>x</td></tr>';
}
else
    echo '0 results';

answered May 29, 2016 at 6:14

Gynteniuxas

7,09918 gold badges41 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

gznero Over a year ago

as i go through your code, "Total" is not in my database, actually it is something that should be calculated from all rows above. Also, now it tells me that there is fatal error in "$result = $db->query($sql);"

Gynteniuxas Over a year ago

That's true, Total is not one of your columns, but Total is a new column that appears from my query. I also noticed you're using PHP's mysql instead of mysqli, so that's why there is a fatal error. Please check how to configure mysqli as mysql is deprecated.

gznero Over a year ago

and what can I replace "Total" in $sql line?

Gynteniuxas Over a year ago

You don't need to replace it, since it's a new column, it doesn't matter what the query is as long as it is doesn't provide any errors. The problem (fatal error) is in PHP mysqli structure (yours is mysql).

Collectives™ on Stack Overflow

(PHP, MYSQL) How to check the conditions from different database table?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related