Separating specific MySQL columns from row fetch into another array in php

What I am trying to do is get back the 5 recent announcements in my announcements table. After that, I am trying to separate the announcementText column data into another array with each row in its own index, and for each index, I would like to echo that out on the HTML page. The code, however, does a weird split of the row data. Sometimes it’s one character per index, and sometimes multiple characters, but it’s never the whole message. The table schema is as follows:

announcement_id     int(11)     NOT NULL   PRIMARY_KEY   auto_increment
announcementText    text        NOT NULL            
announcementDate    date        NOT NULL

The result I get from the final array (varies depending on announcement):

array(3) { 
    [0]=> NULL 
    [1]=> string(1) "h" 
    [2]=> string(1) "2"
}

The result I get from the database:

array(3) { 
    ["announcement_id"]=> int(2) 
    ["announcementText"]=> string(163) "here is some text. kajshdflkjashdflkajshdflkjashdflkjashdflkjahsdfkjahsdfkjhaslkdjfhalkjsdfhakljsdfhlaksjdfhlkasjdfhlkasjdfgkasdfkasdfkasdfaksjdhfkasjdhfkjasdhfhkj" 
    ["announcementDate"]=> string(10) "2020-10-07" 
}

What I expected is to have the announcement show correctly in its entirety.

Here is my code so far:

$getRecentAnnouncements = "SELECT * FROM announcements ORDER BY announcementDate DESC LIMIT 5";
$getRecentAnnouncements = $pdo->prepare($getRecentAnnouncements);
if ($getRecentAnnouncements->execute()) {
    $announcements = $getRecentAnnouncements->fetch(PDO::FETCH_ASSOC);
    //var_dump($announcements);
    $announcementsText = array();
    foreach ($announcements as $announcement) {
        array_push($announcementsText, $announcement[0]);
    }
    var_dump($announcementsText);
    foreach ($announcementsText as $announcement) {
        echo '
            <div class="jumbotron" style="background-color: #3d94ff; color: white; padding-bottom: 3vh; width: 95vw;">
            <h1 class="display-4">REPLACE WITH DATE</h1>
            <hr class="my-4">
            <div class="lead announcement">
                '.$announcement.'
            </div>
        </div>
        ';
    }
}

Answer

You’re using PDO’s fetch() (which only returns one single record) instead of fetchAll() (which returns all matched records).

Try doing something like this:

if ($getRecentAnnouncements->execute()) {
    // Here we're using fetchAll() to get all records
    $announcements = $getRecentAnnouncements->fetchAll(PDO::FETCH_ASSOC);
    $announcementsText = array();
    foreach ($announcements as $announcement) {
        // Let's put the HTML directly here. No need for an extra array
    ?>
        <div class="jumbotron" style="background-color: #3d94ff; color: white; padding-bottom: 3vh; width: 95vw;">
            <h1 class="display-4">REPLACE WITH DATE</h1>
            <hr class="my-4">
            <div class="lead announcement">
                <?php 
                // We're fetching the records as assoc so use the column name to get the value
                echo $announcement['announcementText']; 
                ?>
            </div>
        </div>
    <?php
    }
}

As you can see in the above code, I removed the $announcementText-array and one of the foreach, since they basically just iterate through the same data.

Note: I also changed how the HTML is outputted. I tend to recommend against echoing a bunch of HTML through PHP since it makes it much harder to debug (no syntax highlighting in PHP strings, for example.)

Advertisement

Answer