wpdatatables / source / class.sql.php

class.sql.php in wpDataTables – WordPress Data Table, Dynamic Tables & Table Charts Plugin 6.5.1.7, at source/class.sql.php

310 lines 7.5 KB

Raw Download Zip

1	<?php
2
3	defined('ABSPATH') or die("Cannot access pages directly.");
4
5	/**
6	* MySQL abstract layer for the WPDataTables module
7	*
8	* @author cjbug@ya.ru
9	* @since 10.10.2012
10	*
11	* */
12	class PDTSql
13	{
14
15	private $dbhost;
16	private $dbname;
17	private $dbuser;
18	private $dbpass;
19	private $dbport;
20	private $link;
21	private $sqllog;
22	private $query;
23	private $result;
24	private $error;
25	private $key;
26
27	/**
28	* Constructor
29	* @param string $sqlhost
30	* @param string $sqldbname
31	* @param string $sqluser
32	* @param string $sqlpassword
33	*/
34	function __construct($sqlhost, $sqldbname, $sqluser, $sqlpassword, $sqlport)
35	{
36	$this->dbhost = (((string)$sqlhost)) ? $sqlhost : '';
37	$this->dbname = (((string)$sqldbname)) ? $sqldbname : '';
38	$this->dbuser = (((string)$sqluser)) ? $sqluser : '';
39	$this->dbpass = (((string)$sqlpassword)) ? $sqlpassword : '';
40	$this->dbport = (((int)$sqlport)) ? $sqlport : '3306';
41	$this->sqlConnect();
42	}
43
44	/**
45	* Initializes the connection to the database
46	* @return boolean
47	*/
48	function sqlConnect()
49	{
50	$this->link = @mysqli_connect($this->dbhost, $this->dbuser, $this->dbpass, $this->dbname, $this->dbport);
51	if (!$this->link) {
52	throw new Exception(mysqli_connect_error());
53	} else {
54	$result = mysqli_select_db($this->link, $this->dbname);
55	mysqli_query($this->link, 'SET character_set_client="utf8",character_set_connection="utf8",character_set_results="utf8"; ');
56	if (!$result) {
57	throw new Exception(mysqli_error($this->link));
58	}
59	}
60	return true;
61	}
62
63	/**
64	* Determines if the connection is established
65	*/
66	public function isConnected()
67	{
68	return !empty($this->link);
69	}
70
71	/**
72	* Close the DB connection
73	* @return boolean
74	*/
75	function sqlClose()
76	{
77	mysqli_close();
78	return true;
79	}
80
81	/**
82	* Set the group key
83	* @param string $key
84	*/
85	function setGroupKey($key)
86	{
87	$this->key = $key;
88	}
89
90	/**
91	* Clear the group key
92	*/
93	function dropGroupKey()
94	{
95	$this->key = '';
96	}
97
98	/**
99	* Do a query without expected result (insert, update, delete)
100	* @param $query
101	* @param parameters - a single array, or all values
102	* separated by comma
103	* @return boolean
104	*/
105	function doQuery()
106	{
107	if ($result = $this->prepare(func_get_args())) {
108	return true;
109	} else {
110	return false;
111	}
112	}
113
114	/**
115	* Get a single field value from query result
116	* @param $query
117	* @param parameters - a single array, or all values
118	* separated by comma
119	* @return boolean Get
120	*/
121	function getField()
122	{
123	if ($result = $this->prepare(func_get_args())) {
124	$row = mysqli_fetch_row($result);
125	return $row[0];
126	} else {
127	return false;
128	}
129	}
130
131	/**
132	* Get a single row from query result
133	* @param $query
134	* @param parameters - a single array, or all values
135	* separated by comma
136	* @return boolean
137	*/
138	function getRow()
139	{
140	if ($result = $this->prepare(func_get_args())) {
141	$row = mysqli_fetch_assoc($result);
142	@mysqli_free_result($result);
143	return $row;
144	} else {
145	return false;
146	}
147	}
148
149	/**
150	* Get all results of a query as an indexed array
151	*
152	* @return array\|bool
153	*/
154	function getArray()
155	{
156	$tmp = null;
157	if ($result = $this->prepare(func_get_args())) {
158	while ($row = mysqli_fetch_array($result))
159	$tmp[] = $row;
160	@mysqli_free_result($result);
161	return $tmp;
162	} else {
163	return false;
164	}
165	}
166
167	/**
168	* Get all results of a query as an assoc array
169	* @param $query
170	* @param parameters - a single array, or all values
171	* separated by comma
172	* @return boolean
173	*/
174	function getAssoc()
175	{
176	if ($result = $this->prepare(func_get_args())) {
177	while ($row = mysqli_fetch_assoc($result))
178	$tmp[] = $row;
179	@mysqli_free_result($result);
180	return $tmp;
181	} else {
182	return false;
183	}
184	}
185
186
187	/**
188	* Returns the last MySQL error
189	*/
190	function getLastError()
191	{
192	return mysqli_error($this->link);
193	}
194
195	/**
196	* Get the results of a query as an assoc array
197	* grouped by a provided key
198	* @return bool
199	*/
200	function getAssocGroups()
201	{
202	$properties = func_get_args();
203	$key = $properties[0];
204	array_shift($properties);
205	if ($result = $this->prepare($properties)) {
206	while ($row = mysqli_fetch_assoc($result))
207	$tmp[($row[$key])][] = $row;
208	@mysqli_free_result($result);
209	return $tmp;
210	} else {
211	return false;
212	}
213	}
214
215	/**
216	* Get the results of a query sorted by a provided key
217	* @return bool
218	*/
219	function getAssocByKey()
220	{
221	$properties = func_get_args();
222	$key = $properties[0];
223	array_shift($properties);
224	if ($result = $this->prepare($properties)) {
225	while ($row = mysqli_fetch_assoc($result)) {
226	$tmp[($row[$key])] = $row;
227	}
228	@mysqli_free_result($result);
229	return $tmp;
230	} else {
231	return false;
232	}
233	}
234
235	/**
236	* Get the results of a query as pairs (id/val)
237	* @param $query
238	* @param parameters - a single array, or all values
239	* separated by comma
240	* @return boolean
241	*/
242	function getPairs()
243	{
244	if ($result = $this->prepare(func_get_args())) {
245	while (@$row = mysqli_fetch_row($result))
246	$tmp[strval($row[0])] = $row[1];
247	@mysqli_free_result($result);
248	return $tmp;
249	} else {
250	return false;
251	}
252	}
253
254
255	/**
256	* Prepares the query and the parameters passed
257	*/
258	function prepare($properties)
259	{
260	$q = $properties[0];
261	unset($properties[0]);
262	// $q = preg_replace('/\?/', 'x?x', $q);
263	if (count($properties) > 1) {
264	foreach ($properties as $p) {
265	$p = '\'' . mysqli_real_escape_string($this->link, $p) . '\'';
266	$q = preg_replace('/x\?x/', $p, $q, 1);
267	}
268	} elseif ((count($properties) == 1) && (is_array($properties[1]))) {
269	foreach ($properties[1] as $p) {
270	$p = '\'' . mysqli_real_escape_string($this->link, $p) . '\'';
271	$q = preg_replace('/x\?x/', $p, $q, 1);
272	}
273	}
274	$this->query = $q;
275	$this->error = '';
276
277	$result = mysqli_query($this->link, $this->query);
278
279	if (mysqli_error($this->link)) {
280	return false;
281	}
282
283	while (mysqli_more_results($this->link)) {
284	mysqli_next_result($this->link);
285	mysqli_store_result($this->link);
286	}
287
288	if (@mysqli_num_rows($result)) {
289	$row = mysqli_fetch_assoc($result);
290	if (isset($row['error'])) {
291	$this->error = $row['error'];
292	return false;
293	} else {
294	mysqli_data_seek($result, 0);
295	return $result;
296	}
297	} else {
298	return false;
299	}
300	}
301
302	function getLastInsertId()
303	{
304	return mysqli_insert_id($this->link);
305	}
306
307	}
308
309	?>
310