|
PCCS MySQLDatabase Admin Tool version 1.3.4
|
/xml/ -> createxmlfile.php
1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
2 <HTML>
3 <HEAD>
4 <TITLE>Creating XML files from Database Queries</TITLE>
5 <STYLE TYPE="text/css">
6 H1 {
7 font-weight: bold;
8 font-size: 18pt;
9 line-height: 18pt;
10 font-family: arial,helvetica;
11 font-variant: normal;
12 font-style: normal;
13 }
14 BODY {
15 color: black;
16 background-color: white;
17 background-image: url(manual/images/feather.jpg);
18 background-repeat: no-repeat;
19 }
20 </STYLE>
21 </HEAD>
22 <H2>First I'm a network administrator and php developer. Not a writer.</H2>
23 <H3>Now, Here is the tech... </H3>
24
25 After reading Tim Uckun's article. I installed and tested the ODBC Socket Server
26 and was able to get data from a query. Looking at the raw xml that was returned.
27 I decided to sit down and see if I could generate the exact output from a
28 regular MySQL query...
29 <P>
30 Having a lot of experince using the file functions related to php, which are
31 needed to create a file.<br> NOTE: <i>"You will also need write permission in the
32 directory you run this script..."</i><p>
33 ...I started hacking....
34
35 <P>
36 $ nedit writexml.php & // my favorite editor for php coding
37 <P>
38 <H2>Script to create XML file</H2>
39 <P>
40 We will start writing code now... After working on the script for a while. I
41 added form support.
42 <P> I hope you can follow the code below... if not send me an email or check
43 php.net. NOTE: use http://php.net/xml or /mysql
44
45 <pre>
46 <?php
47
48 // If this is a submit create file...
49 if($submit) {
50
51 // $xml_filename="this variable is set via the form";
52 //file for writing
53 if(!$xmlfile = fopen($xml_filename,"w")) {
54 print "Couldn'tfile";
55 } else {
56 // Make connection to dbhost
57 $f = mysql_connect("localhost","root","") or die("Database Down");
58
59 // Strip slashes that cause errors...
60 $query = stripslashes($query);
61
62 // Execute database query: gotten from form submit
63 $query = "
64 SELECT accounts.*,account_type.*,transactions.*
65 FROM accounts,account_type,transactions
66 WHERE transactions.trans_acctid = accounts.acct_id
67 AND accounts.acct_typeid = account_type.acct_typeid
68 ORDER BY trans_date ASC LIMIT 3";
69
70
71 $res = mysql_db_query("$dbname","$query");
72 if(!$res) {
73 print "ERROR: " . mysql_error();
74 } else {
75
76 // Start creating file
77 // Formating xml file based on output from odbcsocket server
78 // xml statement and version
79 fputs($xmlfile,"<?xml version=\"1.0\" ?>\n");
80
81 // begin xml (similar to <table>)
82 fputs($xmlfile,"<result state=\"success\">\n");
83 $field = array();
84
85 // Get the field names.
86 $field=mysql_fetch_field($res);
87 for($i=0; $i < count($field); $i++) {
88 $fieldname = $field->name;
89 }
90
91 // Get result values
92 while($row=mysql_fetch_array($res)) {
93 // begin row (similar to <tr>)
94 @fputs($xmlfile,"<row>\n");
95 $fields = array();
96
97
98 for($i=0; $i < mysql_num_fields($res); $i++) {
99 $datavalue = $row[$i];
100 $datadescr = mysql_field_name($res,$i);
101
102 // turning off error output. using @
103 // was getting error even though file was being create
104 // with out errors...
105
106 // begin of data (similar to <td>)
107 @fputs($xmlfile,"\t<column name=\"". $datadescr ."\">");
108
109 // end of data (similar to </td>)
110 @fputs($xmlfile,$datavalue."</column>\n");
111 }
112 // end row (similar to </tr>)
113 @fputs($xmlfile,"</row>\n");
114
115 }
116
117 // end xml (similar to </table>)
118 @fputs($xmlfile,"</result>");
119
120 // Close my xml file after writing it...
121 @fclose($xmlfile);
122
123
124 } // end if(!$res)
125
126 } // end if(!$xmlfile)
127
128 // Display some menu like options
129
130 print "\n\nXML File written...\n";
131
132
133 print "<P> View/Edit XML script";
134 print " <A
135 HREF=\"xmleditor.php?directory=./&filename=".$xml_filename."\">".$xml_filename."</a>";
136
137 print "<P> Parse XML script";
138 print " <A
139 HREF=\"xmlparse.php?xml_file=".$xml_filename."\">".$xml_filename."</a>";
140
141 } else {
142
143 // if it wasn't a submit display query form
144 // allow database name, filename to be created
145 // and sql query
146
147
148 print "<CENTER>\n";
149 print "<FORM ACTION=\"".$PHP_SELF ."\" METHOD=post>\n";
150 print "<TABLE>\n";
151 print "<TR>\n";
152 print "<TD>Database</TD><TD><INPUT TYPE=text NAME=dbname
153 SIZE=20></TD>\n";
154 print "</TR><TR>\n";
155 print "<TD>XML FileName</TD><TD><INPUT TYPE=text
156 NAME=xml_filename SIZE=20></TD>\n";
157 print "</TR><TR>\n";
158 print "<TD COLSPAN=2>SQL QUERY</TD>\n";
159 print "</TR><TR>\n";
160 print "<TD COLSPAN=2><TEXTAREA NAME=query COLS=50
161 ROWS=20></TEXTAREA></TD>\n";
162 print "</TR><TR>\n";
163 print "<TD COLSPAN=2 ALIGN=center><INPUT TYPE=submit NAME=submit
164 VALUE=\"Create XML File\">\n";
165 print "</TABLE>\n";
166 print "</FORM>\n";
167 print " </CENTER>\n";
168 } // end of script...
169 ?>
170 </code>
171 <H2>Output of the script</H2>
172 This is the EXACT output that ODBCSocketServer returns. When a query is
173 submitted to a windows odbc dsn connect
174 <P>
175 Took me about 4 hours to get the output perfect...
176 <P>
177
178 <pre>
179 <?xml version="1.0" ?>
180 <result state="success">
181 <row>
182 <column name="acct_id">1</column>
183 <column name="acct_number">610</column>
184 <column name="acct_name">Meals</column>
185 <column name="acct_typeid">1</column>
186 <column name="acct_typeid">1</column>
187 <column name="acct_type">Expenses</column>
188 <column name="trans_id">1</column>
189 <column name="trans_number">2</column>
190 <column name="trans_date"></column>
191 <column name="trans_description">Fancy Meal</column>
192 <column name="trans_acctid">1</column>
193 <column name="trans_withdraw">7.00</column>
194 <column name="trans_deposit">0.00</column>
195 <column name="trans_taxable">Yes</column>
196 </row>
197 <row>
198 <column name="acct_id">1</column>
199 <column name="acct_number">610</column>
200 <column name="acct_name">Meals</column>
201 <column name="acct_typeid">1</column>
202 <column name="acct_typeid">1</column>
203 <column name="acct_type">Expenses</column>
204 <column name="trans_id">15</column>
205 <column name="trans_number">2</column>
206 <column name="trans_date"></column>
207 <column name="trans_description">Fancy Meal</column>
208 <column name="trans_acctid">1</column>
209 <column name="trans_withdraw">7.00</column>
210 <column name="trans_deposit">0.00</column>
211 <column name="trans_taxable">Yes</column>
212 </row>
213 <row>
214 <column name="acct_id">9</column>
215 <column name="acct_number">430</column>
216 <column name="acct_name">Second Mutual Fund</column>
217 <column name="acct_typeid">2</column>
218 <column name="acct_typeid">2</column>
219 <column name="acct_type">Revenues</column>
220 <column name="trans_id">12</column>
221 <column name="trans_number">43</column>
222 <column name="trans_date"></column>
223 <column name="trans_description">Second Mutual Fund</column>
224 <column name="trans_acctid">9</column>
225 <column name="trans_withdraw">0.00</column>
226 <column name="trans_deposit">1000.00</column>
227 <column name="trans_taxable">No</column>
228 </row>
229 </result>
230 </pre>
231
232 <H2>Output of xml parse...</H3>
233 <table border=0 cellpadding=2 cellspacing=0>
234 <tr>
235 <td>1</td>
236 <td>610</td>
237 <td>Meals</td>
238 <td>1</td>
239 <td>1</td>
240 <td>Expenses</td>
241 <td>1</td>
242 <td>2</td>
243 <td></td>
244 <td>Fancy Meal</td>
245 <td>1</td>
246 <td>7.00</td>
247 <td>0.00</td>
248 <td>Yes</td>
249 </tr>
250 <tr>
251 <td>1</td>
252 <td>610</td>
253 <td>Meals</td>
254 <td>1</td>
255 <td>1</td>
256 <td>Expenses</td>
257 <td>15</td>
258 <td>2</td>
259 <td></td>
260 <td>Fancy Meal</td>
261 <td>1</td>
262 <td>7.00</td>
263 <td>0.00</td>
264 <td>Yes</td>
265 </tr>
266 <tr>
267 <td>9</td>
268 <td>430</td>
269 <td>Second Mutual Fund</td>
270 <td>2</td>
271 <td>2</td>
272 <td>Revenues</td>
273 <td>12</td>
274 <td>43</td>
275 <td></td>
276 <td>Second Mutual Fund</td>
277 <td>9</td>
278 <td>0.00</td>
279 <td>1000.00</td>
280 <td>No</td>
281 </tr>
282 </table>
283
284 <p>
285
286 <H2>Script used to parse xml file...</H2>
287 filename: xmlparse.php
288 <p>
289 <code>
290 <?php
291 print '
292 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
293 Transitional//EN" "http://www.w3.org/TR/REC-html40/loose.dtd">
294 <HTML lang="en">
295 <HEAD>
296 <TITLE>XML Parser</TITLE>
297 </HEAD>
298 <BODY>';
299 // Hacked this script from various snippets of code...
300 //handler for the start elements
301 function beginElement($parser, $name, $attribs)
302 {
303 if (strtolower($name) == "row")
304 {
305 //handler for the row element
306 print "<tr>";
307 }
308 if (strtolower($name) == "column")
309 {
310 //handler for the column
311 print "<td>";
312 }
313 if (strtolower($name) == "error")
314 {
315 //handler for the error
316 print "<tr><td>";
317 }
318 if (strtolower($name) == "result")
319 {
320 print "<br><table border=0 cellpadding=2
321 cellspacing=0>";
322 }
323 }
324
325 //handler for the end of elements
326 function endElement($parser, $name)
327 {
328 if (strtolower($name) == "row")
329 {
330 //handler for the row element
331 print "</tr>";
332 }
333 if (strtolower($name) == "column")
334 {
335 //handler for the column
336 print "</td>";
337 }
338 if (strtolower($name) == "error")
339 {
340 //handler for the error
341 print "</td></tr>";
342 }
343 if (strtolower($name) == "result")
344 {
345 print "</table> <br>";
346 }
347 }
348
349 //handler for character data
350 function characterData($parser, $data)
351 {
352 print "$data";
353 }
354
355 // $xml_file = 'this.xml';
356
357 // declare the character set - UTF-8 is the default
358 $type = 'UTF-8';
359
360 // create our parser
361 $xml_parser = xml_parser_create($type);
362
363 // set some parser options
364 // enable case-folding for this parser
365 xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, true);
366
367 // set source encoding for this parser
368 xml_parser_set_option($xml_parser, XML_OPTION_TARGET_ENCODING, 'UTF-8');
369
370
371 //parse the XML
372 $xml_parser = xml_parser_create();
373
374 // set up start and end element handlers
375 xml_set_element_handler($xml_parser, "beginElement", "endElement");
376
377 // set up character data handler
378 xml_set_character_data_handler($xml_parser, "characterData");
379
380
381 if (!($fp = fopen($xml_file, 'r'))) {
382 die("Could not$xml_file for parsing!\n");
383 }
384
385 // loop through the file and parse!
386 while ($data = fread($fp, 4096)) {
387 if (!($data = utf8_encode($data))) {
388 echo 'ERROR'."\n";
389 }
390 if (!xml_parse($xml_parser, $data, feof($fp))) {
391 die(sprintf( "XML error: %s at line %d\n\n",
392 xml_error_string(xml_get_error_code($xml_parser)),
393 xml_get_current_line_number($xml_parser)));
394 }
395 }
396 // Free this XML parser
397 xml_parser_free($xml_parser);
398
399 print '
400 </BODY>
401 </HTML>';
402 ?>
403 </code>
404
405
406 <H2>Script to display/edit xml file</H2>
407 <code>
408 <?
409 // Hacked from phpgen.
410
411 //******************* Save edited file...
412 if ($action == "save_edited"):
413 $fp = fopen($file,"w");
414 fputs($fp,$to_edit);
415 fclose($fp);
416 $path = dirname($file);
417 ?>
418 <SCRIPT LANGUAGE="JavaScript">
419 document.location="./";
420 </SCRIPT>
421
422 <? endif;
423
424 //******************* Edit a file
425 if (empty($action)):
426 $file = $directory . $filename;
427 if (!file_exists($file)) {
428 echo "<H2>$filename does not exist!";
429 echo "<A HREF=javascript:history.back()>Return</A>";
430 exit;
431 }
432
433 echo "<H2>Edit of $file</H2>";
434 echo "<FORM METHOD=POST>";
435 echo "<INPUT TYPE=HIDDEN NAME=action VALUE=save_edited>";
436 echo "<INPUT TYPE=HIDDEN NAME=file VALUE='$file'>";
437 echo "<TEXTAREA NAME=to_edit COLS=70 ROWS=25>";
438 $fp = fopen($file,"r");
439 while (!feof($fp)):
440 $r = fgets($fp,9999);
441 echo $r;
442 endwhile;
443 fclose($fp);
444 echo "</TEXTAREA><BR>";
445 echo "<A HREF=javascript:history.back()>Cancel</A><BR>";
446 echo "<B>Filename: <INPUT SIZE=40 NAME=file VALUE='$file'>";
447 echo "<INPUT TYPE=SUBMIT VALUE=Save>";
448
449 echo "</FORM>";
450 endif; //*Edit
451
452 ?>
453
454 </code>
455 <P>
456 <pre>
457 Chauncey Thorn
458 http://PCCS-Linux.COM
459 </pre>
460
461 </BODY>
462 </HTML>
463
| |