Wednesday, 11 July 2018

Integration with PHP and MySQL using the MySQL extension

Integration with PHP and MySQL using the MySQL extension

This is a matter of formatting what you get back from MySQL into a string, as the MySQL dump and PHP code below shows (it's based on a database called RGraph_example):
#
# Table structure for table `daily_statistics`
#

CREATE TABLE `daily_statistics` (
  `st_day` char(9) NOT NULL,
  `st_statistics` tinyint(4) NOT NULL,
  UNIQUE KEY `st_day` (`st_day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Dumping data for table `daily_statistics`
#

INSERT INTO `daily_statistics` VALUES ('Mon', 124);
INSERT INTO `daily_statistics` VALUES ('Tue', 95);
INSERT INTO `daily_statistics` VALUES ('Wed', 64);
INSERT INTO `daily_statistics` VALUES ('Thu', 94);
INSERT INTO `daily_statistics` VALUES ('Fri', 75);
INSERT INTO `daily_statistics` VALUES ('Sat', 98);
INSERT INTO `daily_statistics` VALUES ('Sun', 84);

<?php
    /**
    * Change these to your own credentials
    */
    $hostname = "localhost";
    $username = "root";
    $password = "PASSWORD";
    $database = "RGraph_example";
    
    $connection = mysql_connect($hostname, $username, $password)
                   OR die('Could not connect to MySQL: ' . mysql_error());
    mysql_select_db($database);
    
    $result = mysql_query("SELECT st_day, st_statistics FROM daily_statistics");
    if ($result) {
    
        $labels = array();
        $data   = array();
    
        while ($row = mysql_fetch_assoc($result)) {
            $labels[] = $row["st_day"];
            $data[]   = $row["st_statistics"];
        }

        // Now you can aggregate all the data into one string
        $data_string = "[" . join(", ", $data) . "]";
        $labels_string = "['" . join("', '", $labels) . "']";
    } else {
        print('MySQL query failed with error: ' . mysql_error());
    }
?>
<html>
<head>

    <!-- Don't forget to update these paths -->

    <script src="libraries/RGraph.common.core.js" ></script>
    <script src="libraries/RGraph.line.js" ></script>

</head>
<body>
    
    <canvas id="cvs" width="600" height="250">[No canvas support]</canvas>
    <script>
        chart = new RGraph.Line({
            id: 'cvs',
            data: <?php print($data_string) ?>,
            options: {
                gutterLeft: 35,
                gutterRight: 5,
                hmargin: 10,
                tickmarks: 'endcircle',
                labels: <?php print($labels_string) ?>
            }
        }).draw()
    </script>

</body>
</html>
This PHP code provides the data in two strings - $labels_string and $data_string. These two variables are then used in the RGraph code to provide the data and the labels.
Remember:
  • Change the database hostname/username/password/database to match your own.
  • Make sure you have the correct paths to the RGraph files.

Integration with PHP and MySQL using the MySQLi extension

Here's a similar example to the above but using the up-to-date MySQLi PHP extension instead of the older MySQL PHP extension
it uses the same table structure, data and query as above so the only thing that has changed is the MySQL extension.
#
# Table structure for table `daily_statistics`
#

CREATE TABLE `daily_statistics` (
  `st_day` char(9) NOT NULL,
  `st_statistics` tinyint(4) NOT NULL,
  UNIQUE KEY `st_day` (`st_day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Dumping data for table `daily_statistics`
#

INSERT INTO `daily_statistics` VALUES ('Mon', 124);
INSERT INTO `daily_statistics` VALUES ('Tue', 95);
INSERT INTO `daily_statistics` VALUES ('Wed', 64);
INSERT INTO `daily_statistics` VALUES ('Thu', 94);
INSERT INTO `daily_statistics` VALUES ('Fri', 75);
INSERT INTO `daily_statistics` VALUES ('Sat', 98);
INSERT INTO `daily_statistics` VALUES ('Sun', 84);

<?php
    $hostname = "localhost";
    $username = "root";
    $password = "PASSWORD";
    $database = "RGraph_example";
    
    // Create the PHP MySQLi object
    $mysqli = new MySQLi($hostname, $username, $password, $database);

    // Check for any connection errors
    if ($mysqli->connect_errno) {
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }


    // Make the query
    $result = $mysqli->query("SELECT st_day, st_statistics FROM daily_statistics");

    if ($result) {
        
        $labels = array();
        $data   = array();
    
        $result->data_seek(0);
        while ($row = $result->fetch_assoc()) {
            $labels[] = $row["st_day"];
            $data[]   = $row["st_statistics"];
        }

        // Now you can aggregate all the data into one string
        $data_string = "[" . join(", ", $data) . "]";
        $labels_string = "['" . join("', '", $labels) . "']";
    } else {
        print('MySQL query failed with error: ' . $mysqli->error);
    }
?>
<html>
<head>

    <!-- Don't forget to update these paths -->

    <script src="libraries/RGraph.common.core.js" ></script>
    <script src="libraries/RGraph.line.js" ></script>

</head>
<body>
    
    <canvas id="cvs" width="600" height="250">[No canvas support]</canvas>
    <script>
        chart = new RGraph.Line({
            id: 'cvs',
            data: <?php print($data_string) ?>,
            options: {
                gutterLeft: 35,
                gutterRight: 5,
                hmargin: 10,
                tickmarks: 'endcircle',
                labels: <?php print($labels_string) ?>
            }
        }).draw()
    </script>

</body>
</html>
This PHP code provides the data in two strings - $labels_string and $data_string. These two variables are then used in the RGraph code to provide the data and the labels.
Remember:
  • Change the database hostname/username/password/database to match your own.
  • Make sure you have the correct paths to the RGraph libraries.

Integration with PHP and MySQL using the PDO extension

Here's a similar example to the above but using the up-to-date PDO PHP extension instead of the older MySQL PHP extension
It uses the same table structure, data and query as above so the only thing that has changed is the MySQL extension.
#
# Table structure for table `daily_statistics`
#

CREATE TABLE `daily_statistics` (
  `st_day` char(9) NOT NULL,
  `st_statistics` tinyint(4) NOT NULL,
  UNIQUE KEY `st_day` (`st_day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Dumping data for table `daily_statistics`
#

INSERT INTO `daily_statistics` VALUES ('Mon', 124);
INSERT INTO `daily_statistics` VALUES ('Tue', 95);
INSERT INTO `daily_statistics` VALUES ('Wed', 64);
INSERT INTO `daily_statistics` VALUES ('Thu', 94);
INSERT INTO `daily_statistics` VALUES ('Fri', 75);
INSERT INTO `daily_statistics` VALUES ('Sat', 98);
INSERT INTO `daily_statistics` VALUES ('Sun', 84);

<?php

    $db = new PDO(
        'mysql:host=localhost;dbname=RGraph_example;charset=utf8',
        'root',
        'PASSWORD'
    );

    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    
    try {
        $labels = array();
        $data   = array();

        $result = $db->query('SELECT st_day, st_statistics FROM daily_statistics');
        $rows = $result->fetchAll(PDO::FETCH_ASSOC);
    
        // No need to check the result because we're using exceptions for
        // error handling
    
        foreach ($rows as $r) {
            $labels[] = $r["st_day"];
            $data[]   = $r["st_statistics"];
        }
    
        // Now you can aggregate all the data into one string
        $data_string = "[" . join(", ", $data) . "]";
        $labels_string = "['" . join("', '", $labels) . "']";
    
    } catch(PDOException $e) {
        
        // Show a user friendly message
        echo "An Error occured!";
        
        // log($e->getMessage());
    }
?>

This PHP code provides the data in two strings - $labels_string and $data_string. These variables are then used in the RGraph code to provide the data and the labels.

1 comment:

Subscribe to: Post Comments (Atom)