Show values from other tables in a loop

Question

In my database I have 3 tables:

train_information:

+----------+-----------------+------------------+
| train_id | number_of_axles | number_of_bogies |
+----------+-----------------+------------------+
|        1 |               4 |                2 |
+----------+-----------------+------------------+

axle:

+---------+----------+------+----------+
| axle_id | train_id | axle | distance |
+---------+----------+------+----------+
|       1 |        1 |    1 |     2500 |
|       2 |        1 |    2 |     5000 |
|       3 |        1 |    3 |     2500 |
+---------+----------+------+----------+

bogie:

+----------+----------+---------+----------+
| bogie_id | train_id | axle_nr | bogie_nr |
+----------+----------+---------+----------+
|        1 |        1 |       1 |        1 |
|        2 |        1 |       2 |        1 |
|        3 |        1 |       3 |        2 |
|        4 |        1 |       4 |        2 |
+----------+----------+---------+----------+

When something gets inserted in the train_information table, a trigger also inserts in the other 2 tables (Distance & bogie_nr get updated later, but in this example everything is filled in already).

Now I make a train model based on the distance & axle values. Right now it looks like this:

<div id="axles">
    <!--This is the last (useless) axle, which always is 0-->
    <div id="useless_circle"></div>
    <!--Here we create the axles and style them with the distances-->
    <?php
        $show_axle = $database->axles($_GET['train_id']);
        $total_distance = 0;
        foreach($show_axle as $number_ofaxles){
            $total_distance += $number_ofaxles['distance']; ?>
            <div id="axle" name="test" style="margin-left:<?= $total_distance/25000*100;?>%">
                <?= "<div id='circle'>" . $number_ofaxles['axle'] . "</div>";?>
            </div>
    <?php } ?>
</div>

And:

function axles($id){
    $sql = "SELECT * FROM axle WHERE train_id = :id2";
    $sth = $this->pdo->prepare($sql);
    $sth->bindParam(":id2", $id, PDO::PARAM_STR);
    $sth->execute();
    return $sth->fetchAll();
}

Now, the page looks like this (With the values of the DB):

The code I provided is only for the axles! (the 4 circles beneath the train)!

Now, what I want:

Right now, I just ask for the value of the axle table. but it only contains 3 axles instead of 4. This is because I want to know the distance BETWEEN each axle. so I always need 1 less.
I solved this by making 1 extra div that creates the circle (axle) and places is to the left.

What I would like to have is something like: show the axle_nr from the bogie table (So it shows 4). Get the distance where axle = axle_nr.
Then you always keep 1 empty. because axle 4. does not exist in the axle table. So i want to make a check: if axle does not exist then distance = 0. I don't want to insert this in the database, but just so i don't need the useless axle div anymore AND the axle stays on the left.

Why do I want this?
This way I can check which bogie numbers are the same, so I can give them each another color etc. Also I won't need the useless_axle div!

EDIT:

Simple explanation:

I want to show the Axle_nr from the bogie table. (So it displays 4 circles) However! i will need the Distance from the axle table in order to make a train figure.
as you can see the axle table has 1 less axle than the bogie table.
So i want the "Not existing" axle to have a value of 0. I want it 0 because then it will show up on the beginning of the train. (Just like the useless axle right now)

CODE EDIT:

Right now I have got this:

   <div id="axles">
        <?php 
        $testingggg = $database->axleees();
        foreach ($testingggg as $lol){  ?>
            <div id="axle">
                    <div id="circle" name="<?= $lol['axle'] ?>"><?= $lol['axle'] ?></div>
            </div>
        <?php } ?>
    </div>

And:

function axleees() {
        $sql = "SELECT ti.axle_nr, ti.train_id, ti.bogie_nr, uti.axle_id, uti.train_id, uti.axle, uti.distance
                FROM bogie as ti
                JOIN axle as uti
                ON ti.train_id = uti.train_id
                WHERE ti.train_id = :train_id";
        $sth = $this->pdo->prepare($sql);
        $sth->bindParam(":train_id", $_GET["train_id"], PDO::PARAM_INT);
        $sth->execute();
        return $sth->fetchAll();
    }

And it shows me 12 axles instead of 4!

EDIT:

