ChartJS和PHP制作MySQL数据源条形图

r囧r小猫 2023-10-05 14:17 12阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70][]

bar-chart.js-php-mysql.php

<?php
    $servername = 'localhost';
    $username = 'phpsamples';
    $password = 'phpsamples';
    $db = 'phpsamples';
    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $db);
    mysqli_set_charset($conn, "utf8");
    // Check connection
    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
    $data1 = '';
    $data2 = '';
    $buildingName = '';
    $query = "SELECT costs.cost_id, costs.building_id, buildings.building_name, costs.consumption_date, costs.gas_consumption, costs.gas_cost, costs.created_at, costs.updated_at FROM costs INNER JOIN buildings ON costs.building_id = buildings.building_id";
    $runQuery = mysqli_query($conn, $query);
    while ($row = mysqli_fetch_array($runQuery)) {
        $data1 = $data1 . '"' . $row['gas_consumption'] . '",';
        $data2 = $data2 . '"' . $row['gas_cost'] . '",';
        $buildingName = $buildingName . '"' . ucwords($row['building_name']) . '",';
    }
    $data1 = trim($data1, ",");
    $data2 = trim($data2, ",");
    $buildingName = trim($buildingName, ",");
    ?>
    
    <!DOCTYPE html>
    <html>
    	<head>
        	<meta name="viewport" content="width=device-width, initial-scale=1.0">
    		<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.2/Chart.bundle.min.js"></script>
    		<title>Bar Chart using PHP MySQL and Chart JS</title>
    
    
    
    	</head>
    
    	<body>	   
       
    	    
    <div id="container" style="width: 75%;">
    		<canvas id="canvas"></canvas>
    	</div>
    	<script>
    		var barChartData = {
    			labels: [<?php echo $buildingName; ?>],
    			datasets: [{
    				label: 'Consumption',
    				backgroundColor: 'rgb(255, 99, 132)',
    				borderColor: 'rgb(255, 99, 132)',
    				borderWidth: 1,
    				data: [<?php echo $data1; ?>]
    			}, {
    				label: 'Cost',
    				backgroundColor: 'rgb(54, 162, 235)',
    				borderColor: 'rgb(54, 162, 235)',
    				borderWidth: 1,
    				data:[<?php echo $data2; ?>]
    			}]
    
    		};
    
    		window.onload = function() {
    			var ctx = document.getElementById('canvas').getContext('2d');
    			window.myBar = new Chart(ctx, {
    				type: 'bar',
    				data: barChartData,
    				options: {
    					responsive: true,
    					legend: {
    						position: 'top',
    					},
    					title: {
    						display: true,
    						text: 'Chart.js Bar Chart'
    					}
    				}
    			});
    
    		};
    
    
    	</script>	    
    	    
    	</body>
    </html>

bar-chart.js-php-mysql.sql

-- phpMyAdmin SQL Dump
    -- version 4.8.5
    -- https://www.phpmyadmin.net/
    --
    -- Host: 127.0.0.1:3306
    -- Generation Time: Mar 04, 2020 at 02:11 PM
    -- Server version: 5.7.26
    -- PHP Version: 7.3.5
    
    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    SET AUTOCOMMIT = 0;
    START TRANSACTION;
    SET time_zone = "+00:00";
    
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8mb4 */;
    
    --
    -- Database: `bar_chart`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `buildings`
    --
    
    DROP TABLE IF EXISTS `buildings`;
    CREATE TABLE IF NOT EXISTS `buildings` (
      `building_id` int(11) NOT NULL AUTO_INCREMENT,
      `building_name` varchar(255) NOT NULL,
      `postal_code` int(11) NOT NULL,
      `town` varchar(255) NOT NULL,
      `district` varchar(255) NOT NULL,
      `address` text NOT NULL,
      `energy_types` varchar(255) NOT NULL,
      `is_active` tinyint(4) NOT NULL,
      PRIMARY KEY (`building_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
    
    --
    -- Dumping data for table `buildings`
    --
    
    INSERT INTO `buildings` (`building_id`, `building_name`, `postal_code`, `town`, `district`, `address`, `energy_types`, `is_active`) VALUES
    (10, 'building 4', 12545, 'ABC', 'XYZ', 'house # 13 street # 4 khanpur', 'gas, water, electricity, fuel', 1),
    (8, 'building 2', 63100, 'bahawalpur', 'bahawalpur', 'house # 13 b street # 4 abc colony', 'gas, water, electricity, fuel', 1),
    (9, 'building 3', 33040, 'abc town', 'abc district', 'this is addresss. ', 'gas, water, electricity, fuel', 1),
    (7, 'building 1', 50309, 'khanpur', 'rahim yar khan', 'house # 13 street # 4 khanpur', 'gas, water, electricity, fuel', 1);
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `costs`
    --
    
    DROP TABLE IF EXISTS `costs`;
    CREATE TABLE IF NOT EXISTS `costs` (
      `cost_id` int(11) NOT NULL AUTO_INCREMENT,
      `building_id` int(11) NOT NULL,
      `consumption_date` date NOT NULL,
      `gas_consumption` varchar(255) NOT NULL,
      `gas_cost` varchar(255) NOT NULL,
      `created_at` datetime DEFAULT NULL,
      `updated_at` datetime DEFAULT NULL,
      PRIMARY KEY (`cost_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;
    
    --
    -- Dumping data for table `costs`
    --
    
    INSERT INTO `costs` (`cost_id`, `building_id`, `consumption_date`, `gas_consumption`, `gas_cost`, `created_at`, `updated_at`) VALUES
    (1, 7, '2020-01-01', '150', '1000', NULL, '2020-03-03 17:46:24'),
    (2, 8, '2020-02-01', '180', '1050', NULL, '2020-03-03 17:46:36'),
    (3, 9, '2020-03-01', '250', '1100', '2020-03-03 17:05:23', NULL),
    (5, 10, '2020-05-01', '150', '1000', NULL, '2020-03-03 17:46:24'),
    (4, 7, '2020-04-01', '120', '1200', NULL, '2020-03-03 18:34:24'),
    (6, 8, '2020-06-01', '180', '1050', NULL, '2020-03-03 17:46:36'),
    (7, 9, '2020-07-01', '250', '1100', '2020-03-03 17:05:23', NULL),
    (8, 10, '2020-08-01', '120', '1200', NULL, '2020-03-03 18:34:24'),
    (9, 7, '2020-09-01', '120', '1200', NULL, '2020-03-03 18:34:24'),
    (10, 8, '2020-10-01', '180', '1050', NULL, '2020-03-03 17:46:36'),
    (11, 9, '2020-11-01', '250', '1100', '2020-03-03 17:05:23', NULL),
    (12, 10, '2020-12-01', '120', '1200', NULL, '2020-03-03 18:34:24');
    COMMIT;
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201123220903641.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FsbHdheTI=,size_16,color_FFFFFF,t_70