www.webdeveloper.com
Results 1 to 5 of 5

Thread: [RESOLVED] List mysql triggers using php

  1. #1
    Join Date
    Jul 2008
    Posts
    37

    resolved [RESOLVED] List mysql triggers using php

    How can I list all the triggers and their actions from an mysql database using PHP?
    Last edited by wspeeckaert; 09-02-2013 at 10:58 AM.

  2. #2
    Join Date
    Jul 2008
    Posts
    37
    Here is what I have so far:
    $sql="SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='database'";
    $result=mysql_query($sql);
    while ($result && $row=mysql_fetch_assoc($result)) {
    $sql_create = "CREATE TRIGGER `{$row['TRIGGER_NAME']}` {$row['ACTION_TIMING']} {$row['EVENT_MANIPULATION']} ON `{$row['EVENT_OBJECT_TABLE']}`";
    $sql_create .= "\n".str_replace("\t",'',$row['ACTION_STATEMENT'])."\n";
    $return .= "\n/*\n".$sql_create."*/\n";
    }


    Here is a sample result:

    CREATE TRIGGER `trg_invoice_detail_before_insert` BEFORE INSERT ON `invoice_details`
    BEGIN
    SET NEW.`total`=NEW.`num_items` * NEW.`cost`;
    END


    However it does not work when I run it as a query. For the moment this is not a problem since my main intent was to extract the trigger if I have to rebuild the database from a backup.
    Last edited by wspeeckaert; 09-09-2013 at 03:34 AM.

  3. #3
    Join Date
    Jul 2008
    Posts
    37
    Here is the code I use to backup a database to a zipped file. Each table is saved to a separate SQL file. A create table SQL file is also created and the triggers are stored in that table. The resulting SQL files are then deleted AFTER the zip file is closed. I then email the zip file to myself.

    $except_tables = array('emails','folders','log','logins','mobile','usersonline');

    function backup_tables($tables,$create_filename,$except_tables)
    { $files=array();
    if ($create_filename!='') $files[]= $create_filename;
    $folder=$_SERVER['DOCUMENT_ROOT'].'/some folder';
    echo 'Backing up: Database<br />';
    connecttodb();mysql_select_db(DATABASE);
    if($tables == '*')
    { $prefix='ALL_';
    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result)){$tables[] = $row[0];}
    }
    else
    {$tables = is_array($tables) ? $tables : explode(',',$tables); $prefix='';}
    $zip = new ZipArchive();
    $zip_filename = $folder.'/'.$prefix.'DB_'.date('Ymd').'.zip'; if (file_exists($zip_filename)) unlink($zip_filename);
    if ($zip->open($zip_filename, ZIPARCHIVE::CREATE)!==true) {die("cannot open <$zip_filename><br />\n");} ;
    $zip->addFile($create_filename,basename($create_filename));
    echo 'CREATE ALL TABLES added to ZIP file<br />';
    $n=0;
    foreach($tables as $table)
    {
    $name=$table;
    $destination_path=$folder;
    if (!file_exists($destination_path)) mkdir($destination_path, 0755, true);
    $backup_filename=$destination_path.'/'.$name.'.sql';
    $files[] = $backup_filename;
    $handle = fopen($backup_filename,'w+');
    $result = mysql_query('SELECT * FROM '.$table);
    if ($result) {
    $num_fields = mysql_num_fields($result);
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return = "\n/*\nDROP TABLE IF EXISTS `$table`;\n*/\n";
    $return.= "/*\n".$row2[1].";\n*/\n\n";
    fwrite($handle,$return);
    } else { $num_fields=0; echo "$table PROBLEM<br />";}
    $n++;
    if (!in_array($table, $except_tables))
    {
    for ($i = 0; $i < $num_fields; $i++)
    {
    while($row = mysql_fetch_row($result))
    {
    $return = 'INSERT INTO '.$table.' VALUES(';
    for($j=0; $j<$num_fields; $j++)
    {
    $row[$j] = addslashes($row[$j]);
    $row[$j] = preg_replace("#\n#","\\n",$row[$j]);
    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
    if ($j<($num_fields-1)) { $return.= ','; }
    }
    $return.= ");\n";
    fwrite($handle,$return);
    }
    }
    } // not exception
    else fwrite($handle,"\n/*\nOmitting DATA - not critical\n*/\n");
    $return ="\n";
    fwrite($handle,$return);
    fclose($handle);
    $zip->addFile($backup_filename,basename($backup_filename));
    } // for each
    //save file
    $zip->close();
    echo "$n tables backed up<br />Deleting SQL files<br />";
    $n=0;
    $sql_filesize=0;
    foreach($files as $file) {$sql_filesize += filesize($file); if (unlink($file)) $n++;}
    echo "$n SQL files deleted (should be 1 more than tables)<br />";
    if (file_exists($zip_filename)) {
    $zip_filesize = filesize($zip_filename);
    $compressed =100-round($zip_filesize/$sql_filesize*100,0);
    $attach = array($zip_filename);
    $email = SPDS_EMAIL;
    if (send_email($email, 'webmaster@' ,'Webmaster','DB SQL Zip Backup', ' DB SQL Zip backup', $attach,'application/octet-stream'))
    echo "SQL Zip file emailed to $email<br />"; else echo 'SQL Zip file NOT emailed!<br />' ;
    echo 'Deleting zip file on server<br />';
    echo "Total SQL files size: $sql_filesize bytes<br />Zip filesize: $zip_filesize bytes<br />Compression: ",$compressed,"%";
    unlink($zip_filename);
    }
    }

    function generate_create()
    { echo 'Generating Create: Database<br />';
    connecttodb();mysql_select_db(DATABASE);

    $tables = array();
    $result = mysql_query('SHOW TABLES');
    while($row = mysql_fetch_row($result)){$tables[] = $row[0];}
    foreach($tables as $table)
    {
    $result = mysql_query('SELECT * FROM '.$table);
    $num_fields = mysql_num_fields($result);
    $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
    $return.= $row2[1].";\n";
    $return.="\n";
    }
    $sql="SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='database'";
    $result=mysql_query($sql);
    while ($result && $row=mysql_fetch_assoc($result)) {
    $sql_create = "CREATE TRIGGER `{$row['TRIGGER_NAME']}` {$row['ACTION_TIMING']} {$row['EVENT_MANIPULATION']} ON `{$row['EVENT_OBJECT_TABLE']}`";
    $sql_create .= "\n".str_replace("\t",'',$row['ACTION_STATEMENT'])."\n";
    $return .= "\n/*\n".$sql_create."*/\n";
    }
    //save file
    $folder=$_SERVER['DOCUMENT_ROOT'].'/some folder';
    if (!file_exists($folder)) mkdir($folder, 0755, true);
    $backup_filename=$folder.'/CREATE_ALL_TABLES.sql';
    if (!file_exists($folder)) mkdir($folder, 0755, true);
    $handle = fopen($backup_filename,'w+');
    fwrite($handle,$return);
    fclose($handle);
    return $backup_filename;
    }


    $create_filename = generate_create();
    backup_tables('*',$create_filename,$except_tables);
    Last edited by wspeeckaert; 09-09-2013 at 03:40 AM.

  4. #4
    Join Date
    Jul 2008
    Posts
    37
    Here is the email function I use (I copied it from somewhere a long time ago):

    function send_email($to, $from, $from_name, $subject, $message, $attachments=false,$attach_type='image/jpeg'){
    //application/octet-stream
    $headers = "From: ".$from_name."<".$from.">\n";
    $headers .= "Reply-To: ".$from_name."<".$from.">\n";
    $headers .= "Return-Path: ".$from_name."<".$from.">\n";
    $headers .= "Message-ID: <".time()."-".$from.">\n";
    $headers .= "X-Mailer: PHP v".phpversion();
    $msg_txt="";
    $email_txt = stripslashes(stripslashes($message));
    $semi_rand = md5(time());
    $mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";
    $headers .= "\nMIME-Version: 1.0\n" .
    "Content-Type: multipart/mixed;\n" .
    " boundary=\"{$mime_boundary}\"";
    $email_txt .= $msg_txt;
    $email_message .= "This is a multi-part message in MIME format.\n\n" .
    "--{$mime_boundary}\n" .
    "Content-Type:text/html; charset=\"iso-8859-1\"\n" .
    "Content-Transfer-Encoding: 7bit\n\n" .
    $email_txt . "\n\n";
    if ($attachments !== false)
    {
    for($i=0; $i < count($attachments); $i++)
    {
    if (is_file($attachments[$i]))
    {
    $fileatt = $attachments[$i];
    $fileatt_type = $attach_type;
    $start= strrpos($attachments[$i], '/') == -1 ? strrpos($attachments[$i], '//') : strrpos($attachments[$i], '/')+1;
    $fileatt_name = substr($attachments[$i], $start, strlen($attachments[$i]));
    $file = fopen($fileatt,'rb');
    $data = fread($file,filesize($fileatt));
    fclose($file);
    $data = chunk_split(base64_encode($data));
    $email_message .= "--{$mime_boundary}\n" .
    "Content-Type: {$fileatt_type};\n" .
    " name=\"{$fileatt_name}\"\n" .
    "Content-Transfer-Encoding: base64\n\n" .
    $data . "\n\n";
    }//is_file
    }//for
    }
    $email_message .= "--{$mime_boundary}--\n";
    return mail($to, $subject, $email_message, $headers);
    }

  5. #5
    Join Date
    Jul 2008
    Posts
    37
    I solved the problem with triggers. Here is my solution:

    $sql="SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='database'";
    $result=mysql_query($sql);
    while ($result && $row=mysql_fetch_assoc($result)) {
    $sql_create = "DROP TRIGGER IF EXISTS `{$row['TRIGGER_NAME']}`;\n";
    $sql_create .= "DELIMITER //\n";
    $sql_create .= "CREATE TRIGGER `{$row['TRIGGER_NAME']}` {$row['ACTION_TIMING']} {$row['EVENT_MANIPULATION']} ON `{$row['EVENT_OBJECT_TABLE']}`";
    $sql_create .= "\nFOR EACH ROW ".str_replace("\t",'',$row['ACTION_STATEMENT'])."\n";
    $sql_create .= "//\n";
    $sql_create .= "DELIMITER ; \n";
    $return .= "\n/*\n".$sql_create."*/\n";


    Here is a sample output:

    /*
    DROP TRIGGER IF EXISTS `trg_invoice_detail_before_update`;
    DELIMITER //
    CREATE TRIGGER `trg_invoice_detail_before_update` BEFORE UPDATE ON `invoice_details`
    FOR EACH ROW BEGIN
    SET NEW.`total`=NEW.`num_items` * NEW.`cost`;
    END
    //
    DELIMITER ;

    */
    Last edited by wspeeckaert; 09-09-2013 at 06:35 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.16595 seconds
  • Memory Usage 2,889KB
  • Queries Executed 13 (?)
More Information
Template Usage (32):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (5)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (5)postbit
  • (5)postbit_onlinestatus
  • (5)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (71):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates