dcsimg
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 11: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 04: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 04: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 07: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



Recent Articles