Posted By

jonniespratley on 08/21/09


Tagged

mysql php


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

umang_nine


MySQL Help Service


 / Published in: PHP
 

  1. <?php
  2.  
  3. class MySQLHelpService
  4. {
  5. private $mysqli;
  6.  
  7. public function __construct( $host, $username, $password )
  8. {
  9. //temporary for the bs warning signs on live
  10. // Report simple running errors
  11. error_reporting ( E_ERROR | E_USER_ERROR | E_PARSE );
  12.  
  13. $this->mysqli = new mysqli ( $host, $username, $password );
  14. /* check connection */
  15. if ( mysqli_connect_errno () )
  16. {
  17. trigger_error ( 'Database connection failure: Username/Password was incorrect.', E_USER_ERROR );
  18. exit ();
  19. }
  20. else
  21. {
  22. return $this->mysqli;
  23. }
  24. }
  25.  
  26. public function getHelpTree()
  27. {
  28. $helpTree [] = array (
  29. 'label' => 'MySQL Help', 'children' => $this->_getAllHelp ()
  30. );
  31.  
  32. return json_encode ( $helpTree );
  33. }
  34.  
  35. public function _getAllHelp()
  36. {
  37. $sql = "SELECT
  38. help_category.name as category,
  39. help_topic.name as label,
  40. help_topic.description as description,
  41. help_topic.example as example
  42. FROM mysql.help_relation
  43. INNER JOIN mysql.help_topic
  44. ON help_relation.help_topic_id = help_topic.help_topic_id
  45. INNER JOIN mysql.help_category
  46. ON help_category.help_category_id = help_topic.help_category_id
  47. ORDER BY category ASC";
  48.  
  49. return $this->_queryToARRAY ( $sql );
  50. }
  51.  
  52. public function _getDataDefinition()
  53. {
  54. $sql = "SELECT
  55. help_keyword.name as keyword,
  56. help_category.name as category,
  57. help_topic.name as topic,
  58. help_topic.description as description,
  59. help_topic.example as example
  60. FROM mysql.help_relation
  61. INNER JOIN mysql.help_keyword
  62. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  63. INNER JOIN mysql.help_topic
  64. ON help_relation.help_topic_id = help_topic.help_topic_id
  65. INNER JOIN mysql.help_category
  66. ON help_category.help_category_id = help_topic.help_category_id
  67. WHERE help_category.name = 'Data Definition'
  68. ORDER BY topic ASC";
  69. return $this->_queryToArray ( $sql );
  70. }
  71.  
  72. public function _getDataManipulation()
  73. {
  74. $sql = "SELECT
  75. help_keyword.name as keyword,
  76. help_category.name as category,
  77. help_topic.name as topic,
  78. help_topic.description as description,
  79. help_topic.example as example
  80. FROM mysql.help_relation
  81. INNER JOIN mysql.help_keyword
  82. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  83. INNER JOIN mysql.help_topic
  84. ON help_relation.help_topic_id = help_topic.help_topic_id
  85. INNER JOIN mysql.help_category
  86. ON help_category.help_category_id = help_topic.help_category_id
  87. WHERE help_category.name = 'Data Manipulation'
  88. ORDER BY topic ASC";
  89. return $this->_queryToArray ( $sql );
  90. }
  91.  
  92. public function _getTableMaintenance()
  93. {
  94. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  95. FROM mysql.help_relation
  96. INNER JOIN mysql.help_keyword
  97. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  98. INNER JOIN mysql.help_topic
  99. ON help_relation.help_topic_id = help_topic.help_topic_id
  100. INNER JOIN mysql.help_category
  101. ON help_category.help_category_id = help_topic.help_category_id
  102. WHERE help_category.name = 'Table Maintenance'";
  103. return $this->_queryToArray ( $sql );
  104. }
  105.  
  106. public function _getStoredRoutines()
  107. {
  108. $sql = "";
  109. return $this->_queryToArray ( $sql );
  110. }
  111.  
  112. public function _getAdministration()
  113. {
  114. $sql = "SELECT
  115. help_keyword.name as keyword,
  116. help_category.name as category,
  117. help_topic.name as topic,
  118. help_topic.description as description,
  119. help_topic.example as example
  120. FROM mysql.help_relation
  121. INNER JOIN mysql.help_keyword
  122. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  123. INNER JOIN mysql.help_topic
  124. ON help_relation.help_topic_id = help_topic.help_topic_id
  125. INNER JOIN mysql.help_category
  126. ON help_category.help_category_id = help_topic.help_category_id
  127. WHERE help_category.name = 'Administration'
  128. ORDER BY topic ASC";
  129. return $this->_queryToArray ( $sql );
  130. }
  131.  
  132. public function _getDataTypes()
  133. {
  134. $sql = "SELECT
  135. help_keyword.name as keyword,
  136. help_category.name as category,
  137. help_topic.name as topic,
  138. help_topic.description as description,
  139. help_topic.example as example
  140. FROM mysql.help_relation
  141. INNER JOIN mysql.help_keyword
  142. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  143. INNER JOIN mysql.help_topic
  144. ON help_relation.help_topic_id = help_topic.help_topic_id
  145. INNER JOIN mysql.help_category
  146. ON help_category.help_category_id = help_topic.help_category_id
  147. WHERE help_category.name = 'Data Types'
  148. ORDER BY topic ASC";
  149. return $this->_queryToArray ( $sql );
  150. }
  151.  
  152. public function _getAccountManagement()
  153. {
  154. $sql = "SELECT
  155. help_keyword.name as keyword,
  156. help_category.name as category,
  157. help_topic.name as topic,
  158. help_topic.description as description,
  159. help_topic.example as example
  160. FROM mysql.help_relation
  161. INNER JOIN mysql.help_keyword
  162. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  163. INNER JOIN mysql.help_topic
  164. ON help_relation.help_topic_id = help_topic.help_topic_id
  165. INNER JOIN mysql.help_category
  166. ON help_category.help_category_id = help_topic.help_category_id
  167. WHERE help_category.name = 'Account Management'
  168. ORDER BY topic ASC";
  169. return $this->_queryToArray ( $sql );
  170. }
  171.  
  172. public function _getStringFunctions()
  173. {
  174. $sql = "SELECT
  175. help_keyword.name as keyword,
  176. help_category.name as category,
  177. help_topic.name as topic,
  178. help_topic.description as description,
  179. help_topic.example as example
  180. FROM mysql.help_relation
  181. INNER JOIN mysql.help_keyword
  182. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  183. INNER JOIN mysql.help_topic
  184. ON help_relation.help_topic_id = help_topic.help_topic_id
  185. INNER JOIN mysql.help_category
  186. ON help_category.help_category_id = help_topic.help_category_id
  187. WHERE help_category.name = 'String Functions'
  188. ORDER BY topic ASC";
  189. return $this->_queryToArray ( $sql );
  190. }
  191.  
  192. public function _getControlFlow()
  193. {
  194. $sql = " SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  195. FROM mysql.help_relation
  196. INNER JOIN mysql.help_keyword
  197. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  198. INNER JOIN mysql.help_topic
  199. ON help_relation.help_topic_id = help_topic.help_topic_id
  200. INNER JOIN mysql.help_category
  201. ON help_category.help_category_id = help_topic.help_category_id
  202. WHERE help_category.name = 'Control flow functions'";
  203. return $this->_queryToArray ( $sql );
  204. }
  205.  
  206. public function _gettTransactions()
  207. {
  208. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  209. FROM mysql.help_relation
  210. INNER JOIN mysql.help_keyword
  211. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  212. INNER JOIN mysql.help_topic
  213. ON help_relation.help_topic_id = help_topic.help_topic_id
  214. INNER JOIN mysql.help_category
  215. ON help_category.help_category_id = help_topic.help_category_id
  216. WHERE help_category.name = 'Transactions'";
  217. return $this->_queryToArray ( $sql );
  218. }
  219.  
  220. public function _getFunctions()
  221. {
  222. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  223. FROM mysql.help_relation
  224. INNER JOIN mysql.help_keyword
  225. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  226. INNER JOIN mysql.help_topic
  227. ON help_relation.help_topic_id = help_topic.help_topic_id
  228. INNER JOIN mysql.help_category
  229. ON help_category.help_category_id = help_topic.help_category_id
  230. WHERE help_category.name = 'Functions'";
  231. return $this->_queryToArray ( $sql );
  232. }
  233.  
  234. public function _getWKT()
  235. {
  236. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  237. FROM mysql.help_relation
  238. INNER JOIN mysql.help_keyword
  239. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  240. INNER JOIN mysql.help_topic
  241. ON help_relation.help_topic_id = help_topic.help_topic_id
  242. INNER JOIN mysql.help_category
  243. ON help_category.help_category_id = help_topic.help_category_id
  244. WHERE help_category.name = 'WKB'";
  245. return $this->_queryToArray ( $sql );
  246. }
  247.  
  248. public function _getWKB()
  249. {
  250. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  251. FROM mysql.help_relation
  252. INNER JOIN mysql.help_keyword
  253. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  254. INNER JOIN mysql.help_topic
  255. ON help_relation.help_topic_id = help_topic.help_topic_id
  256. INNER JOIN mysql.help_category
  257. ON help_category.help_category_id = help_topic.help_category_id
  258. WHERE help_category.name = 'WKB'";
  259. return $this->_queryToArray ( $sql );
  260. }
  261.  
  262. public function _getNumericFunctions()
  263. {
  264. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  265. FROM mysql.help_relation
  266. INNER JOIN mysql.help_keyword
  267. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  268. INNER JOIN mysql.help_topic
  269. ON help_relation.help_topic_id = help_topic.help_topic_id
  270. INNER JOIN mysql.help_category
  271. ON help_category.help_category_id = help_topic.help_category_id
  272. WHERE help_category.name = 'Numeric Functions'";
  273. return $this->_queryToArray ( $sql );
  274. }
  275.  
  276. public function _getLanguageStructure()
  277. {
  278. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  279. FROM mysql.help_relation
  280. INNER JOIN mysql.help_keyword
  281. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  282. INNER JOIN mysql.help_topic
  283. ON help_relation.help_topic_id = help_topic.help_topic_id
  284. INNER JOIN mysql.help_category
  285. ON help_category.help_category_id = help_topic.help_category_id
  286. WHERE help_category.name = 'Language Structure'";
  287. return $this->_queryToArray ( $sql );
  288. }
  289.  
  290. public function _getComparison()
  291. {
  292. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  293. FROM mysql.help_relation
  294. INNER JOIN mysql.help_keyword
  295. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  296. INNER JOIN mysql.help_topic
  297. ON help_relation.help_topic_id = help_topic.help_topic_id
  298. INNER JOIN mysql.help_category
  299. ON help_category.help_category_id = help_topic.help_category_id
  300. WHERE help_category.name = 'Comparison operators'";
  301. return $this->_queryToArray ( $sql );
  302. }
  303.  
  304. public function _getDateTime()
  305. {
  306. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  307. FROM mysql.help_relation
  308. INNER JOIN mysql.help_keyword
  309. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  310. INNER JOIN mysql.help_topic
  311. ON help_relation.help_topic_id = help_topic.help_topic_id
  312. INNER JOIN mysql.help_category
  313. ON help_category.help_category_id = help_topic.help_category_id
  314. WHERE help_category.name = 'Date and Time Functions'";
  315. return $this->_queryToArray ( $sql );
  316. }
  317.  
  318. public function _getLogicalOpperators()
  319. {
  320. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  321. FROM mysql.help_relation
  322. INNER JOIN mysql.help_keyword
  323. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  324. INNER JOIN mysql.help_topic
  325. ON help_relation.help_topic_id = help_topic.help_topic_id
  326. INNER JOIN mysql.help_category
  327. ON help_category.help_category_id = help_topic.help_category_id
  328. WHERE help_category.name = 'Logical operators'";
  329. return $this->_queryToArray ( $sql );
  330. }
  331.  
  332. public function _getEncryptionFunctions()
  333. {
  334. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  335. FROM mysql.help_relation
  336. INNER JOIN mysql.help_keyword
  337. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  338. INNER JOIN mysql.help_topic
  339. ON help_relation.help_topic_id = help_topic.help_topic_id
  340. INNER JOIN mysql.help_category
  341. ON help_category.help_category_id = help_topic.help_category_id
  342. WHERE help_category.name = 'Encryption Functions'";
  343. return $this->_queryToArray ( $sql );
  344. }
  345.  
  346. public function _getGeographicFeatures()
  347. {
  348. $sql = "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  349. FROM mysql.help_relation
  350. INNER JOIN mysql.help_keyword
  351. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  352. INNER JOIN mysql.help_topic
  353. ON help_relation.help_topic_id = help_topic.help_topic_id
  354. INNER JOIN mysql.help_category
  355. ON help_category.help_category_id = help_topic.help_category_id
  356. WHERE help_category.name = 'Geographic Features'";
  357. return $this->_queryToArray ( $sql );
  358. }
  359.  
  360. public function _getTriggers()
  361. {
  362. $sql - "SELECT help_keyword.name, help_category.name, help_topic.name, help_topic.description, help_topic.example
  363. FROM mysql.help_relation
  364. INNER JOIN mysql.help_keyword
  365. ON help_relation.help_keyword_id = help_keyword.help_keyword_id
  366. INNER JOIN mysql.help_topic
  367. ON help_relation.help_topic_id = help_topic.help_topic_id
  368. INNER JOIN mysql.help_category
  369. ON help_category.help_category_id = help_topic.help_category_id
  370. WHERE help_category.name = 'Triggers'";
  371. return $this->_queryToArray ( $sql );
  372. }
  373.  
  374. /* ********************************************************************
  375. * ********************************************************************
  376. *
  377. * 8. RESULT HANDLERS
  378. *
  379. * ********************************************************************
  380. * ********************************************************************/
  381.  
  382. /**
  383. * I execute a query and return the results as json.
  384. *
  385. * @param [string] $sql the query to be executed
  386. * @return [json] the result in json
  387. */
  388. private function _queryToJSON( $sql )
  389. {
  390. $result = mysqli_query ( $this->mysqli, $sql );
  391.  
  392. while ( $row = mysqli_fetch_assoc ( $result ) )
  393. {
  394. $array [] = $row;
  395. }
  396. return json_encode ( $array );
  397. }
  398.  
  399. /**
  400. * I execute a query and return the result as an array.
  401. *
  402. * @param [string] $sql the query to be executed
  403. * @return [array] the result array
  404. */
  405. private function _queryToARRAY( $sql )
  406. {
  407. $query = mysqli_query ( $this->mysqli, $sql );
  408. $array = array ();
  409.  
  410. while ( $row = mysqli_fetch_assoc ( $query ) )
  411. {
  412. $array [] = $row;
  413. }
  414.  
  415. return $array;
  416. }
  417.  
  418. }
  419.  
  420. //testing
  421. //$help = new MySQLHelpService( 'localhost', 'root', 'fred' );
  422. //print_r( $help->getHelpTree() );
  423.  
  424.  
  425. ?>

Report this snippet  

You need to login to post a comment.