What are the maintenance steps and performance steps for Mysql server / database?
2 answers
ANALYZE TABLE scans for keys and saves data to help future JOINs. I don't know how useful / necessary it is, but I'm sure it depends on what your typical workload and table structure is.
OPTIMIZE TABLE will defragment your tables and reclaim unused space, but as you can read in the documentation, this is often unnecessary.
You can call them through SQL or using the mysqlcheck tool .
While this is not necessary in most cases, I usually plan mysqlcheck -Aao
during low periods of activity (which means weekends are weekends in my case).
+2
a source to share
<Preliminary> // --------------------------------------------- --- -------------- // PHP script is located here, you can run weekly. Just change the config information at the beginning // ------------------------------------------ ------ -------------- // ---------------------------- -------------------- -------------- // BEGIN CONFIG (I like to put this in a sep file so I can just change config for // different databases / servers // ------------------------------------- ----------- -------------- // require _once ("mysql_maintenance_config.inc"); $ _DB_Host = '127.0.0.1'; // your server ip $ _DB_Name = 'database_name_goes_here'; // db name to support $ _DB_User = 'database_user_goes_here'; $ _DB_Pass = 'pasword_goes_here'; $ _EMAIL_Admin_Addr = 'yourname@gmail.com'; // access errors / email results // PHP mail must be configured $ _LOG_TABLE_NAME =' log_mysql_maintenance '; // change this if you want results to be stored in a different table name $ _EMAIL_Results = true; $ _NO_WRITE_TO_BINLOG = false; // only set to true if the database is being replicated $ _SKIP_TABLES_WITH_LESS_THAN_X_ROWS = 100; // set to -1 to process all tables // ----------------- ------------------------------- -------------- // END CONFIG SECTION // ------------------------------------------------ -------------- $ total_query_time_for_page = 0; $ debug = false; // set debug mode if (isset ($ _ GET ['dxdb'])) {if ($ _GET ['dxdb'] == 'dcv') {$ debug = true;}} // open a connection to the database $ db = mysql_connect ($ _DB_Host, $ _DB_User, $ _DB_Pass); if (! $ db) {echo 'DATABASE_ERROR_MESSAGE'; Exit; } if (mysql_select_db ($ _ DB_Name, $ db) == false) {echo 'CANNOT_SELECT_DATABASE'; Exit; } // ----------------------------------------------- - -------------- // MYSQL MAINTENANCE TASKS // --------------------------- --------------------- -------------- // 1. Confirm / Create Logging Table // 2. Protocol process initialization // 3. Build table list // 4. Check and repair (only for MyISAM) Tables, if necessary // 5. Optimize tables // 6. Analyze tables // 7. Fill in logs and email // - ---------------------------------------------- ---- ---------- $ _PROCESS_LOG = ''; $ _PROCESS_ID = 0;$ _STARTED_ON = ''; $ _COMPLETED_ON = 0; $ _TABLE_COUNT = 0; $ _TABLES_CHECKED = 0; $ _TABLES_OPTIMIZED = 0; $ _TABLES_ANALYZED = 0; $ _ERRORS_FOUND = 0; $ _ERRORS_FIXED = 0; $ _CURRENT_STATUS = ''; $ _FINAL_STATUS = ''; // ------------------------------------------------ -------------- // 1. Check / create logging table (log table name set in sql_maintenance_config.php) // ------------- ----------------------------------- -------------- $ lgx = dbGetReturnForSQL ("SELECT COUNT (*) as ct FROM information_schema.tables WHERE table_schema = '$ _DB_Name' AND table_name = '$ _LOG_TABLE_NAME'", '', true); if ($ lgx == 0) // create a log table {$ sql = "CREATE TABLE $ _LOG_TABLE_NAME (process_id int (11) NOT NULL auto_increment,start_on varchar (24) NOT NULL default '', completed_on varchar (24) NOT NULL default '', tables_checked int (11) NOT NULL default 0, tables_optimized int (11) NOT NULL default 0, tables_analyzed int (11) NOT NULL default 0, errors_found int (11) NOT NULL default 0, errors_fixed int (11) NOT NULL default 0, current_status varchar (32) NOT NULL default '', final_status varchar (32) NOT NULL default '', process_log varchar (20000) NOT NULL default '', PRIMARY KEY (process_id)) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 "; dbExecuteSQL ($ SQL); $ _PROCESS_LOG ="FIRST RUN - CREATE REGISTRATION TABLE \ n -------------------------------------- ---- -------- \ n ";} else {$ _PROCESS_LOG =" LOG TABLE OK - STARTING PROCESS \ n ----------------------- --------------- ------------ \ n ";} // ---------------- -------------------------------- -------------- // 2. Initialization protocol // ---------------------------------------------- - -------------- $ _STARTED_ON = getLogStamp (); $ _CURRENT_STATUS = 'INIT PROCESS LOG'; $ _FINAL_STATUS = 'IN PROCESS - NO ERRORS'; $ sql = "INSERT INTO $ _LOG_TABLE_NAME (start_on, current_status, final_status, process_log) VALUES ('$ _STARTED_ON', '$ _CURRENT_STATUS', '$ _FINAL_STATUS', '$ _PROCESS_LOG') "; $ _PROCESS_LOG ')"; $ _PROCESS_ID = dbExProCESS_ID = db Log ', true);if ($ _NO_WRITE_TO_BINLOG) {$ _BINLOG = 'NO_WRITE_TO_BINLOG'; add2ProcessLog ('BINLOG WRITE DISABLED'); } else {$ _BINLOG = ''; add2ProcessLog ('BINLOG WRITE ENABLED'); } // ----------------------------------------------- - -------------- // 3. Build a table list // -------------------------- ---------------------- -------------- $ _CURRENT_STATUS = 'BUILDING TABLE LIST; add2ProcessLog ('Building Table List', true); $ sql = "SELECT TABLE_NAME, ENGINE, TABLE_ROWS FROM information_schema.tables WHERE table_schema = '$ _DB_Name'"; $ _TABLE_LIST = dbGetReturnForSQL ($ sql); if ($ _TABLE_LIST == 'x_no_data_x') // no tables found {add2ProcessLog ("ERROR - NO TABLES FOUND", false); emailLogAndExit ();} else {$ _TABLE_COUNT = count ($ _ TABLE_LIST); add2ProcessLog ($ _ TABLE_COUNT. "found found", true); } // ----------------------------------------------- - -------------- // 4. Check and repair (only for MyISAM) Tables, if necessary // ----------------- ------------------------------- -------------- $ _CURRENT_STATUS = 'CHECK AND REPAIR OF TIMES MIZAM; add2ProcessLog ("CHECK AND REPAIR MISAZM TILES", true); foreach ($ _ TABLE_LIST AS $ tbl_row) {$ tbl = $ tbl_row ['TABLE_NAME']; $ eng = $ tbl_row ['ENGINE']; $ rct = $ tbl_row ['TABLE_ROWS']; if ($ tbl! = $ _LOG_TABLE_NAME && $ eng == 'MyISAM') {$ _TABLES_CHECKED ++; add2ProcessLog ("check: $ tbl"); $ sql = "CHECK TABLE $ tbl";$ rs = dbGetReturnForSQL ($ sql); if ($ rs == 'x_no_data_x') {emailLogAndExit ("ERROR CHECK TABLE: $ tbl"); } else {$ rw = getLastRecord ($ rs); if ($ rw ['Msg_text']! = 'OK') {$ _ERRORS_FOUND ++; add2ProcessLog ('ERROR FOUND:'. $ rw ['Msg_text'], true); // problem check table - repair attempt $ sql = "REPAIR TABLE $ tbl"; $ rs = dbGetReturnForSQL ($ sql); if ($ rs == 'x_no_data_x') {emailLogAndExit ("ERROR REPAIR TABLE: $ tbl"); } else {$ rprw = getLastRecord ($ rs); if ($ rw ['Msg_text '] ==' OK ') {$ _ERRORS_FIXED ++; add2ProcessLog ('ERROR REPAIRED on'. $ tbl); } else // failed to recover the error {emailLogAndExit ("ERROR REJECTION TABLE: $ tbl |". $ rw ['Msg_text']); }}} else {add2ProcessLog ($ tbl. 'checked ok'); }}}} add2ProcessLog ("CHECK AND REPAIR TABLE METALS COMPLETED OK", true); // ------------------------------------------------ -------------- // 5. Optimizing tables // ---------------------------- -------------------- -------------- $ _CURRENT_STATUS = 'OPTIMIZE TABLES'; add2ProcessLog ("OPTIMIZE TABLES ", true); foreach ($ _ TABLE_LIST AS $ tbl_row) {$ tbl = $ tbl_row ['TABLE_NAME']; $ eng = $ tbl_row ['ENGINE']; $ rct = $ tbl_row ['TABLE_ROWS']; if ($ tbl! = $ _LOG_TABLE_NAME && intval ($ _ SKIP_TABLES_WITH_LESS_THAN_X_ROWS) 'Jacks function', '// # _ reason _ // #' => 'Because I said so) // $ new_page: if true, clear the buffer and wrap on a new page, if false, display err msg and exit (note that you should use ob_start () for this on the first page // $ dev_dump: any little information you would like in the log like the query that caused the error . this will not be displayed to the user // $ email_err: email address to send the error. if set to "devteam"then the current multicast addon will be used (i.e. devteam @ thinklinklearning.com) // FUTURE - might want to set a way to override this in the error_codes table to send to a specific developer during testing (currently it can only override email mail) // $ log_only: if true, do not display the message, return the code - can be used if we want to register global $ db, $ _EMAIL_Admin_Addr, $ _CURRENT_STATUS, $ _FINAL_STATUS here from perish (); // first log the error $ host_info = mysql_get_host_info (); $ rq = substr (var_export ($ _ REQUEST, true), 0.2000); $ rq = mysql_real_escape_string ($ rq); // add the last sql error to dev_dump $ sql_dump = $ dev_dump; // sql format $ sql_dump = str_replace ("\ r \ n", '',$ sql_dump); $ sql_dump = str_replace ("\ n", '', $ sql_dump); $ sql_dump = str_replace ("\ t", '', $ sql_dump); $ sql_dump = str_ireplace ('SELECT', "\ r \ n SELECT", $ sql_dump); $ sql_dump = str_ireplace ('FROM', "\ r \ n FROM", $ sql_dump); $ sql_dump = str_ireplace ('WHERE', "\ r \ n WHERE", $ sql_dump); $ sql_dump = str_ireplace ("INNER JOIN", "\ r \ n INNER JOIN", $ sql_dump); $ sql_dump = str_ireplace ('LEFT JOIN', "\ r \ n LEFT JOIN", $ sql_dump); $ sql_dump = str_ireplace ('AND', "\ r \ n AND", $ sql_dump); $ sql_dump = str_ireplace ('GROUP BY', "\ r \ n GROUP BY", $ sql_dump); $ sql_dump = str_ireplace ('ORDER BY', "\ r \ n ORDER BY", $ sql_dump);$ sql_dump = str_ireplace (',', "\ r \ n", $ sql_dump); $ sql_dump = str_replace ('', '', $ sql_dump); $ sql_dump = str_replace ('', '', $ sql_dump); $ sql_dump = str_replace ('', '', $ sql_dump); $ sql_err = mysql_error (); $ dev_dump = $ sql_dump. "\ r \ n \ r \ n". $ Sql_err; // echo "
$ sql_err
$ sql_dump
"; $ dev_dump_to_mail = $ host_info." \ r \ n \ r \ n ". $ Dev_dump; $ dev_dump = mysql_real_escape_string ($ dev_dump_to_mail." \ r \ n \ r \ n ". $ dev_dump); // get backtrace stack $ a = debug_backtrace (); $ stack_tr = "stack trace: \ r \ n"; $ stack_tr. = "------------ \ r \ n"; foreach ($ a as $ k => $ v) {$ stack_tr. = "file: $ v [file] \ r \ n"; $ stack_tr. = "string: $ v [string] \ r \ n"; $ stack_tr. = "func: $ v [function] \ r \ n "; $ args = $ v ['args']; foreach ($ args as $ a_label => $ a_val) {$ stack_tr. =" $ a_label => $ a_val \ r \ n ";} $ stack_tr. =" // # \ r \ n ";} $ pg = mysql_real_escape_string ($ _ SERVER ['PHP_SELF']); $ stack_tr_to_mail = $ stack_tr;$ stack_tr = mysql_real_escape_string ($ stack_tr); if ($ err_id == '') {$ err_id = 'GENERAL_ERROR'; } $ email_err = $ _EMAIL_Admin_Addr; // always send error messages to the administrator $ err_html = ''; // email error on request if ($ email_err! = '') {$ pg = explode ('/', $ pg); $ pg = $ pg [count ($ pg) -1]; if (strpos (strtoupper ($ _ SERVER ['SERVER_NAME']), 'DEV')> 2) {$ fsrv = '(DEV)'; } else {$ fsrv = ''; } mail ($ email_err, "Error $ fsrv: $ pg - $ err_id", "PHP_SELF \ t $ _SERVER [PHP_SELF] \ P \ n ------------------- ----------------- \ n \ n error_id: \ t $ err_id \ n \ n \ n \ n --------------- --------------------- \ n \ n sql_err: \ t $ sql_err \ n \ n \ n \ n ----------- ------------------------- \ n \ n sql_dump:\ n \ n $ sql_dump \ n \ n \ n \ n ------------------------------------ \ n \ n last_db_host: \ t $ host_info \ n \ n \ n \ n -------------------------------- ---- \ n \ n stack_tr: \ n $ Stack_tr_to_mail \ n \ n \ n \ n ---------------------------- -------- \ n \ n dev_dump: \ n $ dev_dump_to_mail \ n \ n \ n \ n ------------------------ ------------ \ n \ n err_log_key: $ err_log_key ");} // update the process log $ _CURRENT_STATUS = 'ERROR'; $ _FINAL_STATUS = 'ERROR'; add2ProcessLog ('ERROR -'. $ sql_err, true); echo 'ERROR:'. $ pg. '-'. $ Sql_err; Exit ();} // --------------------- --------------------------- -------------- function getRequestVar ($ fieldname, $ defaultvalue, $ numeric_only = false, $ prep_for_sql_insert = false) {$ rtn = ''; if (isset ($ _ POST [$ fieldname])) {$ rtn = $ _POST [$ fieldname];} else {if (isset ($ _ GET [$ fieldname])) {$ rtn = $ _GET [$ fieldname]; } else {$ rtn = $ defaultvalue; }} if ($ numeric_only == true) {if (is_numeric ($ rtn) == false) {$ rtn = $ defaultvalue;}} if ($ prep_for_sql_insert == true) {$ rtn = "'". mysql_real_escape_string ($ rtn). "'"; } return ($ RTN); } // ----------------------------------------------- - -------------- prepForSQL function ($ rtn_val, $ numeric_only = false) {if ($ numeric_only == true) {if (is_numeric ($ rtn_val) == false) {$ rtn_val = -999;}} else {// removing chrs that we do not allow in the system $ rtn_val = str_replace ("'", ", $ rtn_val); $ rtn_val = str_replace (' ''," ",$ rtn_val); $ rtn_val = str_replace ("\\", "", $ rtn_val); $ rtn_val = str_replace (';', "", $ rtn_val); $ rtn_val = "'". mysql_real_escape_string ($ rtn_val). "'"; } return ($ rtn_val); } // ----------------------------------------------- - -------------- dbGetReturnForSQL function ($ sql, $ field_for_key = '', $ get_scalar_value = false, $ default_value = 'x_no_data_x') {/ * this function works for scalar, one-line and multi-line queries if $ field_for_key = '', then use the index if $ get_scalar_value = true,then return only the 1st row only in an array (not nested in an array of strings) or if only one value returns only a value (no array) - the first row should only be set to true if there is no data, the default_value will be returned if empty, "x_no_data_x" will be returned $ error_id_on_fail, error ID to pass to displayErrorMsg (default - GENERAL_ERROR) * / global $ db, $ debug, $ total_query_time_for_page; $ loc_ptr = 0; $ loc_key = ''; $ loc_arr = array (); $ loc_val = ''; $ use_srv = 'MASTER'; $ error_id_on_fail = ''; if ($ debug) {echo "error identifier to pass to displayErrorMsg (by default - GENERAL_ERROR) * / global $ db, $ debug, $ total_query_time_for_page; $ loc_ptr = 0; $ loc_key = ''; $ loc_arr = array (); $ loc_val = ''; $ use_srv = 'MASTER'; $ error_id_on_fail = ''; if ($ debug) {echo "error identifier to pass to displayErrorMsg (by default - GENERAL_ERROR) * / global $ db, $ debug, $ total_query_time_for_page; $ loc_ptr = 0; $ loc_key = ''; $ loc_arr = array (); $ loc_val = ''; $ use_srv = 'MASTER'; $ error_id_on_fail = ''; if ($ debug) {echo "
QUERY:
\ n ". Ereg_replace (" \ t "," ", $ sql)." \ n \ n
\ n ";} if ($ debug) {$ start_time = microtime (true);} $ result = mysql_query ($ sql, $ db) OR displayErrorMsg ($ error_id_on_fail, '', '', false, $ sql, 'richard.varno @ gmail.com', false); if ($ debug) {$ end_time = microtime (true); $ rows_rtn = mysql_num_rows ($ result); $ qry_time = $ end_time - $ start_time; $ total_query_time_for_page = $ total_query_time_for_page + $ qry_time; $ host_info = mysql_get_host_info (); echo " $ rows_rtn string returned in $ qry_time seconds from $ host_info ($ use_srv)
total query time: past: $ total_query_time_for_page seconds
\ n ";} while ($ rw = mysql_fetch_assoc ($ result)) {if ($ get_scalar_value == true) {// show return val if debug = true if ($ debug) {echo" Scalar value
$ fld_val) {echo "
$ fld_key$ fld_val \ n ";} echo" \ n ";} if (count ($ rw) == 1) {$ rw_key = array_keys ($ rw); $ loc_val = $ rw [$ rw_key [0]]; mysql_free_result ( $ result); return ($ loc_val);} else {mysql_free_result ($ result); return ($ RW);}} else {if ($ field_for_key == '') // use pointer pointer {$ loc_key = $ loc_ptr; } else // using the field {$ loc_key = $ rw [$ field_for_key];} $ loc_arr [$ loc_key] = $ rw; $ Loc_ptr ++;}} mysql_free_result ($ result); // if no results were found,return the default value if ($ loc_ptr == 0) {$ loc_arr = $ default_value; } more {// show return val if debug = true (first 50 rows only) if ($ debug) {if ($ rows_rtn> 50) {echo "Display rows 1 to $ rows_rtn from $ rows_rtn
$ fld_val) {echo " $ fld_key\ N ";} echo" \ n "; // show data $ cur_row = 0; foreach ($ loc_arr as $ ptr => $ row) {echo" \ n "; foreach ($ rw as $ fld_key => $ fld_val ) {echo "$ row [$ fld_key] \ n";} echo "\ n"; $ Cur_row ++; if ($ cur_row> $ max_row_to_display) {break;}} echo "\ n";}} return ($ loc_arr);} // -------------------------------------------- ---- -------------- dbExecuteSQL function ($ sql, $ return_insert_id = false) {/ * this function will also execute the SQL Statement against the underlying database and return either the affected rows, either an insert identifier,if the request is $ error_id_on_fail, the error ID to pass to displayErrorMsg (by default - GENERAL_ERROR) * / global $ db, $ debug, $ total_query_time_for_page; $ loc_ptr = 0; $ loc_insert_id = 0; $ loc_rows_affected = 0; $ error_id_on_fail = ''; if ($ debug) {echo "QUERY:
\ n
\ n ";} if ($ debug) {$ start_time = microtime (true);} $ result = mysql_query ($ sql, $ db) OR displayErrorMsg ($ error_id_on_fail, '', '', false, $ sql, 'richard.varno @ gmail.com', false); $ loc_insert_id = mysql_insert_id ($ db); $ loc_rows_affected = mysql_affected_rows ($ db); $ use_srv = 'MASTER'; if ($ debug) {$ end_time = microtime (true ); $ qry_time = $ end_time - $ start_time; $ total_query_time_for_page = $ total_query_time_for_page + $ qry_time; $ host_info = mysql_get_host_info ($ db); echo "$ loc_rows_affected row (s), affected by $ qry_timer seconds) from $ qry_timer seconds) total query time elapsed: $ total_query_time_for_page seconds
$ sql_err
$ sql_dump
"; $ dev_dump_to_mail = $ host_info." \ r \ n \ r \ n ". $ Dev_dump; $ dev_dump = mysql_real_escape_string ($ dev_dump_to_mail." \ r \ n \ r \ n ". $ dev_dump); // get backtrace stack $ a = debug_backtrace (); $ stack_tr = "stack trace: \ r \ n"; $ stack_tr. = "------------ \ r \ n"; foreach ($ a as $ k => $ v) {$ stack_tr. = "file: $ v [file] \ r \ n"; $ stack_tr. = "string: $ v [string] \ r \ n"; $ stack_tr. = "func: $ v [function] \ r \ n "; $ args = $ v ['args']; foreach ($ args as $ a_label => $ a_val) {$ stack_tr. =" $ a_label => $ a_val \ r \ n ";} $ stack_tr. =" // # \ r \ n ";} $ pg = mysql_real_escape_string ($ _ SERVER ['PHP_SELF']); $ stack_tr_to_mail = $ stack_tr;$ stack_tr = mysql_real_escape_string ($ stack_tr); if ($ err_id == '') {$ err_id = 'GENERAL_ERROR'; } $ email_err = $ _EMAIL_Admin_Addr; // always send error messages to the administrator $ err_html = ''; // email error on request if ($ email_err! = '') {$ pg = explode ('/', $ pg); $ pg = $ pg [count ($ pg) -1]; if (strpos (strtoupper ($ _ SERVER ['SERVER_NAME']), 'DEV')> 2) {$ fsrv = '(DEV)'; } else {$ fsrv = ''; } mail ($ email_err, "Error $ fsrv: $ pg - $ err_id", "PHP_SELF \ t $ _SERVER [PHP_SELF] \ P \ n ------------------- ----------------- \ n \ n error_id: \ t $ err_id \ n \ n \ n \ n --------------- --------------------- \ n \ n sql_err: \ t $ sql_err \ n \ n \ n \ n ----------- ------------------------- \ n \ n sql_dump:\ n \ n $ sql_dump \ n \ n \ n \ n ------------------------------------ \ n \ n last_db_host: \ t $ host_info \ n \ n \ n \ n -------------------------------- ---- \ n \ n stack_tr: \ n $ Stack_tr_to_mail \ n \ n \ n \ n ---------------------------- -------- \ n \ n dev_dump: \ n $ dev_dump_to_mail \ n \ n \ n \ n ------------------------ ------------ \ n \ n err_log_key: $ err_log_key ");} // update the process log $ _CURRENT_STATUS = 'ERROR'; $ _FINAL_STATUS = 'ERROR'; add2ProcessLog ('ERROR -'. $ sql_err, true); echo 'ERROR:'. $ pg. '-'. $ Sql_err; Exit ();} // --------------------- --------------------------- -------------- function getRequestVar ($ fieldname, $ defaultvalue, $ numeric_only = false, $ prep_for_sql_insert = false) {$ rtn = ''; if (isset ($ _ POST [$ fieldname])) {$ rtn = $ _POST [$ fieldname];} else {if (isset ($ _ GET [$ fieldname])) {$ rtn = $ _GET [$ fieldname]; } else {$ rtn = $ defaultvalue; }} if ($ numeric_only == true) {if (is_numeric ($ rtn) == false) {$ rtn = $ defaultvalue;}} if ($ prep_for_sql_insert == true) {$ rtn = "'". mysql_real_escape_string ($ rtn). "'"; } return ($ RTN); } // ----------------------------------------------- - -------------- prepForSQL function ($ rtn_val, $ numeric_only = false) {if ($ numeric_only == true) {if (is_numeric ($ rtn_val) == false) {$ rtn_val = -999;}} else {// removing chrs that we do not allow in the system $ rtn_val = str_replace ("'", ", $ rtn_val); $ rtn_val = str_replace (' ''," ",$ rtn_val); $ rtn_val = str_replace ("\\", "", $ rtn_val); $ rtn_val = str_replace (';', "", $ rtn_val); $ rtn_val = "'". mysql_real_escape_string ($ rtn_val). "'"; } return ($ rtn_val); } // ----------------------------------------------- - -------------- dbGetReturnForSQL function ($ sql, $ field_for_key = '', $ get_scalar_value = false, $ default_value = 'x_no_data_x') {/ * this function works for scalar, one-line and multi-line queries if $ field_for_key = '', then use the index if $ get_scalar_value = true,then return only the 1st row only in an array (not nested in an array of strings) or if only one value returns only a value (no array) - the first row should only be set to true if there is no data, the default_value will be returned if empty, "x_no_data_x" will be returned $ error_id_on_fail, error ID to pass to displayErrorMsg (default - GENERAL_ERROR) * / global $ db, $ debug, $ total_query_time_for_page; $ loc_ptr = 0; $ loc_key = ''; $ loc_arr = array (); $ loc_val = ''; $ use_srv = 'MASTER'; $ error_id_on_fail = ''; if ($ debug) {echo "error identifier to pass to displayErrorMsg (by default - GENERAL_ERROR) * / global $ db, $ debug, $ total_query_time_for_page; $ loc_ptr = 0; $ loc_key = ''; $ loc_arr = array (); $ loc_val = ''; $ use_srv = 'MASTER'; $ error_id_on_fail = ''; if ($ debug) {echo "error identifier to pass to displayErrorMsg (by default - GENERAL_ERROR) * / global $ db, $ debug, $ total_query_time_for_page; $ loc_ptr = 0; $ loc_key = ''; $ loc_arr = array (); $ loc_val = ''; $ use_srv = 'MASTER'; $ error_id_on_fail = ''; if ($ debug) {echo "
QUERY:
\ n ". Ereg_replace (" \ t "," ", $ sql)." \ n \ n
\ n ";} if ($ debug) {$ start_time = microtime (true);} $ result = mysql_query ($ sql, $ db) OR displayErrorMsg ($ error_id_on_fail, '', '', false, $ sql, 'richard.varno @ gmail.com', false); if ($ debug) {$ end_time = microtime (true); $ rows_rtn = mysql_num_rows ($ result); $ qry_time = $ end_time - $ start_time; $ total_query_time_for_page = $ total_query_time_for_page + $ qry_time; $ host_info = mysql_get_host_info (); echo " $ rows_rtn string returned in $ qry_time seconds from $ host_info ($ use_srv)
total query time: past: $ total_query_time_for_page seconds
\ n ";} while ($ rw = mysql_fetch_assoc ($ result)) {if ($ get_scalar_value == true) {// show return val if debug = true if ($ debug) {echo" Scalar value
$ fld_val) {echo "
$ fld_key$ fld_val \ n ";} echo" \ n ";} if (count ($ rw) == 1) {$ rw_key = array_keys ($ rw); $ loc_val = $ rw [$ rw_key [0]]; mysql_free_result ( $ result); return ($ loc_val);} else {mysql_free_result ($ result); return ($ RW);}} else {if ($ field_for_key == '') // use pointer pointer {$ loc_key = $ loc_ptr; } else // using the field {$ loc_key = $ rw [$ field_for_key];} $ loc_arr [$ loc_key] = $ rw; $ Loc_ptr ++;}} mysql_free_result ($ result); // if no results were found,return the default value if ($ loc_ptr == 0) {$ loc_arr = $ default_value; } more {// show return val if debug = true (first 50 rows only) if ($ debug) {if ($ rows_rtn> 50) {echo "Display rows 1 to $ rows_rtn from $ rows_rtn
$ fld_val) {echo " $ fld_key\ N ";} echo" \ n "; // show data $ cur_row = 0; foreach ($ loc_arr as $ ptr => $ row) {echo" \ n "; foreach ($ rw as $ fld_key => $ fld_val ) {echo "$ row [$ fld_key] \ n";} echo "\ n"; $ Cur_row ++; if ($ cur_row> $ max_row_to_display) {break;}} echo "\ n";}} return ($ loc_arr);} // -------------------------------------------- ---- -------------- dbExecuteSQL function ($ sql, $ return_insert_id = false) {/ * this function will also execute the SQL Statement against the underlying database and return either the affected rows, either an insert identifier,if the request is $ error_id_on_fail, the error ID to pass to displayErrorMsg (by default - GENERAL_ERROR) * / global $ db, $ debug, $ total_query_time_for_page; $ loc_ptr = 0; $ loc_insert_id = 0; $ loc_rows_affected = 0; $ error_id_on_fail = ''; if ($ debug) {echo "QUERY:
\ n
\ n ". ereg_replace (" \ t "," ", $ sql)." \ n\ n \ n ";} if ($ debug) {$ start_time = microtime (true);} $ result = mysql_query ($ sql, $ db) OR displayErrorMsg ($ error_id_on_fail, '', '', false, $ sql, 'richard.varno @ gmail.com', false); $ loc_insert_id = mysql_insert_id ($ db); $ loc_rows_affected = mysql_affected_rows ($ db); $ use_srv = 'MASTER'; if ($ debug) {$ end_time = microtime (true ); $ qry_time = $ end_time - $ start_time; $ total_query_time_for_page = $ total_query_time_for_page + $ qry_time; $ host_info = mysql_get_host_info ($ db); echo "$ loc_rows_affected row (s), affected by $ qry_timer seconds) from $ qry_timer seconds) total query time elapsed: $ total_query_time_for_page seconds
+2
a source to share