· 10 years ago · Sep 22, 2016, 10:28 AM
1<?php
2/* Labor Portál Rendszer */
3/* Adatbázis kezelő fv.-ek */
4/* Balog László 2012 (c) */
5
6class labsql
7{
8 private $mylink = false;
9
10 public static $errormsg = '';
11
12 function backup_tables($tables = '*')
13 {
14 $return = '';
15 $this->Connect();
16 if( $this->mylink === false ) return false;
17
18 //get all of the tables
19 if($tables == '*')
20 {
21 $tables = array();
22 $result = mysql_query('SHOW TABLES');
23 while($row = mysql_fetch_row($result))
24 {
25 $tables[] = $row[0];
26 }
27 }
28 else
29 {
30 $tables = is_array($tables) ? $tables : explode(',',$tables);
31 }
32
33 //cycle through
34 foreach($tables as $table)
35 {
36 $result = mysql_query('SELECT * FROM '.$table);
37 $num_fields = mysql_num_fields($result);
38
39 if( substr($table,0,2) == 'v_' )
40 {
41 $return.= 'DROP VIEW IF EXISTS '.$table.';';
42 $crtxt = mysql_query('SHOW CREATE VIEW '.$table);
43 $row2 = mysql_fetch_row($crtxt);
44 $row3 = str_replace(" DEFINER=`root`@`localhost`","",$row2[1]);
45 $return.= "\n\n".$row3.";\n\n";
46 }
47 else
48 {
49 $return.= 'DROP TABLE IF EXISTS '.$table.';';
50 $crtxt = mysql_query('SHOW CREATE TABLE '.$table);
51 $row2 = mysql_fetch_row($crtxt);
52 $return.= "\n\n".$row2[1].";\n\n";
53 }
54
55 if( substr($table,0,2) != 'v_' )
56 {
57 for ($i = 0; $i < $num_fields; $i++)
58 {
59 while($row = mysql_fetch_row($result))
60 {
61 $return.= 'INSERT INTO '.$table.' VALUES(';
62 for($j=0; $j<$num_fields; $j++)
63 {
64 $row[$j] = addslashes($row[$j]);
65 $row[$j] = preg_replace("/\n/","\\n",$row[$j]);
66 if (isset($row[$j])) { $return.= '\''.$row[$j].'\'' ; } else { $return.= '\'\''; }
67 if ($j<($num_fields-1)) { $return.= ','; }
68 }
69 $return.= ");\n";
70 }
71 }
72 }
73 $return.="\n\n\n";
74 }
75
76 return $return;
77 }
78
79 public function Ins($table, $fv, $needid = false)
80 {
81 $this->Connect();
82 if( $this->mylink === false ) return false;
83
84 $querystr = 'INSERT INTO '.$table.' (';
85
86 foreach( $fv as $idx => $item )
87 {
88 $querystr .= $idx.',';
89 }
90 $querystr = mb_substr($querystr, 0, mb_strlen($querystr)-1);
91 $querystr .= ') VALUES (';
92
93 foreach( $fv as $idx => $item )
94 {
95 if( mb_substr($item,0,1) == '%' )
96 {
97 switch( mb_substr($item,1) )
98 {
99 case 'NOW()':
100 $querystr .= 'NOW(),';
101 break;
102 default:
103 $querystr .= mb_substr($item,1).',';
104 break;
105 }
106 }
107 else
108 {
109 if( is_string($item) )
110 {
111 $querystr .= '\''.$item.'\',';
112 }
113 else
114 {
115 $querystr .= $item.',';
116 }
117 }
118 }
119 $querystr = mb_substr($querystr, 0, mb_strlen($querystr)-1);
120 $querystr .= ')';
121 $rtn = mysql_query($querystr, $this->mylink);
122
123 if( mysql_error() != '' ) self::$errormsg = mysql_error();
124
125 if( $rtn === false )
126 {
127 $this->Disconnect();
128 return $rtn;
129 }
130
131 if( $needid )
132 {
133 $int = mysql_insert_id();
134 return $int;
135 }
136
137 $this->Disconnect();
138 return $rtn;
139 }
140
141 public function InsWithID($table, $fv)
142 {
143 $this->Connect();
144 if( $this->mylink === false ) return false;
145
146 $querystr = 'INSERT INTO '.$table.' (';
147
148 foreach( $fv as $idx => $item )
149 {
150 $querystr .= $idx.',';
151 }
152 $querystr = substr($querystr, 0, strlen($querystr)-1);
153 $querystr .= ') VALUES (';
154
155 foreach( $fv as $idx => $item )
156 {
157 if( substr($item,0,1) == '%' )
158 {
159 switch( substr($item,1) )
160 {
161 case 'NOW()':
162 $querystr .= 'NOW(),';
163 break;
164 case 'UUID()':
165 $querystr .= 'MD5(UUID()),';
166 break;
167 default:
168 $querystr .= $item.',';
169 break;
170 }
171 }
172 else
173 {
174 if( is_string($item) )
175 {
176 $querystr .= '\''.$item.'\',';
177 }
178 else
179 {
180 $querystr .= $item.',';
181 }
182 }
183 }
184 $querystr = substr($querystr, 0, strlen($querystr)-1);
185 $querystr .= ')';
186 $rtn = mysql_query($querystr, $this->mylink);
187 $return['back'] = $rtn;
188 $return['id'] = mysql_insert_id($this->mylink);
189 $this->Disconnect();
190 return $return;
191 }
192
193 public function runQuery($querystr, $obj = true, $forcearray = false)
194 {
195 $this->Connect();
196 if( $this->mylink === false ) return false;
197
198 $rtn = mysql_query($querystr, $this->mylink);
199 if( $rtn !== false && $obj && $rtn !== true)
200 {
201 if( mysql_num_rows($rtn) <= 1 )
202 {
203 $rtn = mysql_fetch_object($rtn);
204 if( $rtn === false ) return false;
205 if( $forcearray ) {$rtn1[] = $rtn;$rtn = $rtn1;}
206 }
207 else
208 {
209 while ($row = mysql_fetch_object($rtn))
210 {
211 $rtn1[] = $row;
212 }
213 $rtn = $rtn1;
214 }
215 }
216 if( mysql_error() != '' ) self::$errormsg = mysql_error();
217 $this->Disconnect();
218 return $rtn;
219 }
220
221 public function Del($table, $where = false)
222 {
223 $this->Connect();
224 if( $this->mylink === false ) return false;
225
226 $querystr = 'DELETE FROM '.$table;
227 if( $where !== false )
228 {
229 $querystr .= ' WHERE ';
230 foreach( $where as $idx => $item )
231 {
232 if( is_string($item) )
233 {
234 $querystr .= $idx.'=\''.$item.'\' AND ';
235 }
236 else
237 {
238 $querystr .= $idx.'='.$item.' AND ';
239 }
240 }
241 $querystr = substr($querystr, 0, strlen($querystr)-5);
242 }
243 $rtn = mysql_query($querystr, $this->mylink);
244 if( mysql_error() != '' ) self::$errormsg = mysql_error();
245 $this->Disconnect();
246 return $rtn;
247 }
248
249 public function Upd($table, $fv, $where = false)
250 {
251 $this->Connect();
252 if( $this->mylink === false ) return false;
253 $querystr = 'UPDATE '.$table.' SET ';
254 foreach( $fv as $idx => $item )
255 {
256 if( mb_substr($item,0,1) == '%' )
257 {
258 switch( mb_substr($item,1) )
259 {
260 case 'NOW()':
261 $querystr .= '`'.$idx.'`'.'=NOW(),';
262 break;
263 default:
264 $querystr .= '`'.$idx.'`'.'='.mb_substr($item,1).',';
265 break;
266 }
267 }
268 else if ( mb_substr($item,0,1) == '#' )
269 {
270 $querystr .= '`'.$idx.'`'.'='.mb_substr($item,1).',';
271 }
272 else
273 {
274 if( is_string($item) )
275 {
276 $querystr .= '`'.$idx.'`'.'=\''.$item.'\',';
277 }
278 else
279 {
280 $querystr .= '`'.$idx.'`'.'='.$item.',';
281 }
282 }
283 }
284 $querystr = mb_substr($querystr, 0, mb_strlen($querystr)-1);
285
286 if( $where !== false )
287 {
288 $querystr .= ' WHERE ';
289 foreach( $where as $idx => $item )
290 {
291 if( is_string($item) )
292 {
293 $querystr .= '`'.$idx.'`'.'=\''.$item.'\' AND ';
294 }
295 else
296 {
297 $querystr .= '`'.$idx.'`'.'='.$item.' AND ';
298 }
299 }
300 $querystr = mb_substr($querystr, 0, mb_strlen($querystr)-5);
301 }
302 $rtn = mysql_query($querystr, $this->mylink);
303 if( mysql_error() != '' ) self::$errormsg = mysql_error();
304 $this->Disconnect();
305 return $rtn;
306 }
307
308 public function Sel($table, $where = false, $order = false, $fields = '*',$forcearray = false)
309 {
310 $this->Connect();
311 if( $this->mylink === false ) return false;
312
313 $querystr = 'SELECT '.$fields.' FROM '.$table;
314
315 if( $where !== false )
316 {
317 $querystr .= ' WHERE ';
318 foreach( $where as $idx => $item )
319 {
320 if( $idx != 'specpostfix' )
321 {
322 $sign = '=';
323 $specfv = '';
324 if( mb_substr($idx,0,5) == '@spec' )
325 {
326 $spec = mb_substr($idx,5,mb_strpos($idx,"_")-5);
327 switch($spec)
328 {
329 case 'not': $sign = '<>';
330 break;
331 case 'loe': $sign = '<=';
332 break;
333 case 'moe': $sign = '>=';
334 break;
335 case 'like': $sign = ' like '; $specfv = "LOWER";
336 break;
337 }
338 $idx = mb_substr($idx,mb_strpos($idx,"_")+1);
339 }
340 if( is_string($item) )
341 {
342 $querystr .= $idx.$sign.($specfv!='' ? $specfv.'(' : '').'\''.$item.'\''.($specfv!='' ? ')' : '').' AND ';
343 }
344 else
345 {
346 $querystr .= $idx.$sign.($specfv!='' ? $specfv.'(' : '').$item.($specfv!='' ? ')' : '').' AND ';
347 }
348 }
349 }
350 $querystr = mb_substr($querystr, 0, mb_strlen($querystr)-5);
351 }
352
353 if( $order !== false )
354 {
355 $querystr .= ' ORDER BY ';
356 foreach( $order as $idx => $item )
357 {
358 $querystr .= $idx.' '.$item.',';
359 }
360 $querystr = mb_substr($querystr, 0, mb_strlen($querystr)-1);
361 }
362 if( isset($where['specpostfix']) ) $querystr .= ' '.$where['specpostfix'];
363 $rtn = mysql_query($querystr, $this->mylink);
364 if( $rtn !== false )
365 {
366 if( mysql_num_rows($rtn) <= 1 )
367 {
368 $rtn = mysql_fetch_object($rtn);
369
370 if( $rtn === false ) return false;
371
372 if( $forcearray ) {$rtn1[] = $rtn;$rtn = $rtn1;}
373 }
374 else
375 {
376 while ($row = mysql_fetch_object($rtn))
377 {
378 $rtn1[] = $row;
379 }
380 $rtn = $rtn1;
381 }
382 }
383 if( mysql_error() != '' ) self::$errormsg = mysql_error();
384 $this->Disconnect();
385 return $rtn;
386
387 }
388
389 private function Connect()
390 {
391 $this->mylink = @mysql_connect('localhost',SQL_USER,SQL_PASSWORD,true);
392 if( $this->mylink !== false )
393 {
394 mysql_set_charset('utf8');
395 mysql_select_db(SQL_DB, $this->mylink);
396 }
397 }
398
399 private function Disconnect()
400 {
401 if( $this->mylink !== false ) mysql_close($this->mylink);
402 }
403}
404?>