Connect to a database

Now we would like to add some blog posts. And the most convenient way to store these are in a database. So first we create a database and a table called post.

CREATE TABLE IF NOT EXISTS `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) COLLATE utf8mb4_bin NOT NULL,
  `summary` varchar(250) COLLATE utf8mb4_bin NOT NULL,
  `message` text COLLATE utf8mb4_bin NOT NULL,
  `posted` datetime NOT NULL,
  `tags` varchar(100) COLLATE utf8mb4_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `posted` (`posted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1;

Then we need a way to connect to the database. So we add the following code in global.php

mysqli->set_charset("utf8");

Now add some posts to the post table and then create a folder in views/ called post. Then create a page called views/post/item.php containing the following code

query($sql);
while ($row = $res->fetch_array()) {
    echo $row['title'] . '
';
}
?>

Now access the page via the url http://www.your_site.se/index.php?module=post&view=item and you should get a list of the titles of each item you put in the database.

The line global $mysqli; is used because we use the variable inside the function printContent() and this command gives us access to the globally declared $mysqli variable. This works. But I prefer to use a singleton for this. I guess it is a matter of taste. You can create one by adding a file called ActiveData.php in the classes folder with the following content. For now it has a variable to store the active user and one for the mysqli connection.

// Set the value in global.php
$ad = new ActiveData();
$ad = $ad->getInstance();
$ad->mysqli = $mysqli;

// Get the value where ever you need it
$ad = ActiveData::getInstance();
$mysqli = $ad->mysqli;

mysqli->query($sql);
while ($row = $res->fetch_array()) {
    echo $row['title'] . '
';
}
?>