1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
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();
}
|
