login  Naam:   Wachtwoord: 
Registreer je!
 Scripts:

Scripts > PHP > Database tools > SQL Classe (pgsql + mysql)

SQL Classe (pgsql + mysql)

Auteur: Wim - 14 augustus 2007 - 18:15 - Gekeurd door: Stijn - Hits: 2901 - Aantal punten: (0 stemmen)





Dit script bevat 2 classes en 1 interface.
Interface: SQL
Classes: mysql, pgsql

Beide classes zijn net hetzelfde in gebruik, dus het overschakelen van pgsql naar mysql (of omgekeerd), is zeer simpel (enkel het aanspreken van een classe veranderen; new pgsql ipv new mysql (of omgekeerd).

Om te kijken welke functies alles bevat, is het waarschijnlijk het makkelijkst om naar de interface te kijken.

mysql_real_escape_string en pg_escape_string worden automatisch toegevoegd als je de functies select, insert of delete gebruikt.

DOCUMENTATIE: PDF - ODT (OpenOffice.org writer)

Veel plezier met de classes,
Wim Mariën

UPDATES:
16/08/2007: is_string() functie verwijderd bij de select-query's. Zo konden er namelijk geen integers (voor bvb een id) meegegeven worden voor de WHERE clausule bij een query!

Code:
sql.interface.php
  1. <?php
  2. /**
  3. * @package SQL
  4. * @author Wim MariŽn (http://www.gdx.be/)
  5. * @copyright (c) 2007 Wim MariŽn - gdx.be
  6. * @license http://www.opensource.org/licenses/gpl-license.php
  7. */
  8. interface sql
  9. {
  10. public function __construct ($host=NULL, $user=NULL, $pass=NULL, $db=NULL, $port=NULL);
  11. public function connect ($host, $user, $pass, $db, $port='');
  12. public function query ($query);
  13. public function select ($table, $fields=NULL, $wFields=NULL, $wValues=NULL, $order=NULL, $limit=NULL);
  14. public function insert ($table, $name, $value);
  15. public function delete ($table, $field=NULL, $value=NULL, $limit=NULL);
  16. public function fetchobject ($query);
  17. public function fetcharray ($query);
  18. public function disconnect ();
  19. public function __destruct ();
  20. }
  21. ?>


mysql.class.php:
  1. <?php
  2. /**
  3. * @package SQL
  4. * @author Wim MariŽn (http://www.gdx.be/)
  5. * @copyright (c) 2007 Wim MariŽn - gdx.be
  6. * @license http://www.opensource.org/licenses/gpl-license.php
  7. */
  8. class mysql implements sql
  9. {
  10.  
  11. protected $sqlcon;
  12. public $nQuerys = 0;
  13. public $fetch;
  14.  
  15. public function __construct($host=NULL, $user=NULL, $pass=NULL, $db=NULL, $port=NULL)
  16. {
  17. if(isset($host) && isset($user) && isset($db))
  18. {
  19. $this->connect($host, $user, $pass, $db, $port);
  20. }
  21. }
  22.  
  23. public function connect ($host, $user, $pass, $db, $port='3306')
  24. {
  25. $this->sqlcon = mysql_connect($host.':'.$port, $user, $pass) or die('Could not connect to the database server:<br />'.mysql_error());
  26. mysql_select_db($db, $this->sqlcon) or die('Could not select the database:<br />'.mysql_error());
  27. return true;
  28. }
  29.  
  30. public function query ($query)
  31. {
  32. $this->nQuerys++;
  33. return mysql_query($query);
  34. }
  35.  
  36. public function select ($table, $fields=NULL, $wFields=NULL, $wValues=NULL, $order=NULL, $limit=NULL)
  37. {
  38. if(is_array($fields))
  39. {
  40. $fields = implode(',',$fields);
  41. }
  42.  
  43. $strQuery = 'SELECT '.$fields.' FROM '.$table;
  44.  
  45. if(is_array($wFields) && is_array($wValues) && count($wFields) == count($wValues))
  46. {
  47. $strQuery .= ' WHERE ';
  48. for($i = 0; $i <= count($value)-1; $i++)
  49. {
  50. if($i != 0)
  51. $strQuery .= ' AND ';
  52. $strQuery .= $field[$i].'=\''.mysql_real_escape_string($value[$i]).'\'';
  53. }
  54. }
  55. elseif(isset($wValues) && isset($wFields))
  56. {
  57. $strQuery .= ' WHERE '.$wFields.'=\''.mysql_real_escape_string($wValues).'\'';
  58. }
  59.  
  60. if(isset($order))
  61. {
  62. $strQuery .= ' ORDER BY '.$order;
  63. }
  64.  
  65. if(isset($limit) && is_numeric(str_replace(',', '', $limit)))
  66. {
  67. $strQuery .= ' LIMIT '.$limit;
  68. }
  69. return $this->query($strQuery);
  70. }
  71.  
  72. public function insert ($table, $name, $value)
  73. {
  74. if(is_string($table) && is_array($name) && is_array($value) && count($name) == count($value))
  75. {
  76. $name = implode(',', $name);
  77. $values = array();
  78. foreach($value as $v)
  79. {
  80. $values[] = '\''.mysql_real_escape_string($v).'\'';
  81. }
  82. $value = implode(',', $values);
  83. }
  84.  
  85. return $this->query('INSERT INTO '.$table.' ('.$name.') VALUES ('.$value.')');
  86. }
  87.  
  88. public function delete ($table, $field=NULL, $value=NULL, $limit=NULL)
  89. {
  90. $strQuery = 'DELETE FROM '.$table;
  91. if(is_string($field) && isset($value))
  92. {
  93. $strQuery .= ' WHERE '.$field.'=\''.mysql_real_escape_string($value).'\'';
  94. }
  95. elseif(is_array($field) && is_array($value) && count($field) == count($value))
  96. {
  97. $strQuery .= ' WHERE ';
  98. for($i = 0; $i <= count($value)-1; $i++)
  99. {
  100. if($i != 0)
  101. $strQuery .= ' AND ';
  102. $strQuery .= $field[$i].'=\''.mysql_real_escape_string($value[$i]).'\'';
  103. }
  104. }
  105.  
  106. if(isset($limit) && is_numeric($limit))
  107. {
  108. $strQuery .= ' LIMIT '.$limit;
  109. }
  110.  
  111. return $this->query($strQuery);
  112. }
  113.  
  114. public function fetchobject ($query)
  115. {
  116. return mysql_fetch_object($query);
  117. }
  118.  
  119. public function fetcharray ($query)
  120. {
  121. return mysql_fetch_array($query);
  122. }
  123.  
  124. public function disconnect ()
  125. {
  126. @mysql_close($this->sqlcon);
  127. }
  128.  
  129. public function __destruct ()
  130. {
  131. $this->disconnect();
  132. }
  133. }
  134. ?>


pgsql.class.php:
  1. <?php
  2. /**
  3. * @package SQL
  4. * @author Wim MariŽn (http://www.gdx.be/)
  5. * @copyright (c) 2007 Wim MariŽn - gdx.be
  6. * @license http://www.opensource.org/licenses/gpl-license.php
  7. */
  8. class pgsql implements sql
  9. {
  10.  
  11. protected $sqlcon;
  12. public $nQuerys = 0;
  13. public $fetch;
  14.  
  15. public function __construct($host=NULL, $user=NULL, $pass=NULL, $db=NULL, $port=NULL)
  16. {
  17. if(isset($host) && isset($user) && isset($db))
  18. {
  19. $this->connect($host, $user, $pass, $db, $port);
  20. }
  21. }
  22.  
  23. public function connect ($host, $user, $pass, $db, $port='5432')
  24. {
  25. $pass = (($pass == NULL) ? '' : ' password='.$pass);
  26. $this->sqlcon = pg_connect('host='.$host.' user='.$user.' port='.$port.' dbname='.$db.' '.$pass) or die(pg_last_error());
  27. return true;
  28. }
  29.  
  30. public function query ($query)
  31. {
  32. $this->nQuerys++;
  33. return pg_query($this->sqlcon, $query);
  34. }
  35.  
  36. public function select ($table, $fields=NULL, $wFields=NULL, $wValues=NULL, $order=NULL, $limit=NULL)
  37. {
  38. if(is_array($fields))
  39. {
  40. $fields = implode(',',$fields);
  41. }
  42.  
  43. $strQuery = 'SELECT '.$fields.' FROM '.$table;
  44.  
  45. if(is_array($wFields) && is_array($wValues) && count($wFields) == count($wValues))
  46. {
  47. $strQuery .= ' WHERE ';
  48. for($i = 0; $i <= count($value)-1; $i++)
  49. {
  50. if($i != 0)
  51. $strQuery .= ' AND ';
  52. $strQuery .= $field[$i].'=\''.pg_escape_string($value[$i]).'\'';
  53. }
  54. }
  55. elseif(isset($wValues) && isset($wFields))
  56. {
  57. $strQuery .= ' WHERE '.$wFields.'=\''.pg_escape_string($wValues).'\'';
  58. }
  59.  
  60. if(isset($order))
  61. {
  62. $strQuery .= ' ORDER BY '.$order;
  63. }
  64.  
  65. if(isset($limit) && is_numeric(str_replace(',', '', $limit)))
  66. {
  67. $strQuery .= ' LIMIT '.$limit;
  68. }
  69. return $this->query($strQuery);
  70. }
  71.  
  72. public function insert ($table, $name, $value)
  73. {
  74. if(is_string($table) && is_array($name) && is_array($value) && count($name) == count($value))
  75. {
  76. $name = implode(',', $name);
  77. $values = array();
  78. foreach($value as $v)
  79. {
  80. $values[] = '\''.pg_escape_string($v).'\'';
  81. }
  82. $value = implode(',', $values);
  83. }
  84.  
  85. return $this->query('INSERT INTO '.$table.' ('.$name.') VALUES ('.$value.')');
  86. }
  87.  
  88. public function delete ($table, $field=NULL, $value=NULL, $limit=NULL)
  89. {
  90. $strQuery = 'DELETE FROM '.$table;
  91. if(is_string($field) && isset($value))
  92. {
  93. $strQuery .= ' WHERE '.$field.'=\''.pg_escape_string($value).'\'';
  94. }
  95. elseif(is_array($field) && is_array($value) && count($field) == count($value))
  96. {
  97. $strQuery .= ' WHERE ';
  98. for($i = 0; $i <= count($value)-1; $i++)
  99. {
  100. if($i != 0)
  101. $strQuery .= ' AND ';
  102. $strQuery .= $field[$i].'=\''.pg_escape_string($value[$i]).'\'';
  103. }
  104. }
  105.  
  106. if(isset($limit) && is_numeric($limit))
  107. {
  108. $strQuery .= ' LIMIT '.$limit;
  109. }
  110.  
  111. return $this->query($strQuery);
  112. }
  113.  
  114. public function fetchobject ($query)
  115. {
  116. return pg_fetch_object($query);
  117. }
  118.  
  119. public function fetcharray ($query)
  120. {
  121. return pg_fetch_array($query);
  122. }
  123.  
  124. public function disconnect ()
  125. {
  126. @pg_close($this->sqlcon);
  127. }
  128.  
  129. public function __destruct ()
  130. {
  131. $this->disconnect();
  132. }
  133. }
  134. ?>
Download code! Download code (.txt)

 Stemmen
Niet ingelogd.

 Reacties
Post een reactie
Lees de reacties (6)
© 2002-2023 Sitemasters.be - Regels - Laadtijd: 0.062s