Database connect

(1)

1 februari 2016

Database connect

formulieren

(2)

Example – show data in the tables Example – show data in the tables

• Function: list all tables in your database. Users can select one of tables, and show all contents in this table.

• second.php

• showtable.php

http://www.cs.kent.edu/~nruan/second.php

Mysqli = depreciated

PDO-CONNECT

(3)

second.php second.php

<html><head><title>MySQL Table Viewer</title></head><body>

<?php

// change the value of $dbuser and $dbpass to your username and password

$dbhost = 'hercules.cs.kent.edu:3306';

$dbuser = 'nruan';

$dbpass = ‘*****************’;

$dbname = $dbuser;

$table = 'account';

$conn = mysqli_connect($dbhost, $dbuser, $dbpass);

if (!$conn) {

die('Could not connect: ' . mysqli_error());

}

if (!mysqli_select_db($dbname))

die("Can't select database");

(4)

second.php (cont.) second.php (cont.)

$result = mysql_query("SHOW TABLES");

if (!$result) {

die("Query to show fields from table failed");

}

$num_row = mysqli_num_rows($result);

echo "<h1>Choose one table:<h1>";

echo "<form action=\"showtable.php\" method=\"POST\">";

echo "<select name=\"table\" size=\"1\" Font size=\"+2\">";

for($i=0; $i<$num_row; $i++) {

$tablename=mysqli_fetch_row($result);

echo "<option value=\"{$tablename[0]}\" >{$tablename[0]}</option>";

}

echo "</select>";

echo "<div><input type=\"submit\" value=\"submit\"></div>";

echo "</form>";

mysqli_free_result($result);

mysqli_close($conn);

?>

</body></html>

(5)

showtable.php showtable.php

<html><head>

<title>MySQL Table Viewer</title>

</head>

<body>

<?php

$dbhost = 'hercules.cs.kent.edu:3306';

$dbuser = 'nruan';

$dbpass = ‘**********’;

$dbname = 'nruan';

$table = $_POST[“table”];

$conn = mysqli_connect($dbhost, $dbuser, $dbpass);

if (!$conn)

die('Could not connect: ' . mysql_error());

if (!mysqli_select_db($dbname)) die("Can't select database");

**$result = mysqli_query("SELECT * FROM {$table}");**

if (!$result) die("Query to show fields from table failed!" . mysqli_error());

(6)

showtable.php (cont.) showtable.php (cont.)

$fields_num = mysql_num_fields($result);

echo "<h1>Table: {$table}</h1>";

echo "<table border='1'><tr>";

// printing table headers

for($i=0; $i<$fields_num; $i++) {

$field = mysqli_fetch_field($result);

echo "<td><b>{$field->name}</b></td>";

}

echo "</tr>\n";

while($row = mysqli_fetch_row($result)) { echo "<tr>";

// $row is array... foreach( .. ) puts every element // of $row to $cell variable

foreach($row as $cell) echo "<td>$cell</td>";

echo "</tr>\n";

}

mysqli_free_result($result);

mysqli_close($conn);

?>

</body></html>

(7)

Functions Covered Functions Covered

• mysqli_connect() mysqli_select_db()

• include()

• mysqli_query() mysqli_num_rows()

• mysqli_fetch_array() mysqli_close()

(8)

PDO-connect voorbeeld PDO-connect voorbeeld

// PDO connect try {

$dbh = new PDO('mysql:host=‘localhost’;dbname=werknemers', $user, $password);

// read rows from select statement

foreach($dbh->query('SELECT * from werknemers') as $row) { // show results

echo "<tr>

<td>$row[voornaam]</th>

<td>$row[achternaam]</th>

<td>$row[kamer]</th>

<td>$row[toestel]</th>

</tr>";

}

$dbh = null;

(9)

PDO-connect PDO-connect

// PDO connect Try {

..

} catch (PDOException $e) {

print "Error!: " . $e->getMessage() . "<br/>";

die();

}

(10)

PDO-connect

MYSQLi-connect is feitelijk alleen voor MYSQL

databases maar wat nu indien je het programma PHP wilt behouden en een andere database engine wilt

benaderen?

PDO-connect is database onafhankelijk zodat je een minimale aanpassing hebt.

<?php

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

?>

$dbh = de verbinding naar de database

bakker 2016

(11)

