SQL DB Auslesen und darstellen via php nach Aktuellen Datum

posted in: Allgemein | 0

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>