[Gandi][Simple Hosting] Mysql PDO

1) Créer la base de donnée et une table de test :

CREATE DATABASE AddressBook;
CREATE TABLE IF NOT EXISTS `Person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

2) Insertion de données :
Ouvrir :

mysql.pdo.insert.php

Ajouter :

$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // prepare sql and bind parameters
    $stmt = $conn->prepare("INSERT INTO Person (firstname, lastname, email)
    VALUES (:firstname, :lastname, :email)");
    $stmt->bindParam(':firstname', $firstname);
    $stmt->bindParam(':lastname', $lastname);
    $stmt->bindParam(':email', $email);
    // insert a row
    $firstname = "John";
    $lastname = "Doe";
    $email = "john@example.com";
    $stmt->execute();
    // insert another row
    $firstname = "Mary";
    $lastname = "Moe";
    $email = "mary@example.com";
    $stmt->execute();
    // insert another row
    $firstname = "Julie";
    $lastname = "Dooley";
    $email = "julie@example.com";
    $stmt->execute();
    echo "New records created successfully";
    }
catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }
$conn = null;

3) Sélectionner les données :
Ouvrir :

mysql.pdo.select.php

Ajouter :

header("Cache-Control: max-age=1");
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "SELECT v1

";
    $sql = "SELECT * FROM Person";
    foreach ($conn->query($sql) as $row) {
        echo $row['firstname'] .' - '. $row['lastname'] . "
";
     }
    echo "

SELECT v2

";
    $result = $conn->query("SELECT * FROM Person");
    $result->setFetchMode(PDO::FETCH_OBJ);
    while( $row = $result->fetch() ) {
       echo $row->firstname . " - " . $row->lastname . "
";
    }
    $result->closeCursor();
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;

4) Importer des données :
Ouvrir :

/srv/data/web/vhosts/monsite.fr/htdocs/MYSQL.pdo/data.csv

Ajouter :

test1;test2;test@test.fr
fake1;fake2;fake@fake.com
foo1;foo2;foo@foo.be

Ouvrir :

mysql.pdo.import.php

Ajouter :

header("Cache-Control: max-age=1");
$servername = "localhost";
$username = "";
$password = "";
$dbname = "";
try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // prepare sql and bind parameters
    $stmt = $conn->prepare("LOAD DATA INFILE :file INTO TABLE Person FIELDS TERMINATED BY ';' ");
    $stmt->bindParam(':file', $file);
    // insert another row
    $file = "/srv/data/web/vhosts/monsite.fr/htdocs/MYSQL.pdo/data.csv";
    $stmt->execute();
    echo "New records created successfully";
    }
catch(PDOException $e)
    {
    echo "Error: " . $e->getMessage();
    }
$conn = null;