PDO-connect

<?php

// variabelen

$host = ‘localhost’;

$database=‘test’;

$user = ‘root’;

$pass = ‘1234567’;

$dbh = new PDO(“mysql:host=$host;dbname=$database”, $user, $pass);

?>

Om een verbinding te maken heb je nodig:

Host Database User Password

$dbh is een object ook wel DataBaseHandler genoemd

bakker 2016

(12)

PDO-connect

<?php

// variabelen

$host = ‘localhost’;

$database=‘test’;

$user = ‘root’;

$pass = ‘1234567’;

$dbh = new PDO(“mysql:host=$host;dbname=$database”, $user, $pass);

?>

Met de verbinding ga je verbinden naar de tabel

$dbh->query('SELECT * from FOO’)

$query = “SELECT * from FOO”;

$dbh->query($query) // dit geeft een aantal rijen PDO::query

bakker 2016

(13)

PDO-connect

Try en Catch

Een database kan een connectie fout geven waardoor het programma ‘hangt’.

<?php try {

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

**foreach($dbh->query('SELECT * from FOO') as $row) {** print_r($row);

}

$dbh = null;

} catch (PDOException $e) {

print "Error!: " . $e->getMessage() . "<br/>";

die();

}

?>

bakker 2016

(14)

PDO-connect

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

**foreach($dbh->query('SELECT * from FOO') as $row) {** print $row[‘voornaam'] . "\t";

print $row[‘achternaam'] . "\t";

print $row[‘telefoon'] . "\n";

print_r($row);

}

Foreach

Regel voor regel ophalen en verwerken

bakker 2016

(15)

PDO-connect

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

$query=“Select vn, an from users”;

$qres = $dbh->prepare($query);

$qres->execute($query);

while ( $row = $qres->fetch() ) { print_r($row);

}

While

Is er wel iets gevonden, test op $row blijf verwerken

bakker 2016

(16)

PDO-connect

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

$qres = $dbh->prepare("select name FROM tbl_name");

$qres->execute();

For ( $i=0; $row = $qres->fetch(); $i++) { echo $i." - ".$row['name']."<br/>";

} For

In een lus afvragen van het resultaat

bakker 2016

(17)

PDO-connect

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

$t1 = ‘voornaam’;

$t2 = ‘achternaam’;

$t3 = ‘email’;

$qres = $dbh->query("SELECT * FROM table_users

WHERE voornaam=? AND achternaam=? AND email=?",$t1,$t2,$t3);

while ($row = $qres->fetch()) {

print_r($row);

}

PDO variabelen doorgeven

Doorgeven van variabelen in een querystring

bakker 2016

(18)

PDO-connect

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

$dbh->setAttribute(

PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);

PDO::ATTR_ERRMODE = foutmelding afhandeling

PDO::ERRMODE_SILENT = Geen foutmelding van de database

Dus geen melding op

print "Error!: " . $e->getMessage() . "<br/>";

PDO setattribute

Opties die je kunt gebruiken

bakker 2016

(19)

PDO-connect

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

$dbh-> setAttribute(PDO::ATTR_TIMEOUT, 30);

Geef de database 30 seconden te tijd om met een resultaat te komen

$dbh->setAttribute(PDO::ATTR_PERSISTENT , TRUE);

Verbinding behouden en niet verbreken

PDO setattribute

Opties die je kunt gebruiken

bakker 2016

(20)

PDO-connect

PDO::ATTR_CASE

PDO::ATTR_ERRMODE

PDO::ATTR_ORACLE_NULLS

PDO::ATTR_STRINGIFY_FETCHES PDO::ATTR_STATEMENT_CLASS PDO::ATTR_TIMEOUT

PDO::ATTR_AUTOCOMMIT

PDO::ATTR_EMULATE_PREPARES

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY PDO::ATTR_DEFAULT_FETCH_MODE

Bron:www.php.net

PDO attribute

Opties die je kunt gebruiken

bakker 2016

(21)

Mysqli versus PDO connect

bakker 2016

(22)

Mysqli versus PDO connect

bakker 2016

(23)

Mysqli versus PDO connect

bakker 2016

(24)

Mysqli versus PDO connect

bakker 2016

(25)

Mysqli versus PDO connect

bakker 2016

(26)

Mysqli versus PDO connect

bakker 2016