MySQL: create a blog-style page with different types of data at different points

Question

I am trying to develop something similar to this with PHP and MySQL: http://a-bittersweet-life.tumblr.com/

I would like to make a page that consists mainly of words, but can be interspersed with images or embedded videos or other types of data. I don’t know exactly how to do this, but I have an idea:

id | entry_date |  text | image_link | vid_embed | sound_embed
 0 |   June 1st |  data |    null    |   null    |  null 
 1 |   June 1st |  null |    data    |   null    |  null   
 2 |   June 1st |  null |    data    |   null    |  null  
 3 |   June 1st |  data |    null    |   null    |  null  
 4 |   June 2nd |  data |    null    |   null    |  null   
 5 |   June 2nd |  null |    null    |   data    |  null   
 6 |   June 2nd |  data |    null    |   null    |  null  
 7 |   June 2nd |  null |    data    |   null    |  null   
 8 |   June 2nd |  null |    data    |   null    |  null   
 ....
 ....
 ....       

So, for each blog entry, the date is displayed first, and then the order in which the data was placed in the SQL table (sorted by id):

June 1st

• Text
• Picture
• Picture
• Text

2 June

• Text

• Video

• Text

• Picture

• Picture

Is there any other way to do this, or does this seem like a pretty practical way to do this?

Answer 1

, php. , , GIST.

2 , MVC , ...

<?php
declare(strict_types = 1);
// This function will provide data from DB.
// Here I`ve skipped validation
// because I had relied on PHP7 scalar type declarations.
function getDataFromDB(int $limit, int $offset) {
    $db = new \PDO('mysql:dbname={YOUR_DB};host={YOUR_HOST}', '{USER}', '{PASS}');
    // Here I`ve used super simple SQL,
    // but you can improve it
    // and filters by date or author or category or something else...
    $sql = sprintf(
        '
            SELECT *
            FROM {YOUR_TABLE_NAME}
            ORDER BY entry_date DESC
            LIMIT %d OFFSET %d
        ',
        $limit,
        $offset
    );
    // In this query, I`ve only provided params for pagination
    // because they are required on the first stage...
    $s = $db->prepare($sql);
    if (!$s->execute()) {
        throw new \RuntimeException($s->errorInfo());
    }
    // Next param is very IMPORTANT,
    // it will group data into array with format where:
    // key - date and value - array of posts, like:
    // array (
    //   'June 1st' => array (
    //     0 => array ('text' => '...', 'image_link' => ...),
    //     1 => array ('text' => '...', 'image_link' => ...),
    //   ),
    //   'June 2nd' => array (
    //     0 => array ('text' => '...', 'image_link' => ...),
    //     1 => array ('text' => '...', 'image_link' => ...),
    //     2 => array ('text' => '...', 'image_link' => ...),
    //     3 => array ('text' => '...', 'image_link' => ...),
    //   )
    // )
    return $s->fetchAll(\PDO::FETCH_GROUP | \PDO::FETCH_ASSOC);

}

db , html json - ...

// This function will render data.
function render(array $data) {
    $html = '';
    foreach ($data as $day => $posts) {
        // Caption for certain day, like 'June 1st' or 'June 2nd'.
        $html .= sprintf('<div class="dayPosts"><div class="day">%s</div>', $day);
        foreach ($posts as $post) {
            // Particular blog post.
            // It is super simple HEREDOC example,
            // but you can do here anything you wish.
            $html .= <<<"BLOGPOST"
                <div class="post">
                    <h3>{$post['title']}</h3>
                    <div class="content">
                        <img src="{$post['image_link']}">
                        <p class="text"{$post['test']}></p>
                        <video controls>
                            <source src="{$post['vid_embed']}" type="video/mp4">
                        </video>
                    </div>
                </div>
BLOGPOST;
        }
        $html .= '</div>';
    }
    return $html;
}

ve used plain php functions with purposer to prepare html, but you cna use something better like twig or volt or smarty` - . Alsom -...

, :

// Now you can get and render your content.
echo render(getDataFromDB(20, 0));

PS: Ti - ... , !
, ! 😉

Answer 2

, - , if .

$sql = "SELECT * FROM Schema.Table WHERE CAST(entry_date AS DATE) BETWEEN '06/01/2017' AND '06/02/2017' ORDER BY id";
/* Query your database however you might do */

$lastDay = null;
while ($row in $sqlResult->fetch_row()) {
    if ($row->entry_date != $lastDay) {
        $lastDay = $row->entry_date;
        echo $row->entry_date;
    }
    if ($row->text != null) {
        echo $row->text;
    } else if ($row->vid_embed != null) {
        echo '<videoplayer src="' + $row->vid_embed + '">';
    } else if ($row->image_link != null) {
        echo '<img src="' + $row->image_link + '">';
    } else if ($row->sound_embed!= null) {
        echo '<soundplayer src="' + $row->sound_embed+ '">';
    }
}

psuedo-code-ish, .

Answer 3

Or take a hint from HTML.

blah blah -- text
<img src=...> -- image
etc.

Or two columns; one of which indicates the MIME type (or, nevertheless, you want to classify them), one with the data in question.

One more tip from HTML: do not put an image, video or sound in a table cell; put a link to it.