Duplicate Frame Titles

General PHP Help
#1

I’m creating an edit page that has a many to many relationship between the banners table and the frames table.
The problem I’m having is that I’m getting duplicates of the frames.

I hope I’ve explained everything clearly.

So for example I have

Frame 1
Frame 1
Frame 2
Frame 2
Frame 3

Instead of

Frame 1
Frame 2
Frame 3

This is my tables

banners table

id  |   title
1   |   Home banner

frames table

id  |   title   |   description |   image
1   |   Frame 1 |   Frame 1     |   frame.jpg
2   |   Frame 2 |   Frame 2     |   frame2.jpg
3   |   Frame 3 |   Frame 3     |   frame3.jpg
4   |   Frame 4 |   Frame 4     |   frame4.jpg
5   |   Frame 5 |   Frame 5     |   frame5.jpg

banner_frame table

id  |   banner_id   |   frame_id
1   |       1       |       1
2   |       2       |       1
2   |       2       |       5

Here is my code

$query = "SELECT frames.id as frameId, frames.title as frameTitle, banners.id as banner_id ";
$query .= "FROM frames ";
$query .= "LEFT JOIN banner_frame ON banner_frame.frame_id = frames.id ";
$query .= "LEFT JOIN banners ON banners.id = banner_frame.banner_id";
        
$banner_frame = mysqli_query($conn, $query);

confirmQuery($banner_frame);

while($row = mysqli_fetch_assoc($banner_frame))
{   
    $frame_id = $row['frameId'];
    $frame_title = $row['frameTitle'];

    $checked = '';
    if ($row['banner_id'] == $banner_id) {
        $checked = 'checked';
    }

    echo "<div class='form-check'>";
    echo    "<input type='checkbox' name='frames[]' id='frame_checkbox' value='$frame_id' $checked> ";
    echo    "<label for='frame_checkbox'>";
    echo        $frame_title;
    echo    "</label>";
    echo "</div>";
}
#2

Why are you doing left joins?

#3

With a many to many each banner could have multiple frames. I think that this is a bad example? Or should you use a many to one?

A better example would be a blogAtricle with tags.

some blogArticle titles:

  • grid systems
  • php arrays
  • html buttons
    etc:

Some tags:

  • css
  • html
  • frontend
  • backend
  • php

Each blogArticle can have multiple (or many) tags.
Each Tag can be used on multiple blogArticles.

So that is a many-to-many!

now query all articles with the tag ‘frontend’ (frontend has id 5):

SELECT
    a.title, t.name
FROM 
    blogArticles  a 
JOIN 
    blogArticles_tags at 
ON 
    a.id = at.blogArticle_id
JOIN 
    tags t
ON 
    t.id = at.tag_id
WHERE
    t.id = 5

The result would be:

Title        | Name
-------------|----------
grid systems | frontend
html buttons | frontend

As you can see the tag name will always be the same because of the WHERE claus and therefore not really necessary. But when you change the WHERE claus to some tags e.g. t.id = 5 OR td.id = 8 then it you would get something like this (8 = HTML)

Title        | Name
-------------|----------
grid systems | frontend
html buttons | frontend
grid systems | html
html buttons | html

Although you see double values the combinations of every row are different.

#4

Checking just your sql from above and I noticed you don’t have banners id banners.id as banner_id from frames table is this intentional from your select sql? If so there is a chance for duplicated result because null will be returned using left join. see link for further explanation

#5

OP was already given the answer on another forum.

Sponsor our Newsletter | Privacy Policy | Terms of Service