CrimeMap.github.io/get_vio_info.php at master · hebutBryant/CrimeMap.github.io · GitHub

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
<?php
$host = "localhost";
$port = "5432";
$dbname = "Crime-Data";
$user = "postgres";
$password = "";

$searchTerm = $_POST['select_area'];
$searchTerm2 = $_POST['Type'];

$count = 0;
// 创建数据库连接
$conn = pg_connect("host=$host port=$port dbname=$dbname user=$user password=$password");

// 检查连接是否成功
if (!$conn) {
echo "Failed to connect to the database";
exit;
}

// 准备查询语句
if ($searchTerm2 === "Violent") {
$query = "SELECT COUNT(*) AS count FROM merged_violent WHERE \"area unit\" = '$searchTerm'";
// 执行查询
    $result = pg_query($conn, $query);

    if (!$result) {
        echo "Query failed";
        exit;
    }

    $row = pg_fetch_assoc($result);
    $count = intval($row['count']);


} else if ($searchTerm2 === "Robbery") {
$query = "SELECT COUNT(*) AS count FROM merged_robbery WHERE \"area unit\" = '$searchTerm'";
// 执行查询
    $result = pg_query($conn, $query);

    if (!$result) {
        echo "Query failed";
        exit;
    }

    $row = pg_fetch_assoc($result);
    $count = intval($row['count']);


}else if($searchTerm2 === "Both Type"){
    $queryViolent = "SELECT COUNT(*) AS count FROM merged_violent WHERE \"area unit\" = '$searchTerm'";
    $queryRobbery = "SELECT COUNT(*) AS count FROM merged_robbery WHERE \"area unit\" = '$searchTerm'";
    $resultViolent = pg_query($conn, $queryViolent);
    $resultRobbery = pg_query($conn, $queryRobbery);

    if (!$resultViolent || !$resultRobbery) {
        echo "Query failed";
        exit;
    }

    $rowViolent = pg_fetch_assoc($resultViolent);
    $rowRobbery = pg_fetch_assoc($resultRobbery);

    $countViolent = intval($rowViolent['count']);
    $countRobbery = intval($rowRobbery['count']);

    $count = $countViolent + $countRobbery;


}
else {
echo "Invalid search term";
exit;
}

//// 执行查询
//$result = pg_query($conn, $query);
//
//if (!$result) {
//echo "Query failed";
//exit;
//}
//
//$row = pg_fetch_assoc($result);
//$count = intval($row['count']);
echo "<h1>Crime Data Search Results</h1>";
echo "<p>The count of area unit '$searchTerm' $searchTerm2 Crime is: $count</p>";


if($searchTerm2 === "Both Type") {
    if ($count <= 10) {
        echo "It is a safe place to live";
    } else if ($count <= 20) {
        echo "Exercise caution, as the area has a moderate crime rate";
    } else {
        echo "The area has a high crime rate. Take necessary precautions.";
    }
}


// 关闭数据库连接
pg_close($conn);
?>
<div>
<a href="Map.html" class="btn btn-primary">Return to Map</a>
</div>