Open Sencillo  2015.003
Long live the simplicity of PHP
 All Data Structures Namespaces Functions
core_sql.php
1 <?PHP
11 class mysql
12 {
13  public $DBHost;
14  public $DBName;
15  public $DBUser;
16  public $DBPass;
17  private $checksum;
18  public $con;
19 
27  public function __construct($DBHost,$DBName,$DBUser,$DBPass)
28  {
29  $this->DBHost = $DBHost;
30  $this->DBName = $DBName;
31  $this->DBUser = $DBUser;
32  $this->DBPass = $DBPass;
33 
34  if(($this->DBHost!='')&&($this->DBUser!='')&&($this->DBPass!='')&&($this->DBName!=''))
35  {
36  $this->checksum=md5($this->DBHost.$this->DBUser.$this->DBPass.$this->DBName);
37  }
38  $this->con = mysql_connect($this->DBHost, $this->DBUser, $this->DBPass);
39  if(! $this->con)
40  {
41  die("<b>core_sql: MySQL connection failed!</b> ".mysql_error());
42  }
43  mysql_select_db($this->DBName, $this->con);
44  }
45 
50  final public function query($sql)
51  {
52  mysql_query($sql);
53  }
54 
59  final public function write($sql)
60  {
61  $this->query($sql);
62  }
63 
67  final public function close()
68  {
69  mysql_close($this->con);
70  }
71 
76  final public function test()
77  {
78  if($this->checksum==md5($this->DBHost.$this->DBUser.$this->DBPass.$this->DBName))
79  {
80  if(! $this->con)
81  {
82  return mysql_error();
83  }
84  else
85  {
86  return true;
87  }
88  }
89  }
90 
95  final public function integrity($type)
96  {
97  $handle = fopen("firststart.json", "r");
98  $contents = fread($handle, filesize($filename));
99  fclose($handle);
100  $contents = json_decode($contents,false);
101  if(md5($_SERVER['SERVER_NAME'].$_SERVER['SERVER_ADDR'].$this->DBHost.$this->DBUser.$type)!=$contents->hash)
102  {
103  die('Integrity_Error: Illegal system operation!');
104  }
105  return true;
106  }
107 }
108 
118 class mysqlEdit extends mysql
119 {
120  private $construct;
121  private $key;
122  private $table;
123  private $sql;
124  private $result;
125  private $column;
126  private $setupdate;
127  private $colout;
128  private $out;
129  private $metaout;
130  private $csum;
131  private $sizeout;
132 
138  public function newColumn($name,$type="INT")
139  {
140  $this->construct .= ' , `'.$name.'` '.strtoupper($type).'';
141  }
142 
147  public function prepareTable($name)
148  {
149  $this->table = $name;
150  }
151 
156  public function uniqueKey($keyName)
157  {
158  $this->key .= ' , UNIQUE KEY `'.$this->table.'` (`'.$keyName.'`)';
159  }
160 
165  public function createTable($name)
166  {
167  $this->table = $name;
168  $this->query('CREATE TABLE IF NOT EXISTS `'.$name.'` ( `id` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(`id`)'.$this->construct.');');
169  $this->construct = null;
170  }
171 
176  public function openTable($name)
177  {
178  $this->table = $name;
179  $this->sql="SHOW COLUMNS FROM ".$this->DBName.".".$this->table;
180  $this->con=mysql_connect($this->DBHost,$this->DBUser,$this->DBPass);
181  mysql_select_db($this->DBName, $this->con);
182  $this->result=mysql_query($this->sql);
183  $this->column=null;
184  while($row=mysql_fetch_array($this->result))
185  {
186  $this->column.='`'.$row['Field'].'`,';
187  }
188  }
189 
195  public function insert($values)
196  {
197  $this->query('INSERT INTO '.$this->table.' ('.substr($this->column, 0, -1).') VALUES (null,'.$values.');');
198  }
199 
205  public function set($column,$value)
206  {
207  if(is_numeric($value))
208  {
209  $this->setupdate.='`'.$column.'`='.$value.',';
210  }
211  else
212  {
213  $this->setupdate.='`'.$column.'`="'.$value.'",';
214  }
215  }
216 
223  public function update($if,$sets=null)
224  {
225  $this->query('UPDATE '.$this->table.' SET '.substr($this->setupdate, 0, -1).$sets.' WHERE '.$if.';');
226  }
227 
234  public function delete($if)
235  {
236  if($if=="all")
237  {
238  $this->query('DELETE FROM `'.$this->table.'` WHERE `id`>0;');
239  }
240  else
241  {
242  $this->query('DELETE FROM `'.$this->table.'` WHERE '.$if.';');
243  }
244  }
245 
253  public function output($if="`id`>0",$order="`id` ASC",$limit=1000)
254  {
255  $this->sql="SELECT * FROM `".$this->table."` WHERE ".$if." ORDER BY ".$order." LIMIT ".$limit.";";
256  $this->con=mysql_connect($this->DBHost,$this->DBUser,$this->DBPass);
257  mysql_select_db($this->DBName, $this->con);
258  $this->result=mysql_query($this->sql);
259  $this->colout=explode(",",str_replace("`","",substr($this->column, 0, -1)));
260  $i=0;
261  $j=0;
262  $this->out = array('header'=>$this->colout,'line'=>array(array()));
263  $this->csum = md5($this->con);
264  while($row=mysql_fetch_array($this->result))
265  {
266  $i=0;
267  $j++;
268  foreach($this->colout as $val)
269  {
270  $this->out['line'][$j][$i++]=$row[$val];
271  }
272  }
273  return $this->out;
274  }
275 }
276 
281 {
282  protected $save;
283  protected $mysqli;
284  protected $connect;
285  private $default;
286 
321  public function dbCreateTable($array)
322  {
323  foreach($array as $key=>$val)
324  {
325  foreach($val as $key_col=>$val_col)
326  {
327  $data=null;
328 
329  foreach($val_col as $key_att=>$val_att)
330  {
331  switch(strtolower($key_att))
332  {
333  case 'type':
334  $data.=strtoupper($val_col[$key_att]);
335  break;
336  case 'null':
337  $data.=($val_col[$key_att]===false ? ' NOT NULL' : '');
338  break;
339  case 'auto_increment':
340  $data.=($val_col[$key_att]===false ? '' : ' AUTO_INCREMENT');
341  break;
342  case 'primary_key':
343  $data.=($val_col[$key_att]===false ? '' : ',PRIMARY KEY ('.$key_col.')');
344  break;
345  case 'foregin_key':
346  $data.=($val_col[$key_att]===false ? '' : ',FOREGIN KEY ('.$key_col.') REFERENCES '.$val_att);
347  break;
348  case 'unique_key':
349  case 'unique':
350  $data.=($val_col[$key_att]===false ? '' : ',UNIQUE KEY ('.$key_col.')');
351  break;
352  }
353  }
354  $this->construct .= ',`'.$key_col.'` '.$data.'';
355  }
356  $this->save .= 'CREATE TABLE IF NOT EXISTS `'.$key.'` ('.substr($this->construct,1).');';
357  }
358  }
359 
370  public function insert($array)
371  {
372  foreach($array as $key=>$val)
373  {
374  $this->save.='INSERT INTO `'.$key.'`';
375  $col=' (';
376  $values=' VALUES (';
377  foreach($val as $sub_key=>$sub_val)
378  {
379  $col.=$sub_key.',';
380  $values.=$sub_val.',';
381  }
382  $col=substr($col, 0, -1);
383  $values=substr($values, 0, -1);
384  $col=$col.')';
385  $values=$values.')';
386  $this->save.=$col.$values.';';
387  }
388  }
389 
400  public function filter($def)
401  {
402  foreach($def as $key=>$val)
403  {
404  foreach($val as $sub_key=>$sub_val)
405  {
406  $this->default.=$val.'.'.$sub_val.',';
407  }
408  }
409  $this->default=substr($this->default,0,-1);
410  }
411 
432  public function update($array)
433  {
434  $this->select($array,true);
435  }
436 
465  public function select($array,$update=false)
466  {
467  if(empty($this->default))
468  {
469  $this->default='*';
470  }
471  foreach($array as $key=>$val)
472  {
473  if($update)
474  {
475  $this->save.='UPDATE `'.$key.'` ';
476  }
477  else
478  {
479  $this->save.='SELECT '.$this->default.' FROM `'.$key.'` ';
480  }
481 
482  foreach($val as $key_col=>$val_col)
483  {
484  $data=null;
485 
486  switch(strtolower($key_col))
487  {
488  case 'if':
489  case 'where':
490  case 'condition':
491  $data_condition.=' WHERE ';
492  foreach($val_col as $key_att=>$val_att)
493  {
494  switch(strtolower($key_att))
495  {
496  case '0':
497  $data_condition.=$val_att;
498  break;
499  case 'or':
500  $data_condition.=' OR '.$val_att;
501  break;
502  default:
503  $data_condition.=' AND '.$val_att;
504  break;
505  }
506  }
507  break;
508  case 'between':
509  $data_condition.=' BETWEEN '.$key_att.' AND '.$val_att;
510  break;
511  case 'set':
512  $data_set.=$key_att.'='.$val_att.',';
513  break;
514  case 'sort':
515  $data_sort=' ORDER BY ';
516  foreach($val_col as $key_att=>$val_att)
517  {
518  switch(strtolower($key_att))
519  {
520  case 'asc':
521  $data_sort.=$val_att.' ASC';
522  break;
523  case 'desc':
524  $data_sort.=$val_att.' DESC';
525  break;
526  }
527  }
528  break;
529  case 'like':
530  $data_like=' LIKE '.$val_col;
531  break;
532  case 'start':
533  $data_limit_start=$val_col.',';
534  break;
535  case 'limit':
536  $data_limit_max=' '.$val_col;
537  break;
538  case 'fulljoin':
539  case 'fjoin':
540  case 'full':
541  $data_join.=' FULL OUTER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
542  break;
543  case 'innerjoin':
544  case 'ijoin':
545  case 'join':
546  case 'inner':
547  $data_join.=' INNER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
548  break;
549  case 'leftjoin':
550  case 'ljoin':
551  case 'left':
552  $data_join.=' INNER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
553  break;
554  case 'rightjoin':
555  case 'rjoin':
556  case 'right':
557  $data_join.=' INNER JOIN '.$key_att.' ON '.$key.'.'.$val_att[0].'='.$key_att.'.'.$val_att[1];
558  break;
559  case 'ignore_first':
563  break;
564  case 'ignore_last':
568  break;
569  }
570  }
571  $this->save.=(isset($data_set)?' SET '.substr($data_set,0,-1):'').$data_condition.$data_like.$data_sort.(isset($data_limit_max)? ' LIMIT '.$data_limit_start.$data_limit_max : '').';';
572  }
577  return $this->save;
578  }
579 
583  public function config()
584  {
585  $this->mysqli=array(
586  'dbhost'=>$this->DBHost,
587  'dbname'=>$this->DBName,
588  'dbuser'=>$this->DBUser,
589  'dbpass'=>$this->DBPass
590  );
591  }
592 
596  public function connect()
597  {
598  $this->config();
599  $this->connect = new mysqli($this->mysqli['dbhost'], $this->mysqli['dbuser'], $this->mysqli['dbpass'], $this->mysqli['dbname']);
600  if($this->connect->connect_errno)
601  {
602  $this->mysqli['dberror']['message'] = "Failed to connect to MySQL: (" . $this->connect->connect_errno . ") " . $this->connect->connect_error;
603  $this->mysqli['dberror']['code'] = 'mysqlInterface:001';
604  try
605  {
606  log::vd($this->mysqli);
607  }
608  catch(Exception $e)
609  {
610  var_dump($this->mysqli);
611  }
612 
613  die();
614  }
615  }
616 
621  public function validator()
622  {
623  if(!$this->connect->multi_query($this->save))
624  {
625  return false;
626  }
627  else
628  {
629  return true;
630  }
631  }
632 
638  public function debug()
639  {
640  return ($this->validator()? $this->connect->errno : 'Query is OK');
641  }
642 
647  public function execute()
648  {
649  if(!$this->connect->multi_query($this->save))
650  {
651  $this->mysqli['dberror']['message'] = "Multi query failed: (" . $this->connect->errno . ") " . $this->connect->error;
652  $this->mysqli['dberror']['code'] = 'mysqlInterface:002';
653  try
654  {
655  log::vd($this->mysqli);
656  }
657  catch(Exception $e)
658  {
659  var_dump($this->mysqli);
660  }
661  }
662  else
663  {
664  $this->save = null;
665  }
666 
667  $result=array();
668  do
669  {
670  if($res = $this->connect->store_result())
671  {
672  while ($row = $res->fetch_array())
673  {
674  $result[] = $row;
675  }
676  $res->free();
677  }
678  }
679  while($this->connect->more_results() && $this->connect->next_result());
680 
681  return $result;
682  }
683 }
684 $mysql = new mysqlEdit($DBHost,$DBName,$DBUser,$DBPass);
685 ?>
select($array, $update=false)
Definition: core_sql.php:465
test()
Definition: core_sql.php:76
newColumn($name, $type="INT")
Definition: core_sql.php:138
__construct($DBHost, $DBName, $DBUser, $DBPass)
Definition: core_sql.php:27
integrity($type)
Definition: core_sql.php:95
query($sql)
Definition: core_sql.php:50
prepareTable($name)
Definition: core_sql.php:147
uniqueKey($keyName)
Definition: core_sql.php:156
write($sql)
Definition: core_sql.php:59
openTable($name)
Definition: core_sql.php:176
close()
Definition: core_sql.php:67
set($column, $value)
Definition: core_sql.php:205