Posted By

jonniespratley on 02/26/09


Tagged

mysql sql php textmate json


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

vali29


Full REST JSON MySQL Management Class


 / Published in: PHP
 

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

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

Report this snippet  

You need to login to post a comment.