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

<?php
function __autoload($className) {
    // This will include the class files as they are needed.
    // The className need to be the same as the name of the included class
    require_once('classes/' . $className . '.php');
}

$dbHost = 'localhost';
$dbUser = 'your_username';
$dbPass = 'your_password';
$dbName = 'your_database_name';
$dbPort = 3306;
$mysqli = new mysqli($dbHost, $dbUser, $dbPass, $dbName, $dbPort);

// The default communication is acually latin1 so we need to set it to utf8 if we really want to use it in the database.
$ad = ActiveData::getInstance();
$ad->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

<div>
<?php
global $mysqli;
$sql = 'SELECT `title` FROM `post`';
$res = $mysqli->query($sql);
while ($row = $res->fetch_array()) {
    echo $row['title'] . '<br>';
}
?>
</div>

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.

<?php
// ActiveData is a Singleton
// There can only be one user that is active and logged on in one session
class ActiveData {
    public $user;
    public $mysqli;
    function &getInstance() {
         static $obj;
         if (!isset($obj)) {
             // Assign a reference to the static variable
             $v = new ActiveData;
             $obj = $v;
         }
         return $obj;
    }
}

You use inte like this

// 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;

So if you add the code for setting the ActiveData in global.php the item.php could look like this

<div>
<?php
$ad = ActiveData::getInstance();
$sql = 'SELECT `title` FROM `post`';
$res = $ad->mysqli->query($sql);
while ($row = $res->fetch_array()) {
    echo $row['title'] . '<br>';
}
?>
</div>

2015-09-20 00:08:00 - framework, php, database, software

Follow using RSS