questions/29460447 - test view creation and select

<?php // http://stackoverflow.com/questions/29460447/pdoexception-syntax-error-or-access-violation-1142-when-creating-view-referenci?noredirect=1#comment47088865_29460447

// PHP 5.3.29 -

// db connection to TestMysql and Rags1
$dsn = 'mysql:host=localhost;dbname=testmysql';
$username = 'test';
$password = 'test';
$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$pdoDB = new PDO($dsn, $username, $password, $options);
$pdoDB->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


$sql_T1 = "
    CREATE TABLE SOQ_T (
        id int(11) NOT NULL AUTO_INCREMENT,
        foo varchar(45) DEFAULT NULL,
        PRIMARY KEY (id)
    );
";

$sql_V1 = "
    CREATE VIEW SOQ_V1 AS (
        SELECT SOQ_T.id   AS V1_T1_Id,
               SOQ_T.foo  AS V1_T1_Foo
        FROM SOQ_T
    );
";

$sql_V2 = "
    CREATE VIEW SOQ_V2 AS (
        SELECT SOQ_T.Id AS V2_T1_Id,
               SOQ_V1.V1_T1_Foo AS V2_V1_Foo
        FROM SOQ_T
            INNER JOIN SOQ_V1 ON SOQ_V1.V1_T1_Id = SOQ_T.id
    );
";

// CREATE TABLE SOQ_T (
try {
     $pdoDB->exec($sql_T1);
     echo("Created $sql_T1 Table.<br />");

} catch(PDOException $e) {
    echo $e->getMessage();//Remove or change message in production code
    echo '<br />';
}

// CREATE VIEW SOQ_V1 AS (
try {
     $pdoDB->exec($sql_V1);
     echo("Created $sql_V1 View.<br />");

} catch(PDOException $e) {
    echo $e->getMessage();//Remove or change message in production code
    echo '<br />';
}

// CREATE VIEW SOQ_V2 AS (
try {
     $pdoDB->exec($sql_V2);
     echo("Created $sql_V2 View.<br />");

} catch(PDOException $e) {
    echo $e->getMessage();//Remove or change message in production code
    echo '<br />';
}

// insert some data
$sql = "insert into  SOQ_T (foo) values (:foo)";
$stmt = $pdoDB->prepare($sql);

$stmt->bindValue(':foo', "row 1", PDO::PARAM_STR);
$allOk = $stmt->execute();

$lastId = $pdoDB->lastInsertId();

// select the data,,,
$sql = "select id, foo from SOQ_T where id = ?";
$stmt = $pdoDB->prepare($sql);

// ----------------
$stmt->bindValue(1, $lastId, PDO::PARAM_STR);
$allOk = $stmt->execute();
echo '<br />', $sql, '<br />';
while ($row = $stmt->fetch()) {
    var_dump($row);
}
echo '<br />';
$stmt->closeCursor();

// ----------------
$sql = "select V1_T1_Id, V1_T1_Foo from SOQ_V1 where V1_T1_Id = ?";
$stmt = $pdoDB->prepare($sql);
$stmt->bindValue(1, $lastId, PDO::PARAM_STR);
$allOk = $stmt->execute();
echo '<br />', $sql, '<br />';
while ($row = $stmt->fetch()) {
    var_dump($row);
}
echo '<br />';
$stmt->closeCursor();

// ----------------
$sql = "select V2_T1_Id, V2_V1_Foo from SOQ_V2 where V2_T1_Id = ?";
$stmt = $pdoDB->prepare($sql);
$stmt->bindValue(1, $lastId, PDO::PARAM_STR);
$allOk = $stmt->execute();
echo '<br />', $sql, '<br />';
while ($row = $stmt->fetch()) {
    var_dump($row);
}
echo '<br />';
$stmt->closeCursor();

// cleanup
$sql = "drop view SOQ_V2;";
$stmt = $pdoDB->prepare($sql);
$stmt->execute();
echo '<br />';

$sql = "drop view SOQ_V1;";
$stmt = $pdoDB->prepare($sql);
$stmt->execute();
echo '<br />';

$sql = "drop table SOQ_T;";
$stmt = $pdoDB->prepare($sql);
$stmt->execute();
echo '<br />';