MySQL reference INNER JOIN and reach data

Question

I have some code and a database that does not really speak together the way I want. But taken this information, you might be able to help. I have a MySQL database which looks like this:

house_room1:

objects:

I then have a database connection using PHP prepared statements which uses INNER JOIN to connect these two tables together with object_id -> ref_id.

As you can see, I have 5 different images in the objects table that I display with the PHP, however I want one of the images, the one with object_id = 2 to be displayed twice.

My PHP code looks like this:

 $n = 0;
    $item_number = 0;
    //Array which iterates over all objects in a given users object_id
    $ref[$n];

    for ($i = 0; $i < $rowsize; $i++) {
        if ($i == $ref[$n]) {
            $stmt = $mysqli->stmt_init();
            $stmt->prepare('SELECT x, y, src, link, div_id FROM house_room1 INNER JOIN objects ON house_room1.ref_id=objects.object_id WHERE house_room1.ref_id = ?'); 
            $stmt->bind_param('i', $i
            );
            if ($stmt->execute()) {
                $stmt->bind_result($x, $y, $src, $link, $div_id);
                while($stmt->fetch()) {
                    if ($link != "") { 
                        echo '<a href="' . $link . '"> '; 
                    }
                    if ($div_id != "") { 
                        echo '<a href="#" onClick="' . $div_id . '"> '; 
                    }
                    echo '<img src="' . $src . '"class="item' . $item_number . '" style="position:absolute; left:' . $x . 'px; top:' . $y . 'px;">'; if ($x != 0) { echo'</a>'; }
                }
            } else {
                echo 'Something went terrible wrong' . $mysqli->error;
            }
            $stmt->close();
            $n++;
            $item_number++;
        }
    }

What happens is that only 5 images are displayed and the last one is missing. I guess it has something to do with the fact that the two tables does not have the same amount of rows but I am not quite sure, and if that is the case, I have some DB issues. I hope you understood my problem and have some advice/suggestions, thanks in advance.

Answer 1

Why are you joining object_id -> ref_id? It seems like it should be house_room1.object_id=objects.object_id