View sourcecode

<?php

/**
 * Generate random values to use in lab.
 */
include __DIR__ . "/../random.php";

// ################### QUERY DATA ##################

$q1 = ["USA", "England"];
$q1Use = $q1[rand_int(0, count($q1)-1)];

$q2 = [1943, 1956, 1977, 1929, 1905, 1934, 1984, 1963, 1930, 1962, 1987, 1964, 1937];
$q2Use = $q2[rand_int(0, count($q2)-1)];

$q3a = ["De Niro", "Nicholson", "Hepburn", "Garbo", "Hanks", "Loren", "Blanchett"];
$q3b = ["Depp", "Bloom", "Carrey", "Johansson", "Connery", "Cage", "Aniston", "Clarke"];
$q3Use1 = $q3a[rand_int(0, count($q3a)-1)];
$q3Use2 = $q3b[rand_int(0, count($q3b)-1)];

// ################### Connect to Database ##################

try {
    $mydb = new PDO("sqlite:" . __DIR__ . "/myDB.sqlite");
    $mydb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
}
catch(PDOException $e) {
    echo $e->getMessage();
}

// ################### SETUP QUERIES ##################

$query1 = "SELECT firstName FROM people WHERE countryOfBirth=?";
$query2 = "SELECT * FROM people WHERE born=?";
$query3 = "SELECT born FROM people WHERE lastName IN (?,?)";
$query4 = "SELECT count(*) FROM people";
$query5 = "SELECT MIN(born), countryOfBirth FROM people";
$query6 = "SELECT AVG(born) FROM people";
$query7 = "SELECT MAX(born), cityOfBirth FROM people WHERE countryOfBirth='USA'";
$query8 = "SELECT firstName, lastname FROM people ORDER BY lastName";

// ################### PREPARE STATEMENTS ##################

$stmt1 = $mydb->prepare($query1);
$stmt2 = $mydb->prepare($query2);
$stmt3 = $mydb->prepare($query3);
$stmt4 = $mydb->prepare($query4);
$stmt5 = $mydb->prepare($query5);
$stmt6 = $mydb->prepare($query6);
$stmt7 = $mydb->prepare($query7);
$stmt8 = $mydb->prepare($query8);

// ################### EXECUTE STATEMENTS ##################

$stmt1->execute(array($q1Use));
$stmt2->execute(array($q2Use));
$stmt3->execute(array($q3Use1, $q3Use2));
$stmt4->execute();
$stmt5->execute();
$stmt6->execute();
$stmt7->execute();
$stmt8->execute();

// ################### GET RESULTS #################

$res1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
$res2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
$res3 = $stmt3->fetchAll(PDO::FETCH_ASSOC);
$res4 = $stmt4->fetch(PDO::FETCH_NUM);
$res5 = $stmt5->fetchAll(PDO::FETCH_ASSOC);
$res6 = $stmt6->fetch(PDO::FETCH_NUM);
$res7 = $stmt7->fetch(PDO::FETCH_ASSOC);
$res8 = $stmt8->fetchAll(PDO::FETCH_ASSOC);

// ################### FIX ANSWERS ##################

$answer1 = [];
$answer2 = "";
$answer3 = 0;
$answer4 = (int)$res4[0];
$answer5 = "";
$answer6 = (int)$res6[0];
$answer7 = (string)$res7['cityOfBirth'];
$answer8 = [];

foreach ($res1 as $row) {
    array_push($answer1, $row['firstName']);
}

foreach ($res2 as $row) {
    $answer2 = $row['firstName'] . " " . $row['lastName'];
}

foreach ($res3 as $row) {
    $answer3 += (int)$row['born'];
}

foreach ($res5 as $row) {
    $answer5 = $row['countryOfBirth'];
}

foreach ($res8 as $row) {
    array_push($answer8, $row['lastName'] . " " . $row['firstName']);
}

// ################### Close the connection ##################

unset($mydb);



/**
 * Titel and introduction to the lab.
 */


return [



/**
 * Titel and introduction to the lab.
 */
"title" => "Lab 5 - Htmlphp",

"intro" => "
<p>In this lab you will be working with a SQLite database file: 'myDB.sqlite' and PDO. References: http://php.net/manual/en/book.pdo.php, http://dbwebb.se/htmlphp/me/kmom10/guide.php?id=sqlite20#s10
</p>
",


"sections" => [



/** ===================================================================================
 * New section of exercises.
 */
[
"title" => "Working with SQLite and PDO",

"intro" => "
<p>The database has one table called 'people'. 'people' has six columns: 'id', 'firstName', 'lastName', 'born', 'cityOfBirth', 'countryOfBirth'.
</p>
",

"shuffle" => false,

"questions" => [



/** -----------------------------------------------------------------------------------
 * A question.
 */
[

"text" => "
<p>Find the firstnames of the people born in $q1Use. Answer with an array containing the names.
</p>
",

"answer" => function () use($answer1) {

    return $answer1;
},

],



/** -----------------------------------------------------------------------------------
 * A question.
 */
[

"text" => "
<p>Find the first name and last name of the person born $q2Use. Answer with a string in the format: 'Firstname Lastname'.
</p>
",

"answer" => function () use($answer2) {

    return $answer2;
},

],



/** -----------------------------------------------------------------------------------
 * A question.
 */
[

"text" => "
<p>Sum the years the persons with the lastnames '$q3Use1' and '$q3Use2' were born. Answer with an integer.
</p>
",

"answer" => function () use($answer3) {

    return $answer3;
},

],



/** -----------------------------------------------------------------------------------
 * A question.
 */
[

"text" => "
<p>Count the number of entries in the table 'people'. Answer with an integer.
</p>
",

"answer" => function () use($answer4) {

    return $answer4;
},

],



/** -----------------------------------------------------------------------------------
 * A question.
 */
[

"text" => "
<p>Find which country ('countryOfBirth') the oldest person was born in. Answer with a string.
</p>
",

"answer" => function () use($answer5) {

    return $answer5;
},

],



/** -----------------------------------------------------------------------------------
 * A question.
 */
[

"text" => "
<p>What is the average value of the column 'born'? Answer with an integer.
</p>
",

"answer" => function () use($answer6){

    return $answer6;
},

],



/** -----------------------------------------------------------------------------------
 * A question.
 */
[

"text" => "
<p>Find the youngest person born in USA. Which city is he/she born in? Answer with a string.
</p>
",

"answer" => function () use($answer7){

    return $answer7;
},

],



/** -----------------------------------------------------------------------------------
 * A question.
 */
[

"text" => "
<p>Get the first name and lastname of all persons in the database. Order them by their last name, alphabetically and ascending. Answer with an array of strings, ['lastName firstName', 'lastName firstName'].
</p>
",

"answer" => function () use($answer8){

    return $answer8;
},

],



/**
 * Closing up this section.
 */
], // EOF questions
], // EOF section



/** -----------------------------------------------------------------------------------
 * Closing up all sections.
 */
] // EOF sections



/**
 * Closing up this lab.
 */
]; // EOF the enritre lab