Untitled

<html>
	<head>
	</head>
	<body>
		<form action="seite2.php" method="post">
		Interpret:<input type="text" name="artist"/>
		Titel:<input type="text" name="title"/>
		<input type="submit" value="Senden"/>
<?php

?>
	</body>
</html>
________________________________________________________  CONNECT

<?php
$servername = "localhost";
$username = "username";
$password = "password";

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

mysqli_close($conn);
________________________________________________________  INSERT
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

_________________________________________________ POST / GET
$interpret = $_POST["artist"];
$interpret = $_GET["artist"];

foreach ($array as $value){
}


________________________________________________ QUERY / FETCH / READ .. fetch_all FÜR ALLE
$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
    }
} else {
    echo "0 results";
}

_________________________________________________ UPDATE
$sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=2";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}

_________________________________________________ table
echo '<table border="1">';
while ($zeile = mysqli_fetch_array( $db_erg, MYSQL_ASSOC))
{
  echo "<tr>";
  echo "<td>". $zeile['id'] . "</td>";
  echo "<td>". $zeile['nachname'] . "</td>";
  echo "<td>". $zeile['vorname'] . "</td>";
  echo "<td>". $zeile['akuerzel'] . "</td>";
  echo "<td>". $zeile['strasse'] . "</td>";
  echo "<td>". $zeile['plz'] . "</td>";
  echo "<td>". $zeile['telefon'] . "</td>";
  echo "</tr>";
}
echo "</table>";

mysqli_free_result( $db_erg );
_____________________________________________________________
<a href="index.php>ZURÜCK ZUR STARTSEITE</a>

__________________________ ausgabe print für 1 sonst fetch_all
<?php
require 'inc/db.php';
echo "<h1>Programm Adressbuch</h1>";
$erg = $db->query("SELECT id, vorname, nachname FROM kontakte")
	or die($db->error);
print_r($erg);
if ($erg->num_rows) {
	echo "<p>Daten vorhanden: Anzahl ";
	echo $erg->num_rows;
}
$datensatz = $erg->fetch_assoc();
echo "<pre>";
print_r($datensatz);
echo "</pre>";
?>
______________________________ null / empty / isempty / isNull
$var = '';

// Dieser Ausdruck wird zu TRUE ausgewertet, also wird der Text angezeigt
if (isset($var)) {
    echo "Die Variable ist gesetzt, also wird etwas ausgegeben.";
}

<?php

	//Überprüfung ob die daten Gesetzt worden sind
	if(isset($_POST['Vorname']) && isset($_POST['Nachname']))
	{
		//Ausgabe der Begrüßung
		echo "Hallo ".$_POST['Vorname']." ".$_POST['Nachname'];
	} else {
		//Formular eingabe
		?>
			<form action="" method="post">
			<p>Vorname: <input type="Text" name="Vorname" /></p>
			<p>Nachname: <input type="Text" name="Nachname" /></p>
			<p><input type="submit" value="Senden"/></p>
			</form>
		<?php
	}

?>


//kontrolle ob daten eingegeben wurden
	if(empty ($_POST['geburtstag']) == FALSE && empty ($_POST['geburtstagheute']) == FALSE)
	{
		//Um casten auf die andere DateTime
		$datetime1 = new DateTime($_POST['geburtstag']);
		$datetime2 = new DateTime($_POST['geburtstagheute']);

		echo "Ich Geburstag ist am ".$_POST['geburtstag']."<br>";
		echo "Heute ist der ".$_POST['geburtstagheute']."<br>";

		Datediff($datetime1,$datetime2);
	}
_______________________________ differenzen tage

//Berechnet die differenzierenden daten und gibt die Differenz in Tagen und Wochen an
	function Datediff($date1,$date2)
	{
		$days = round(($date2->format("U") - $date1->format("U")) / 86400);

		//Ausgabe der Berechnung
		echo "Der ZeitUnterschied betraegt: ".$days." Tage oder ca. ".round($days/7)." Wochen";

	}
____________________________________
sql:
describe student;
INSERT into STUDENT values (vorname, nachnname..);
INSERT into STUDENT(vorname,..) values (vorname, nachnname..);
UPDATE student set vorname "Tarek" where name "elyas"

mysql:
Count rows per group	COUNT(column | *)
Average value of group	AVG(column)
Minumum value of group	MIN(column)
Maximum value of group	MAX(column)
Sum values in a group	SUM(column)
Absolute value	abs(number)
Rounding numbers	round(number)
Largest integer not greater	floor(number)
Smallest integer not smaller	ceiling(number)
Square root	sqrt(number)

Compare strings	strcmp(string1,string2)
Convert to lower case	lower(string)
Convert to upper case	upper(string)
Left-trim whitespace (similar right)	ltrim(string)
Substring of string	substring(string,index1,index2)
Encrypt password	password(string)
Encode string	encode(string,key)
Decode string	decode(string,key)
Get date	curdate()
Get time	curtime()
Extract day name from date string	dayname(string)
Extract day number from date string	dayofweek(string)
Extract month from date string	monthname(string)


