Dumping Database Contents into an XML File

   

Although it doesn't require the PHP XML functions, dumping data from a database into an XML file using PHP is a great way to get some structured XML that you can use in other XML-enabled applications.

The nature of database data lends itself nicely to use in XML applications. You have already created a database structure for the data by specifying the table structures. Using PHP, you can easily extract those tables into an XML file, which can be stored in a text file or displayed in a browser window.

This next script extracts the contents of a database table containing address book entries and displays that information in the browser. If you have a browser such as Internet Explorer, then you can view the XML natively, as in Figure 10-4. Otherwise, you can view the browser source to see the XML.

Script 10-5 xml_dump.php

[View full width]

  1.  <?  2.  /* SQL Table Required For This Script  3.  create table xmladdressbook (  4.    id INT NOT NULL,  5.    first VARCHAR(32),  6.    last VARCHAR(32),  7.    phone VARCHAR(12),  8.    email VARCHAR(64),  9.    address VARCHAR(64), 10.    city VARCHAR(32), 11.    state VARCHAR(2), 12.    zip VARCHAR(5), 13.    type VARCHAR(16) 14.    primary key(id)); 15. 16.    insert into xmladdressbook values (1, 'Neil','Armstrong','555-555-1234','neil@example. graphics/ccc.gifcom','1 Moon Road','Lunar City','LN', '99999', 'Friend'); 17.    insert into xmladdressbook values (2, 'Buzz','Aldrin','555-555-1235','buzz@example.com', graphics/ccc.gif'2 Moon Road','Lunar City','LN', '99999', 'Business'); 18.  */ 19. 20.  function connect() { 21.    if(!$db = @mssql_pconnect("localhost","mssqluser","password")) { 22.      print("<h1>Cannot Connect to the DB!</h1>\n"); 23.      return 0; 24.    } else { 25.      mssql_select_db("php", $db); 26.      return 1; 27.    } 28.  } 29. 30.  if(connect()) { 31.    print('<?xml version="1.0"?>'); 32.    ?><addressbook><? 33.    $sql = "select * from xmladdressbook order by last, first"; 34.    $result = mssql_query($sql); 35.    while($row = mssql_fetch_array($result)) { 36.      ?> 37.      <contact type="<?=$row["type"]?>"> 38.        <name> 39.          <last_name><?=$row["last"]?></last_name> 40.          <first_name><?=$row["first"]?></first_name> 41.        </name> 42.        <details> 43.          <phone><?=$row["phone"]?></phone> 44.          <email><?=$row["email"]?></email> 45.          <address><?=$row["address"]?></address> 46.          <city><?=$row["city"]?></city> 47.          <state><?=$row["state"]?></state> 48.          <zip><?=$row["zip"]?></zip> 49.        </details> 50.      </contact> 51.      <? 52.    } 53.    ?></addressbook><? 54.  } 55.  ?> 
Figure 10-4. xml_dump.php shown in Internet Explorer

graphics/10fig04.jpg

Script 10-5. xml_dump.php Line-by-Line Explanation

LINE

DESCRIPTION

2 14

Use these lines to create the table in your SQL database.

16

Create an entry in the database.

17

Create an additional entry in the database.

20 28

Create a function called connect(). This function is used to create a connection to the database.

21

Attempt to connect to the database, in this case, an MS SQL database. (You can replace all the MS SQL functions with MySQL functions simply by changing the first "s" in mssql to a "y" "mysql").

22

If the script cannot connect to the database, then print an error.

23

Return false (0) to the calling program, because the database connection failed.

24 25

If the connection attempt was successful, then select the database on the database server that the script will use.

26

Return true (1) to the calling program, because the connection was successful.

27

Close the if statement started on line 12.

28

End the function declaration.

30

If the connect() function returns true, then continue on to the next line.

31

Print out the XML required to begin an XML page. You need to use the print statement to enclose the native <? and ?> XML processing instructions so that they are not interpreted to be PHP tags.

32

Print the top level XML tag to the screen. Since this is an address book, the top level tag is <addressbook>.

33

Generate an SQL statement to select all of the contents of the table and order the results by last name, then first name.

34

Execute the SQL query and place the result in the $result variable.

35 52

Loop through the result set with a while loop.

36 51

Stop processing the page as PHP. Since we are printing out a lot of data, it's easier to just have the server send out plain XML instead of processing each line as a print statement. Print out each of the results returned in the current row as part of a "contact" element. The subelements found within "contact" contain the details of the address book entry.

52

Close the while loop.

53

Close out the XML by printing out a close tag for the root element <addressbook>.

54

Close the if statement started on line 30.

55

End the script.


   
Top


Advanced PHP for Web Professionals
Advanced PHP for Web Professionals
ISBN: 0130085391
EAN: 2147483647
Year: 2005
Pages: 92

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net