· 9 years ago · Jul 18, 2017, 09:26 PM
1<?php
2function database() {
3 $mysqlhost="localhost";
4 $mysqluser="123";
5 $mysqlpass="123";
6 $mysqldata="123";
7 $mysqli = new mysqli($mysqlhost,$mysqluser,$mysqlpass,$mysqldata);
8 if ($mysqli->connect_error) {
9 die('Verbindungsfehler (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
10 }
11 if (!mysqli_set_charset($mysqli, "utf8")) {
12 printf("Error loading character set utf8: %s\n", mysqli_error($mysqli));
13 }
14 return $mysqli;
15}
16function do_query($query) {
17 $mysqli = database();
18 $result = $mysqli->query($query);
19 unset($result_array);
20 switch (gettype($result)) {
21 case "object":
22 if($result->num_rows > 0) {
23 while($row = $result->fetch_assoc()) {
24 $result_array[] = $row;
25 }
26 return $result_array;
27 //if the result is of type object and has 1 or more rows the array of results will get returned
28 }
29 break;
30 }
31}
32
33?>
34<!DOCTYPE html>
35<html lang="de">
36<head>
37 <meta charset="utf-8">
38 <!-- Bootstrap core CSS -->
39 <link href="css/bootstrap.min.css" rel="stylesheet">
40 <!-- IE10 viewport hack for Surface/desktop Windows 8 bug -->
41 <link href="assets/css/ie10-viewport-bug-workaround.css" rel="stylesheet">
42 <!-- Custom styles for this template -->
43 <link href="justified-nav.css" rel="stylesheet">
44 <link href="css/cookie.css" rel="stylesheet">
45 <script src="assets/js/ie-emulation-modes-warning.js"></script>
46 <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
47 <!--[if lt IE 9]>
48 <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
49 <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
50 <![endif]-->
51</head>
52<body>
53<?php
54if (isset($_POST['commit']) && $_POST['commit']=="sub") {
55 $query="INSERT INTO taettoowierer (NAME, contact)
56 VALUES
57 (
58 '".$_POST[' NAME ']."',
59 '".$_POST[' contact ']."'
60 )";
61 do_query($query);
62}
63if (isset($_POST['reset']) && $_POST['reset']=="sub") {
64 $query="TRUNCATE TABLE zuweisung";
65 do_query($query);
66}
67if (isset($_POST['losung']) && $_POST['losung']=="sub") {
68?>
69
70<?php
71 #Einzelbewerber eintragen
72 $query = "INSERT INTO zuweisung (
73 SELECT
74 c.id,
75 c.erstwunsch,
76 'alleiniger Erstwunsch'
77 FROM
78 (
79 SELECT
80 id,
81 erstwunsch,
82 count(*) AS cnt
83 FROM
84 bewerber
85 GROUP BY
86 erstwunsch
87 ) c
88 WHERE
89 c.cnt = 1,
90
91 )";
92 $qry = do_query($query);
93
94
95 #Erstwünsche auslosen
96 $query = "SELECT DISTINCT erstwunsch as dis_wish from bewerber";
97 if (do_query($query)) {foreach(do_query($query) as $res) {
98 $query = "SELECT count(*) as cnt_primary from bewerber where erstwunsch =".$res['dis_wish'];
99 $cnt_primary = do_query($query)[0]['cnt_primary'];
100 if ($cnt_primary <= 1) break;
101 $query = "SELECT * from bewerber where erstwunsch = ".$res['dis_wish'];
102 $applicants = do_query($query);
103 #var_dump($applicants[rand(0,$cnt_primary-1)]);
104 $query = "INSERT INTO zuweisung (
105 bewerber_id,
106 taettoowierer_id,
107 COMMENT
108 )
109 VALUES
110 (
111 ".$applicants[rand(0,$cnt_primary-1)]['id'].",
112 ".$res['dis_wish'].",
113 'geloster Erstwunsch'
114 )";
115 $qry = do_query($query);
116 }}
117
118 #einzelne Zweitbewerber eintragen
119 $query = "INSERT INTO zuweisung (
120 SELECT
121 c.id,
122 c.zweitwunsch,
123 'alleiniger Zweitwunsch'
124 FROM
125 (
126 SELECT
127 id,
128 zweitwunsch,
129 count(*) AS cnt
130 FROM
131 bewerber
132 WHERE
133 zweitwunsch IS NOT NULL
134 AND id NOT IN (
135 SELECT
136 zuweisung.bewerber_id
137 FROM
138 zuweisung
139 )
140 AND zweitwunsch NOT IN (
141 SELECT
142 zuweisung.taettoowierer_id
143 FROM
144 zuweisung
145 )
146 GROUP BY
147 zweitwunsch
148 ) c
149 WHERE
150 c.cnt = 1
151 )";
152 $qry = do_query($query);
153
154 #Zweitwünsche auslosen
155 $query = "SELECT
156 *
157 FROM
158 (
159 SELECT DISTINCT
160 zweitwunsch AS dis_2wish
161 FROM
162 bewerber
163 WHERE
164 bewerber.id NOT IN (
165 SELECT
166 bewerber_id
167 FROM
168 zuweisung
169 )
170 AND zweitwunsch IS NOT NULL
171 AND zweitwunsch NOT IN (
172 SELECT
173 taettoowierer_id
174 FROM
175 zuweisung
176 )
177 ) a
178 WHERE
179 a.dis_2wish NOT IN (
180 SELECT
181 taettoowierer_id
182 FROM
183 zuweisung)";
184 if (do_query($query)) {foreach(do_query($query) as $res) {
185 $query = "SELECT count(*) as cnt_secondary from bewerber where bewerber.id NOT IN( SELECT bewerber_id FROM zuweisung) and zweitwunsch =".$res['dis_2wish'];
186 $cnt_secondary = do_query($query)[0]['cnt_secondary'];
187 if ($cnt_secondary <= 1) break;
188 $query = "SELECT * from bewerber where bewerber.id NOT IN( SELECT bewerber_id FROM zuweisung) and zweitwunsch = ".$res['dis_2wish'];
189 $applicants = do_query($query);
190 #var_dump($applicants[rand(0,$cnt_primary-1)]);
191 $query = "INSERT INTO zuweisung (bewerber_id, taettoowierer_id, comment) VALUES (".$applicants[rand(0,$cnt_secondary-1)]['id'].", ".$res['dis_2wish'].", 'geloster Zweitwunsch')";
192 $qry = do_query($query);
193 }}
194
195
196
197
198}
199?>
200 <hr>
201 <h2> Neuen Tätowierer hinzufügen</h2>
202 <form action="/cpanel.php" method="POST">
203 <label for"name">Tätowierer</label><input type="text" name="name" required><br>
204 <label for"contact">E-Mail: </label><input type="text" name="contact" required><br>
205 <button type="submit" name="commit" value="sub">Eintragen</button>
206 </form><br /><br /><br /><br />
207 <hr>
208 <h2>Auslosung starten</h2>
209 <form action="/cpanel.php" method="POST">
210 <button type="submit" name="losung" value="sub">Auslosen</button>
211 </form>
212 <br /><br /><br /><br />
213 <hr>
214 <h2>Zurücksetzen der Zuordnungen</h2>
215 <form action="/cpanel.php" method="POST">
216 <button type="submit" name="reset" value="sub">Zurücksetzen</button>
217 </form>
218 <br /><br /><br /><br />
219 <hr>
220 <h2>Zuordnungen anzeigen</h2>
221 <form action="/cpanel.php" method="POST">
222 <button type="submit" name="showtable" value="sub">Anzeigen</button>
223 </form>
224 <?php
225 if (isset($_POST['showtable']) && $_POST['showtable']=="sub") {
226 show_matches();
227 }
228 ?>
229</body>
230</html>
231
232<?php
233
234function show_matches() {
235 echo "<table> <tr><th>Bewerber</th><th>Tätowierer</th><th>Kommentar</th></tr>";
236 #Anzeige der Paare
237 $query = "SELECT concat(bewerber.vorname, ' ', bewerber.`name`) as Bewerber, taettoowierer.`name` as Taettoowierer, comment FROM zuweisung INNER JOIN taettoowierer ON zuweisung.taettoowierer_id = taettoowierer.id INNER JOIN bewerber ON zuweisung.bewerber_id = bewerber.id";
238 if (do_query($query)) {foreach(do_query($query) as $res) {
239 echo "<tr><td>".$res['Bewerber']."</td><td>".$res['Taettoowierer']."</td><td>".$res['comment']."</td></tr>";
240 }}
241 echo "</table>";
242}
243//Einzige Bewerber finden und ausgeben
244#SELECT concat( bewerber.vorname, ' ', bewerber.`name`) AS Bewerber, taettoowierer.`name` AS Taettoowierer FROM( SELECT c.bewerber_id, c.taettoowierer_id FROM ( SELECT id AS bewerber_id, erstwunsch AS taettoowierer_id, count(*) AS cnt FROM bewerber GROUP BY erstwunsch ) c WHERE c.cnt = 1 ) d INNER JOIN taettoowierer ON d.taettoowierer_id = taettoowierer.id INNER JOIN bewerber ON d.bewerber_id = bewerber.id
245
246//Einzigen Bewerber finden und zuweisen
247#CREATE TEMPORARY TABLE IF NOT EXISTS c AS( SELECT id, erstwunsch, count(*) AS cnt FROM bewerber GROUP BY erstwunsch); CREATE TEMPORARY TABLE IF NOT EXISTS d AS ( SELECT c.id, c.erstwunsch FROM c WHERE c.cnt = 1 ); INSERT INTO zuweisung (SELECT * FROM d)
248
249//Anzeige von Bewerber + zugewiesenen Taettoowierer
250#SELECT concat(bewerber.vorname, ' ', bewerber.`name`) as Bewerber, taettoowierer.`name` as Taettoowierer FROM zuweisung INNER JOIN taettoowierer ON zuweisung.taettoowierer_id = taettoowierer.id INNER JOIN bewerber ON zuweisung.bewerber_id = bewerber.id