Daten via Crontab einlesen
mysql -hlocalhost -uroot -pPASSWORD< /home/administrator/search.sql
//////////////////////////////////////////
Datenbank erstellen
USE Search;
DROP TABLE IF EXISTS `users`;
CREATE TABLE
`users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`logo` varchar(255) NOT NULL,
`raum` varchar(255) NOT NULL,
`va_id` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`va_ausschilderung` varchar(255) NOT NULL,
`anlass_id` varchar(255) NOT NULL,
`anlass_name` varchar(255) NOT NULL,
`va_name` varchar(255) NOT NULL,
`end_zeit` varchar(255) NOT NULL,
`anlass_ausschilderung` varchar(255) NOT NULL,
`start_datum` VARCHAR(30) NOT NULL,
`start_zeit` VARCHAR(30) NOT NULL,
`anlass_art` VARCHAR(50) NOT NULL,
`kunden_name` varchar(255) collate utf8_unicode_ci NOT NULL default »,
PRIMARY KEY (`id`),
KEY `logo` (`logo`),
KEY `va_ausschilderung` (`va_ausschilderung`),
KEY `anlass_ausschilderung` (`anlass_ausschilderung`),
KEY `va_id` (`va_id`),
KEY `anlass_id` (`anlass_id`),
KEY `raum` (`raum`),
KEY `va_name` (`va_name`),
KEY `start_datum` (`start_datum`),
KEY `end_zeit` (`end_zeit`),
KEY `start_zeit` (`start_zeit`),
KEY `anlass_art` (`anlass_art`),
KEY `kunden_name` (`kunden_name`),
KEY `anlass_name` (`anlass_name`),
KEY `name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=2
DEFAULT CHARSET=utf8;
SET NAMES utf8;
SET character_set_client = utf8;
LOAD DATA LOCAL INFILE ‹/var/www/xibo-cms/web/uploads/liste.csv›
INTO TABLE users FIELDS TERMINATED BY ‹;›
IGNORE 1 ROWS (id, logo, raum, va_id, name, va_ausschilderung, anlass_id, anlass_name, va_name, end_zeit, anlass_ausschilderung, start_datum, start_zeit, anlass_art, kunden_name );
//////////////////////////////////////////
id logo raum va_id name va_ausschilderung anlass_id anlass_name va_name end_zeit anlass_ausschilderung start_datum start_zeit anlass_art kunden_name
CSV Tabelle erstellen Achtung DATUM im US Format YYYY-MM-DD
//////////////////////////////////////////
<?php
// Ersteller Enrico
// 2019-11-08
// some script settings
$page_title = «terminliste»;
// get the $id value from GET string
$id = intval($_GET[‹id›]);
/*
escape to HTML
notice we use almost the exact same HTML and CSS as before,
changed slightly to allow for PHP dynamic content
*/
?>
<html>
<head>
<title><?php echo $page_title; ?></title>
header (‹Content-type: text/html; charset=utf-8›);
<body onLoad=»start()»>
</script>
<style type=»text/css»>
body { color: #000; background: #000000; font-family: sans-serif; }
p { color: #000000; background: #000000; text-align: center; }
th { color: #000000; background: #000000; text-align: center; font-size:32px; text-align: left;}
tr { color: #ffffff; background: #787878; text-align: center; font-size:40px; text-align: left;}
table {
border-collapse: separate;
border-spacing: 0;
}
th,
td {
padding: 4px 4px;
}
thead {
background: #395870;
color: #000000;
}
tbody tr:nth-child(even) {
background: #383838;
}
td {
border-bottom: 1px solid #cecfd5;
border-right: 1px solid #000000;
}
td:first-child {
border-left: 1px solid #000000;
}
</style>
</head>
<body>
<?php
header(‹Content-Type: text/html; charset=utf8›);
?>
<?php
$db = new mysqli(‹localhost›, ‹benutzername›, ‹password›, ‹Search›);
if (mysqli_connect_errno()) {
die (‹Konnte keine Verbindung zur Datenbank aufbauen:
‹.mysqli_connect_error().'(‹.mysqli_connect_errno().›)›);
}
$sql = «SELECT * FROM users WHERE DATE(end_zeit) = CURDATE() AND (va_id NOT LIKE ‹Bewohner%›) AND (va_id NOT LIKE ‹1%›) AND (va_id NOT LIKE ‹3%›) ORDER BY name ASC ;»;
$erg = $db->query($sql);
if (!$erg){
die (‹Etwas stimmte mit dem Query nicht: ‹.$db->error);
}
?>
<table>
<?php
while ($row = $erg->fetch_assoc()) {
?>
<tr>
<td><img src=»<?=$row[ ‹logo› ];?>» alt=»<?=$row[ ‹logo› ];?>»style=»width: 300px»></td>
<td width=»350px»><?php echo $row[‹va_ausschilderung›]; ?></td>
<td width=»100px»><?php echo $row[‹va_name›]; ?></td>
<td width=»300px»><?php echo $row[‹kunden_name›]; ?></td>
</tr>
<?php
}
?>
</table>
<?php
$erg->close();
?>
</body>
</html>