Posted By

jonniespratley on 02/26/09


Tagged

sql php textmate generator json


Versions (?)

Who likes this?

6 people have marked this snippet as a favorite

umang_nine
vali29
thastyle
prashantvictory
breign
tux-world


JSON To SQL Generator (JQUERY)


 / Published in: PHP
 

  1. <?php
  2. /**
  3.  * I am a JSON Query builder
  4.  *
  5.  * Here is a inline example of how to create your json when sending it.
  6.  *
  7.  * <code>
  8.  * $flexJSON = '[{"table":"TABLENAME","objectName":"FIELDNAME","objectValue":"FIELDVALUE","database":"DATABASE","tableKey":"PRI KEY","objectKey":"RECORD KEY"}]';
  9.  * $j = new JSONQuery( 'host', 'user', 'pass' );
  10.  * $j->dump( $j->buildQuery( $flexJSON, 'INSERT' ), 'INSERT JSON ' );
  11.  * $j->dump( $j->buildQuery( $flexJSON, 'UPDATE' ), 'UPDATE JSON' );
  12.  * $j->dump( $j->buildQuery( $flexJSON, 'DELETE' ), 'DELETE JSON' );
  13.  * $j->setJSONString( $flexJSON );
  14.  * $j->dump( $j->getJSONString(), 'JSON STRING' );
  15.  * </code>
  16.  *
  17.  * @author Jonnie Spratley
  18.  * @copyright 2009 http://jonniespratley.com
  19.  * @version 0.7
  20.  *
  21.  */
  22. class JSONQuery
  23. {
  24. private $mysqli;
  25. private $jsonArray;
  26. private $jsonString;
  27. private $jsonQuery;
  28.  
  29. /**
  30. * Database Link
  31. *
  32. * @param [database] $link
  33. */
  34. public function __construct( $link )
  35. {
  36. $this->mysqli = $link;
  37. }
  38.  
  39. /**
  40. * I build a INSERT/UPDATE/DELETE from a json string.
  41. * I can either auto update the database, or return the sql.
  42. * <code>
  43. * [{"table":"TABLENAME","objectName":"FIELDNAME","objectValue":"FIELDVALUE","database":"DATABASE","tableKey":"PRI KEY","objectKey":"RECORD KEY"}]
  44. * </code>
  45. *
  46. * @param [json] $json JSON string like [{"table":"TABLENAME","objectName":"FIELDNAME","objectValue":"FIELDVALUE","database":"DATABASE","tableKey":"PRI KEY","objectKey":"RECORD KEY"}]
  47. * @param [string] $type INSERT/UPDATE/DELETE
  48. * @param [boolean] $autoInsert = false Auto update database
  49. * @return unknown
  50. */
  51. public function buildQuery( $json, $type, $autoInsert = false )
  52. {
  53. $jsonString = str_replace ( "\\", "", $json );
  54. $jsonArray = json_decode ( $jsonString, true );
  55. $query = '';
  56. $queryArray = array ();
  57.  
  58. switch ( $type )
  59. {
  60. case 'INSERT' :
  61. //do insert statement
  62. foreach ( $jsonArray as $record )
  63. {
  64. $query = sprintf ( 'INSERT INTO %s.%s SET %s = "%s"', $record [ 'database' ], $record [ 'table' ], $record [ 'objectName' ], $record [ 'objectValue' ] );
  65. //echo $query;
  66. $queryArray [] = $query;
  67. }
  68.  
  69. break;
  70.  
  71. case 'UPDATE' :
  72.  
  73. foreach ( $jsonArray as $record )
  74. {
  75. $query = sprintf ( 'UPDATE %s.%s SET %s = "%s" WHERE %s = "%s"', $record [ 'database' ], $record [ 'table' ], $record [ 'objectName' ], $record [ 'objectValue' ], $record [ 'tableKey' ], $record [ 'objectKey' ] );
  76. //echo $query;
  77. $queryArray [] = $query;
  78. }
  79.  
  80. break;
  81.  
  82. case 'DELETE' :
  83.  
  84. foreach ( $jsonArray as $record )
  85. {
  86. $query = sprintf ( 'DELETE FROM %s.%s WHERE %s = "%s"', $record [ 'database' ], $record [ 'table' ], $record [ 'tableKey' ], $record [ 'objectKey' ] );
  87. //echo $query;
  88. $queryArray [] = $query;
  89. }
  90.  
  91. break;
  92.  
  93. default :
  94. echo 'Specify a type of statement';
  95. exit ();
  96. break;
  97. }
  98. if ( $autoInsert )
  99. {
  100. return $this->_batchQuery ( $queryArray );
  101. }
  102. return $queryArray;
  103. }
  104.  
  105. /**
  106. * I take an array of sql statements and execute them in order to the database.
  107. *
  108. * @param [array] $sql array of sql statements
  109. */
  110. private function _batchQuery( $sql )
  111. {
  112. foreach ( $sql as $q )
  113. {
  114. $result = $this->mysqli->query ( $q );
  115. if ( $result )
  116. {
  117. printf ( "%d row affected.", $this->mysqli->affected_rows );
  118. }
  119. }
  120. }
  121.  
  122. private function _filterTable( $s )
  123. {
  124. $tablename = stripos ( $s, 'table' );
  125.  
  126. return $tablename;
  127. }
  128.  
  129. /**
  130. * Escape SQL
  131. *
  132. * @param [string] $str
  133. * @return escaped string
  134. */
  135. private function _sqlQuote( $str )
  136. {
  137. if ( ! isset ( $str ) )
  138. return ( "NULL" );
  139.  
  140. $func = function_exists ( "mysqli_escape_string" ) ? "mysqli_escape_string" : "addslashes";
  141. return ( "'" . $func ( $str ) . "'" );
  142. }
  143.  
  144. /**
  145. * @return associative array
  146. */
  147. public function getJSONArray()
  148. {
  149. return $this->jsonArray;
  150. }
  151.  
  152. /**
  153. * @return json string
  154. */
  155. public function getJSONString()
  156. {
  157. return $this->jsonString;
  158. }
  159.  
  160. /**
  161. * @return sql query
  162. */
  163. public function getQuery()
  164. {
  165. return $this->query;
  166. }
  167.  
  168. /**
  169. * @param associative $jsonArray
  170. */
  171. public function setJSONArray( $json )
  172. {
  173. $jsonArray = json_decode ( $json, true );
  174.  
  175. $this->jsonArray = $jsonArray;
  176. }
  177.  
  178. /**
  179. * @param json $jsonString
  180. */
  181. public function setJSONString( $jsonString )
  182. {
  183. $this->jsonString = $jsonString;
  184. }
  185.  
  186. /**
  187. * @param string $query
  188. */
  189. public function setJSONQuery( $query )
  190. {
  191. $this->jsonQuery = $query;
  192. }
  193.  
  194. /**
  195. * I dump vars
  196. *
  197. * @param [var] $var
  198. * @param [string] $name
  199. */
  200. public function dump( $var, $name = 'Variable Dump' )
  201. {
  202. echo "<b>$name</b><br/>";
  203. echo '<pre>';
  204. print_r ( $var );
  205. echo '</pre>';
  206. }
  207.  
  208. }
  209. ?>

Report this snippet  

You need to login to post a comment.