/
SQL_List.php
255 lines (209 loc) · 8.64 KB
/
SQL_List.php
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
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
<?php
namespace CB;
class SQL_List_Ex extends \Exception
{
const E_UNKNOWN = 0;
const E_EMPTY = 1;
const E_BLANK = 2;
const E_BAD_SQL = 3;
const E_INVALID_DB_TYPE = 4;
const E_ILLEGAL = 5;
/*
* Parameters
* code (INT) One of the error code constants E_*
* [message] (string) append a custom error message
* Returns : none
* Example : throw new CB\SQL_List_Ex(CB\SQL_List_Ex::E_NO_USER);
*/
public function __construct($code = self::E_UNKNOWN, $message = NULL)
{
switch ($code)
{
case self::E_BLANK:
$msg = "Blank Value";
break;
case self::E_EMPTY:
$msg = "Empty Value";
break;
case self::E_BAD_SQL:
$msg = "Bad SQL Statement";
break;
case self::E_ILLEGAL:
$msg = "Illegal Operation";
break;
case self::E_UNKNOWN: default:
$msg = "An unknown message has occurred";
break;
}
if (isset($message)) $msg .= ".\n$message."; # Add extra message to default message line
parent::__construct($msg, $code);
}
}
/*
* CB\SQL_List class
* Author: Anjanesh Lekshminarayanan
* Modified: 01/09/2007
* Description: A generic list class based on a database-table fieldnames with filter criteria
*/
class SQL_List # throws SQL_List_Ex
{
const SEARCH_ALL = 0;
const SEARCH_ANY = 1;
const DB_MySQL = 0;
const DB_PDO = 1;
# $_GET keys passed from the form
protected $GET = ['PageNo' => 'pg', 'Rows' => 'norpp', 'SortBy' => 'SortBy', 'AscDesc' => 'AscDesc', 'Keywords' => 'Keywords'];
protected $dbType; # DB_MySQL | DB_PDO
protected $SELECT; # (string) { SELECT ... } SQL statement
protected $FROM; # (string) { FROM ... } SQL statement
public $WHERE; # (string) WHERE condition for SELECT statement
protected $TextSearchFields = array(); # (array) Basic Search - Search mysql fields for textual search
public $Pg; # Object of CB\Pagination
public $data; # Object of CB\MySQL
protected $tables = [];
# (array) Used to filter out common words in textual search
protected static $CommonWords = ['and', 'or', 'the', 'is', 'a', 'this'];
/*
* Parameters
* SELECT : SELECT statement
* FROM : FROM statement
*/
public function __construct($SELECT, $FROM, $Type = self::DB_MySQL)
{
$this->SELECT = $SELECT;
$this->FROM = $FROM;
$this->WHERE = "1"; # Since all other condition clauses would be starting with AND, 1 makes it IF 1 AND
$this->dbType = $Type;
// Pagination Settings
$this->Pg = new Pagination();
$this->Pg->PageNo = isset($_GET[$this->GET['PageNo']]) ? $_GET[$this->GET['PageNo']] >= 1 ? $_GET[$this->GET['PageNo']] : 1 : 1;
$this->Pg->Rows = isset($_GET[$this->GET['Rows']]) ? $_GET[$this->GET['Rows']] >= 10 ? $_GET[$this->GET['Rows']] : 10 : 10;
$SortBy = isset($_GET[$this->GET['SortBy']]) ? $_GET[$this->GET['SortBy']] : 0;
$AscDesc = isset($_GET[$this->GET['AscDesc']]) ? $_GET[$this->GET['AscDesc']] : Pagination::DSC;
#$this->Pg->addSortPriority($SortBy, $AscDesc); # First ORDER BY : ORDER BY Alumni.`Joined`
#$this->Pg->addSortPriority($SortBy == 0 ? 1 : 0); # Second ORDER BY : ORDER BY Alumni.`Joined` if the above addSortPriority is 0, otherwise ORDER BY `FullName`
}
// $obj->add_WHERE("AND Alumni.`Batch` > 1998 AND Alumni.`Batch` <= 2001")
public function add_WHERE($expr) { $this->WHERE .= " $expr "; }
/*
* Set column(s) for textual search
* $obj->addTextSearchFields("Alumni.`FirstName`", "Alumni.`MiddleName`", "Alumni.`LastName`", "Alumni.`About`");
*/
public function addTextSearchFields()
{
$columns = func_get_args();
foreach ($columns as $column)
{
$this->TextSearchFields[] = $column;
}
}
// Apply Basic Search functionality to TextSearchFields
public function filter_basicSearch($type = self::SEARCH_ALL)
{
if ($type != self::SEARCH_ALL && $type != self::SEARCH_ANY)
$type = self::SEARCH_ALL;
if (isset($_GET[$this->GET['Keywords']]))
$this->WHERE .= self::SearchKeywords($_GET[$this->GET['Keywords']], $type);
}
// For ones not impementing $data as CB\MySQL
public function getSQL($num)
{
$SQL = "SELECT ".$this->SELECT." FROM ".$this->FROM." WHERE ".$this->WHERE; # echo $SQL."\n";
$this->Pg->set($num);
$SQL .= $this->Pg->getSQL_ORDER_BY_LIMIT();
return $SQL;
}
// This should be called after $WHERE is built, and before calling next()
public function fetch($dblink = NULL)
{
$SQL = "SELECT ".$this->SELECT." FROM ".$this->FROM." WHERE ".$this->WHERE; # echo $SQL."\n";
switch($this->dbType)
{
case self::DB_MySQL:
$this->data = new MySQL($SQL, FALSE);
break;
case self::DB_PDO: # Will not work
$this->data = new CB\PDO($this->SELECT, $this->FROM, $this->WHERE, $dblink);
break;
default: throw new SQL_List_Ex(SQL_List_Ex::E_INVALID_DB_TYPE);
}
$this->Pg->set($this->data->num);
$SQL .= $this->Pg->getSQL_ORDER_BY_LIMIT();
$this->data->sql = $SQL; # Reset SQL with added ORDER BY & LIMIT
# echo $SQL."\n";
}
public function next()
{
return $this->data->next();
}
public function prev()
{
return $this->data->prev();
}
# From tablename select columns
public function select($tablename, $columns) # Dropped
{
}
/*
* from table
* @param $tablename
* @param $alias
*/
public function from($tablename, $alias = NULL) # Dropped
{
# Get rid off existing leading & trailing `
$tablename = trim($tablename, " \t\n\r\0`");
if (isset($alias)) $alias = trim($alias, " \t\n\r\0`");
$this->tables[] = array('`'.$tablename.'`', isset($alias) ? '`'.$alias.'`' : '`'.$tablename.'`');
}
protected function SearchKeywords($Keywords, $searchType = self::SEARCH_ALL)
{
$Keywords = addslashes($Keywords);
$WHERE = " AND (";
// FULL MATCH: Any field containing the Exact string of keywords
foreach ($this->TextSearchFields as $TextSearchField)
{
$WHERE .= "$TextSearchField LIKE '%$Keywords%' OR ";
}
$Words = explode(" ", $Keywords); // Identify words - separated by whitespace(s) - TODO - catch words within "..." as one word
$Words = array_unique($Words); // Remove redundant words
// Remove Common Words
$tempWords = [];
foreach ($Words as $Word)
{
if (array_search($Word, self::$CommonWords) === FALSE)
$tempWords[] = $Word;
}
$Words = $tempWords; unset($tempWords);
if (count($Words) > 0) // Atleast one word IS NOT a common word - atleast one word is searchable
{
// Start : Phase II
$WHERE .= " (";
// Loop through valid keywords array
foreach ($Words as $Word)
{
$WHERE .= "(";
// Loop through TextSearchFields array
foreach ($this->TextSearchFields as $TextSearchField)
{
$WHERE .= "$TextSearchField LIKE '%$Word%' OR ";
}
$WHERE = substr($WHERE, 0, -4); // Remove trailing OR
switch ($searchType)
{
case self::SEARCH_ALL : $WHERE .= ") AND "; break;
case self::SEARCH_ANY : $WHERE .= ") OR "; break;
}
}
$WHERE = substr($WHERE, 0, -5); // Remove trailing AND / OR
$WHERE .= ")"; // End of OR
}
else // All words are common words - rejected - Example : searh terms are : this is a
{
$WHERE = substr($WHERE, 0, -4); // Remove trailing OR
}
$WHERE .= ")"; // End of AND
return $WHERE;
}
}
?>