database.lib.php 57 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * This is the main database library for Chamilo.
  5. * Include/require it in your code to use its functionality.
  6. * Because this library contains all the basic database calls, it could be
  7. * replaced by another library for say, PostgreSQL, to actually use Chamilo
  8. * with another database (this is not ready yet because a lot of code still
  9. * uses the MySQL database functions extensively).
  10. *
  11. * @package chamilo.library
  12. * @todo the table constants have all to start with TABLE_
  13. * This is because of the analogy with the tool constants TOOL_
  14. */
  15. /**
  16. * Code
  17. */
  18. /* CONSTANTS */
  19. define('DB_COURSE_PREFIX', 'c_');
  20. require_once 'database.constants.inc.php';
  21. /* DATABASE CLASS
  22. The class and its methods
  23. */
  24. class Database {
  25. /*
  26. Accessor methods
  27. Usually, you won't need these directly but instead
  28. rely on of the get_xxx_table methods.
  29. */
  30. /**
  31. * Returns the name of the main database.
  32. */
  33. public static function get_main_database() {
  34. global $_configuration;
  35. return $_configuration['main_database'];
  36. }
  37. /**
  38. * Returns the name of the statistics database.
  39. */
  40. public static function get_statistic_database() {
  41. global $_configuration;
  42. return $_configuration['statistics_database'];
  43. }
  44. /**
  45. * Returns the name of the SCORM database.
  46. * @deprecated
  47. */
  48. public static function get_scorm_database() {
  49. global $_configuration;
  50. return $_configuration['scorm_database'];
  51. }
  52. /**
  53. * Returns the name of the database where all the personal stuff of the user is stored
  54. */
  55. public static function get_user_personal_database() {
  56. global $_configuration;
  57. return $_configuration['user_personal_database'];
  58. }
  59. /**
  60. * Returns the name of the current course database.
  61. * @return mixed Glued database name of false if undefined
  62. */
  63. public static function get_current_course_database() {
  64. $course_info = api_get_course_info();
  65. if (empty($course_info['dbName'])) {
  66. return false;
  67. }
  68. return $course_info['dbName'];
  69. }
  70. /**
  71. * Returns the glued name of the current course database.
  72. * @return mixed Glued database name of false if undefined
  73. */
  74. public static function get_current_course_glued_database() {
  75. $course_info = api_get_course_info();
  76. if (empty($course_info['dbNameGlu'])) {
  77. return false;
  78. }
  79. return $course_info['dbNameGlu'];
  80. }
  81. /**
  82. * The glue is the string needed between database and table.
  83. * The trick is: in multiple databases, this is a period (with backticks).
  84. * In single database, this can be e.g. an underscore so we just fake
  85. * there are multiple databases and the code can be written independent
  86. * of the single / multiple database setting.
  87. */
  88. public static function get_database_glue() {
  89. global $_configuration;
  90. return $_configuration['db_glue'];
  91. }
  92. /**
  93. * Returns the database prefix.
  94. * All created COURSE databases are prefixed with this string.
  95. *
  96. * TIP: This can be convenient e.g. if you have multiple system installations
  97. * on the same physical server.
  98. */
  99. public static function get_database_name_prefix() {
  100. global $_configuration;
  101. return $_configuration['db_prefix'];
  102. }
  103. /**
  104. * Returns the course table prefix for single database.
  105. * Not certain exactly when this is used.
  106. * Do research.
  107. * It's used in local.inc.php.
  108. */
  109. public static function get_course_table_prefix() {
  110. global $_configuration;
  111. return $_configuration['table_prefix'];
  112. }
  113. /*
  114. Table name methods
  115. Use these methods to get table names for queries,
  116. instead of constructing them yourself.
  117. Backticks automatically surround the result,
  118. e.g. COURSE_NAME.link
  119. so the queries can look cleaner.
  120. Example:
  121. $table = Database::get_course_table(TABLE_DOCUMENT);
  122. $sql_query = "SELECT * FROM $table WHERE $condition";
  123. $sql_result = Database::query($sql_query);
  124. $result = Database::fetch_array($sql_result);
  125. */
  126. /**
  127. * A more generic method than the other get_main_xxx_table methods,
  128. * This one returns the correct complete name of any table of the main database of which you pass
  129. * the short name as a parameter.
  130. * Please, define table names as constants in this library and use them
  131. * instead of directly using magic words in your tool code.
  132. *
  133. * @param string $short_table_name, the name of the table
  134. */
  135. public static function get_main_table($short_table_name) {
  136. return self::format_table_name(self::get_main_database(), $short_table_name);
  137. }
  138. /**
  139. * A more generic method than the older get_course_xxx_table methods,
  140. * This one can return the correct complete name of any course table of which you pass
  141. * the short name as a parameter.
  142. * Please, define table names as constants in this library and use them
  143. * instead of directly using magic words in your tool code.
  144. *
  145. * @param string $short_table_name, the name of the table
  146. * @param string $database_name, optional, name of the course database
  147. * - if you don't specify this, you work on the current course.
  148. */
  149. public static function get_course_table($short_table_name, $database_name = '') {
  150. return self::format_table_name(self::get_main_database(), DB_COURSE_PREFIX.$short_table_name);
  151. //return self::format_glued_course_table_name(self::fix_database_parameter($database_name), $short_table_name);
  152. }
  153. /**
  154. * Gets a complete course table name from a course code
  155. *
  156. * @param string $course_code
  157. * @param string $table the name of the table
  158. * @todo this function should be deprecated use api_get_course_info() and then get_course_table()
  159. */
  160. public static function get_course_table_from_code($course_code, $table) {
  161. $course_table = self::get_main_table(TABLE_MAIN_COURSE);
  162. $course_cat_table = self::get_main_table(TABLE_MAIN_CATEGORY);
  163. $sql = "SELECT $course_table.db_name, $course_cat_table.code
  164. FROM $course_table
  165. LEFT JOIN $course_cat_table
  166. ON $course_table.category_code = $course_cat_table.code
  167. WHERE $course_table.code = '$course_code'
  168. LIMIT 1";
  169. $result = self::fetch_array(self::query($sql));
  170. return sprintf("%s.%s", $result[0], $table);
  171. }
  172. /**
  173. * This generic method returns the correct and complete name of any statistic table
  174. * of which you pass the short name as a parameter.
  175. * Please, define table names as constants in this library and use them
  176. * instead of directly using magic words in your tool code.
  177. *
  178. * @param string $short_table_name, the name of the table
  179. */
  180. public static function get_statistic_table($short_table_name) {
  181. return self::format_table_name(self::get_statistic_database(), $short_table_name);
  182. }
  183. /**
  184. * This generic method returns the correct and complete name of any scorm
  185. * table of which you pass the short name as a parameter. Please, define
  186. * table names as constants in this library and use them instead of directly
  187. * using magic words in your tool code.
  188. *
  189. * @param string $short_table_name, the name of the table
  190. */
  191. public static function get_scorm_table($short_table_name) {
  192. return self::format_table_name(self::get_scorm_database(), $short_table_name);
  193. }
  194. /**
  195. * This generic method returns the correct and complete name of any scorm
  196. * table of which you pass the short name as a parameter. Please, define
  197. * table names as constants in this library and use them instead of directly
  198. * using magic words in your tool code.
  199. *
  200. * @param string $short_table_name, the name of the table
  201. */
  202. public static function get_user_personal_table($short_table_name) {
  203. return self::format_table_name(self::get_user_personal_database(), $short_table_name);
  204. }
  205. public static function get_course_chat_connected_table($database_name = '') {
  206. return self::format_glued_course_table_name(self::fix_database_parameter($database_name), CHAT_CONNECTED_TABLE);
  207. }
  208. /*
  209. Query methods
  210. These methods execute a query and return the result(s).
  211. */
  212. /**
  213. * @return a list (array) of all courses.
  214. * @todo shouldn't this be in the course.lib.php script?
  215. */
  216. public static function get_course_list() {
  217. $table = self::get_main_table(TABLE_MAIN_COURSE);
  218. return self::store_result(self::query("SELECT *, id as real_id FROM $table"));
  219. }
  220. /**
  221. * Returns an array with all database fields for the specified course.
  222. *
  223. * @param the real (system) code of the course (ID from inside the main course table)
  224. * @todo shouldn't this be in the course.lib.php script?
  225. */
  226. public static function get_course_info($course_code) {
  227. $course_code = self::escape_string($course_code);
  228. $table = self::get_main_table(TABLE_MAIN_COURSE);
  229. $result = self::generate_abstract_course_field_names(
  230. self::fetch_array(self::query("SELECT *, id as real_id FROM $table WHERE code = '$course_code'")));
  231. return $result === false ? array('db_name' => '') : $result;
  232. }
  233. /**
  234. * @param $user_id (integer): the id of the user
  235. * @return $user_info (array): user_id, lastname, firstname, username, email, ...
  236. * @author Patrick Cool <patrick.cool@UGent.be>, expanded to get info for any user
  237. * @author Roan Embrechts, first version + converted to Database API
  238. * @version 30 September 2004
  239. * @desc find all the information about a specified user. Without parameter this is the current user.
  240. * @todo shouldn't this be in the user.lib.php script?
  241. */
  242. public static function get_user_info_from_id($user_id = '') {
  243. if (empty($user_id)) {
  244. return $GLOBALS['_user'];
  245. }
  246. $table = self::get_main_table(TABLE_MAIN_USER);
  247. $user_id = self::escape_string($user_id);
  248. return self::generate_abstract_user_field_names(
  249. self::fetch_array(self::query("SELECT * FROM $table WHERE user_id = '$user_id'")));
  250. }
  251. /**
  252. * Returns course code from a given gradebook category's id
  253. * @param int Category ID
  254. * @return string Course code
  255. * @todo move this function in a gradebook-related library
  256. */
  257. public static function get_course_by_category($category_id) {
  258. $info = self::fetch_array(self::query('SELECT course_code FROM '.self::get_main_table(TABLE_MAIN_GRADEBOOK_CATEGORY).' WHERE id='.$category_id), 'ASSOC');
  259. return $info ? $info['course_code'] : false;
  260. }
  261. /**
  262. * This method creates an abstraction layer between database field names
  263. * and field names expected in code.
  264. *
  265. * This approach helps when changing database names.
  266. * It's also useful now to get rid of the 'franglais'.
  267. *
  268. * @todo add more array entries to abstract course info from field names
  269. * @author Roan Embrechts
  270. *
  271. * @todo What's the use of this method. I think this is better removed.
  272. * There should be consistency in the variable names and the use throughout the scripts
  273. * for the database name we should consistently use or db_name or database (db_name probably being the better one)
  274. */
  275. public static function generate_abstract_course_field_names($result_array) {
  276. $visual_code = isset($result_array['visual_code']) ? $result_array['visual_code'] : null;
  277. $code = isset($result_array['code']) ? $result_array['code'] : null;
  278. $title = isset($result_array['title']) ? $result_array['title'] : null;
  279. $db_name = isset($result_array['db_name']) ? $result_array['db_name'] : null;
  280. $category_code = isset($result_array['category_code']) ? $result_array['category_code'] : null;
  281. $result_array['official_code'] = $visual_code;
  282. $result_array['visual_code'] = $visual_code;
  283. $result_array['real_code'] = $code;
  284. $result_array['system_code'] = $code;
  285. $result_array['title'] = $title;
  286. $result_array['database'] = $db_name;
  287. $result_array['faculty'] = $category_code;
  288. //$result_array['directory'] = $result_array['directory'];
  289. /*
  290. still to do: (info taken from local.inc.php)
  291. $_course['id' ] = $cData['cours_id' ]; //auto-assigned integer
  292. $_course['name' ] = $cData['title' ];
  293. $_course['official_code'] = $cData['visual_code' ]; // use in echo
  294. $_course['sysCode' ] = $cData['code' ]; // use as key in db
  295. $_course['path' ] = $cData['directory' ]; // use as key in path
  296. $_course['dbName' ] = $cData['db_name' ]; // use as key in db list
  297. $_course['dbNameGlu' ] = $_configuration['table_prefix'] . $cData['dbName'] . $_configuration['db_glue']; // use in all queries
  298. $_course['titular' ] = $cData['tutor_name' ];
  299. $_course['language' ] = $cData['course_language' ];
  300. $_course['extLink' ]['url' ] = $cData['department_url' ];
  301. $_course['extLink' ]['name'] = $cData['department_name'];
  302. $_course['categoryCode'] = $cData['faCode' ];
  303. $_course['categoryName'] = $cData['faName' ];
  304. $_course['visibility' ] = (bool) ($cData['visibility'] == 2 || $cData['visibility'] == 3);
  305. $_course['registrationAllowed'] = (bool) ($cData['visibility'] == 1 || $cData['visibility'] == 2);
  306. */
  307. return $result_array;
  308. }
  309. /**
  310. * This method creates an abstraction layer between database field names
  311. * and field names expected in code.
  312. *
  313. * This helps when changing database names.
  314. * It's also useful now to get rid of the 'franglais'.
  315. *
  316. * @todo add more array entries to abstract user info from field names
  317. * @author Roan Embrechts
  318. * @author Patrick Cool
  319. *
  320. * @todo what's the use of this function. I think this is better removed.
  321. * There should be consistency in the variable names and the use throughout the scripts
  322. */
  323. public static function generate_abstract_user_field_names($result_array) {
  324. $result_array['firstName'] = $result_array['firstname'];
  325. $result_array['lastName'] = $result_array['lastname'];
  326. $result_array['mail'] = $result_array['email'];
  327. #$result_array['picture_uri'] = $result_array['picture_uri'];
  328. #$result_array ['user_id'] = $result_array['user_id'];
  329. return $result_array;
  330. }
  331. /**
  332. * Counts the number of rows in a table
  333. * @param string $table The table of which the rows should be counted
  334. * @return int The number of rows in the given table.
  335. */
  336. public static function count_rows($table) {
  337. $obj = self::fetch_object(self::query("SELECT COUNT(*) AS n FROM $table"));
  338. return $obj->n;
  339. }
  340. /*
  341. An intermediate API-layer between the system and the dabase server.
  342. */
  343. /**
  344. * Returns the number of affected rows in the last database operation.
  345. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  346. * @return int Returns the number of affected rows on success, and -1 if the last query failed.
  347. */
  348. public static function affected_rows($connection = null) {
  349. return self::use_default_connection($connection) ? mysql_affected_rows() : mysql_affected_rows($connection);
  350. }
  351. /**
  352. * Closes non-persistent database connection.
  353. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  354. * @return bool Returns TRUE on success or FALSE on failure.
  355. */
  356. public static function close($connection = null) {
  357. return self::use_default_connection($connection) ? mysql_close() : mysql_close($connection);
  358. }
  359. /**
  360. * Opens a connection to a database server.
  361. * @param array $parameters (optional) An array that contains the necessary parameters for accessing the server.
  362. * @return resource/boolean Returns a database connection on success or FALSE on failure.
  363. * Note: Currently the array could contain MySQL-specific parameters:
  364. * $parameters['server'], $parameters['username'], $parameters['password'],
  365. * $parameters['new_link'], $parameters['client_flags'], $parameters['persistent'].
  366. * For details see documentation about the functions mysql_connect() and mysql_pconnect().
  367. * @link http://php.net/manual/en/function.mysql-connect.php
  368. * @link http://php.net/manual/en/function.mysql-pconnect.php
  369. */
  370. public static function connect($parameters = array()) {
  371. // A MySQL-specific implementation.
  372. if (!isset($parameters['server'])) {
  373. $parameters['server'] = @ini_get('mysql.default_host');
  374. if (empty($parameters['server'])) {
  375. $parameters['server'] = 'localhost:3306';
  376. }
  377. }
  378. if (!isset($parameters['username'])) {
  379. $parameters['username'] = @ini_get('mysql.default_user');
  380. }
  381. if (!isset($parameters['password'])) {
  382. $parameters['password'] = @ini_get('mysql.default_password');
  383. }
  384. if (!isset($parameters['new_link'])) {
  385. $parameters['new_link'] = false;
  386. }
  387. if (!isset($parameters['client_flags'])) {
  388. $parameters['client_flags'] = 0;
  389. }
  390. return $parameters['persistent']
  391. ? mysql_pconnect($parameters['server'], $parameters['username'], $parameters['password'], $parameters['client_flags'])
  392. : mysql_connect($parameters['server'], $parameters['username'], $parameters['password'], $parameters['new_link'], $parameters['client_flags']);
  393. }
  394. /**
  395. * Returns the error number from the last operation done on the database server.
  396. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  397. * @return int Returns the error number from the last database (operation, or 0 (zero) if no error occurred.
  398. */
  399. public static function errno($connection = null) {
  400. return self::use_default_connection($connection) ? mysql_errno() : mysql_errno($connection);
  401. }
  402. /**
  403. * Returns the error text from the last operation done on the database server.
  404. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  405. * @return string Returns the error text from the last database operation, or '' (empty string) if no error occurred.
  406. */
  407. public static function error($connection = null) {
  408. return self::use_default_connection($connection) ? mysql_error() : mysql_error($connection);
  409. }
  410. /**
  411. * Escapes a string to insert into the database as text
  412. * @param string The string to escape
  413. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  414. * @return string The escaped string
  415. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  416. * @author Patrick Cool <patrick.cool@UGent.be>, Ghent University
  417. */
  418. public static function escape_string($string, $connection = null) {
  419. return get_magic_quotes_gpc()
  420. ? (self::use_default_connection($connection)
  421. ? mysql_real_escape_string(stripslashes($string))
  422. : mysql_real_escape_string(stripslashes($string), $connection))
  423. : (self::use_default_connection($connection)
  424. ? mysql_real_escape_string($string)
  425. : mysql_real_escape_string($string, $connection));
  426. }
  427. /**
  428. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  429. * @param resource The result from a call to sql_query (e.g. Database::query)
  430. * @param string Optional: "ASSOC","NUM" or "BOTH", as the constant used in mysql_fetch_array.
  431. * @return array Array of results as returned by php
  432. * @author Yannick Warnier <yannick.warnier@beeznest.com>
  433. */
  434. public static function fetch_array($result, $option = 'BOTH') {
  435. if ($result === false) { return array(); }
  436. return $option == 'ASSOC' ? mysql_fetch_array($result, MYSQL_ASSOC) : ($option == 'NUM' ? mysql_fetch_array($result, MYSQL_NUM) : mysql_fetch_array($result));
  437. }
  438. /**
  439. * Gets an associative array from a SQL result (as returned by Database::query).
  440. * This method is equivalent to calling Database::fetch_array() with 'ASSOC' value for the optional second parameter.
  441. * @param resource $result The result from a call to sql_query (e.g. Database::query).
  442. * @return array Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead.
  443. */
  444. public static function fetch_assoc($result) {
  445. return mysql_fetch_assoc($result);
  446. }
  447. /**
  448. * Gets the next row of the result of the SQL query (as returned by Database::query) in an object form
  449. * @param resource The result from a call to sql_query (e.g. Database::query)
  450. * @param string Optional class name to instanciate
  451. * @param array Optional array of parameters
  452. * @return object Object of class StdClass or the required class, containing the query result row
  453. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  454. */
  455. public static function fetch_object($result, $class = null, $params = null) {
  456. return !empty($class) ? (is_array($params) ? mysql_fetch_object($result, $class, $params) : mysql_fetch_object($result, $class)) : mysql_fetch_object($result);
  457. }
  458. /**
  459. * Gets the array from a SQL result (as returned by Database::query) - help achieving database independence
  460. * @param resource The result from a call to sql_query (see Database::query()).
  461. * @return array Array of results as returned by php (mysql_fetch_row)
  462. */
  463. public static function fetch_row($result) {
  464. return mysql_fetch_row($result);
  465. }
  466. /**
  467. * Frees all the memory associated with the provided result identifier.
  468. * @return bool Returns TRUE on success or FALSE on failure.
  469. * Notes: Use this method if you are concerned about how much memory is being used for queries that return large result sets.
  470. * Anyway, all associated result memory is automatically freed at the end of the script's execution.
  471. */
  472. public static function free_result($result) {
  473. return mysql_free_result($result);
  474. }
  475. /**
  476. * Returns the database client library version.
  477. * @return strung Returns a string that represents the client library version.
  478. */
  479. public function get_client_info() {
  480. return mysql_get_client_info();
  481. }
  482. /**
  483. * Returns a list of databases created on the server. The list may contain all of the
  484. * available database names or filtered database names by using a pattern.
  485. * @param string $pattern (optional) A pattern for filtering database names as if it was needed for the SQL's LIKE clause, for example 'chamilo_%'.
  486. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  487. * @return array Returns in an array the retrieved list of database names.
  488. */
  489. public static function get_databases($pattern = '', $connection = null) {
  490. $result = array();
  491. $query_result = Database::query(!empty($pattern) ? "SHOW DATABASES LIKE '".self::escape_string($pattern, $connection)."'" : "SHOW DATABASES", $connection);
  492. while ($row = Database::fetch_row($query_result)) {
  493. $result[] = $row[0];
  494. }
  495. return $result;
  496. }
  497. /**
  498. * Returns a list of the fields that a given table contains. The list may contain all of the available field names or filtered field names by using a pattern.
  499. * By using a special option, this method is able to return an indexed list of fields' properties, where field names are keys.
  500. * @param string $table This is the examined table.
  501. * @param string $pattern (optional) A pattern for filtering field names as if it was needed for the SQL's LIKE clause, for example 'column_%'.
  502. * @param string $database (optional) The name of the targeted database. If it is omited, the current database is assumed, see Database::select_db().
  503. * @param bool $including_properties (optional) When this option is true, the returned result has the followong format:
  504. * array(field_name_1 => array(0 => property_1, 1 => property_2, ...), fieald_name_2 => array(0 => property_1, ...), ...)
  505. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  506. * @return array Returns in an array the retrieved list of field names.
  507. */
  508. public static function get_fields($table, $pattern = '', $database = '', $including_properties = false, $connection = null) {
  509. $result = array();
  510. $query = "SHOW COLUMNS FROM `".self::escape_string($table, $connection)."`";
  511. if (!empty($database)) {
  512. $query .= " FROM `".self::escape_string($database, $connection)."`";
  513. }
  514. if (!empty($pattern)) {
  515. $query .= " LIKE '".self::escape_string($pattern, $connection)."'";
  516. }
  517. $query_result = Database::query($query, $connection);
  518. if ($including_properties) {
  519. // Making an indexed list of the fields and their properties.
  520. while ($row = Database::fetch_row($query_result)) {
  521. $result[$row[0]] = $row;
  522. }
  523. } else {
  524. // Making a plain, flat list.
  525. while ($row = Database::fetch_row($query_result)) {
  526. $result[] = $row[0];
  527. }
  528. }
  529. return $result;
  530. }
  531. /**
  532. * Returns information about the type of the current connection and the server host name.
  533. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  534. * @return string/boolean Returns string data on success or FALSE on failure.
  535. */
  536. public function get_host_info($connection = null) {
  537. return self::use_default_connection($connection) ? mysql_get_host_info() : mysql_get_host_info($connection);
  538. }
  539. /**
  540. * Retrieves database client/server protocol version.
  541. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  542. * @return int/boolean Returns the protocol version on success or FALSE on failure.
  543. */
  544. public function get_proto_info($connection = null) {
  545. return self::use_default_connection($connection) ? mysql_get_proto_info() : mysql_get_proto_info($connection);
  546. }
  547. /**
  548. * Retrieves the database server version.
  549. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  550. * @return string/boolean Returns the MySQL server version on success or FALSE on failure.
  551. */
  552. public function get_server_info($connection = null) {
  553. return self::use_default_connection($connection) ? mysql_get_server_info() : mysql_get_server_info($connection);
  554. }
  555. /**
  556. * Returns a list of tables within a database. The list may contain all of the
  557. * available table names or filtered table names by using a pattern.
  558. * @param string $database (optional) The name of the examined database. If it is omited, the current database is assumed, see Database::select_db().
  559. * @param string $pattern (optional) A pattern for filtering table names as if it was needed for the SQL's LIKE clause, for example 'access_%'.
  560. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  561. * @return array Returns in an array the retrieved list of table names.
  562. */
  563. public static function get_tables($database = '', $pattern = '', $connection = null) {
  564. $result = array();
  565. $query = "SHOW TABLES";
  566. if (!empty($database)) {
  567. $query .= " FROM `".self::escape_string($database, $connection)."`";
  568. }
  569. if (!empty($pattern)) {
  570. $query .= " LIKE '".self::escape_string($pattern, $connection)."'";
  571. }
  572. $query_result = Database::query($query, $connection);
  573. while ($row = Database::fetch_row($query_result)) {
  574. $result[] = $row[0];
  575. }
  576. return $result;
  577. }
  578. /**
  579. * Gets the ID of the last item inserted into the database
  580. * This should be updated to use ADODB at some point
  581. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  582. * @return int The last ID as returned by the DB function
  583. */
  584. public static function insert_id($connection = null) {
  585. return self::use_default_connection($connection) ? mysql_insert_id() : mysql_insert_id($connection);
  586. }
  587. /**
  588. * Gets the number of rows from the last query result - help achieving database independence
  589. * @param resource The result
  590. * @return integer The number of rows contained in this result
  591. * @author Yannick Warnier <yannick.warnier@dokeos.com>
  592. **/
  593. public static function num_rows($result) {
  594. return is_resource($result) ? mysql_num_rows($result) : false;
  595. }
  596. /**
  597. * Acts as the relative *_result() function of most DB drivers and fetches a
  598. * specific line and a field
  599. * @param resource The database resource to get data from
  600. * @param integer The row number
  601. * @param string Optional field name or number
  602. * @return mixed One cell of the result, or FALSE on error
  603. */
  604. public static function result($resource, $row, $field = '') {
  605. return self::num_rows($resource) > 0 ? (!empty($field) ? mysql_result($resource, $row, $field) : mysql_result($resource, $row)) : null;
  606. }
  607. /**
  608. * This method returns a resource
  609. * Documentation has been added by Arthur Portugal
  610. * Some adaptations have been implemented by Ivan Tcholakov, 2009, 2010
  611. * @author Olivier Brouckaert
  612. * @param string $query The SQL query
  613. * @param resource $connection (optional) The database server (MySQL) connection.
  614. * If it is not specified, the connection opened by mysql_connect() is assumed.
  615. * If no connection is found, the server will try to create one as if mysql_connect() was called with no arguments.
  616. * If no connection is found or established, an E_WARNING level error is generated.
  617. * @param string $file (optional) On error it shows the file in which the error has been trigerred (use the "magic" constant __FILE__ as input parameter)
  618. * @param string $line (optional) On error it shows the line in which the error has been trigerred (use the "magic" constant __LINE__ as input parameter)
  619. * @return resource The returned result from the query
  620. * Note: The parameter $connection could be skipped. Here are examples of this method usage:
  621. * Database::query($query);
  622. * $result = Database::query($query);
  623. * Database::query($query, $connection);
  624. * $result = Database::query($query, $connection);
  625. * The following ways for calling this method are obsolete:
  626. * Database::query($query, __FILE__, __LINE__);
  627. * $result = Database::query($query, __FILE__, __LINE__);
  628. * Database::query($query, $connection, __FILE__, __LINE__);
  629. * $result = Database::query($query, $connection, __FILE__, __LINE__);
  630. */
  631. public static function query($query, $connection = null, $file = null, $line = null) {
  632. $use_default_connection = self::use_default_connection($connection);
  633. if ($use_default_connection) {
  634. // Let us do parameter shifting, thus the method would be similar
  635. // (in regard to parameter order) to the original function mysql_query().
  636. $line = $file;
  637. $file = $connection;
  638. $connection = null;
  639. }
  640. error_log($query);
  641. //Check if the table contains a c_ (means a course id)
  642. if (strpos($query, 'c_')) {
  643. //Check if the table contains inner joins
  644. if (
  645. strpos($query, 'INNER JOIN') === false &&
  646. strpos($query, 'inner join') === false &&
  647. strpos($query, 'left join') === false &&
  648. strpos($query, 'LEFT JOIN') === false &&
  649. strpos($query, 'c_id') === false
  650. ) {
  651. $limit_list = explode('LIMIT', $query);
  652. $limit = '';
  653. if (isset($limit_list[1])) {
  654. $limit = ' LIMIT '.$limit_list[1];
  655. }
  656. $group_list = explode('GROUP', $limit_list[0]);
  657. $group = '';
  658. if (isset($group_list[1])) {
  659. $group = ' GROUP '.$group_list[1];
  660. }
  661. $order_list = explode('ORDER', $group_list[0]);
  662. $order = '';
  663. if (isset($order_list[1])) {
  664. $order = ' ORDER '.$order_list[1];
  665. }
  666. $where_list = explode('WHERE', $order_list[0]);
  667. $select = $where_list[0];
  668. if (isset($where_list[1])) {
  669. $where = $where_list[1].' AND c_id = '.api_get_course_int_id().' ';
  670. $query = $select.' WHERE '.$where.' '.$order.$group.$limit;
  671. //echo ($query );var_dump('julio');
  672. //exit;
  673. } else {
  674. //check if order ?
  675. $query = $select. ' WHERE 1 AND c_id = '.api_get_course_int_id().' '.$order.$group.$limit;
  676. }
  677. }
  678. //echo ($query);var_dump('d');
  679. }
  680. if (!($result = $use_default_connection ? @mysql_query($query) : @mysql_query($query, $connection))) {
  681. $backtrace = debug_backtrace(); // Retrieving information about the caller statement.
  682. if (isset($backtrace[0])) {
  683. $caller = & $backtrace[0];
  684. } else {
  685. $caller = array();
  686. }
  687. if (isset($backtrace[1])) {
  688. $owner = & $backtrace[1];
  689. } else {
  690. $owner = array();
  691. }
  692. if (empty($file)) {
  693. $file = $caller['file'];
  694. }
  695. if (empty($line) && $line !== false) {
  696. $line = $caller['line'];
  697. }
  698. $type = $owner['type'];
  699. $function = $owner['function'];
  700. $class = $owner['class'];
  701. $server_type = api_get_setting('server_type');
  702. if (!empty($line) && !empty($server_type) && $server_type != 'production') {
  703. $info = '<pre>' .
  704. '<strong>DATABASE ERROR #'.self::errno($connection).':</strong><br /> ' .
  705. self::remove_XSS(self::error($connection)) . '<br />' .
  706. '<strong>QUERY :</strong><br /> ' .
  707. self::remove_XSS($query) . '<br />' .
  708. '<strong>FILE :</strong><br /> ' .
  709. (empty($file) ? ' unknown ' : $file) . '<br />' .
  710. '<strong>LINE :</strong><br /> ' .
  711. (empty($line) ? ' unknown ' : $line) . '<br />';
  712. if (empty($type)) {
  713. if (!empty($function)) {
  714. $info .= '<strong>FUNCTION :</strong><br /> ' . $function;
  715. }
  716. } else {
  717. if (!empty($class) && !empty($function)) {
  718. $info .= '<strong>CLASS :</strong><br /> ' . $class . '<br />';
  719. $info .= '<strong>METHOD :</strong><br /> ' . $function;
  720. }
  721. }
  722. $info .= '</pre>';
  723. echo $info;
  724. }
  725. }
  726. return $result;
  727. }
  728. /**
  729. * Selects a database.
  730. * @param string $database_name The name of the database that is to be selected.
  731. * @param resource $connection (optional) The database server connection, for detailed description see the method query().
  732. * @return bool Returns TRUE on success or FALSE on failure.
  733. */
  734. public static function select_db($database_name, $connection = null) {
  735. return self::use_default_connection($connection) ? mysql_select_db($database_name) : mysql_select_db($database_name, $connection);
  736. }
  737. /**
  738. * Stores a query result into an array.
  739. *
  740. * @author Olivier Brouckaert
  741. * @param resource $result - the return value of the query
  742. * @param option BOTH, ASSOC, or NUM
  743. * @return array - the value returned by the query
  744. */
  745. public static function store_result($result, $option = 'BOTH') {
  746. $array = array();
  747. if ($result !== false) { // For isolation from database engine's behaviour.
  748. while ($row = self::fetch_array($result, $option)) {
  749. $array[] = $row;
  750. }
  751. }
  752. return $array;
  753. }
  754. /*
  755. Encodings and collations supported by MySQL database server
  756. */
  757. /**
  758. * Checks whether a given encoding is supported by the database server.
  759. * @param string $encoding The encoding (a system conventional id, for example 'UTF-8') to be checked.
  760. * @return bool Returns a boolean value as a check-result.
  761. * @author Ivan Tcholakov
  762. */
  763. public static function is_encoding_supported($encoding) {
  764. static $supported = array();
  765. if (!isset($supported[$encoding])) {
  766. $supported[$encoding] = false;
  767. if (strlen($db_encoding = self::to_db_encoding($encoding)) > 0) {
  768. if (self::num_rows(self::query("SHOW CHARACTER SET WHERE Charset = '".self::escape_string($db_encoding)."';")) > 0) {
  769. $supported[$encoding] = true;
  770. }
  771. }
  772. }
  773. return $supported[$encoding];
  774. }
  775. /**
  776. * Constructs a SQL clause about default character set and default collation for newly created databases and tables.
  777. * Example: Database::make_charset_clause('UTF-8', 'bulgarian') returns
  778. * DEFAULT CHARACTER SET `utf8` DEFAULT COLLATE `utf8_general_ci`
  779. * @param string $encoding (optional) The default database/table encoding (a system conventional id) to be used.
  780. * @param string $language (optional) Language (a system conventional id) used for choosing language sensitive collation (if it is possible).
  781. * @return string Returns the constructed SQL clause or empty string if $encoding is not correct or is not supported.
  782. * @author Ivan Tcholakov
  783. */
  784. public static function make_charset_clause($encoding = null, $language = null) {
  785. if (empty($encoding)) {
  786. $encoding = api_get_system_encoding();
  787. }
  788. if (empty($language)) {
  789. $language = api_get_interface_language();
  790. }
  791. $charset_clause = '';
  792. if (self::is_encoding_supported($encoding)) {
  793. $db_encoding = Database::to_db_encoding($encoding);
  794. $charset_clause .= " DEFAULT CHARACTER SET `".$db_encoding."`";
  795. $db_collation = Database::to_db_collation($encoding, $language);
  796. if (!empty($db_collation)) {
  797. $charset_clause .= " DEFAULT COLLATE `".$db_collation."`";
  798. }
  799. }
  800. return $charset_clause;
  801. }
  802. /**
  803. * Converts an encoding identificator to MySQL-specific encoding identifictor,
  804. * i.e. 'UTF-8' --> 'utf8'.
  805. * @param string $encoding The conventional encoding identificator.
  806. * @return string Returns the corresponding MySQL-specific encoding identificator if any, otherwise returns NULL.
  807. * @author Ivan Tcholakov
  808. */
  809. public static function to_db_encoding($encoding) {
  810. static $result = array();
  811. if (!isset($result[$encoding])) {
  812. $result[$encoding] = null;
  813. $encoding_map = & self::get_db_encoding_map();
  814. foreach ($encoding_map as $key => $value) {
  815. if (api_equal_encodings($encoding, $key)) {
  816. $result[$encoding] = $value;
  817. break;
  818. }
  819. }
  820. }
  821. return $result[$encoding];
  822. }
  823. /**
  824. * Converts a MySQL-specific encoding identifictor to conventional encoding identificator,
  825. * i.e. 'utf8' --> 'UTF-8'.
  826. * @param string $encoding The MySQL-specific encoding identificator.
  827. * @return string Returns the corresponding conventional encoding identificator if any, otherwise returns NULL.
  828. * @author Ivan Tcholakov
  829. */
  830. public static function from_db_encoding($db_encoding) {
  831. static $result = array();
  832. if (!isset($result[$db_encoding])) {
  833. $result[$db_encoding] = null;
  834. $encoding_map = & self::get_db_encoding_map();
  835. foreach ($encoding_map as $key => $value) {
  836. if (strtolower($db_encoding) == $value) {
  837. $result[$db_encoding] = $key;
  838. break;
  839. }
  840. }
  841. }
  842. return $result[$db_encoding];
  843. }
  844. /**
  845. * Chooses the default MySQL-specific collation from given encoding and language.
  846. * @param string $encoding A conventional encoding id, i.e. 'UTF-8'
  847. * @param string $language (optional) A conventional for the system language id, i.e. 'bulgarian'. If it is empty, the chosen collation is the default server value corresponding to the given encoding.
  848. * @return string Returns a suitable default collation, for example 'utf8_general_ci', or NULL if collation was not found.
  849. * @author Ivan Tcholakov
  850. */
  851. public static function to_db_collation($encoding, $language = null) {
  852. static $result = array();
  853. if (!isset($result[$encoding][$language])) {
  854. $result[$encoding][$language] = null;
  855. if (self::is_encoding_supported($encoding)) {
  856. $db_encoding = self::to_db_encoding($encoding);
  857. if (!empty($language)) {
  858. $lang = api_purify_language_id($language);
  859. $res = self::check_db_collation($db_encoding, $lang);
  860. if (empty($res)) {
  861. $db_collation_map = & self::get_db_collation_map();
  862. if (isset($db_collation_map[$lang])) {
  863. $res = self::check_db_collation($db_encoding, $db_collation_map[$lang]);
  864. }
  865. }
  866. if (empty($res)) {
  867. $res = self::check_db_collation($db_encoding, null);
  868. }
  869. $result[$encoding][$language] = $res;
  870. } else {
  871. $result[$encoding][$language] = self::check_db_collation($db_encoding, null);
  872. }
  873. }
  874. }
  875. return $result[$encoding][$language];
  876. }
  877. /*
  878. Private methods
  879. You should not access these from outside the class
  880. No effort is made to keep the names / results the same.
  881. */
  882. /**
  883. * Glues a course database.
  884. * glue format from local.inc.php.
  885. */
  886. private static function glue_course_database_name($database_name) {
  887. return self::get_course_table_prefix().$database_name.self::get_database_glue();
  888. }
  889. /**
  890. * @param string $database_name, can be empty to use current course db
  891. *
  892. * @return the glued parameter if it is not empty,
  893. * or the current course database (glued) if the parameter is empty.
  894. */
  895. private static function fix_database_parameter($database_name) {
  896. if (empty($database_name)) {
  897. $course_info = api_get_course_info();
  898. return $course_info['dbNameGlu'];
  899. }
  900. return self::glue_course_database_name($database_name);
  901. }
  902. /**
  903. * Structures a course database and table name to ready them
  904. * for querying. The course database parameter is considered glued:
  905. * e.g. COURSE001`.`
  906. */
  907. private static function format_glued_course_table_name($database_name_with_glue, $table) {
  908. return '`'.$database_name_with_glue.$table.'`';
  909. }
  910. /**
  911. * Structures a database and table name to ready them
  912. * for querying. The database parameter is considered not glued,
  913. * just plain e.g. COURSE001
  914. */
  915. private static function format_table_name($database, $table) {
  916. global $_configuration;
  917. if ($_configuration['single_database']) {
  918. $table_name = '`'.$database.'`.`'.$table.'`';
  919. } else {
  920. $table_name = '`'.$database.$_configuration['db_glue'].$table.'`';
  921. }
  922. return $table_name;
  923. }
  924. /**
  925. * This private method is to be used by the other methods in this class for
  926. * checking whether the input parameter $connection actually has been provided.
  927. * If the input parameter connection is not a resource or if it is not FALSE (in case of error)
  928. * then the default opened connection should be used by the called method.
  929. * @param resource/boolean $connection The checked parameter $connection.
  930. * @return boolean TRUE means that calling method should use the default connection.
  931. * FALSE means that (valid) parameter $connection has been provided and it should be used.
  932. */
  933. private static function use_default_connection($connection) {
  934. return !is_resource($connection) && $connection !== false;
  935. }
  936. /**
  937. * This private method tackles the XSS injections. It is similar to Security::remove_XSS() and works always,
  938. * including the time of initialization when the class Security has not been loaded yet.
  939. * @param string The input variable to be filtered from XSS, in this class it is expected to be a string.
  940. * @return string Returns the filtered string as a result.
  941. */
  942. private static function remove_XSS(& $var) {
  943. return class_exists('Security') ? Security::remove_XSS($var) : @htmlspecialchars($var, ENT_QUOTES, api_get_system_encoding());
  944. }
  945. /**
  946. * This private method encapsulates a table with relations between
  947. * conventional and MuSQL-specific encoding identificators.
  948. * @author Ivan Tcholakov
  949. */
  950. private static function & get_db_encoding_map() {
  951. static $encoding_map = array(
  952. 'ARMSCII-8' => 'armscii8',
  953. 'BIG5' => 'big5',
  954. 'BINARY' => 'binary',
  955. 'CP866' => 'cp866',
  956. 'EUC-JP' => 'ujis',
  957. 'EUC-KR' => 'euckr',
  958. 'GB2312' => 'gb2312',
  959. 'GBK' => 'gbk',
  960. 'ISO-8859-1' => 'latin1',
  961. 'ISO-8859-2' => 'latin2',
  962. 'ISO-8859-7' => 'greek',
  963. 'ISO-8859-8' => 'hebrew',
  964. 'ISO-8859-9' => 'latin5',
  965. 'ISO-8859-13' => 'latin7',
  966. 'ISO-8859-15' => 'latin1',
  967. 'KOI8-R' => 'koi8r',
  968. 'KOI8-U' => 'koi8u',
  969. 'SHIFT-JIS' => 'sjis',
  970. 'TIS-620' => 'tis620',
  971. 'US-ASCII' => 'ascii',
  972. 'UTF-8' => 'utf8',
  973. 'WINDOWS-1250' => 'cp1250',
  974. 'WINDOWS-1251' => 'cp1251',
  975. 'WINDOWS-1252' => 'latin1',
  976. 'WINDOWS-1256' => 'cp1256',
  977. 'WINDOWS-1257' => 'cp1257'
  978. );
  979. return $encoding_map;
  980. }
  981. /**
  982. * A helper language id translation table for choosing some collations.
  983. * @author Ivan Tcholakov
  984. */
  985. private static function & get_db_collation_map() {
  986. static $db_collation_map = array(
  987. 'german' => 'german2',
  988. 'simpl_chinese' => 'chinese',
  989. 'trad_chinese' => 'chinese',
  990. 'turkce' => 'turkish'
  991. );
  992. return $db_collation_map;
  993. }
  994. /**
  995. * Constructs a MySQL-specific collation and checks whether it is supported by the database server.
  996. * @param string $db_encoding A MySQL-specific encoding id, i.e. 'utf8'
  997. * @param string $language A MySQL-compatible language id, i.e. 'bulgarian'
  998. * @return string Returns a suitable default collation, for example 'utf8_general_ci', or NULL if collation was not found.
  999. * @author Ivan Tcholakov
  1000. */
  1001. private static function check_db_collation($db_encoding, $language) {
  1002. if (empty($db_encoding)) {
  1003. return null;
  1004. }
  1005. if (empty($language)) {
  1006. $result = self::fetch_array(self::query("SHOW COLLATION WHERE Charset = '".self::escape_string($db_encoding)."' AND `Default` = 'Yes';"), 'NUM');
  1007. return $result ? $result[0] : null;
  1008. }
  1009. $collation = $db_encoding.'_'.$language.'_ci';
  1010. $query_result = self::query("SHOW COLLATION WHERE Charset = '".self::escape_string($db_encoding)."';");
  1011. while ($result = self::fetch_array($query_result, 'NUM')) {
  1012. if ($result[0] == $collation) {
  1013. return $collation;
  1014. }
  1015. }
  1016. return null;
  1017. }
  1018. /*
  1019. New useful DB functions
  1020. */
  1021. /**
  1022. * Experimental useful database insert
  1023. * @todo lot of stuff to do here
  1024. */
  1025. public static function insert($table_name, $attributes, $show_query = false) {
  1026. if (empty($attributes) || empty($table_name)) {
  1027. return false;
  1028. }
  1029. $filtred_attributes = array();
  1030. foreach($attributes as $key => $value) {
  1031. $filtred_attributes[$key] = "'".self::escape_string($value)."'";
  1032. }
  1033. $params = array_keys($filtred_attributes); //@todo check if the field exists in the table we should use a describe of that table
  1034. $values = array_values($filtred_attributes);
  1035. if (!empty($params) && !empty($values)) {
  1036. $sql = 'INSERT INTO '.$table_name.' ('.implode(',',$params).') VALUES ('.implode(',',$values).')';
  1037. $result = self::query($sql);
  1038. if ($show_query) {
  1039. echo $sql;
  1040. }
  1041. return self::get_last_insert_id();
  1042. }
  1043. return false;
  1044. }
  1045. /**
  1046. * Experimental useful database finder
  1047. * @todo lot of stuff to do here
  1048. * @todo known issues, it doesn't work when using LIKE conditions example: array('where'=>array('course_code LIKE "?%"'))
  1049. */
  1050. public static function select($columns = '*' , $table_name, $conditions = array(), $type_result = 'all', $option = 'ASSOC') {
  1051. $conditions = self::parse_conditions($conditions);
  1052. //@todo we could do a describe here to check the columns ...
  1053. $clean_columns = '';
  1054. if (is_array($columns)) {
  1055. $clean_columns = implode(',', $columns);
  1056. } else {
  1057. if ($columns == '*') {
  1058. $clean_columns = '*';
  1059. } else {
  1060. $clean_columns = (string)$columns;
  1061. }
  1062. }
  1063. $sql = "SELECT $clean_columns FROM $table_name $conditions";
  1064. //echo $sql.'<br />';
  1065. $result = self::query($sql);
  1066. $array = array();
  1067. //if (self::num_rows($result) > 0 ) {
  1068. if ($type_result == 'all') {
  1069. while ($row = self::fetch_array($result, $option)) {
  1070. if (isset($row['id'])) {
  1071. $array[$row['id']] = $row;
  1072. } else {
  1073. $array[] = $row;
  1074. }
  1075. }
  1076. } else {
  1077. $array = self::fetch_array($result, $option);
  1078. }
  1079. return $array;
  1080. }
  1081. /**
  1082. * Parses WHERE/ORDER conditions i.e array('where'=>array('id = ?' =>'4'), 'order'=>'id DESC'))
  1083. * @todo known issues, it doesn't work when using LIKE conditions example: array('where'=>array('course_code LIKE "?%"'))
  1084. * @param array
  1085. * @todo lot of stuff to do here
  1086. */
  1087. public function parse_conditions($conditions) {
  1088. if (empty($conditions)) {
  1089. return '';
  1090. }
  1091. $return_value = $where_return = '';
  1092. foreach ($conditions as $type_condition => $condition_data) {
  1093. if ($condition_data == false) {
  1094. continue;
  1095. }
  1096. $type_condition = strtolower($type_condition);
  1097. switch($type_condition) {
  1098. case 'where':
  1099. foreach ($condition_data as $condition => $value_array) {
  1100. if (is_array($value_array)) {
  1101. $clean_values = array();
  1102. foreach($value_array as $item) {
  1103. $item = Database::escape_string($item);
  1104. $clean_values[]= "'$item'";
  1105. }
  1106. } else {
  1107. $value_array = Database::escape_string($value_array);
  1108. $clean_values = "'$value_array'";
  1109. }
  1110. if (!empty($condition) && !empty($clean_values)) {
  1111. //$condition = str_replace('%','@percentage@', $condition);
  1112. $condition = str_replace('?','%s', $condition); //we treat everything as string
  1113. $condition = vsprintf($condition, $clean_values);
  1114. //$condition = str_replace('@percentage@','%', $condition);
  1115. $where_return .= $condition;
  1116. }
  1117. }
  1118. if (!empty($where_return)) {
  1119. $return_value = " WHERE $where_return" ;
  1120. }
  1121. break;
  1122. case 'order':
  1123. $order_array = explode(' ', $condition_data);
  1124. if (!empty($order_array)) {
  1125. if (count($order_array) > 1 && !empty($order_array[0])) {
  1126. $order_array[0] = self::escape_string($order_array[0]);
  1127. if (!empty($order_array[1])) {
  1128. $order_array[1] = strtolower($order_array[1]);
  1129. $order = 'desc';
  1130. if (in_array($order_array[1], array('desc', 'asc'))) {
  1131. $order = $order_array[1];
  1132. }
  1133. }
  1134. $return_value .= ' ORDER BY '.$order_array[0].' '.$order;
  1135. } else {
  1136. $return_value .= ' ORDER BY '.$order_array[0].' DESC ';
  1137. }
  1138. }
  1139. break;
  1140. case 'limit':
  1141. $limit_array = explode(',', $condition_data);
  1142. if (!empty($limit_array)) {
  1143. if (count($limit_array) > 1) {
  1144. $return_value .= ' LIMIT '.intval($limit_array[0]).' , '.intval($limit_array[1]);
  1145. } else {
  1146. $return_value .= ' LIMIT '.intval($limit_array[0]);
  1147. }
  1148. }
  1149. break;
  1150. }
  1151. }
  1152. return $return_value;
  1153. }
  1154. private function parse_where_conditions($coditions) {
  1155. return self::parse_conditions(array('where'=>$coditions));
  1156. }
  1157. /**
  1158. * Experimental useful database update
  1159. * @todo lot of stuff to do here
  1160. */
  1161. public static function delete($table_name, $where_conditions, $show_query = false) {
  1162. $result = false;
  1163. $where_return = self::parse_where_conditions($where_conditions);
  1164. $sql = "DELETE FROM $table_name $where_return ";
  1165. if ($show_query) { echo $sql; echo '<br />'; }
  1166. $result = self::query($sql);
  1167. $affected_rows = self::affected_rows();
  1168. //@todo should return affected_rows for
  1169. return $affected_rows;
  1170. }
  1171. /**
  1172. * Experimental useful database update
  1173. * @param string table name use Database::get_main_table
  1174. * @param array array with values to updates, keys are the fields in the database: Example: $params['name'] = 'Julio'; $params['lastname'] = 'Montoya';
  1175. * @param array where conditions i.e array('id = ?' =>'4')
  1176. * @todo lot of stuff to do here
  1177. */
  1178. public static function update($table_name, $attributes, $where_conditions = array(), $show_query = false) {
  1179. if (!empty($table_name) && !empty($attributes)) {
  1180. $update_sql = '';
  1181. //Cleaning attributes
  1182. $count = 1;
  1183. foreach ($attributes as $key=>$value) {
  1184. $value = self::escape_string($value);
  1185. $update_sql .= "$key = '$value' ";
  1186. if ($count < count($attributes)) {
  1187. $update_sql.=', ';
  1188. }
  1189. $count++;
  1190. }
  1191. if (!empty($update_sql)) {
  1192. //Parsing and cleaning the where conditions
  1193. $where_return = self::parse_where_conditions($where_conditions);
  1194. $sql = "UPDATE $table_name SET $update_sql $where_return ";
  1195. if ($show_query) { echo $sql; echo '<br />'; }
  1196. $result = self::query($sql);
  1197. $affected_rows = self::affected_rows();
  1198. return $affected_rows;
  1199. }
  1200. }
  1201. return false;
  1202. }
  1203. /*
  1204. DEPRECATED METHODS
  1205. */
  1206. /**
  1207. * @deprecated Use api_get_language_isocode($language) instead.
  1208. */
  1209. public static function get_language_isocode($language) {
  1210. return api_get_language_isocode($language);
  1211. }
  1212. /**
  1213. * @deprecated Use Database::insert_id() instead.
  1214. */
  1215. public static function get_last_insert_id() {
  1216. return mysql_insert_id();
  1217. }
  1218. }
  1219. //end class Database