Duplicate Frame Titles

Chibi · September 25, 2019

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

Quote

Frame 1
Frame 1
Frame 2
Frame 2
Frame 3

Instead of

Quote

Frame 1
Frame 2
Frame 3

This is my tables

banners table

Quote

id | title
1 | Home banner

frames table

Quote

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

Quote

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>";
    }

Edited September 25, 2019 by Chibi

Barand · September 25, 2019

With the provided data your query gives

mysql> SELECT frames.id as frameId, frames.title as frameTitle, banners.id as banner_id
    -> FROM frames
    -> LEFT JOIN banner_frame ON banner_frame.frame_id = frames.id
    -> LEFT JOIN banners ON banners.id = banner_frame.banner_id
    -> ORDER BY frames.id;
+---------+------------+-----------+
| frameId | frameTitle | banner_id |
+---------+------------+-----------+
|       1 | Frame 1    |         1 |
|       1 | Frame 1    |      NULL |
|       2 | Frame 2    |      NULL |
|       3 | Frame 3    |      NULL |
|       4 | Frame 4    |      NULL |
|       5 | Frame 5    |      NULL |
+---------+------------+-----------+

Beacause you have "... FROM frames LEFT JOIN ..." you get every frames record even if there is no matching data in the other tables.

You get 2 rows for Frame 1 because there are 2 matching records for frame 1 in "banner_frame" table

57 minutes ago, Chibi said:

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

So the question is "What is the purpose of the query?"

Chibi · September 25, 2019

The purpose of the query is to list all the frames from the frames table and then only put a check next to those that
is linked to the banner table through the pivot table.

Barand · September 25, 2019

Something like this?

mysql> SELECT f.id as frameId
    ->      , f.title as frameTitle
    ->      , COUNT(DISTINCT bf.banner_id) as banner_tot
    -> FROM frames f
    -> LEFT JOIN banner_frame bf ON bf.frame_id = f.id
    -> GROUP BY f.id;
+---------+------------+------------+
| frameId | frameTitle | banner_tot |
+---------+------------+------------+
|       1 | Frame 1    |          2 |
|       2 | Frame 2    |          0 |
|       3 | Frame 3    |          0 |
|       4 | Frame 4    |          0 |
|       5 | Frame 5    |          1 |
+---------+------------+------------+

Chibi · September 25, 2019

No that didn't work. The way it's looking now is

Banner 1 has Frame 3, Frame 4 and Frame 5 checked, instead of Frame 1

Banner 2 has Frame 1 checked instead of Frame 1 and Frame 2

Then I created Banner 3 and that has nothing checked instead of Frame 3 and Frame 4

Here is my whole code. Maybe the issue is with my other code and not the sql statement

<?php
    //GETS THE BANNER FROM THE b_id FROM THE URL
    if(isset($_GET['b_id']))
    {
        $banner_id = $_GET['b_id'];
    }

    $query = "SELECT * FROM banners WHERE id = $banner_id";

    $select_banner_by_id = mysqli_query($conn, $query);

    while($row = mysqli_fetch_assoc($select_banner_by_id))
    {
        $banner_id = $row['id'];
        $banner_title = $row['title'];
    }

    //THIS UPDATES THE BANNER
    if(isset($_POST['edit_banner']))
    {
        $banner_title = $_POST['banner_title'];

        $query = "UPDATE banners SET ";
        $query .= "title = '{$banner_title}' ";
        $query .= "WHERE id = $banner_id ";

        $update_banner = mysqli_query($conn, $query);
        confirmQuery($update_banner);

        //THIS REDIRECTS THE PAGE AFTER SAVING
        header("Location: banners.php");
    }
?>

<form action="" method="POST">
    <div class="form-group">
        <label for="banner_title">Title</label>
        <input type="text" class="form-control" name="banner_title" value="<?php echo $banner_title ?>">
    </div>

    <!-- BEGIN FRAMES -->
    <?php
        $query = " SELECT f.id as frameId, f.title as frameTitle, COUNT(DISTINCT bf.banner_id) as banner_table ";
        $query .= "FROM frames f ";
        $query .= "LEFT JOIN banner_frame bf ON bf.frame_id = f.id ";
        $query .= "GROUP BY f.id";
        
        $banner_frame = mysqli_query($conn, $query);

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

            $checked = '';
            if ($row['banner_table'] == $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>";
        }
    ?>

    <div class="form-group">
        <button type="submit" class="btn btn-dark btn-lg btn-block" name="edit_banner">Save</button>
    </div>
</form>

Barand · September 25, 2019

4 hours ago, Chibi said:

The purpose of the query is to list all the frames from the frames table and then only put a check next to those that
is linked to the banner table through the pivot table.

I used

COUNT(DISTINCT bf.banner_id) as banner_table

so that it would be non-zero if the frame were linked to any banners. You are trying to match that that count value against a $banner_id (???).

Does that mean you want to show as checked those frames that are link to s specific banner and not just any banner? If so the query will be different.

Chibi · September 25, 2019

Quote

Does that mean you want to show as checked those frames that are link to s specific banner and not just any banner?

Yep that is what I was trying to get to in my question. Sorry it wasn't clear enough.

Barand · September 25, 2019

In that case you need to specify the banner you are looking for in the LEFT JOIN's ON clause

EG (looking for banner #2)

SELECT DISTINCT
       f.id as frameId
     , f.title as frameTitle
     , bf.banner_id
FROM frames f
LEFT JOIN banner_frame bf ON bf.frame_id = f.id
                          AND bf.banner_id = 2
ORDER BY f.id;

+---------+------------+-----------+
| frameId | frameTitle | banner_id |
+---------+------------+-----------+
|       1 | Frame 1    |         2 |
|       2 | Frame 2    |      NULL |
|       3 | Frame 3    |      NULL |
|       4 | Frame 4    |      NULL |
|       5 | Frame 5    |         2 |
+---------+------------+-----------+

Chibi · September 25, 2019

That was it. THANK YOU SO MUCH. 😀

Barand · September 25, 2019

Another option is to display, for each frame, all the referenced banners. For example if you have banner_frames table thus

+----+-----------+----------+
| id | banner_id | frame_id |
+----+-----------+----------+
|  1 |         1 |        1 |
|  2 |         2 |        1 |
|  3 |         2 |        3 |
|  4 |         3 |        3 |
|  5 |         1 |        4 |
|  6 |         2 |        4 |
|  7 |         3 |        4 |
+----+-----------+----------+

then the output would look like

CODE:

<?php
include 'db_inc.php';
$db = pdoConnect('chibi');

//
//  Get the banner data
//
$res = $db->query("SELECT id
                        , title
                   FROM banners
                   ORDER BY id
                  ");
$banner_data = $res->fetchAll();
$banner_count = count($banner_data);
$banner_heads = '<th>' . join('</th><th>', array_column($banner_data,'title')) . '</th>';
$banner_template = array_fill_keys(array_column($banner_data, 'id'), '');
//
//  Get frame-banner data
//      store in array
//
$res = $db->query("SELECT f.id as fid
                        , f.title
                        , b.id as bid
                        , CASE WHEN bf.banner_id IS NULL THEN ''
                               ELSE 'Y'
                          END as checked
                   FROM frames f
                          CROSS JOIN
                        banners b
                          LEFT JOIN 
                        banner_frame bf ON f.id = bf.frame_id 
                                        AND b.id = bf.banner_id
                   ORDER BY f.id, b.id                 
                  ");
$data = [];
foreach ($res as $r) {
    if (!isset($data[$r['fid']])) {
        $data[$r['fid']] = [ 'title' => $r['title'],
                             'banners' => $banner_template
                           ];
    }
    $data[$r['fid']]['banners'][$r['bid']] = $r['checked'];
}

?>
<!DOCTYPE html >
<html>
   <head>
       <meta http-equiv="content-language" content="en">
       <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
       <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
      <title>Banner Frames</title>
   </head>
   <body>
   <header class="w3-container w3-blue">
       <h1>Banner Frames</h1>
   </header>
   <div class="w3-container w3-margin w3-padding">
       <table class="w3-table w3-bordered">
           <tr class="w3-brown">
               <th rowspan="2">Frame ID</th>
               <th rowspan="2">Title</th>
               <th colspan="<?=$banner_count?>">Banners</th>
           </tr>
           <tr class="w3-brown">
               <?=$banner_heads?>
           </tr>
           
           <?php
               // output the data array as table rows
               foreach ($data as $fid => $fdata) {
                   echo "<tr><td>$fid</td><td>{$fdata['title']}</td>";
                   foreach ($fdata['banners'] as $bid => $chkd) {
                       $checked = $chkd=='Y' ? 'checked' : '';
                       echo "<td><input type=\"checkbox\" name=\"banner[$fid][]\" value=\"$bid\" $checked></td>";
                   }
                   echo "</tr>\n";
               }
           ?>
       </table>
   </div>
   </body>

</html>

Sign In

Duplicate Frame Titles

Recommended Posts

Chibi

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Chibi

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Chibi

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Chibi

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Chibi

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information