mysql no gui;
mysql -u root -p
show databases;
create database name;
use name;
create table tName (vorname varchar(100), age int, dob Date);
alter table tName add new varchar(100);
drop database name;


//if everything fails
SQL Commands: Modifying
What	How	Example(s)
Create table	CREATE TABLE table (
        column1 type [[NOT] NULL]
                [AUTO_INCREMENT],
        column2 type [[NOT] NULL]
                [AUTO_INCREMENT],
        ...
        other options,
        PRIMARY KEY (column(s))    );	CREATE TABLE Students (
        LastName varchar(30) NOT NULL,
        FirstName varchar(30) NOT NULL,
        StudentID int NOT NULL,
        Major varchar(20),
        Dorm varchar(20),
        PRIMARY KEY (StudentID)     );
Insert data	INSERT INTO table VALUES
        (list of values);
INSERT INTO table SET
        column1=value1,
        column2=value2,
        ...
        columnk=valuek;
INSERT INTO table (column1,column2,...)
        VALUES (value1,value2...);	INSERT INTO Students VALUES
        ('Smith','John',123456789,'Math','Selleck');
INSERT INTO Students SET
        FirstName='John',
        LastName='Smith',
        StudentID=123456789,
        Major='Math';
INSERT INTO Students
        (StudentID,FirstName,LastName)
        VALUES (123456789,'John','Smith');
Insert/Select	INSERT INTO table (column1,column2,...)
        SELECT statement;
        (See below)	INSERT INTO Students
        (StudentID,FirstName,LastName)
        SELECT StudentID,FirstName,LastName
        FROM OtherStudentTable;
        WHERE LastName like '%son';
Delete data	DELETE FROM table
        [WHERE condition(s)];


(Omit WHERE to delete all data)	DELETE FROM Students
        WHERE LastName='Smith';
DELETE FROM Students
        WHERE LastName like '%Smith%';
        AND FirstName='John';
DELETE FROM Students;
Updating Data	UPDATE table SET
        column1=value1,
        column2=value2,
        ...
        columnk=valuek
        [WHERE condition(s)];	UPDATE Students SET
        LastName='Jones' WHERE
        StudentID=987654321;
UPDATE Students SET
        LastName='Jones', Major='Theatre'
        WHERE StudentID=987654321 OR
        (MAJOR='Art' AND FirstName='Pete');
Insert column	ALTER TABLE table ADD COLUMN
        column type options;	ALTER TABLE Students ADD COLUMN
        Hometown varchar(20);
Delete column	ALTER TABLE table
        DROP COLUMN column;	ALTER TABLE Students
        DROP COLUMN Dorm;
Delete table (Careful!)	DROP TABLE [IF EXISTS] table;	DROP TABLE Animals;


SQL Commands: Querying
What	How	Example(s)
All columns	SELECT * FROM table;	SELECT * FROM Students;
Some columns	SELECT column1,column2,... FROM table;	SELECT LastName, FirstName FROM Students;
Some rows/
columns	SELECT column1,column2,...
        FROM table
        [WHERE condition(s)];	SELECT LastName,FirstName
        FROM Students
        WHERE StudentID LIKE '%123%';
No Repeats	SELECT [DISTINCT] column(s)
        FROM table;
SELECT DISTINCT LastName
        FROM Students;
Ordering	SELECT column1,column2,...
        FROM table
        [ORDER BY column(s) [DESC]];	SELECT LastName,FirstName
        FROM Students
        ORDER BY LastName, FirstName DESC;
Column
Aliases	SELECT column1 [AS alias1],
        column2 [AS alias2], ...
        FROM table1;	SELECT LastName,FirstName AS First
        FROM Students;
Grouping	SELECT column1,column2,...
        FROM table
        [GROUP BY column(s)];	SELECT LastName,COUNT(*)
        FROM Students
        GROUP BY LastName;
Group Filtering	SELECT column1,column2,...
        FROM table
        [GROUP BY column(s)]
        [HAVING condition(s)];	SELECT LastName,COUNT(*)
        FROM Students
        GROUP BY LastName
        HAVING LastName like '%son';
Joins	SELECT column1,column2,...
        FROM table1,table2,...
        [WHERE condition(s)];	SELECT LastName,Points
        FROM Students,Assignments
        WHERE AssignmentID=12 AND
        Students.StudentID=Assignments.StudentID;
Table
Aliases	SELECT column1,column2,...
        FROM table1 [alias1],
        table2 [alias2],...
        [WHERE condition(s)];	SELECT LastName,Points
        FROM Students S,Assignments A
        WHERE S.StudentID=A.StudentID AND
        A.AssignmentID=12;
Everything	SELECT [DISTINCT]
        column1 [AS alias1],
        column2 [AS alias2], ...
        FROM table1 [alias1],
        table2 [alias2],...
        [WHERE condition(s)]
        [GROUP BY column(s)]
        [HAVING condition(s)]
        [ORDER BY column(s) [DESC]];	SELECT Points, COUNT(*) AS Cnt
        FROM Students S,Assignments A
        WHERE S.StudentID=A.StudentID AND
        A.AssignmentID=12
        GROUP BY Points
        HAVING Points > 10
        ORDER BY Cnt, Points DESC;