· 8 years ago · Apr 23, 2018, 09:14 PM
1<html>
2 <head>
3 </head>
4 <body>
5 <form action="seite2.php" method="post">
6 Interpret:<input type="text" name="artist"/>
7 Titel:<input type="text" name="title"/>
8 <input type="submit" value="Senden"/>
9<?php
10
11?>
12 </body>
13</html>
14________________________________________________________ CONNECT
15
16<?php
17$servername = "localhost";
18$username = "username";
19$password = "password";
20
21// Create connection
22$conn = mysqli_connect($servername, $username, $password);
23
24// Check connection
25if (!$conn) {
26 die("Connection failed: " . mysqli_connect_error());
27}
28echo "Connected successfully";
29?>
30
31mysqli_close($conn);
32________________________________________________________ INSERT
33<?php
34$servername = "localhost";
35$username = "username";
36$password = "password";
37$dbname = "myDB";
38
39// Create connection
40$conn = mysqli_connect($servername, $username, $password, $dbname);
41// Check connection
42if (!$conn) {
43 die("Connection failed: " . mysqli_connect_error());
44}
45
46$sql = "INSERT INTO MyGuests (firstname, lastname, email)
47VALUES ('John', 'Doe', 'john@example.com')";
48
49if (mysqli_query($conn, $sql)) {
50 echo "New record created successfully";
51} else {
52 echo "Error: " . $sql . "<br>" . mysqli_error($conn);
53}
54
55mysqli_close($conn);
56?>
57
58_________________________________________________ POST / GET
59$interpret = $_POST["artist"];
60$interpret = $_GET["artist"];
61
62foreach ($array as $value){
63}
64
65
66________________________________________________ QUERY / FETCH / READ .. fetch_all FÃœR ALLE
67$sql = "SELECT id, firstname, lastname FROM MyGuests";
68$result = mysqli_query($conn, $sql);
69
70if (mysqli_num_rows($result) > 0) {
71 // output data of each row
72 while($row = mysqli_fetch_assoc($result)) {
73 echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
74 }
75} else {
76 echo "0 results";
77}
78
79_________________________________________________ UPDATE
80$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";
81
82if (mysqli_query($conn, $sql)) {
83 echo "Record updated successfully";
84} else {
85 echo "Error updating record: " . mysqli_error($conn);
86}
87
88_________________________________________________ table
89echo '<table border="1">';
90while ($zeile = mysqli_fetch_array( $db_erg, MYSQL_ASSOC))
91{
92 echo "<tr>";
93 echo "<td>". $zeile['id'] . "</td>";
94 echo "<td>". $zeile['nachname'] . "</td>";
95 echo "<td>". $zeile['vorname'] . "</td>";
96 echo "<td>". $zeile['akuerzel'] . "</td>";
97 echo "<td>". $zeile['strasse'] . "</td>";
98 echo "<td>". $zeile['plz'] . "</td>";
99 echo "<td>". $zeile['telefon'] . "</td>";
100 echo "</tr>";
101}
102echo "</table>";
103
104mysqli_free_result( $db_erg );
105_____________________________________________________________
106<a href="index.php>ZURÃœCK ZUR STARTSEITE</a>
107
108__________________________ ausgabe print für 1 sonst fetch_all
109<?php
110require 'inc/db.php';
111echo "<h1>Programm Adressbuch</h1>";
112$erg = $db->query("SELECT id, vorname, nachname FROM kontakte")
113 or die($db->error);
114print_r($erg);
115if ($erg->num_rows) {
116 echo "<p>Daten vorhanden: Anzahl ";
117 echo $erg->num_rows;
118}
119$datensatz = $erg->fetch_assoc();
120echo "<pre>";
121print_r($datensatz);
122echo "</pre>";
123?>
124______________________________ null / empty / isempty / isNull
125$var = '';
126
127// Dieser Ausdruck wird zu TRUE ausgewertet, also wird der Text angezeigt
128if (isset($var)) {
129 echo "Die Variable ist gesetzt, also wird etwas ausgegeben.";
130}
131
132<?php
133
134 //Überprüfung ob die daten Gesetzt worden sind
135 if(isset($_POST['Vorname']) && isset($_POST['Nachname']))
136 {
137 //Ausgabe der Begrüßung
138 echo "Hallo ".$_POST['Vorname']." ".$_POST['Nachname'];
139 } else {
140 //Formular eingabe
141 ?>
142 <form action="" method="post">
143 <p>Vorname: <input type="Text" name="Vorname" /></p>
144 <p>Nachname: <input type="Text" name="Nachname" /></p>
145 <p><input type="submit" value="Senden"/></p>
146 </form>
147 <?php
148 }
149
150?>
151
152
153//kontrolle ob daten eingegeben wurden
154 if(empty ($_POST['geburtstag']) == FALSE && empty ($_POST['geburtstagheute']) == FALSE)
155 {
156 //Um casten auf die andere DateTime
157 $datetime1 = new DateTime($_POST['geburtstag']);
158 $datetime2 = new DateTime($_POST['geburtstagheute']);
159
160 echo "Ich Geburstag ist am ".$_POST['geburtstag']."<br>";
161 echo "Heute ist der ".$_POST['geburtstagheute']."<br>";
162
163 Datediff($datetime1,$datetime2);
164 }
165_______________________________ differenzen tage
166
167//Berechnet die differenzierenden daten und gibt die Differenz in Tagen und Wochen an
168 function Datediff($date1,$date2)
169 {
170 $days = round(($date2->format("U") - $date1->format("U")) / 86400);
171
172 //Ausgabe der Berechnung
173 echo "Der ZeitUnterschied betraegt: ".$days." Tage oder ca. ".round($days/7)." Wochen";
174
175 }
176____________________________________
177sql:
178describe student;
179INSERT into STUDENT values (vorname, nachnname..);
180INSERT into STUDENT(vorname,..) values (vorname, nachnname..);
181UPDATE student set vorname "Tarek" where name "elyas"
182
183mysql:
184Count rows per group COUNT(column | *)
185Average value of group AVG(column)
186Minumum value of group MIN(column)
187Maximum value of group MAX(column)
188Sum values in a group SUM(column)
189Absolute value abs(number)
190Rounding numbers round(number)
191Largest integer not greater floor(number)
192Smallest integer not smaller ceiling(number)
193Square root sqrt(number)
194
195Compare strings strcmp(string1,string2)
196Convert to lower case lower(string)
197Convert to upper case upper(string)
198Left-trim whitespace (similar right) ltrim(string)
199Substring of string substring(string,index1,index2)
200Encrypt password password(string)
201Encode string encode(string,key)
202Decode string decode(string,key)
203Get date curdate()
204Get time curtime()
205Extract day name from date string dayname(string)
206Extract day number from date string dayofweek(string)
207Extract month from date string monthname(string)
208
209
210mysql no gui;
211mysql -u root -p
212show databases;
213create database name;
214use name;
215create table tName (vorname varchar(100), age int, dob Date);
216alter table tName add new varchar(100);
217drop database name;
218
219
220
221
222//if everything fails
223SQL Commands: Modifying
224What How Example(s)
225Create table CREATE TABLE table (
226 column1 type [[NOT] NULL]
227 [AUTO_INCREMENT],
228 column2 type [[NOT] NULL]
229 [AUTO_INCREMENT],
230 ...
231 other options,
232 PRIMARY KEY (column(s)) ); CREATE TABLE Students (
233 LastName varchar(30) NOT NULL,
234 FirstName varchar(30) NOT NULL,
235 StudentID int NOT NULL,
236 Major varchar(20),
237 Dorm varchar(20),
238 PRIMARY KEY (StudentID) );
239Insert data INSERT INTO table VALUES
240 (list of values);
241INSERT INTO table SET
242 column1=value1,
243 column2=value2,
244 ...
245 columnk=valuek;
246INSERT INTO table (column1,column2,...)
247 VALUES (value1,value2...); INSERT INTO Students VALUES
248 ('Smith','John',123456789,'Math','Selleck');
249INSERT INTO Students SET
250 FirstName='John',
251 LastName='Smith',
252 StudentID=123456789,
253 Major='Math';
254INSERT INTO Students
255 (StudentID,FirstName,LastName)
256 VALUES (123456789,'John','Smith');
257Insert/Select INSERT INTO table (column1,column2,...)
258 SELECT statement;
259 (See below) INSERT INTO Students
260 (StudentID,FirstName,LastName)
261 SELECT StudentID,FirstName,LastName
262 FROM OtherStudentTable;
263 WHERE LastName like '%son';
264Delete data DELETE FROM table
265 [WHERE condition(s)];
266
267
268
269(Omit WHERE to delete all data) DELETE FROM Students
270 WHERE LastName='Smith';
271DELETE FROM Students
272 WHERE LastName like '%Smith%';
273 AND FirstName='John';
274DELETE FROM Students;
275Updating Data UPDATE table SET
276 column1=value1,
277 column2=value2,
278 ...
279 columnk=valuek
280 [WHERE condition(s)]; UPDATE Students SET
281 LastName='Jones' WHERE
282 StudentID=987654321;
283UPDATE Students SET
284 LastName='Jones', Major='Theatre'
285 WHERE StudentID=987654321 OR
286 (MAJOR='Art' AND FirstName='Pete');
287Insert column ALTER TABLE table ADD COLUMN
288 column type options; ALTER TABLE Students ADD COLUMN
289 Hometown varchar(20);
290Delete column ALTER TABLE table
291 DROP COLUMN column; ALTER TABLE Students
292 DROP COLUMN Dorm;
293Delete table (Careful!) DROP TABLE [IF EXISTS] table; DROP TABLE Animals;
294
295
296SQL Commands: Querying
297What How Example(s)
298All columns SELECT * FROM table; SELECT * FROM Students;
299Some columns SELECT column1,column2,... FROM table; SELECT LastName, FirstName FROM Students;
300Some rows/
301columns SELECT column1,column2,...
302 FROM table
303 [WHERE condition(s)]; SELECT LastName,FirstName
304 FROM Students
305 WHERE StudentID LIKE '%123%';
306No Repeats SELECT [DISTINCT] column(s)
307 FROM table;
308SELECT DISTINCT LastName
309 FROM Students;
310Ordering SELECT column1,column2,...
311 FROM table
312 [ORDER BY column(s) [DESC]]; SELECT LastName,FirstName
313 FROM Students
314 ORDER BY LastName, FirstName DESC;
315Column
316Aliases SELECT column1 [AS alias1],
317 column2 [AS alias2], ...
318 FROM table1; SELECT LastName,FirstName AS First
319 FROM Students;
320Grouping SELECT column1,column2,...
321 FROM table
322 [GROUP BY column(s)]; SELECT LastName,COUNT(*)
323 FROM Students
324 GROUP BY LastName;
325Group Filtering SELECT column1,column2,...
326 FROM table
327 [GROUP BY column(s)]
328 [HAVING condition(s)]; SELECT LastName,COUNT(*)
329 FROM Students
330 GROUP BY LastName
331 HAVING LastName like '%son';
332Joins SELECT column1,column2,...
333 FROM table1,table2,...
334 [WHERE condition(s)]; SELECT LastName,Points
335 FROM Students,Assignments
336 WHERE AssignmentID=12 AND
337 Students.StudentID=Assignments.StudentID;
338Table
339Aliases SELECT column1,column2,...
340 FROM table1 [alias1],
341 table2 [alias2],...
342 [WHERE condition(s)]; SELECT LastName,Points
343 FROM Students S,Assignments A
344 WHERE S.StudentID=A.StudentID AND
345 A.AssignmentID=12;
346Everything SELECT [DISTINCT]
347 column1 [AS alias1],
348 column2 [AS alias2], ...
349 FROM table1 [alias1],
350 table2 [alias2],...
351 [WHERE condition(s)]
352 [GROUP BY column(s)]
353 [HAVING condition(s)]
354 [ORDER BY column(s) [DESC]]; SELECT Points, COUNT(*) AS Cnt
355 FROM Students S,Assignments A
356 WHERE S.StudentID=A.StudentID AND
357 A.AssignmentID=12
358 GROUP BY Points
359 HAVING Points > 10
360 ORDER BY Cnt, Points DESC;