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

Question

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

It would really help if you showed us the database schema for the table you're using, example data, expected result from that data and what you're currently getting. Showing is way more effective than just talking about it. We have literally no idea what "does a weird split of the row data" means. — M. Eriksson
– M. Eriksson, Commented Oct 9, 2020 at 12:46
I have added the schema. What I mean by that is it takes the announcement (only 1 currently) and then my code does the array push and some of the indexes have one character, some more, but the full announcement doesn't go into the array, only part of it. — Revvz
– Revvz, Commented Oct 9, 2020 at 12:53
Please update the question to include example data, expected result from that data and what you're currently getting (which I also asked for in the first comment). — M. Eriksson
– M. Eriksson, Commented Oct 9, 2020 at 12:55

M. Eriksson · Accepted Answer · 2020-10-09 13:16:33Z

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.)

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related