1 files changed, 58 insertions, 0 deletions
diff --git a/usth/ICT3.2/prac/4/8.php b/usth/ICT3.2/prac/4/8.php
new file mode 100644
index 0000000..ab84cd1
--- /dev/null
+++ b/usth/ICT3.2/prac/4/8.php
@@ -0,0 +1,58 @@
+<?php
+try {
+    $conn = new PDO('mysql:host=localhost', 'wensleydale');
+    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+    $conn->exec('DROP DATABASE IF EXISTS foo');
+    $conn->exec('CREATE DATABASE foo');
+    $conn->exec('USE foo');
+    $conn->exec("CREATE TABLE student (
+                 id smallint AUTO_INCREMENT PRIMARY KEY,
+                 name char(14),
+                 class char(7),
+                 mark smallint,
+                 sex char(7))");
+
+    $orig = [['John Deo', 'Four', 75, 'female'],
+             ['Max Ruin', 'Three', 86, 'male'],
+             ['Arnold', 'Three', 55, 'male'],
+             ['Krish Star', 'Four', 60, 'female'],
+             ['John Mike', 'Four', 60, 'female'],
+             ['Alex John', 'Four', 55, 'male'],
+             ['My John Rob', 'Fifth', 78, 'male'],
+             ['Asruid', 'Five', 85, 'male'],
+             ['Tes Qry', 'Six', 78, 'male'],
+             ['Big John', 'Four', 55, 'female']];
+
+    $insert = $conn->prepare(
+        'INSERT IGNORE INTO student (name, class, mark, sex)'
+        . ' VALUES (:name, :class, :mark, :sex)');
+    $insert->bindParam(':name', $name);
+    $insert->bindParam(':class', $class);
+    $insert->bindParam(':mark', $mark);
+    $insert->bindParam(':sex', $sex);
+
+    foreach ($orig as list($name, $class, $mark, $sex))
+        $insert->execute();
+
+    $conn->exec("UPDATE student SET class = 'Two' WHERE mark<60");
+
+    $best = $conn->query("SELECT * FROM student WHERE mark>75");
+    echo "Best student:<br>\n";
+    while ($row = $best->fetch())
+        echo $row['name'] . ' (class ' . $row['class']
+             . ', mark ' . $row['mark'] . ")<br>\n";
+
+    echo "Good student:<br>\n";
+    $good = $conn->query("SELECT * FROM student WHERE mark>60 AND mark<=75");
+    while ($row = $good->fetch())
+        echo $row['name'] . ' (class ' . $row['class']
+             . ', mark ' . $row['mark'] . ")<br>\n";
+
+    echo "Average student:<br>\n";
+    $avg = $conn->query("SELECT * FROM student WHERE mark<60");
+    while ($row = $avg->fetch())
+        echo $row['name'] . ' (class ' . $row['class']
+             . ', mark ' . $row['mark'] . ")<br>\n";
+} catch(PDOException $e) {
+    echo "Connection failed: " . $e->getMessage();
+}