1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
<?php
// ** GET CONFIGURATION DATA **
require_once('constants.inc');
require_once(FILE_FUNCTIONS);
require_once(FILE_CLASS_OPTIONS);
// ** START SESSION **
session_start();
// ** OPEN CONNECTION TO THE DATABASE **
$db_link = openDatabase($db_hostname, $db_username, $db_password, $db_name);
$options = new Options();
// ** CHECK FOR LOGIN **
checkForLogin();
// ** EXPORT FORMATS **
switch($_GET['format']) {
/********************************************************************************
** MYSQL DUMP FORMAT
**
********************************************************************************/
case "mysql":
// FUNCTION DECLARATION
function createInsertQuery($table) {
global $db_link;
// Obtain the information from the table
$result = mysql_query("SELECT * FROM " . $table, $db_link);
// Note on this query -- previously, it had the additional statement ORDER BY id at the end of it, which created a very clean export
// But the Options and Scratchpad tables don't have an id field, so you can't tell it to ORDER BY id -- you'd get no results from the query if you did
// So it has been removed and that way it works for any table that don't have an id field... unfortunately the output is now not as clean. But
// it's not as important to have it that way.
// Create the Insert Query
while ($resultrow = mysql_fetch_row($result)) {
echo "INSERT INTO " . $table . " VALUES(";
for ($i=0; $i < count($resultrow); $i++) {
if ($i != 0) {
echo ","; // As long as it's not the first element, print a comma separation
}
echo (is_numeric($resultrow[$i]) ? "$resultrow[$i]" : "\"" . addslashes($resultrow[$i]) . "\""); // outputs numbers without quotes, strings with addslashes/double-quotes
}
echo ");\n";
}
// Clear the result from memory -- we don't need it anymore
mysql_free_result($result);
// end function
}
// OUTPUT
header("Content-type: text/plain");
header("Content-disposition: attachment; filename=tab_mysql.txt");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
header("Expires: 0");
echo " * ". $lang['EXP_MYSQL_1']." \n";
echo " * ". $lang['EXP_MYSQL_2']." \n";
echo " * ". $lang['EXP_MYSQL_3']." \n";
echo " *\n";
echo " * ". $lang['EXP_MYSQL_4']." \n";
echo " *\n";
echo " * ". $lang['EXP_MYSQL_5']." \n";
echo " *\n";
echo " * ". $lang['EXP_MYSQL_6']." \n";
echo " * ". $lang['EXP_MYSQL_7']." \n";
echo " * ". $lang['EXP_MYSQL_8']." \n";
echo " * ". $lang['EXP_MYSQL_9']." ". date("l F j Y, H:i:s\n");
echo " * ". $lang['EXP_MYSQL_10']." \n";
echo " * ". $lang['EXP_MYSQL_11']." \n";
echo " * ". $lang['TAB']." ".VERSION_NO ." \n";
echo " *\n";
// The following block of code must be automated.
echo("\n\n");
echo "DROP TABLE IF EXISTS " . TABLE_ADDITIONALDATA . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_ADDRESS . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_CONTACT . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_EMAIL . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_GROUPLIST . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_GROUPS . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_MESSAGING . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_OPTIONS . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_OTHERPHONE . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_WEBSITES . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_USERS . ";\n";
echo "DROP TABLE IF EXISTS " . TABLE_SCRATCHPAD . ";\n";
echo "CREATE TABLE " . TABLE_ADDITIONALDATA . " (id INT(11) NOT NULL DEFAULT '0', type VARCHAR(20) DEFAULT NULL, value TEXT) TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_ADDRESS . " (refid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, id INT(11) NOT NULL DEFAULT '0', type VARCHAR(20) NOT NULL DEFAULT '', line1 VARCHAR(100) DEFAULT NULL, line2 VARCHAR(100) DEFAULT NULL, city VARCHAR(50) DEFAULT NULL, state VARCHAR(10) DEFAULT NULL, zip VARCHAR(20) DEFAULT NULL, country VARCHAR(3) DEFAULT NULL, phone1 VARCHAR(20) DEFAULT NULL, phone2 VARCHAR(20) DEFAULT NULL ) TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_CONTACT . " (id INT(11) NOT NULL AUTO_INCREMENT, firstname VARCHAR(40) NOT NULL DEFAULT '', lastname VARCHAR(80) NOT NULL DEFAULT '', middlename VARCHAR(40) DEFAULT NULL, primaryAddress INT(11) DEFAULT NULL, birthday DATE DEFAULT NULL, nickname VARCHAR(40) DEFAULT NULL, pictureURL VARCHAR(255) DEFAULT NULL, notes TEXT, lastUpdate DATETIME DEFAULT NULL, hidden INT(1) DEFAULT '0' NOT NULL, whoAdded VARCHAR(15), PRIMARY KEY (id)) TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_EMAIL . " (id INT(11) NOT NULL DEFAULT '0', email VARCHAR(100) DEFAULT NULL, type VARCHAR(20) DEFAULT NULL) TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_GROUPLIST . " (groupid INT(11) NOT NULL DEFAULT '0', groupname VARCHAR(60) DEFAULT NULL, PRIMARY KEY (groupid)) TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_GROUPS . " (id INT(11) NOT NULL DEFAULT '0', groupid TINYINT(4) NOT NULL DEFAULT '0') TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_MESSAGING . " (id INT(11) NOT NULL DEFAULT '0', handle VARCHAR(30) DEFAULT NULL, type VARCHAR(20) DEFAULT NULL) TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_OPTIONS . " (bdayInterval INT(3) DEFAULT '21' NOT NULL, bdayDisplay INT(1) DEFAULT '1' NOT NULL, displayAsPopup INT(1) DEFAULT '0' NOT NULL, useMailScript INT(1) DEFAULT '1' NOT NULL, picAlwaysDisplay INT(1) DEFAULT '0' NOT NULL, picWidth INT(1) DEFAULT '140' NOT NULL, picHeight INT(1) DEFAULT '140' NOT NULL, picDupeMode INT(1) DEFAULT '1' NOT NULL, picAllowUpload INT(1) DEFAULT '1' NOT NULL, modifyTime VARCHAR(3) DEFAULT '0' NOT NULL, msgLogin TEXT NULL, msgWelcome VARCHAR(255) NULL, countryDefault CHAR(3) DEFAULT '0' NULL, allowUserReg INT(1) DEFAULT '0' NOT NULL, eMailAdmin int(1) NOT NULL default '0', requireLogin INT(1) DEFAULT '1' NOT NULL, language VARCHAR(25) NOT NULL, defaultLetter char(2) default NULL, limitEntries smallint(3) NOT NULL default '0') TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_OTHERPHONE . " (id INT(11) NOT NULL DEFAULT '0', phone VARCHAR(20) DEFAULT NULL, type VARCHAR(20) DEFAULT NULL) TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_WEBSITES . " (id INT(11) NOT NULL DEFAULT '0', webpageURL VARCHAR(255) DEFAULT NULL, webpageName VARCHAR(255) DEFAULT NULL) TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_USERS . " (id INT(2) NOT NULL AUTO_INCREMENT, username VARCHAR(15) NOT NULL, usertype ENUM('admin','user','guest') NOT NULL DEFAULT 'user', password VARCHAR(32) NOT NULL DEFAULT '', email VARCHAR(50) NOT NULL, confirm_hash VARCHAR(50) NOT NULL, is_confirmed TINYINT(1) DEFAULT '0' NOT NULL, bdayInterval int(3) default NULL, bdayDisplay int(1) default NULL, displayAsPopup int(1) default NULL, useMailScript int(1) default NULL, language varchar(25) default NULL, defaultLetter char(2) default NULL, limitEntries smallint(3) NOT NULL default '0', PRIMARY KEY (id), UNIQUE KEY username (username)) TYPE=MyISAM;\n";
echo "CREATE TABLE " . TABLE_SCRATCHPAD . " (notes TEXT NOT NULL) TYPE=MyISAM;\n";
// GET AND OUTPUT ALL THE DATA
$tables = array(TABLE_ADDITIONALDATA, TABLE_ADDRESS, TABLE_CONTACT, TABLE_EMAIL, TABLE_GROUPLIST, TABLE_GROUPS, TABLE_MESSAGING, TABLE_OPTIONS, TABLE_OTHERPHONE, TABLE_WEBSITES, TABLE_USERS, TABLE_SCRATCHPAD);
while ($a = each($tables)) {
createInsertQuery($a[1]);
}
// END
break;
/********************************************************************************
** COMMA-SEPARATED VALUES (CSV) FORMAT
********************************************************************************/
case "csv":
// QUERY
$csvQuery = "SELECT contact.id, titre, firstname, middlename, lastname, birthday, notes,
email.email, address.line1, address.line2, address.city, address.state, address.zip,
address.phone1, address.phone2, otherphone.phone, websites.webpageURL
FROM ". TABLE_CONTACT ." AS contact
LEFT JOIN ". TABLE_EMAIL ." AS email ON contact.id=email.id
LEFT JOIN ". TABLE_ADDRESS ." AS address ON address.id=contact.id
LEFT JOIN ". TABLE_OTHERPHONE ." AS otherphone ON contact.id=otherphone.id
LEFT JOIN ". TABLE_WEBSITES ." AS websites ON contact.id=websites.id";
$r_contact = mysql_query($csvQuery, $db_link)
or die(reportSQLError($csvQuery));
// OUTPUT
header("Content-Type: text/comma-separated-values");
header("Content-disposition: attachment; filename=tab.csv");
header("Cache-Control: no-store, no-cache, must-revalidate");
header("Cache-Control: post-check=0, pre-check=0", false);
header("Pragma: no-cache");
header("Expires: 0");
echo("titre,firstname,middlename,lastname,birthday,email,address1,address2,city,state,zip,phone1,phone2,phone3,website,notes\n");
while ($tbl_contact = mysql_fetch_array($r_contact)) {
// Most variables are checked for the comma (,) character, which will be
// removed if found. This is to prevent these fields from breaking the CSV format.
echo(str_replace(",","",$tbl_contact['titre']) . "," .
str_replace(",","",$tbl_contact['firstname']) . "," .
str_replace(",","",$tbl_contact['middlename']) . "," .
str_replace(",","",$tbl_contact['lastname']) . "," .
$tbl_contact['birthday'] . "," .
$tbl_contact['email'] . "," .
str_replace(",","",$tbl_contact['line1']) . "," .
str_replace(",","",$tbl_contact['line2']) . "," .
str_replace(",","",$tbl_contact['city']) . "," .
str_replace(",","",$tbl_contact['state']) . "," .
str_replace(",","",$tbl_contact['zip']) . "," .
str_replace(",","",$tbl_contact['phone1']) . "," .
str_replace(",","",$tbl_contact['phone2']) . "," .
str_replace(",","",$tbl_contact['phone']) . "," .
str_replace(",","",$tbl_contact['webpageURL']) . "," .
str_replace(",","",$tbl_contact['notes']) . "\n");
}
// END
break;
/******************************************************************************
** EXPORT MAIN MENU
********************************************************************************/
// ** EXPORT MENU
default:
// OUTPUT
echo($lang['EXP_TO_FILE']);
echo("<UL>");
echo(" <LI><A HREF=\"" . FILE_EXPORT . "?format=csv\">".$lang['EXP_CSV']);
echo("</UL>");
echo("</BODY></HTML>");
// END
break;
// END SWITCH
}
?> |
Récupération de données incluant trois tables
Répondre avec citation
.
Envoyé par sabotage
Partager