it is showing me 4 axles now which is correct. However I also need the correct distances. Code I have:

    <div id="axles">
    <?php
        $total_distance = 0;
        foreach ($testingggg as $lol){ 
            $total_distance += $lol['distance'];
    ?>
            <div id="axle" style="margin-left:<?= $total_distance/25000*100;?>%">
                    <div id="circle" name="<?= $lol['axle'] ?>"><?= $lol['axle_nr'] ?></div>
            </div>
        <?php } ?>
    </div>

Right now, it shows me that every axle has a margin of 10%. this is correct (if you only have the first axle). it needs to be something like 10-15-10-15 or so. How do I do this?

EDIT:

Right now I have the following query:

function axleees() {
        $sql = "SELECT ti.axle_nr, ti.train_id, ti.bogie_nr, uti.axle_id, uti.train_id, uti.axle, uti.distance
                    FROM bogie as ti
                    JOIN axle as uti
                    ON ti.train_id = uti.train_id
                    WHERE ti.train_id = :train_id
                    GROUP BY uti.axle_id";
        $sth = $this->pdo->prepare($sql);
        $sth->bindParam(":train_id", $_GET["train_id"], PDO::PARAM_INT);
        $sth->execute();
        return $sth->fetchAll();
    }

And I call it here:

<div id="axles">
            <?php
                $total_distance = 0;
                foreach ($testingggg as $lol){ 
                $total_distance += $lol['distance'];
                $margin = $total_distance/25000*100;
            ?>
            <div id="axle" style="margin-left:<?= $margin; ?>%">
                    <div id="circle" name="<?= $lol['axle'] ?>"><?= $lol['axle_nr'] ?></div>
            </div>
        <?php } ?>
            </div>

Image EDIT:

Answer 1

It seems to me that this is a rather convoluted way to work around the original problem. You are shy one axle, and need that axle to be in your database. You said that all values are added via a trigger in the database. If that is the case, why not add a value that has a distance of '0' with the id of the train. This will give you not only the axle, but the rendered div as well.

If your table looked like this after generation (please forgive me if the index is off in the wrong direction. I am struggling just a touch to understand your database layout):

+---------+----------+------+----------+
| axle_id | train_id | axle | distance |
+---------+----------+------+----------+
|       0 |        1 |    0 |        0 |
|       1 |        1 |    1 |     2500 |
|       2 |        1 |    2 |     5000 |
|       3 |        1 |    3 |     2500 |
+---------+----------+------+----------+

Then the following whould generate all circles, including the one that has a margin (or distance as you stated earlier) of '0'. Technically speaking, you have an axle with a distance of '0' from the front of the train, so why not track it in your database.

<div id="axles">
    <!--Here we create the axles and style them with the distances-->
    <?php
    $show_axle = $database->axles($_GET['train_id']);
    $total_distance = 0;
    foreach($show_axle as $number_ofaxles){
        // Because the first value is 0, the first run will be against the left edge.
        $total_distance += $number_ofaxles['distance']; ?>
        <div id="axle" name="test" style="margin-left:<?=$total_distance/25000*100;?>%">
            <?= "<div id='circle'>" . $number_ofaxles['axle'] . "</div>";?>
        </div>
    <?php } ?>
</div>

Taking this approach both simplifies and solves your problem.

Answer 2

Change

   $sql = "SELECT ti.axle_nr, ti.train_id, ti.bogie_nr, uti.axle_id, uti.train_id, uti.axle, uti.distance
            FROM bogie as ti
            JOIN axle as uti
            ON ti.train_id = uti.train_id
            WHERE ti.train_id = :train_id";

to

$sql = "SELECT ti.axle_nr, ti.train_id, ti.bogie_nr, uti.axle_id, uti.train_id, uti.axle, uti.distance
                    FROM bogie as ti
                    LEFT JOIN axle as uti
                    ON ti.train_id = uti.train_id AND uti.axle_id = ti.axle_nr
                    WHERE ti.train_id = :train_id";

Or run for testing next sql:

SELECT
      b.*,
      a.*
 FROM bogie AS b
 LEFT JOIN axle AS a ON a.train_id = b.train_id AND a.axle_id = b.axle_nr
WHERE b.train_id = 1

return 4 rows instead of 12.