Сергей Ермилов   5 марта в 16:47

Извлечение и отрисовка данных из базы данных MySql

В этом уроке мы извлечем данные из базы данных MySQL и отобразим их на нескольких разных графиках. Это продолжение урока по созданию регистратора температуры и влажности на Raspberry Pi.

Оборудование

Raspberry Pi 3 Model B подключена к Интернету, на ней работает Apache и является веб-сервером, который мы используем. На Raspberry Pi также установлена MariaDB, которая является базой данных mySQL, из которой мы получаем данные.

Программное обеспечение

  • NOOBS, установленные на Raspberry Pi
  • LAMPS, установленные на Raspberry Pi
  • Sublime, установленный на компьютере разработчика

Есть несколько разных источников для создания графиков в Интернете. Я собираюсь сосредоточиться на Google Chart, главным образом потому, что Google - большая компания, поэтому их веб-сайт вряд ли когда-нибудь рухнет. Google предлагает большой выбор различных графиков. Мы собираемся сосредоточиться на трех типах.

Извлечение данных

Чтобы сделать график, нам нужны данные. Данные, которые мы используем, взяты из таблицы температуры и влажности в базе данных db_Kajsa из предыдущего урока. Она включает измерения, которые были собраны в течение некоторого времени.

На данный момент нас интересуют только время и температура. Чтобы иметь возможность подключиться к базе данных, нам необходимы:

  1. Адрес веб-сервера;
  2. Имя пользователя и пароль;
  3. Имя базы данных для подключения;
  4. Название таблицы, из которой мы получаем данные.

Всю эту информацию мы помещаем в файл с именем db_config.ini:

[database]
db_host = "localhost"
db_name = "db_Kajsa"
db_table = "tbl_temperature"
db_user = "my_user_name"
db_password = "my_password"

Поскольку мы сохранили это как *.ini файл, то нам нужно использовать PHP-функцию parse_ini_file. Одной из причин, по которой я решил сохранить все учетные данные в одном файле является поддержка. Гораздо эффективнее хранить всю информацию в одном файле, а не иметь кучу файлов с помощью которых необходимо извлекать данные из базы данных. Этот файл хранится в папке с именем cfg, которая является подпапкой в корневом каталоге веб-сервера.

Следующий код - это вариант чтения *.ini-файла на веб-странице:

# Loading config data from *.ini-file
$ini = parse_ini_file ('cfg/db_config.ini');

# Присвоение значений ini используемым переменным
$db_host = $ini['db_host'];
$db_name = $ini['db_name'];
$db_table = $ini['db_table'];
$db_user = $ini['db_user'];
$db_password = $ini['db_password'];

Теперь, когда мы загрузили наши учетные данные в наш основной файл, пришло время подключиться к базе данных. Мы будем извлекать последние 10 значений.

# Подготовить соединение с базой данных MySQL
$connection = new mysqli($db_host, $db_user, $db_password, $db_name);

# Если есть какие-либо ошибки или соединение не в порядке
if ($connection->connect_error) {
	die ('Connection error: '.$connection->connect_error);
}
else {
	echo 'Connection is OK.<br />'; # For debugging purposes
}

# Подготовка запроса к базе данных mySQL и получение списка последних 10 значений
$sql = "SELECT * FROM $db_table WHERE sensor='Kajsa' ORDER BY id DESC LIMIT 10";
$result = $connection->query($sql);

# Если у нас есть хотя бы одно значение мы его покажем
if ($result->num_rows > 0) {
	while ($row = $result->fetch_assoc()) {
		echo "Celsius: ".$row['temperature']."<br />";
	}
} else {
	echo "<p>0 result. The ".$db_table." must be empty.</p>";
}

Если мы поместим эти два блока кода в файл *.php и откроем его в веб-браузере, мы получим следующее:

Это говорит нам о том, что соединение с базой данных работает. Показывается 10 последних значений в базе данных. Хорошее начало, но если немного подправить код, мы также сможем получить метку времени:

