· 9 years ago · Jun 06, 2017, 09:58 AM
1<html><head><style>
2body
3{
4 width: 100%;
5 height: 100%;
6 padding: 0px;
7 margin: 0px;
8 border: 0px;
9 color: #000000;
10 font: 11px/20px verdana, arial, sans-serif;
11 background: #001930 url('./images/background_wrap.png') no-repeat center;
12}
13</style>
14<body>
15<div style="margin: auto; margin-top: 20px; background: #fff; width: 800px; padding: 20px;">
16<?php
17$host = 'localhost';
18$user = 'xx';
19$pass = 'xxx';
20$name = 'main_world';
21$name_trigger = 'main_world_trigger';
22$type = 'DELETE';
23
24mysql_connect($host, $user, $pass);
25mysql_select_db($name);
26
27$tables = mysql_list_tables($name);
28while($row = mysql_fetch_array($tables))
29{
30 $query[$row[0]]['table_name'] = $row[0];
31 $query[$row[0]]['trigger_delete'] = "DROP TRIGGER IF EXISTS `{$row[0]}_delete`;";
32 $query[$row[0]]['trigger_update'] = "DROP TRIGGER IF EXISTS `{$row[0]}_update`;";
33
34 // DELETE STRUCTURE TABLE
35 $i = 0;
36 $query[$row[0]]['create_delete'][$i] = "DROP TABLE IF EXISTS `{$name_trigger}`.`{$row[0]}_delete`;";
37 $query[$row[0]]['create_delete'][$i+1] = "CREATE TABLE `{$name_trigger}`.`{$row[0]}_delete` (";
38 $i = 2;
39
40 $fields_show = mysql_query("SHOW FIELDS FROM `{$row[0]}`");
41 $num_fields = mysql_num_fields(mysql_query("SELECT * FROM `{$row[0]}` LIMIT 1"));
42 $nf = 0;
43 while($fields = mysql_fetch_array($fields_show))
44 {
45 $nf++;
46 $query[$row[0]]['create_delete'][$i] .= " {$fields['Field']} {$fields['Type']}";
47
48 if (!empty($fields['Default']))
49 $query[$row[0]]['create_delete'][$i] .= " DEFAULT '{$fields['Default']}'";
50 if ($fields['Null'] != 'Yes')
51 $query[$row[0]]['create_delete'][$i] .= " NOT NULL";
52 if (!empty($fields['Extra']))
53 $query[$row[0]]['create_delete'][$i] .= " {$fields['Extra']}";
54
55 if($nf <= $num_fields)
56 $query[$row[0]]['create_delete'][$i] .= ",";
57
58 $i++;
59 }
60
61 $query[$row[0]]['create_delete'][$i] = " trigger_date TIMESTAMP NOT NULL";
62 $i++;
63 $query[$row[0]]['create_delete'][$i] = ");";
64
65 // UPDATE STRUCTURE TABLE
66 $i++;
67 $query[$row[0]]['create_update'][$i] = "DROP TABLE IF EXISTS `{$name_trigger}`.`{$row[0]}_update`;";
68 $i++;
69 $query[$row[0]]['create_update'][$i] = "CREATE TABLE `{$name_trigger}`.`{$row[0]}_update` (";
70 $i++;
71
72 $fields_show = mysql_query("SHOW FIELDS FROM `{$row[0]}`");
73 $num_fields = mysql_num_fields(mysql_query("SELECT * FROM `{$row[0]}` LIMIT 1"));
74 $nf = 0;
75 while($fields = mysql_fetch_array($fields_show))
76 {
77 $nf++;
78 $query[$row[0]]['create_update'][$i] .= " {$fields['Field']} {$fields['Type']}";
79
80 if (!empty($fields['Default']))
81 $query[$row[0]]['create_update'][$i] .= " DEFAULT '{$fields['Default']}'";
82 if ($fields['Null'] != 'Yes')
83 $query[$row[0]]['create_update'][$i] .= " NOT NULL";
84 if (!empty($fields['Extra']))
85 $query[$row[0]]['create_update'][$i] .= " {$fields['Extra']}";
86
87 if($nf <= $num_fields)
88 $query[$row[0]]['create_update'][$i] .= ",";
89
90 $i++;
91 }
92
93 $query[$row[0]]['create_update'][$i] = " trigger_date TIMESTAMP NOT NULL";
94 $i++;
95 $query[$row[0]]['create_update'][$i] = ");";
96
97 // ---------------------------------------
98 $i = 0;
99 $query[$row[0]]['create_trigger_delete'][$i++] = "CREATE TRIGGER `{$row[0]}_delete` BEFORE DELETE ON `{$name}`.`{$row[0]}`";
100 $query[$row[0]]['create_trigger_delete'][$i++] = "FOR EACH ROW";
101 $query[$row[0]]['create_trigger_delete'][$i++] = "BEGIN";
102 $fields_show = mysql_query("SHOW FIELDS FROM `{$row[0]}`");
103 $st = 0;
104 $i++;
105 while($fields = mysql_fetch_array($fields_show))
106 {
107 if($st == 0)
108 {
109 $query[$row[0]]['create_trigger_delete'][$i] = "INSERT INTO `{$name_trigger}`.`{$row[0]}_delete`(";
110 $st++;
111 }
112
113 $query[$row[0]]['create_trigger_delete'][$i] .= " {$fields['Field']},";
114 }
115 $query[$row[0]]['create_trigger_delete'][$i] .= " trigger_date) VALUES(";
116
117 $fields_show = mysql_query("SHOW FIELDS FROM `{$row[0]}`");
118 $st = 0;
119 while($fields = mysql_fetch_array($fields_show))
120 $query[$row[0]]['create_trigger_delete'][$i] .= " OLD.{$fields['Field']},";
121
122 $query[$row[0]]['create_trigger_delete'][$i] .= " NOW());";
123 $i++;
124 $query[$row[0]]['create_trigger_delete'][$i] = "END;";
125
126
127 // ---------------------------------------
128 $i = 0;
129 $query[$row[0]]['create_trigger_update'][$i++] = "CREATE TRIGGER `{$row[0]}_update` BEFORE UPDATE ON `{$name}`.`{$row[0]}`";
130 $query[$row[0]]['create_trigger_update'][$i++] = "FOR EACH ROW";
131 $query[$row[0]]['create_trigger_update'][$i++] = "BEGIN";
132 $fields_show = mysql_query("SHOW FIELDS FROM `{$row[0]}`");
133 $st = 0;
134 $i++;
135 while($fields = mysql_fetch_array($fields_show))
136 {
137 if($st == 0)
138 {
139 $query[$row[0]]['create_trigger_update'][$i] = "INSERT INTO `{$name_trigger}`.`{$row[0]}_update`(";
140 $st++;
141 }
142
143 $query[$row[0]]['create_trigger_update'][$i] .= " {$fields['Field']},";
144 }
145 $query[$row[0]]['create_trigger_update'][$i] .= " trigger_date) VALUES(";
146
147 $fields_show = mysql_query("SHOW FIELDS FROM `{$row[0]}`");
148 $st = 0;
149 while($fields = mysql_fetch_array($fields_show))
150 $query[$row[0]]['create_trigger_update'][$i] .= " OLD.{$fields['Field']},";
151
152 $query[$row[0]]['create_trigger_update'][$i] .= " NOW());";
153 $i++;
154 $query[$row[0]]['create_trigger_update'][$i] = "END;";
155
156}
157
158
159foreach($query as $array)
160{
161 echo "##############################<br />";
162 echo "# Table {$array['table_name']}<br />";
163 echo "##############################<br />";
164 echo $array['trigger_delete'].'<br />';
165 echo $array['trigger_update'].'<br /><br />';
166
167 foreach($array['create_delete'] as $key => $value)
168 {
169 echo $value.'<br />';
170 }
171 echo "<br /><br />";
172
173 foreach($array['create_update'] as $key => $value)
174 {
175 echo $value.'<br />';
176 }
177 echo "<br /><br />";
178
179 foreach($array['create_trigger_delete'] as $key => $value)
180 {
181 echo $value.'<br />';
182 }
183 echo "<br /><br />";
184
185 foreach($array['create_trigger_update'] as $key => $value)
186 {
187 echo $value.'<br />';
188 }
189
190 echo "<br /><br /><br />";
191}