PHP Mysql while loop set default array value for empty row with limit

Question

I have 2 groups of banner ads and 2 banner ads. The database is like this.

groups_ads

| id |  name  | max_places
  1    group1       3
  2    group2       2

banner_ads

| id | group_id  | url_images
  1       1         https://example.com/banner.png
  2       1         https://example.com/images.png

this code is to show all the banners_ads based on their group.

$res_arr = array();
$stmt = $conn->prepare("SELECT * FROM groups_ads");
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
while ($res = $stmt->fetch())
{
    $result = array();
    $max_slot = $res['max_places'];
    $count = 0;
    $banners = array();
    $stmts = $conn->prepare('
        SELECT * FROM banner_ads
        WHERE group_id = "'.$res['id'].'"
    ');
    $stmts->execute();
    $stmts->setFetchMode(PDO::FETCH_ASSOC);
    while ($count < $max_slot && $banner = $stmts->fetch()){
        $count++;
        if($banner){
        $banners = array(
            "type" => 2,
            "url" => $banner['url_images'],
            "count" => $count
        );
        }else{
            $banners = array(
                "type" => "default",
                "count" => $count
            );
        }
    $result[$count] = $banners;
    }
    $res_arr[$res['id']] = $result;
}

the result of this code is this

Array
(
    [1] => Array
        (
            [1] => Array
                (
                    [type] => 2
                    [url] => https://example.com/banner.png
                    [count] => 1
                )

            [2] => Array
                (
                    [type] => 2
                    [url] => https://example.com/images.png
                    [count] => 2
                )

        )

    [2] => Array
        (
        )

)

Because I have 2 banner ads in my database which is in group 1.

The question is, how to fill the empty row based on max_places ? in database groups_ads, max_places for group 1 is 3. But in database banner_ads only have 2 banner. So how can I fill the empty places to my default string? so the result will be like this.

Array
(
    [1] => Array
        (
            [1] => Array
                (
                    [type] => 2
                    [url] => https://example.com/banner.png
                    [count] => 1
                )

            [2] => Array
                (
                    [type] => 2
                    [url] => https://example.com/images.png
                    [count] => 2
                )

            [3] => Array
                (
                    [type] => default
                    [count] => 3
                )

        )

    [2] => Array
        (
        )

)

Thanks for help.

Nick · Accepted Answer · 2019-12-14 04:13:46Z

1

If you remove your test on $banner in the while loop conditional, you will ensure that you always fill $max_slots values for each array. Replace:

while ($count < $max_slot && $banner = $stmts->fetch()){
    $count++;
    if($banner){

with

while ($count < $max_slot) {
    $count++;
    if ($banner = $stmts->fetch()) {

answered Dec 14, 2019 at 4:13

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP Mysql while loop set default array value for empty row with limit

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related