Full REST JSON MySQL Management Class


/ Published in: PHP
Save to your folder(s)

I am currently still working on this, and will make changes.


Copy this code and paste it in your HTML
  1. <?php
  2. require_once 'FileSystemService.php';
  3. require_once 'JSONQuery.php';
  4. require_once 'MySQLDump.php';
  5. /**
  6.  * I am a REST MySQL Database Manager Service the structure for the url is as follows:
  7.  *
  8.  * @eample
  9.  * <code>
  10.  * url string:
  11.  *
  12.  * http://localhost/service.php?
  13.  * h=HOST
  14.  * &u=USERNAME
  15.  * &p=PASSWORD
  16.  * &m=MODE
  17.  * &d=DATABASE
  18.  * &t=TABLE
  19.  * &q=QUERY
  20.  * </code>
  21.  *
  22.  * <code>
  23.  * result:
  24.  *
  25.  * [
  26.  * {
  27.  * "Database":"information_schema"
  28.  * },
  29.  * {
  30.  * "Database":"mysql"
  31.  * },
  32.  * {
  33.  * "Database":"test"
  34.  * }
  35.  * ]
  36.  * </code>
  37.  *
  38.  * TABLE OF CONTENTS
  39.  *
  40.  * 1. MYSQL SHOW METHODS
  41.  * 2. PRIVATE DATABASE/TABLE METHODS
  42.  * 3. QUERY BUILDER/CREATE/UPDATE/DELETE METHODS
  43.  * 4. ANALYZE/CHECK/OPTIMIZE/REPAIR METHODS
  44.  * 5. BACKUP/IMPORT/EXPORT METHODS
  45.  * 6. SERVER VARIABLES
  46.  * 7. DATA METHODS
  47.  * 8. UTILITY METHODS
  48.  * 9. CLASS TESTING
  49.  *
  50.  *
  51.  * @name MySQLService
  52.  * @author Jonnie Spratley
  53.  * @version 1.0
  54.  * @license http://opensource.org/licenses/gpl-license.php GNU Public License
  55.  */
  56. class MySQLService
  57. {
  58. /**
  59. * I am the database link
  60. *
  61. * @var private
  62. */
  63. private $mysqli;
  64. private $query_pieces = array ();
  65. private $fileSvc;
  66. private $jquery;
  67.  
  68. /**
  69. * I hold alot of access to monitor and manager mysql tables
  70. *
  71. * @param [string] $host Host name
  72. * @param [string] $username User name
  73. * @param [string] $password User password
  74. *
  75. * @return MySQLService
  76. */
  77. public function MySQLService( $host, $username, $password )
  78. {
  79. //temporary for the bs warning signs on live
  80. // Report simple running errors
  81. error_reporting ( E_ERROR | E_USER_ERROR | E_PARSE );
  82.  
  83. //$this->mysqli = new mysqli ( $host, $username, $password );
  84.  
  85.  
  86. /* create a connection object which is not connected */
  87. $this->mysqli = mysqli_init ();
  88.  
  89. /* set connection options */
  90. $this->mysqli->options ( MYSQLI_CLIENT_COMPRESS );
  91.  
  92. $this->mysqli->options ( MYSQLI_OPT_CONNECT_TIMEOUT, 5 );
  93.  
  94. /* connect to server */
  95. $this->mysqli->real_connect ( $host, $username, $password );
  96.  
  97. /* check connection */
  98. {
  99. trigger_error ( 'Database connection failure: Username/Password was incorrect.', E_USER_ERROR );
  100. exit ();
  101. }
  102.  
  103. $this->fileSvc = new FileSystemService ( );
  104. $this->jquery = new JSONQuery ( $this->mysqli );
  105. }
  106.  
  107. /* ********************************************************************
  108.  * ********************************************************************
  109.  *
  110.  * 1. MYSQL SHOW METHODS
  111.  *
  112.  * Below is all the methods for getting tables, columns, databases,
  113.  * indexs, statusus from the database.
  114.  *
  115.  * SHOW DATABASES;
  116.  * SHOW TABLES FROM test;
  117.  * SHOW TABLE STATUS LIKE 'users';
  118.  * SHOW INDEX FROM 'contacts';
  119.  * SHOW INDEX FROM contacts;
  120.  * SHOW COLUMNS FROM contacts;
  121.  * SHOW STATUS FROM test;
  122.  * SHOW TABLE STATUS FROM test;
  123.  *
  124.  * ********************************************************************
  125.  * *********************************************************************/
  126.  
  127. /**
  128. * I show all of the columns for a specified table.
  129. *
  130. * @param [string] $whatDatabase the database
  131. * @param [string] $whatTable the table
  132. * @return [json] Json of all the columns
  133. */
  134. public function getTableColumns( $whatDatabase, $whatTable )
  135. {
  136. $sql = "SHOW COLUMNS FROM $whatDatabase.$whatTable";
  137.  
  138. return $this->_queryToJSON ( $sql );
  139. }
  140.  
  141. /**
  142. * I get all tables in database
  143. *
  144. * @param [string] $database the database
  145. * @return [json]
  146. */
  147. public function showTableStatus( $whatDatabase )
  148. {
  149. $sql = mysqli_query ( $this->mysqli, "SHOW TABLE STATUS FROM $whatDatabase" );
  150. $tables = array ();
  151.  
  152. while ( $row = mysqli_fetch_assoc ( $sql ) )
  153. {
  154. $tables [] = $row;
  155. }
  156. return json_encode ( $tables );
  157. }
  158.  
  159. /**
  160. * I get the primary key for the table.
  161. *
  162. * @param [string] $database the database
  163. * @param [string] $table the table
  164. * @return [json]
  165. */
  166. public function getTableIndex( $whatDatabase, $whatTable )
  167. {
  168. $sql = "SHOW INDEX FROM $whatDatabase.$whatTable";
  169.  
  170. return $this->_queryToARRAY ( $sql );
  171. }
  172.  
  173. /**
  174. * I get all databases, tables, columns, and fields in the database.
  175. * Formatted specially for Flex's Tree control.
  176. *
  177. * @return [json]
  178. */
  179. public function getDatabasesAndTables()
  180. {
  181. //Database query
  182. $databaseSQL = mysqli_query ( $this->mysqli, "SHOW DATABASES" );
  183.  
  184. //New database array
  185. $databases = array ();
  186.  
  187. //Loop the query
  188. while ( $database = mysqli_fetch_assoc ( $databaseSQL ) )
  189. {
  190. //Create a new array of tables for each database
  191. $tables = array ();
  192.  
  193. foreach ( $database as $key => $value )
  194. {
  195. //Set the table array to get the tbles from the database
  196. $tables = $this->_getTables ( $value );
  197. }
  198.  
  199. //Add the tables to the database array
  200. $databases [] = array (
  201. "aDatabase" => $value, "aType" => "database", "aData" => $key, "aIcon" => "databaseIcon", "aTables" => $tables
  202. );
  203. }
  204. sort ( $databases );
  205.  
  206. //Encode in json
  207. return json_encode ( $databases );
  208. }
  209.  
  210. /**
  211. * I get all the databases
  212. *
  213. * @return [json]
  214. */
  215. public function getDatabases()
  216. {
  217. //Database query
  218. $databaseSQL = mysqli_query ( $this->mysqli, "SHOW DATABASES" );
  219.  
  220. //New database array
  221. $databases = array ();
  222.  
  223. //Loop the query
  224. while ( $database = mysqli_fetch_assoc ( $databaseSQL ) )
  225. {
  226. //Create a new array of tables for each database
  227. $tables = array ();
  228. $status = array ();
  229. $size = array ();
  230. foreach ( $database as $key => $value )
  231. {
  232. //Set the table array to get the tbles from the database
  233. $tables = $this->_getTables ( $value );
  234. $status = $this->_getTableStatus ( $value );
  235. $size = $this->_getDatabaseSize ( $value );
  236. }
  237.  
  238. //Add the tables to the database array
  239. $databases [] = array (
  240. "aDatabase" => $value, "aType" => "database", "aData" => $key, "aIcon" => "databaseIcon", "aTables" => $tables, "aStatus" => $status, "aSize" => $size
  241. );
  242. }
  243. sort ( $databases );
  244.  
  245. //Encode in json
  246. return json_encode ( $databases );
  247. //return $databases;
  248. }
  249.  
  250. /**
  251. * I get all tables in the database
  252. *
  253. * @param [string] $whatDatabase the name of the database
  254. * @return [json]
  255. */
  256. public function getTables( $whatDatabase )
  257. {
  258. //table query
  259. $tableSQL = mysqli_query ( $this->mysqli, "SHOW TABLES FROM $whatDatabase" );
  260.  
  261. //create a new array of tables
  262. $tables = array ();
  263.  
  264. //loop all the results
  265. while ( $table = mysqli_fetch_assoc ( $tableSQL ) )
  266. {
  267. $fields = array ();
  268. $indexes = array ();
  269. $statuss = array ();
  270. //for each table in the result make an array
  271. foreach ( $table as $key => $value )
  272. {
  273. //now descibe each table
  274. $fields = $this->_describeTable ( $whatDatabase, $value );
  275. //now get the indexes
  276. $indexes = $this->_getTableIndexes ( $whatDatabase, $value );
  277. //now get the status for that table
  278. $statuss = $this->_getSingleTableStatus ( $whatDatabase, $value );
  279. }
  280. //build a tree
  281. $tables [] = array (
  282. "tableName" => $value, "aFields" => $fields, "aIndexes" => $indexes, 'aStatus' => $statuss
  283. );
  284. }
  285.  
  286. return json_encode ( $tables );
  287. }
  288.  
  289. /**
  290. * I describe a table
  291. *
  292. * @param [string] $whatDatabase the database
  293. * @param [string] $whatTable the table
  294. * @return [json]
  295. */
  296. public function describeTable( $whatDatabase, $whatTable )
  297. {
  298. $sql = mysqli_query ( $this->mysqli, "DESCRIBE $whatDatabase.$whatTable" );
  299. $tables = array ();
  300.  
  301. while ( $row = mysqli_fetch_assoc ( $sql ) )
  302. {
  303. $tables [] = array (
  304. 'Field' => $row [ 'Field' ], 'Type' => $row [ 'Type' ], 'Null' => $row [ 'Null' ], 'Default' => $row [ 'Default' ], 'Extra' => $row [ 'Extra' ], 'Key' => $row [ 'Key' ]
  305. );
  306. }
  307. sort ( $tables );
  308.  
  309. return json_encode ( $tables );
  310. }
  311.  
  312. /**
  313. * I get user information
  314. *
  315. * @param [string] $username the users name
  316. * @return [json]
  317. */
  318. public function getUserInfo( $username )
  319. {
  320. $sql = "SELECT * FROM mysql.user_info WHERE User = '$username'";
  321. $result = mysqli_query ( $this->mysqli, $sql );
  322. //return $this->_queryToJSON($sql);
  323. $array = array ();
  324.  
  325. while ( $row = mysqli_fetch_assoc ( $result ) )
  326. {
  327. $array [] = array (
  328. "User" => $row [ 'User' ],
  329. /*"Fullname" => $row[ 'Fullname' ],*/
  330. "Description" => $row [ 'Description' ], "Icon" => base64_encode ( $row [ 'Icon' ] ), "Email" => $row [ 'Email' ], "Info" => $row [ 'Contact_information' ]
  331. );
  332. }
  333. return json_encode ( $array );
  334. }
  335.  
  336. /**
  337. * I get all open tables for a database
  338. *
  339. * @param [string] $whatDatabase the database name
  340. * @return [json]
  341. */
  342. public function getOpenTables( $whatDatabase )
  343. {
  344. $sql = "SHOW OPEN TABLES FROM $whatDatabase";
  345. return $this->_queryToJSON ( $sql );
  346.  
  347. }
  348.  
  349. /**
  350. * I get a count of rows from the table
  351. *
  352. * @param [string] $whatDatabase the database
  353. * @param [string] $whatTable the table
  354. * @return [json]
  355. */
  356. public function getTableRows( $whatDatabase, $whatTable )
  357. {
  358. return $this->_queryToJSON ( "SELECT COUNT(*) FROM $whatDatabase.$whatTable" );
  359. }
  360.  
  361. /* ********************************************************************
  362.  * ********************************************************************
  363.  *
  364.  * 2. PRIVATE DATABASE/TABLE METHODS
  365.  *
  366.  * Below is all the private methods that build up the database
  367.  * and table tree with information about each item.
  368.  *
  369.  * Example:
  370.  *
  371.  * DatabaseName/
  372.  * TableName/
  373.  * FieldName/
  374.  *
  375.  * ********************************************************************
  376.  * ********************************************************************
  377.  
  378. /**
  379. * I get all tables for a database
  380. *
  381. * @param [string] $whatDatabase the database
  382. * @return [array]
  383. */
  384. public function _getTables( $whatDatabase )
  385. {
  386. //table query
  387. $tableSQL = mysqli_query ( $this->mysqli, "SHOW TABLES FROM $whatDatabase" );
  388.  
  389. //create a new array of tables
  390. $tables = array ();
  391.  
  392. //loop all the results
  393. while ( $table = mysqli_fetch_assoc ( $tableSQL ) )
  394. {
  395. $fields = array ();
  396. $statuss = array ();
  397. $indexes = array ();
  398.  
  399. //for each table in the result make an array
  400. foreach ( $table as $t_key => $t_value )
  401. {
  402. //get the tables fields for each table
  403. $fields = $this->_describeTable ( $whatDatabase, $t_value );
  404.  
  405. //now get the primary key for each table
  406. $primaryKey = $this->_getTableKey ( $whatDatabase, $t_value );
  407.  
  408. //now get the status for that table
  409. $statuss = $this->_getSingleTableStatus ( $whatDatabase, $t_value );
  410.  
  411. //now get the indexes for each table
  412. $indexes = $this->_getTableIndexes ( $whatDatabase, $t_value );
  413.  
  414. }
  415.  
  416. $tables [] = array (
  417. "aTable" => $t_value, "aKey" => $primaryKey, "aType" => "table", "aIcon" => "tableIcon", "aData" => $t_key, "aFields" => $fields, "aStatus" => $statuss, "aIndexes" => $indexes
  418. );
  419. }
  420. //sort ( $tables );
  421.  
  422.  
  423. return $tables;
  424. }
  425.  
  426. /**
  427. * I describe a table for the getDatabasesAndTables() method
  428. *
  429. * @param [string] $database the database
  430. * @param [string] $table the table
  431. * @return [array]
  432. */
  433. public function _describeTable( $whatDatabase, $whatTable )
  434. {
  435. return $this->_queryToARRAY ( "SHOW FIELDS FROM $whatDatabase.$whatTable" );
  436. }
  437.  
  438. public function _getTableIndexes( $whatDatabase, $whatTable )
  439. {
  440. return $this->_queryToARRAY ( "SHOW INDEX FROM $whatDatabase.$whatTable" );
  441. }
  442.  
  443. /**
  444. * I get the table status for a table only when called from getDatabases()
  445. *
  446. * @param [string] $whatDatabase
  447. * @return [array]
  448. */
  449. public function _getTableStatus( $whatDatabase )
  450. {
  451. return $this->_queryToARRAY ( "SHOW TABLE STATUS FROM $whatDatabase" );
  452. }
  453.  
  454. public function _getSingleTableStatus( $whatDatabase, $whatTable )
  455. {
  456. return $this->_queryToARRAY ( "SHOW TABLE STATUS FROM $whatDatabase LIKE '$whatTable'" );
  457. }
  458.  
  459. /**
  460. * I get tables and fields
  461. *
  462. * @param [string] $whatDatabase the database
  463. * @return [array]
  464. */
  465. private function _getTableAndFields( $whatDatabase )
  466. {
  467. $tableInfoSql = mysqli_query ( $this->mysqli, "SHOW TABLE STATUS FROM $whatDatabase" );
  468.  
  469. $tables = array (); //array of all table info
  470. $fields = array (); //array of all field info
  471.  
  472.  
  473. //get the table name from the result
  474. while ( $tableInfo = mysqli_fetch_row ( $tableInfoSql ) )
  475. {
  476. $tables [] = $tableInfo [ 0 ];
  477.  
  478. //loop threw every table inside of the tables array
  479. foreach ( $tables as $table )
  480. {
  481. //for each table, get the fields info for that table
  482. $fields = $this->_showFieldInfo ( $whatDatabase, $table );
  483. }
  484. $tableInfoAndFields [] = array (
  485. 'aTable' => $table, 'aType' => 'table', 'aFields' => $fields
  486. );
  487. }
  488. $databaseInfoAndTables [] = array (
  489. 'aDatabase' => $whatDatabase, 'aType' => 'database', 'aTables' => $tableInfoAndFields
  490. );
  491.  
  492. //return $fields;
  493. //return $databaseInfoAndTables;
  494. return json_encode ( $databaseInfoAndTables );
  495. }
  496.  
  497. private function _getDatabasesTablesAndFields()
  498. {
  499. $databaseInfoSql = mysqli_query ( $this->mysqli, "SHOW DATABASES" );
  500.  
  501. $databases = array (); //array of all databases info
  502. $tables = array (); //array of all table info
  503. $fields = array (); //array of all field info
  504.  
  505.  
  506. //get the table name from the result
  507. while ( $databaseInfo = mysqli_fetch_row ( $databaseInfoSql ) )
  508. {
  509.  
  510. $databases [] = $databaseInfo [ 0 ];
  511. //loop threw every table inside of the tables array
  512. foreach ( $databases as $database )
  513. {
  514. $tables = $this->_showTableInfo ( $database );
  515. //for each table, get the fields info for that table
  516. foreach ( $tables as $table )
  517. {
  518. $fields = $this->_showFieldInfo ( $database, $table );
  519. }
  520. }
  521.  
  522. $tableInfoAndFields [] = array (
  523. 'aTable' => $table, 'aFields' => $fields
  524. );
  525. }
  526.  
  527. //return $fields;
  528. //return $tables;
  529. return json_encode ( $tableInfoAndFields );
  530. }
  531.  
  532. /**
  533. * I get information about the table.
  534. *
  535. * @param [string] $whatDatabase the database
  536. * @return [array]
  537. */
  538. private function _showTableInfo( $whatDatabase )
  539. {
  540. $tableInfoSql = "SHOW TABLE STATUS FROM $whatDatabase";
  541. $result = mysqli_query ( $this->mysqli, $tableInfoSql );
  542. $tableInfo = array ();
  543.  
  544. while ( $tables = mysqli_fetch_assoc ( $result ) )
  545. {
  546. $tableInfo [] = $tables;
  547. }
  548. return $tableInfo;
  549. }
  550.  
  551. /**
  552. * I get the fields for a table
  553. *
  554. * @param [string] $whatDatabase the database
  555. * @param [string] $whatTable the table
  556. * @return [array]
  557. */
  558. private function _showFieldInfo( $whatDatabase, $whatTable )
  559. {
  560. $fieldInfoSql = "SHOW FIELDS FROM $whatDatabase.$whatTable";
  561. $fieldInfo = array ();
  562.  
  563. $result = $this->mysqli->query ( $fieldInfoSql );
  564.  
  565. while ( $fields = mysqli_fetch_assoc ( $result ) )
  566. {
  567. $fieldInfo [] = $fields;
  568. }
  569. return $fieldInfo;
  570. }
  571.  
  572. /**
  573. * I get the key for the table.
  574. *
  575. * @param [string] $whatDatabase the database
  576. * @param [string] $whatTable the table
  577. * @return [string]
  578. */
  579. public function _getTableKey( $whatDatabase, $whatTable )
  580. {
  581. $indexInfoSql = "SHOW INDEX FROM $whatDatabase.$whatTable";
  582. $index = array ();
  583.  
  584. $result = $this->mysqli->query ( $indexInfoSql );
  585.  
  586. while ( $indexes = mysqli_fetch_assoc ( $result ) )
  587. {
  588. if ( $indexes [ 'Key_name' ] == 'PRIMARY' )
  589. {
  590. $index = $indexes [ 'Column_name' ];
  591. }
  592. }
  593. return $index;
  594. }
  595.  
  596. /**
  597. * I get the size of all databases in the database
  598. *
  599. * @return [json]
  600. */
  601. public function getDatabaseSpace()
  602. {
  603. $sql = 'SELECT table_schema "Database",
  604. sum( data_length + index_length ) / 1024 / 1024 "TotalSize",
  605. sum( data_length ) / 1024 / 1024 "DataSize",
  606. sum( index_length ) / 1024 / 1024 "IndexSize",
  607. sum( data_free ) / 1024 / 1024 "FreeSize"
  608. FROM information_schema.TABLES
  609. GROUP BY table_schema';
  610.  
  611. return $this->_queryToJSON ( $sql );
  612. }
  613.  
  614. /**
  615. * I get the database size for all tables
  616. *
  617. * @param [string] $whatDatabase the database name
  618. */
  619. public function _getDatabaseSize( $whatDatabase )
  620. {
  621. $statusSQL = mysqli_query ( $this->mysqli, "SHOW TABLE STATUS FROM $whatDatabase" );
  622. $sizeArray = array ();
  623.  
  624. $totalSize = 0;
  625. $dataSize = 0;
  626. $indexSize = 0;
  627.  
  628. //loop all the results
  629. while ( $size = mysqli_fetch_assoc ( $statusSQL ) )
  630. {
  631. $dataSize += $size [ 'Data_length' ];
  632. $indexSize += $size [ 'Index_length' ];
  633. }
  634. $totalSize = $dataSize + $indexSize;
  635. $sizeArray [] = array (
  636. 'totalSize' => $totalSize, 'dataSize' => $dataSize, 'indexSize' => $indexSize
  637. );
  638.  
  639. return $sizeArray;
  640. }
  641.  
  642. /* ********************************************************************
  643.  * ********************************************************************
  644.  *
  645.  * 3. QUERY BUILDER/CREATE/UPDATE/DELETE METHODS
  646.  *
  647.  * Below is all the methods for building insert queries, creating
  648.  * databases, creating tables, creating users, removing data,
  649.  * inserting data, and updating data.
  650.  * Also there is methods for altering databases, and tables.
  651.  *
  652.  * ********************************************************************
  653.  * *********************************************************************/
  654.  
  655. /**
  656. * I create a database
  657. *
  658. * @param [string] $whatDatabase the name of the database
  659. * @return [string] the result outcome
  660. */
  661. public function createDatabase( $whatDatabase )
  662. {
  663. //CREATE DATABASE `tutorial_library`
  664. //DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
  665. $sql = "CREATE SCHEMA IF NOT EXISTS $whatDatabase
  666. CHARACTER SET utf8";
  667. $result = mysqli_query ( $this->mysqli, $sql );
  668.  
  669. if ( ! $result )
  670. {
  671. return 'There was an error creating the database.';
  672. }
  673. return 'Database created!';
  674. }
  675.  
  676. /**
  677. * I create a table
  678. *
  679. * @param [string] $whatDatabase the database
  680. * @param [string] $whatTable the name of the new table
  681. * @return [string] the result
  682. */
  683. public function createTable( $whatDatabase, $whatTable )
  684. {
  685. /**
  686. * CREATE TABLE `books` ( id int ) DEFAULT CHARACTER SET latin1;
  687. *
  688. */
  689. $sql = "CREATE TABLE $whatDatabase.$whatTable ( id int ) DEFAULT CHARACTER SET latin1";
  690.  
  691. $result = mysqli_query ( $this->mysqli, $sql );
  692.  
  693. if ( ! $result )
  694. {
  695. return false;
  696. }
  697. return true;
  698. }
  699.  
  700. /**
  701. * I alter a database table
  702. *
  703. * @param [string] $whatDatabase the database
  704. * @param [string] $whatTable the name of the new table
  705. * @param [string] $whatQuery a query array of what to change
  706. * @return [string] the result
  707. */
  708. public function alterTable( $whatDatabase, $whatTable, $whatQuery )
  709. {
  710. /**
  711. * ALTER TABLE `cars` ADD `engine` varchar(225) DEFAULT NULL ;
  712. */
  713. $sql = "ALTER TABLE $whatDatabase.$whatTable ADD $whatQuery";
  714.  
  715. $result = mysqli_query ( $this->mysqli, $sql );
  716.  
  717. if ( ! $result )
  718. {
  719. return false;
  720. }
  721. return true;
  722. }
  723.  
  724. /**
  725. * I remove a table
  726. *
  727. * @param [string] $whatDatabase the database
  728. * @param [string] $whatTable the table
  729. * @return [string] the result
  730. */
  731. public function removeTable( $whatDatabase, $whatTable )
  732. {
  733. /**
  734. * DROP TABLE `library`;
  735. */
  736. $sql = "DROP TABLE $whatDatabase.$whatTable";
  737.  
  738. $result = mysqli_query ( $this->mysqli, $sql );
  739.  
  740. if ( ! $result )
  741. {
  742. return false;
  743. }
  744. return true;
  745. }
  746.  
  747. /**
  748. * I rename a table
  749. *
  750. * @param [string] $whatDatabase the database
  751. * @param [string] $whatTable the table
  752. * @param [string] $newName the new name
  753. * @return [string] the result
  754. */
  755. public function renameTable( $whatDatabase, $whatTable, $newName )
  756. {
  757. /**
  758. * RENAME TABLE test.books TO test.the_books
  759. */
  760. $sql = "RENAME TABLE $whatDatabase.$whatTable TO $newName";
  761.  
  762. $result = mysqli_query ( $this->mysqli, $sql );
  763.  
  764. if ( ! $result )
  765. {
  766. return false;
  767. }
  768. return true;
  769. }
  770.  
  771. /**
  772. * I insert data into the database
  773. *
  774. *
  775. *
  776. * @param [string] $whatDatabase the database
  777. * @param [string] $whatTable the table
  778. * @param [array] $whatQuery Array of Key/Value pairs for inserting in the database
  779. * @return [string] the result
  780. */
  781. public function insertRecord( $jsonQuery )
  782. {
  783. return $this->jquery->buildQuery ( $jsonQuery, 'INSERT', true );
  784. //$queryArray[] = $this->jquery->buildQuery ( $jsonQuery, 'INSERT', false );
  785. }
  786.  
  787. /**
  788. * I update data from the database
  789. * UPDATE db.tbl SET name='value'
  790. *
  791. *
  792. * @param [string] $jsonQuery Array of Key/Value pairs for updating the database
  793. * @return [string] the result
  794. */
  795. public function updateRecord( $jsonQuery )
  796. {
  797. return $this->jquery->buildQuery ( $jsonQuery, 'UPDATE', true );
  798. }
  799.  
  800. /**
  801. * I remove data from the database
  802. *
  803. * @param [array] $jsonQuery the value to which remove by
  804. * @return [string] the result
  805. */
  806. public function removeRecord( $jsonQuery )
  807. {
  808. return $this->jquery->buildQuery ( $jsonQuery, 'DELETE', true );
  809. }
  810.  
  811. /* ********************************************************************
  812.  * ********************************************************************
  813.  *
  814.  * 4. ANALYZE/CHECK/OPTIMIZE/REPAIR METHODS
  815.  *
  816.  * Below is all the methods analyzing, checking, optimizing and repairing
  817.  * database tables.
  818.  *
  819.  * ********************************************************************
  820.  * *********************************************************************/
  821.  
  822. /**
  823. * I analyze a table
  824. *
  825. * @param [string] $whatDatabase the database
  826. * @param [string] $whatTable the table
  827. * @return [json]
  828. */
  829. public function analyzeTable( $whatDatabase, $whatTable )
  830. {
  831. $sql = "ANALYZE TABLE $whatDatabase.$whatTable";
  832.  
  833. return $this->_queryToJSON ( $sql );
  834. }
  835.  
  836. /**
  837. * I check a table
  838. *
  839. * @param [string] $whatDatabase the database
  840. * @param [string] $whatTable the table
  841. * @return [json]
  842. */
  843. public function checkTable( $whatDatabase, $whatTable )
  844. {
  845. $sql = "CHECK TABLE $whatDatabase.$whatTable";
  846.  
  847. return $this->_queryToJSON ( $sql );
  848. }
  849.  
  850. /**
  851. * I optimize a table
  852. *
  853. * @param [string] $whatDatabase the database
  854. * @param [string] $whatTable the table
  855. * @return [json]
  856. */
  857. public function optimizeTable( $whatDatabase, $whatTable )
  858. {
  859. $sql = "OPTIMIZE TABLE $whatDatabase.$whatTable";
  860.  
  861. return $this->_queryToJSON ( $sql );
  862. }
  863.  
  864. /**
  865. * I repair a table
  866. *
  867. * @param [string] $whatDatabase the database
  868. * @param [string] $whatTable the table
  869. * @return [json]
  870. */
  871. public function repairTable( $whatDatabase, $whatTable )
  872. {
  873. $sql = "REPAIR TABLE $whatDatabase.$whatTable";
  874.  
  875. return $this->_queryToJSON ( $sql );
  876. }
  877.  
  878. /**
  879. * I analyze a query are return the statistics
  880. *
  881. * @param [string] $sql query string
  882. * @return [json] json results
  883. */
  884. public function analyzeQuery( $sql )
  885. {
  886. $setProfileSQL = $this->mysqli->query ( 'SET profiling = 1' );
  887. $analyzeSQL = $this->mysqli->query ( $sql );
  888. $showProfileSQL = $this->mysqli->query ( 'SHOW PROFILE' );
  889. $showProfilesSQL = $this->mysqli->query ( 'SHOW PROFILES' );
  890.  
  891. $resultArray = array ();
  892. $profileArray = array ();
  893. $profilesArray = array ();
  894.  
  895. /* fetch associative array */
  896. while ( $row1 = $analyzeSQL->fetch_assoc () )
  897. {
  898. $resultArray [] = $row1;
  899. }
  900.  
  901. /* fetch associative array */
  902. while ( $row2 = $showProfileSQL->fetch_assoc () )
  903. {
  904. $profileArray [] = $row2;
  905. }
  906.  
  907. /* fetch associative array */
  908. while ( $row3 = $showProfilesSQL->fetch_assoc () )
  909. {
  910. $profilesArray [] = $row3;
  911. }
  912.  
  913. $analyzedQuery [] = array (
  914. 'aProfile' => $profileArray, 'aProfiles' => $profilesArray, 'aResults' => $resultArray
  915. );
  916. //$analyzedQuery[] = array( $profileArray, $profilesArray, $resultArray );
  917.  
  918.  
  919. /* Free all of the results */
  920. $analyzeSQL->close ();
  921. $showProfileSQL->close ();
  922. $showProfilesSQL->close ();
  923.  
  924. /* close connection */
  925. //$this->mysqli->close ();
  926.  
  927.  
  928. return json_encode ( $analyzedQuery );
  929. //return $analyzedQuery;
  930. }
  931.  
  932. /* ********************************************************************
  933.  * ********************************************************************
  934.  *
  935.  * 5. BACKUP/IMPORT/EXPORT METHODS
  936.  *
  937.  * Below is all the methods for backing up the database, importing data,
  938.  * exporting data.
  939.  *
  940.  * ********************************************************************
  941.  * *********************************************************************/
  942. /**
  943. * I execute a query and return XML
  944. *
  945. * @param [string] $query the query
  946. * @return [xml]
  947. */
  948. public function queryResultToXML( $query )
  949. {
  950. $xmlResult = '<?xml version="1.0"?>';
  951.  
  952. /* Set the content type for the browser */
  953.  
  954. //table query
  955. $sql = mysqli_query ( $this->mysqli, "$query" );
  956.  
  957. $xmlResult .= "<results>";
  958.  
  959. //loop all the results
  960. while ( $rows = mysqli_fetch_assoc ( $sql ) )
  961. {
  962. $xmlResult .= "<result>";
  963. //for each table in the result make an array
  964. foreach ( $rows as $key => $value )
  965. {
  966. $xmlResult .= "<$key>" . htmlspecialchars ( $value ) . "</$key>";
  967. }
  968. $xmlResult .= "</result>";
  969. }
  970. $xmlResult .= "</results>";
  971.  
  972. return $xmlResult;
  973. }
  974.  
  975. /**
  976. * I execute a query and return JSON
  977. *
  978. * @param [string] $query the query
  979. * @return [json]
  980. */
  981. private function queryResultToJSON( $query )
  982. {
  983. return $this->_queryToJSON ( $query );
  984. }
  985.  
  986. /**
  987. * I execute a query and return json
  988. *
  989. * @param [string] $whatDatabase the database
  990. * @param [string] $whatTable the table
  991. * @return [json]
  992. */
  993. public function exportToJSON( $whatDatabase, $whatTable )
  994. {
  995. $sql = "SELECRT * FROM $whatDatabase.$whatTable";
  996.  
  997. return $this->_queryToJSON ( $sql );
  998. }
  999.  
  1000. /**
  1001. * I export data from the database
  1002. *
  1003. * @param [string] $whatDatabase
  1004. * @param [string] $whatTable
  1005. * @param [string all, db_structure, db_data, tbl_structure, tbl_data ] $whatMode
  1006. * @return [string] the filename of the file.
  1007. */
  1008. public function createBackup( $whatDatabase, $whatTable, $whatMode )
  1009. {
  1010.  
  1011. $result = '';
  1012. $filename = $whatDatabase . '-' . $whatTable . '-' . $whatMode . '-' . $this->makeTimestamp () . '.sql';
  1013.  
  1014. //$dbDir = mkdir( "backups/".$whatDatabase );
  1015.  
  1016.  
  1017. //Set the database, filename, and we don't want to use compression.
  1018. $dumper = new MySQLDump ( $whatDatabase, "../backups/" . $filename, false );
  1019. $mode = $whatMode;
  1020.  
  1021. //Switch based on what mode is specified
  1022. switch ( $mode )
  1023. {
  1024. case 'all' :
  1025. $dumper->doDump ();
  1026. $result = 'Dumping all data';
  1027. return true;
  1028. break;
  1029.  
  1030. case 'db_structure' :
  1031. $dumper->getDatabaseStructure ();
  1032. $result = 'Database structure backed up successfully.';
  1033. $resultArray [] = array (
  1034. 'mode' => $mode, 'result' => $result, 'filename' => $filename
  1035. );
  1036. return $resultArray;
  1037. break;
  1038.  
  1039. case 'db_data' :
  1040. $dumper->getDatabaseData ( false );
  1041. $result = 'Database data backed up successfully.';
  1042. $resultArray [] = array (
  1043. 'mode' => $mode, 'result' => $result, 'filename' => $filename
  1044. );
  1045. return $resultArray;
  1046. break;
  1047.  
  1048. case 'tbl_structure' :
  1049. $dumper->getTableStructure ( $whatTable );
  1050. $result = 'Table structure backed up successfully.';
  1051. $resultArray [] = array (
  1052. 'mode' => $mode, 'result' => $result, 'filename' => $filename
  1053. );
  1054. return $resultArray;
  1055. break;
  1056.  
  1057. case 'tbl_data' :
  1058. $dumper->getTableData ( $whatTable, false );
  1059. $result = 'Table data backed up successfully.';
  1060. $resultArray [] = array (
  1061. 'mode' => $mode, 'result' => $result, 'filename' => $filename
  1062. );
  1063. return $resultArray;
  1064. break;
  1065.  
  1066. default :
  1067. $result = 'Please specify a mode.';
  1068. $resultArray [] = array (
  1069. 'mode' => $mode, 'result' => $result, 'filename' => $filename
  1070. );
  1071. return $resultArray;
  1072. break;
  1073. }
  1074. return $result;
  1075. }
  1076.  
  1077. /**
  1078. * I get a list of all the backups in the backup folder
  1079. *
  1080. * @return [json]
  1081. */
  1082. public function getDatabaseBackups()
  1083. {
  1084. return $this->fileSvc->browseDirectory ( './backups', 'json' );
  1085. }
  1086.  
  1087. public function removeBackup( $whatDatabase, $whatFile )
  1088. {
  1089. return $this->fileSvc->removeFile ( './backups', $whatFile );
  1090. }
  1091.  
  1092. /* ********************************************************************
  1093.  * ********************************************************************
  1094.  *
  1095.  * 6. SERVER VARIABLES
  1096.  *
  1097.  * Below is all the methods that build up information about the server
  1098.  * and system.
  1099.  *
  1100.  *
  1101.  * ********************************************************************
  1102.  * ********************************************************************/
  1103.  
  1104. /**
  1105. * I kill a thread that is connected or running
  1106. *
  1107. * @param [int] $whatThread the id of the thread
  1108. * @return [boolean] true or false
  1109. */
  1110. public function killProcess( $whatThread )
  1111. {
  1112. $sql = "KILL $whatThread";
  1113. $message = '';
  1114.  
  1115. if ( mysqli_query ( $this->mysqli, $sql ) )
  1116. {
  1117. $message = array (
  1118. 'message' => true, 'thread' => $whatThread
  1119. );
  1120. }
  1121. else
  1122. {
  1123. $message = array (
  1124. 'message' => false, 'thread' => $whatThread
  1125. );
  1126. }
  1127. return json_encode ( $message );
  1128. }
  1129.  
  1130. /**
  1131. * I show all mysql system variables
  1132. *
  1133. * @return [json]
  1134. */
  1135. public function showSystemVariables()
  1136. {
  1137. return $this->_queryStatusToJSON ( "SHOW GLOBAL VARIABLES" );
  1138. }
  1139.  
  1140. /**
  1141. * I show all system privileges
  1142. *
  1143. * @return [json]
  1144. */
  1145. public function showSystemPrivileges()
  1146. {
  1147. return $this->_queryToJSON ( "SHOW PRIVILEGES" );
  1148. }
  1149.  
  1150. /**
  1151. * I show the system status
  1152. *
  1153. * @return [json]
  1154. */
  1155. public function showSystemStatus()
  1156. {
  1157. return $this->_queryStatusToJSON ( "SHOW GLOBAL STATUS" );
  1158. }
  1159.  
  1160. /**
  1161. * I show system processes
  1162. *
  1163. * @return [json]
  1164. */
  1165. public function showSystemProcess()
  1166. {
  1167. return $this->_queryStatusToJSON ( "SHOW FULL PROCESSLIST" );
  1168. }
  1169.  
  1170. /**
  1171. * I show all of the systems users
  1172. *
  1173. * @return [json]
  1174. */
  1175. public function showSystemUsers()
  1176. {
  1177. return $this->_queryToJSON ( "SELECT * FROM mysql.user" );
  1178. }
  1179.  
  1180. /**
  1181. * I get server info
  1182. *
  1183. * @return [json]
  1184. */
  1185. public function _getServerInfo()
  1186. {
  1187. $serverArray = array ();
  1188. $aPath = $_SERVER [ 'DOCUMENT_ROOT' ];
  1189.  
  1190. $serverArray [] = array (
  1191.  
  1192. 'aDiskFreeSpace' => disk_free_space ( $aPath ),
  1193. 'aDiskTotalSize' => disk_total_space ( $aPath ),
  1194. 'aServerSoftware' => $_SERVER [ 'SERVER_SOFTWARE' ],
  1195. 'aServerName' => $_SERVER [ 'SERVER_NAME' ],
  1196. 'aPHPVersion' => PHP_VERSION,
  1197. 'aPHPOs' => PHP_OS,
  1198. 'aPHPExtensionDir' => PHP_EXTENSION_DIR,
  1199. 'aMySQLClientV' => mysqli_get_client_info ( $this->mysqli ),
  1200. 'aMySQLServerV' => mysqli_get_server_version ( $this->mysqli ),
  1201. 'aMySQLHost' => mysqli_get_host_info ( $this->mysqli ),
  1202. 'aMySQLProtocol' => mysqli_get_proto_info ( $this->mysqli ),
  1203. 'aUptime' => $this->_getUptime ()
  1204. );
  1205.  
  1206. return json_encode ( $serverArray );
  1207. }
  1208.  
  1209. /**
  1210. * I get all of the threads
  1211. *
  1212. * @return [json]
  1213. */
  1214. public function _getThreads()
  1215. {
  1216. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Threads%'" );
  1217. }
  1218.  
  1219. /**
  1220. * I get the temp size
  1221. *
  1222. * @return [json]
  1223. */
  1224. public function _getTemp()
  1225. {
  1226. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%tmp%'" );
  1227. }
  1228.  
  1229. /**
  1230. * I get open tables
  1231. *
  1232. * @return [json]
  1233. */
  1234. public function _getOpen()
  1235. {
  1236. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Open%'" );
  1237. }
  1238.  
  1239. /**
  1240. * I get the handlers variables
  1241. *
  1242. * @return [json]
  1243. */
  1244. public function _getHandlers()
  1245. {
  1246. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Handler%'" );
  1247. }
  1248.  
  1249. /**
  1250. * I get the server uptime
  1251. *
  1252. * @return [array]
  1253. */
  1254. public function _getUptime()
  1255. {
  1256. $result = mysqli_query ( $this->mysqli, "SHOW STATUS LIKE '%uptime%'" );
  1257. $row = mysqli_fetch_row ( $result );
  1258. $array = $this->_formatUptime ( $row [ 1 ] );
  1259.  
  1260. return $array;
  1261. }
  1262.  
  1263. private function _getUnixTimestamp( $unix )
  1264. {
  1265. return $this->_queryToARRAY ( "SELECT UNIX_TIMESTAMP() - $unix" );
  1266. }
  1267.  
  1268. /**
  1269. * I get the recent queries
  1270. *
  1271. * @return [json]
  1272. */
  1273. public function _getQuestions()
  1274. {
  1275. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE 'Questions%'" );
  1276. }
  1277.  
  1278. /**
  1279. * I get the query cache
  1280. *
  1281. * @return [json]
  1282. */
  1283. public function _getQcache()
  1284. {
  1285. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Qcache%'" );
  1286. }
  1287.  
  1288. /**
  1289. * I get InnoDB
  1290. *
  1291. * @return [json]
  1292. */
  1293. public function _getInnoDb()
  1294. {
  1295. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Innodb%'" );
  1296. }
  1297.  
  1298. /**
  1299. * I get the key cache
  1300. *
  1301. * @return [json]
  1302. */
  1303. public function _getKeys()
  1304. {
  1305. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Key%'" );
  1306. }
  1307.  
  1308. /**
  1309. * I get the performance of mysql.
  1310. *
  1311. * @return [json]
  1312. */
  1313. public function _getPerformance()
  1314. {
  1315. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Slow%'" );
  1316. }
  1317.  
  1318. /**
  1319. * I get all the sort
  1320. *
  1321. * @return [json]
  1322. */
  1323. public function _getSort()
  1324. {
  1325. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Sort%'" );
  1326. }
  1327.  
  1328. /**
  1329. * I get the connections
  1330. *
  1331. * @return [json]
  1332. */
  1333. public function _getConnections()
  1334. {
  1335. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Connections%'" );
  1336. }
  1337.  
  1338. /**
  1339. * I get the aborted clients and connections
  1340. *
  1341. * @return unknown
  1342. */
  1343. public function _getClients()
  1344. {
  1345. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Aborted%'" );
  1346. }
  1347.  
  1348. /**
  1349. * I get mysql bytes
  1350. *
  1351. * @return [json]
  1352. */
  1353. public function _getBytes()
  1354. {
  1355. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Bytes%'" );
  1356. }
  1357.  
  1358. /**
  1359. * I get all the slave hosts
  1360. *
  1361. * @return [json]
  1362. */
  1363. public function _getReplication()
  1364. {
  1365. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Slave%'" );
  1366. }
  1367.  
  1368. /**
  1369. * I get the commands
  1370. *
  1371. * @return [json]
  1372. */
  1373. public function _getCommands()
  1374. {
  1375. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Com%'" );
  1376. }
  1377.  
  1378. /**
  1379. * I show all of the SHOW commands
  1380. *
  1381. * @return [json]
  1382. */
  1383. public function _getShowCommands()
  1384. {
  1385. return $this->_queryStatusToJSON ( "SHOW STATUS LIKE '%Com_show%'" );
  1386. }
  1387.  
  1388. /**
  1389. * I get the stats of the mysql connection
  1390. *
  1391. * @return [array]
  1392. */
  1393. public function _getStat()
  1394. {
  1395. $stats = $this->mysqli->stat ();
  1396. $newStats = explode ( ' ', $stats );
  1397.  
  1398. return $newStats;
  1399. }
  1400.  
  1401. /* ********************************************************************
  1402.  * ********************************************************************
  1403.  *
  1404.  * 7. POLLING METHODS
  1405.  *
  1406.  * Below is all the methods for executing a query on the database,
  1407.  * and getting all records from the database.
  1408.  *
  1409.  * ********************************************************************
  1410.  * ********************************************************************/
  1411.  
  1412. /**
  1413. * I get the health of a mysql server
  1414. *
  1415. * @return [array] of results
  1416. */
  1417. public function _getHealth()
  1418. {
  1419. $query = $this->mysqli->query ( "SHOW GLOBAL STATUS LIKE '%Key_%'" );
  1420. $array = array ();
  1421.  
  1422. while ( $row = mysqli_fetch_assoc ( $query ) )
  1423. {
  1424. $array [ $row [ 'Variable_name' ] ] = array (
  1425. $row [ 'Variable_name' ] => $row [ 'Value' ]
  1426. );
  1427. }
  1428.  
  1429. return $array;
  1430. }
  1431.  
  1432. /**
  1433. * I am a polling method for checking the current select statements.
  1434. * @example Results
  1435. * <code>
  1436. * [
  1437. * {
  1438. * "Threads_cached":"0",
  1439. * "aTimestamp":"2009-02-20T21:52:34-08:00"
  1440. * },
  1441. * {
  1442. * "Threads_connected":"1",
  1443. * "aTimestamp":"2009-02-20T21:52:34-08:00"
  1444. * },
  1445. * {
  1446. * "Threads_created":"2070",
  1447. * "aTimestamp":"2009-02-20T21:52:34-08:00"
  1448. * },
  1449. * {
  1450. * "Threads_running":"1",
  1451. * "aTimestamp":"2009-02-20T21:52:34-08:00"
  1452. * }
  1453. * ]
  1454. *</code>
  1455. * @return [json] encoded results
  1456. */
  1457. public function pollQueries()
  1458. {
  1459. $result = mysqli_query ( $this->mysqli, "SHOW GLOBAL STATUS LIKE '%Com_select%'" );
  1460. $timestamp = date ( DATE_W3C );
  1461.  
  1462. while ( $row = mysqli_fetch_row ( $result ) )
  1463. {
  1464. $array [] = array (
  1465. $row [ 0 ] => $row [ 1 ], 'aTimestamp' => $timestamp
  1466. );
  1467. }
  1468. return json_encode ( $array );
  1469. }
  1470.  
  1471. /**
  1472. * I am a polling method for checking the current bytes sent.
  1473. * @example Results
  1474. * <code>
  1475. * [
  1476. * {
  1477. * "Bytes_sent":"48438",
  1478. * "aTimestamp":"2009-02-20T21:52:34-08:00"
  1479. * }
  1480. * ]
  1481. *</code>
  1482. * @return [json] encoded results
  1483. */
  1484. public function pollTraffic()
  1485. {
  1486. $result = mysqli_query ( $this->mysqli, "SHOW GLOBAL STATUS LIKE '%Bytes_sent%'" );
  1487. $timestamp = date ( DATE_W3C );
  1488.  
  1489. while ( $row = mysqli_fetch_row ( $result ) )
  1490. {
  1491. $array [] = array (
  1492. $row [ 0 ] => $row [ 1 ], 'aTimestamp' => $timestamp
  1493. );
  1494. }
  1495. return json_encode ( $array );
  1496. }
  1497.  
  1498. /**
  1499. * I am a polling method for checking the current connections.
  1500. * @example Results
  1501. * <code>
  1502. * [
  1503. * {
  1504. * "Com_select":"97",
  1505. * "aTimestamp":"2009-02-20T21:52:34-08:00"
  1506. * }
  1507. * ]
  1508. *</code>
  1509. *
  1510. * @return [json] encoded results
  1511. */
  1512. public function pollConnections()
  1513. {
  1514. $result = mysqli_query ( $this->mysqli, "SHOW GLOBAL STATUS LIKE '%Threads_%'" );
  1515. $timestamp [] = array (
  1516. 'aTimestamp' => date ( DATE_W3C )
  1517. );
  1518.  
  1519. while ( $row = mysqli_fetch_row ( $result ) )
  1520. {
  1521. $array [] = array (
  1522. $row [ 0 ] => $row [ 1 ]
  1523. );
  1524. }
  1525. //$a[] = array_merge( $timestamp, $array );
  1526. //return $a;
  1527. return json_encode ( $array );
  1528. }
  1529.  
  1530. /* ********************************************************************
  1531.  * ********************************************************************
  1532.  *
  1533.  * 8. DATA METHODS
  1534.  *
  1535.  * Below is all the methods for executing a query on the database,
  1536.  * and getting all records from the database.
  1537.  *
  1538.  * ********************************************************************
  1539.  * ********************************************************************/
  1540.  
  1541. /**
  1542. * I get all the table data
  1543. *
  1544. * @param [string] $whatDatabase the database
  1545. * @param [string] $whatTable the table
  1546. * @return [json]
  1547. */
  1548. public function getTableData( $whatDatabase, $whatTable )
  1549. {
  1550. $sql = "SELECT * FROM $whatDatabase.$whatTable";
  1551.  
  1552. return $this->_queryToJSON ( $sql );
  1553. }
  1554.  
  1555. /**
  1556. * I execute a query
  1557. *
  1558. * @param [string] $query the query to execute
  1559. * @return [json]
  1560. */
  1561. public function executeQuery( $sql )
  1562. {
  1563. $query = mysqli_escape_string ( $this->mysqli, $sql );
  1564. return $this->_queryToJSON ( $query );
  1565. }
  1566.  
  1567. /* ********************************************************************
  1568. * ********************************************************************
  1569. *
  1570. * 9. RESULT HANDLERS
  1571. *
  1572. * ********************************************************************
  1573. * ********************************************************************/
  1574.  
  1575. /**
  1576. * I execute a query and return the results as json.
  1577. *
  1578. * @param [string] $sql the query to be executed
  1579. * @return [json] the result in json
  1580. */
  1581. private function _queryToJSON( $sql )
  1582. {
  1583. $result = mysqli_query ( $this->mysqli, $sql );
  1584.  
  1585. while ( $row = mysqli_fetch_assoc ( $result ) )
  1586. {
  1587. $array [] = $row;
  1588. }
  1589. return json_encode ( $array );
  1590. }
  1591.  
  1592. /**
  1593. * I execute a query and return the result as an array.
  1594. *
  1595. * @param [string] $sql the query to be executed
  1596. * @return [array] the result array
  1597. */
  1598. private function _queryToARRAY( $sql )
  1599. {
  1600. $query = mysqli_query ( $this->mysqli, $sql );
  1601. $array = array ();
  1602.  
  1603. while ( $row = mysqli_fetch_assoc ( $query ) )
  1604. {
  1605. $array [] = $row;
  1606. }
  1607.  
  1608. return $array;
  1609. }
  1610.  
  1611. /**
  1612. * I get the query status
  1613. *
  1614. * @param [string] $sql
  1615. * @return [json] mysql status with the ('_') striped out
  1616. */
  1617. private function _queryStatusToJSON( $sql )
  1618. {
  1619. $result = mysqli_query ( $this->mysqli, $sql );
  1620.  
  1621. while ( $row = mysqli_fetch_assoc ( $result ) )
  1622. {
  1623. //replace some of the names
  1624. $row = str_replace ( 'Com_', '', $row );
  1625. //take out the _ of the rows
  1626. $row = str_replace ( '_', ' ', $row );
  1627.  
  1628. $array [] = $row;
  1629. }
  1630. sort ( $array );
  1631.  
  1632. return json_encode ( $array );
  1633. }
  1634.  
  1635. /* ********************************************************************
  1636. * ********************************************************************
  1637. *
  1638. * 10. UTILITY METHODS
  1639. *
  1640. * Below is all the utility methods for handling the results from a query
  1641. * and dumping variables or creating timestamps
  1642. *
  1643. *
  1644. * ********************************************************************
  1645. * ********************************************************************/
  1646.  
  1647. /**
  1648. * I ping mysql for a connection
  1649. *
  1650. * @return true or false
  1651. */
  1652. public function ping()
  1653. {
  1654. $msg = '';
  1655. /* check if server is alive */
  1656. if ( $this->mysqli->ping () )
  1657. {
  1658. $msg = true;
  1659. }
  1660. else
  1661. {
  1662. $msg = false;
  1663. }
  1664. return $msg;
  1665. }
  1666.  
  1667. /**
  1668. * I get help from the mysql database
  1669. *
  1670. * @return [json]
  1671. */
  1672. public function getHelp()
  1673. {
  1674. $sql = 'SELECT help_keyword.name,
  1675. help_topic.name,
  1676. help_topic.description,
  1677. help_category.name AS AVG_help_category_name,
  1678. help_category.url,
  1679. help_topic.example,
  1680. help_topic.url
  1681. FROM mysql.help_keyword
  1682. INNER JOIN mysql.help_relation
  1683. ON help_keyword.help_keyword_id = help_relation.help_keyword_id
  1684. INNER JOIN mysql.help_topic
  1685. ON help_topic.help_topic_id = help_relation.help_topic_id
  1686. INNER JOIN mysql.help_category
  1687. ON help_topic.help_category_id = help_category.help_category_id';
  1688. return $this->_queryToJSON ( $sql );
  1689. }
  1690.  
  1691. /**
  1692. * I format debug dumps
  1693. *
  1694. * @param [var] the variable you with to dump
  1695. */
  1696. public function dumpIt( $var )
  1697. {
  1698. print "<pre>n";
  1699. print_r ( $var );
  1700. print "</pre>n";
  1701. }
  1702.  
  1703. /**
  1704. * I make a formatted timestamp.
  1705. * <code>
  1706. * 2008-12-30 22:40:00
  1707. * </code>
  1708. *
  1709. * @return [string] a timestamp
  1710. */
  1711. private function makeTimestamp()
  1712. {
  1713. $time = time ();
  1714.  
  1715. return date ( 'm-d-Y-H-i', $time );
  1716. }
  1717.  
  1718. /**
  1719. * I format uptime from MySQL
  1720. *
  1721. * @param [int] $time the old time
  1722. * @return [string] the new time
  1723. */
  1724. private function _formatUptime( $time = 0 )
  1725. {
  1726. $days = ( int ) floor ( $time / 86400 );
  1727. $hours = ( int ) floor ( $time / 3600 ) % 24;
  1728. $minutes = ( int ) floor ( $time / 60 ) % 60;
  1729.  
  1730. if ( $days == 1 )
  1731. {
  1732. $uptime = "$days day, ";
  1733. }
  1734. else if ( $days > 1 )
  1735. {
  1736. $uptime = "$days days, ";
  1737. }
  1738. if ( $hours == 1 )
  1739. {
  1740. $uptime .= "$hours hour";
  1741. }
  1742. else if ( $hours > 1 )
  1743. {
  1744. $uptime .= "$hours hours";
  1745. }
  1746. if ( $uptime && $minutes > 0 && $seconds > 0 )
  1747. {
  1748. $uptime .= ", ";
  1749. }
  1750. else if ( $uptime && $minutes > 0 & $seconds == 0 )
  1751. {
  1752. $uptime .= " and ";
  1753. }
  1754. ( $minutes > 0 ) ? $uptime .= "$minutes minute" . ( ( $minutes > 1 ) ? "s" : NULL ) : NULL;
  1755.  
  1756. return $uptime;
  1757. }
  1758.  
  1759. /**
  1760. * I try and throw an error.
  1761. *
  1762. * @param [string] $msg the message of the mess
  1763. * @param [string] $type the type of error
  1764. * @return error
  1765. */
  1766. private function _throwError( $msg, $type )
  1767. {
  1768. switch ( $type )
  1769. {
  1770. case 'user' :
  1771. throw ErrorException ();
  1772. break;
  1773.  
  1774. case 'error' :
  1775. return trigger_error ( $msg, E_ERROR );
  1776. break;
  1777.  
  1778. case 'other' :
  1779. return trigger_error ( $msg, E_USER_ERROR );
  1780. break;
  1781. }
  1782. return trigger_error ( $msg, E_USER_ERROR );
  1783. }
  1784.  
  1785. }
  1786.  
  1787. ?>

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.