Чтобы получить метку времени нам нужно добавить в цикл while:

echo "Time: ".$row['timestamp']." Celsius: ".$row['temperature']."<br />";

Как сейчас, мы получаем полную метку времени. Что если нам нужно только время без даты? Нет проблем. Мы просто удаляем все, кроме последних 8 символов:

$timestamp_rest = substr($row["timestamp"],-8);
echo "Time: ".$timestamp_rest." Celsius: ".$row['temperature']."<br />";

Теперь мы получаем:

Теперь у нас есть таблица с некоторыми данными. Давайте визуализируем наши данные. Как уже упоминалось, Google предлагает широкий спектр диаграмм. Мы подробнее рассмотрим три типа диаграмм.

Визуализация данных

Исходные программы вы можете скачать в zip-файле ниже:

Общим для всех графиков является то, что они подгружают один и тот же загрузчик JavaScript, который указан в начале файла.

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

Затем нам нужно выбрать пакет диаграмм, который мы хотим использовать. В нашем случае мы используем два пакета: corechart и bar. Мы сосредоточимся на пакете corechart. Этот пакет имеет, среди прочего, LineChart-диаграмму (линейный вид). После загрузки пакета corechart мы просим браузер отрисовать диаграмму при загрузке страницы. Вызываем функцию drawChart.

В начале функции drawChart мы определяем наши данные. Это немного сложнее, так как мы получаем его из базы данных mySQL, но когда я щелкаю правой кнопкой мыши и выбираю «показать источник» в браузере, то вижу начало функции:

function drawChart() {
	var data = google.visualization.arrayToDataTable([			
	['Time', 'Temperature'],
	['19:00:01',4.625],['18:55:02',4.562],['18:50:01',4.625],
	['18:45:01',4.75],['18:40:02',4.812],['18:35:02',4.5],
	['18:30:01',4.875],['18:25:01',4.937],['18:20:02',4.875],
	['18:15:01',5],
	]);

В нашем исходном файле начало функции:

function drawChart() {
	var data = google.visualization.arrayToDataTable([			
	['Time', 'Temperature'],
<?php

# Этот запрос подключается к базе данных и получает последние 10 значений
$sql = "SELECT temperature, timestamp FROM $db_table WHERE sensor='Kajsa' 
		ORDER BY id DESC LIMIT 10";

$result = $connection->query($sql);  

# Этот цикл while - форматирует и помещает все полученные данные в виде ['timestamp', 'temperature']
	while ($row = $result->fetch_assoc()) {
		$timestamp_rest = substr($row["timestamp"],-8);
		echo "['".$timestamp_rest."',".$row['temperature']."],";
		}
?>
]);

Если бы мы использовали только статичные данные, то можно было бы жестко закодировать их на веб-странице. Однако мы используем динамические данные, поэтому нам нужно подключиться к базе данных внутри функции drawChart, извлечь данные и отформатировать их так, как этого хочет Google Chart.

Затем нам нужно сообщить drawChart несколько данных о диаграмме, которую мы хотим, например, title, curveType.

// Изогнутая линия
var options = {
		title: 'Temperature',
		curveType: 'function',
		legend: { position: 'bottom' }
		};

После этого нам нужно создать и нарисовать график:

// Изогнутая диаграмма
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);

Наконец, нам нужен способ отображения графика. Это делается путем помещения его в тег div:

<div id="curve_chart" style="width: 900px; height: 480px;"></div>

Этот код должен привести к чему-то вроде этого:

В исходном коде вы также найдете код для создания столбчатой диаграммы и гистограммы.

Со временем можно добавить больше данных на свой график. Например, следующим шагом может быть добавление влажности на график в сочетании с температурой.

В этом уроке мы показали один из способов подключения к базе данных MySQL, извлечения некоторых данных и создания нескольких различных диаграмм на основе Google Chart.

За урок мы благодарим Йенса Кристофферсена с сайта maker.pro.