Check if FK order is numbered correctly

Question

I have some absences, and each absence has an FK to an employee, which I wanna display in a generated HTML table. However, if I delete an employee the order changes from 0, 1, 2 to 1, 2 (for example if the second employee got deleted).

This messed up my code, because I need the FK to check where I have to insert the leave (in which <tr>). Here I count the employees:

$result = mysql_query("select count(1) FROM employee");
$row    = mysql_fetch_array($result);
$count_user = $row[0];

Later in my code I make a query in a loop. The loop runs as many times as there are users. And heres the problem: if an employee gets deleted it will not reach the other one ($i goes 0 and then 1 if there are 2 users, but if one is deleted the FK is 3, so it would need to go one further). to

 $result = mysql_query("select start, end, type_FK, employee_FK FROM absences where employee_FK = $i");
                            while ($row = mysql_fetch_assoc($result)) {
                            $array_absences[] = $row;
                            }

Has anyone an Idea that is not based on the FK order? Also this topic needs a better title so feel free to edit it.

<html>
    <head>
        <title>Absence System</title>
    </head>

    <body>
        <?php
            $con = mysql_connect("localhost", "root", "");
            if (!$con) {
                die('Could not connect: ' . mysql_error());
            }

            mysql_select_db("absence_system", $con);


            $result = mysql_query("select count(1) FROM employee");
            $row    = mysql_fetch_array($result);
            $count_user = $row[0];




            $result = mysql_query("select count(1) FROM absences");
            $row    = mysql_fetch_array($result);
            $count_absences = $row[0];



            $result = mysql_query("select name, surename, employee_ID FROM employee");
                while ($row = mysql_fetch_assoc($result)) {
                    $array_user[] = $row;
            }


            for($i = 0; $i < $count_user; $i++){
                echo '<table = border = 1px>';
                    echo '</tr>';

                        $result = mysql_query("select start, end, type_FK, employee_FK FROM absences where employee_FK = $i");
                        while ($row = mysql_fetch_assoc($result)) {
                        $array_absences[] = $row;
                        }

                        $count = count($array_absences);
                        echo $count;

                        print_r($array_absences);

                        for($j = 0; $j < 32; $j++){
                        $true = 0;
                        if($j == 0){
                        echo '<td>';
                        echo $array_user[$i]['name'], " ", $array_user[$i]['surename'] ;
                        echo '</td>';
                        }

                        for($k = 0; $k < $count; $k++)
                        {

                        $array_absences[$k]['start'] = substr($array_absences[$k]['start'], -2);
                        $array_absences[$k]['end']   = substr($array_absences[$k]['end'], -2);

                        $array_absences[$k]['start'] = ereg_replace("^0", "", $array_absences[$k]['start']);
                        $array_absences[$k]['end']   = ereg_replace("^0", "", $array_absences[$k]['end']);

                        if($j == $array_absences[$k]['start'] && $array_absences[$k]['employee_FK'] == $i){
                        $true = 1;
                        echo '<td>';
                        echo $array_absences[$k]['type_FK'];
                        echo '</td>';
                        }
                        }


                        if($j != 0 && $true == 0){
                        echo '<td>';
                        echo "$j";
                        echo '</td>';
                        }
                        }
                    echo '</tr>';
                echo '</table>';
            }
        ?>
    </body>

</html>

score 1 · Accepted Answer · answered Feb 24 '15 at 09:17

1

If employee_ID corresponds to employee_FK, then you should not iterate over $count_user, but rather over $array_user - then your select would looke like this:

$result = mysql_query("select start, end, type_FK, employee_FK FROM absences where employee_FK = {$array_user[$i]['employee_ID']}");

while $i will still represent sequence number, because

$count_user == count($array_user)

answered Feb 24 '15 at 09:17

n-dru

9,285
2
29
42

I see your point, but why do you add add braces? And is that the only step I have to do? – Gintoki Feb 24 '15 at 09:26
Instead of curly braces, you can join those strings ".$array_user[$i]['employee_ID']." to know what those curly braces mean, you can read this: http://stackoverflow.com/questions/2596837/curly-braces-in-string-in-php – n-dru Feb 24 '15 at 09:28
Alright I got my failure, I wrote employe_id in the query instead of employee_ID. I will try and test it and post here again. – Gintoki Feb 24 '15 at 09:43

Check if FK order is numbered correctly

1 Answers1