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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
| public function advancedSearch($title, $category, $year, $mediatype, $owner, $imdbgrade) {
try {
$query = "SELECT DISTINCT v.vcd_id, v.title, v.category_id, v.year, u.media_type_id, i.rating
FROM $this->TABLE_vcd v ";
$query .= "LEFT OUTER JOIN $this->TABLE_vcdtouser u ON v.vcd_id = u.vcd_id ";
$query .= "LEFT OUTER JOIN $this->TABLE_vcdtosources s ON s.vcd_id = v.vcd_id ";
$query .= "LEFT OUTER JOIN $this->TABLE_imdb i ON i.imdb = s.external_id ";
$query .= "LEFT OUTER JOIN $this->TABLE_comments c ON c.vcd_id = v.vcd_id ";
$bCon = false;
if (!is_null($title)) {
$title = "%".$title."%";
$query .= "WHERE v.title LIKE ".$this->db->qstr($title);
$bCon = true;
}
if (is_numeric($owner)) {
if ($bCon) {
$query .= " AND u.user_id = ".$owner." ";
} else {
$query .= " WHERE u.user_id = ".$owner." ";
$bCon = true;
}
}
if (is_numeric($mediatype)) {
if ($bCon) {
$query .= " AND u.media_type_id = ".$mediatype." ";
} else {
$query .= " WHERE u.media_type_id = ".$mediatype." ";
$bCon = true;
}
}
if (is_numeric($category)) {
if ($bCon) {
$query .= " AND v.category_id = ".$category."";
} else {
$query .= " WHERE v.category_id = ".$category."";
$bCon = true;
}
}
$commentColumn = "c.comment";
if ($this->isOracle()) {
$commentColumn = "c.comments";
}
// Check for all public comments and users private if user is logged in
if (!is_null($title) && (!is_numeric($owner) && !is_numeric($mediatype) && !is_numeric($category) && !is_numeric($year))) {
if (VCDUtils::isLoggedIn()) {
$user_id = VCDUtils::getUserID();
$query .= " OR (($commentColumn LIKE {$this->db->qstr($title)} AND isPrivate = 0) OR ($commentColumn LIKE {$this->db->qstr($title)} AND c.user_id = {$user_id}))";
} else {
$query .= " OR ($commentColumn LIKE {$this->db->qstr($title)} AND isPrivate = 0) ";
}
}
$query .= " ORDER BY v.title";
// Transform the queries with LOWER() if postgres ..
if ($this->isPostgres()) {
$arrFields = array('v.title', 'c.comment', $this->db->qstr($title));
// create the replacement array ..
$arrLower = array();
for ($i=0;$i<sizeof($arrFields);$i++) { $arrLower[$i] = "lower(".$arrFields[$i].")"; }
$query = str_replace($arrFields, $arrLower, $query);
}
$results = array();
$rs = $this->db->Execute($query);
if ($rs) {
foreach ($rs as $row) {
if (is_numeric($imdbgrade)) {
if ($row[5] >= $imdbgrade) {
$item = array('id' => $row[0], 'title' => $row[1], 'cat_id' => $row[2],
'year' => $row[3], 'media_id' => $row[4], 'rating' => $row[5]);
// Update the titles from lowercase to UcFirst if Postgres
if ($this->isPostgres()) {
$item['title'] = ucwords($item['title']);
}
array_push($results, $item);
}
} else {
$item = array('id' => $row[0], 'title' => $row[1], 'cat_id' => $row[2],
'year' => $row[3], 'media_id' => $row[4], 'rating' => $row[5]);
// Update the titles from lowercase to UcFirst if Postgres
if ($this->isPostgres()) {
$item['title'] = ucwords($item['title']);
}
array_push($results, $item);
}
}
$rs->Close();
}
return $results;
} catch (Exception $ex) {
throw new VCDSqlException($ex->getMessage(), $ex->getCode());
}
} |
[Tableaux] Dédoublonner un tableau pour une seule valeur
Répondre avec citation
Envoyé par luan220
Partager