VB: Working with PHP and XML

When it comes to getting a job as VB programmer you need to know a lot more then just VB now a days. Some companies want you to know XML, ASP, PHP, SQL and ADO just to name a few. While we have touched on ADO and SQL in the Working with Databases series, this time around we will be working with XML and PHP since they are becoming more and more popular. We have done Focus On XML and PHP but this time we will actually be working with this languages instead of just reading about them.

Now you may think that you need money to get a web site that allows you to use PHP. Or you need to purchase software in order to write or view XML scripts, well you know me, "Free" is always good! If you have IE5 and up it comes with a DLL file that will let you use VB with XML. As for finding a web service that allows you to use PHP no need, you can download a web server called OmniHTTPd, it takes less then 5 mins to install and it includes PHP that's already configured. This is what I use to test my PHP and web pages.

OmniHTTPd

http://www.omnicron.ca/httpd/download.html

Testing the server

When you install OmniHTTPd you should be able to use it right off the bat. Since we will be testing our scripts locally the default for the server IP address should be 127.0.0.1 and the server name should be localhost. This way you don't have to worry about being connected via a dialup or DSL/Cable modem.

Now to test that everything is working as it should and you can run PHP scripts, do the following.

Open notepad copy and paste the code below and put it in the HtDocs folder, then save the file as phpinfo.php. Remember when saving a file type other then txt in notepad you must uses quotes. "phpinfo.php", if you don't the file will be saved as phpinfor.php.txt.

<html><head><title>PHP Infor</title></head>
<body>

< ?php
phpinfo();
?>
</body></html>

Now make sure that the server is running, open your web browser and type the following.

http://127.0.0.1/phpinfo.php

If everything is setup correct you should get a page that shows the version of PHP installed and the configuration.

Our First PHP script

Writing PHP scripts are like writing ASP, JavaScript's or VBScript's. If you have any programming experience then the learning curve will be short.

PHP scripts like ASP, JavaScript's and VBscripts can be embedded in the HTML page. However unlike JavaScript's and vbscripts the web page extension must be php and not html or the script will not execute. Like ASP the reason for this is that the php extension tell the php engine to execute the script, if there is no php extension then the php engine doesn't know that its a php script.

This is the difference between ASP, PHP and JavaScript's and VBScripts, they are considered client side scripts while ASP and PHP are considered server side scripts. This means that as long as you put the scripting language your using JavaScript's and VBScripts will execute via your web browser. But server side scripts can only execute the script by contacting the server and use the engine to run the script.

Showing what type of script your writing.

When writing a PHP script you must use the
< html>< head>< title>< /title>< /head>
< body>

< ?php
$hello = "Hello World!";

echo "This is my first php script: $hello"
?>

< /body>< /html>

Or you could have written the script like this.

< html>< head>< title>< /title>< /head>
< body>
< ?php

echo "This is my first php script: Hello World";
?>

< /body>< /html>

Now save the file as hello.php in the HtDocs folder and type the following in your web browser. Make sure that the server is running.

http://127.0.0.1/hello.php

One finial note, you can use print in place of echo, you will get the same results.

Next up more PHP scripts

Reading and Writing to files - Strings

There will come a time that you need to use PHP to write or read a file on the server. Here are some examples of doing just that. We will be using @fopen, @fwrite and fclose as well as is_readable is_writable. These functions do exactly what they say they open the file write to it, checks to see if a file is readable or writable.

Test if a file exist - testfile.php

<?php

$fp = @fopen("myfile.txt","r");

if ($fp)

{ print"The file exists!"; }

else

{ print"The file does not exist"; }

write to a file - savefile.php

<?php

$file = fopen("myfile.txt","w");

fwrite($file,"This is where the contents of the text file goes");

fclose($file);

read a file into an array, then use 'file' function:

The file - linenum.txt

line 1
line 2
line 3
line 4

The script to read the file - line.php

<?php

$lines=file('linenum.txt');

echo "<PRE>\n";

for($i=0;$i<count($lines);$i++){

echo "This is: ".$lines[$i];

}

echo "</PRE>\n";

When the code above is run you get the following results.

This is: line 1
This is: line 2
This is: line 3
This is: line 4

File verification

is_readable()

checks wether a specified file is readable by PHP. The function returns TRUE if redable and FALSE otherwise.

$file = "data.txt";

if(is_readable($file)) {

// open and write to file

}

else {

echo "Cannot write to file <b>$file</b>.";

}

is_writable()

is_writable() function is similar to is_readable(), but it checks instead wether a file is writable or not.

String Access Mode

r Access the file for reading only, start access from the beginning of the file.

r+ Access the file for both reading and writing, start access from the beginnin of the file

w
Access the file for writing only, if the file exists, erase all contents. If file does not exist
an attempt well be made to create the file. In either case the file access at the beginning.

w+
Access the file for both reading and writing, if the file exists erase all contents. If the file
does not exist an attempt will be made to create it. In either case access to the file is at the beginning

a
Open the file for writing only. If the file doesn't exist then an attempt will be made to create it. if the file
does exsit then access well be at the end of the file

a+
Open the file for reading and writing. If the file does not exsit then an attempt will be make to create it.
If the file does exsit then access well be at the end of the file

Reading and Writing to files - Binary

First I should you how to read and write files that contained strings. This time around we will go over how to read and write binary files. We already went over fopen(), fwrite(), fputs() and fclose(). We will be using those to read binary files as well as a few new functions. The new functions we will be using is fgets()and fread(), the fread() takes accepts two parameters:

fread($file_reference, $length);

<?php

$fr = fopen(myfile.dat, 'r');
$data = fread($fr, 1024);
fclose($fr);
?>

To read a file on a Windows system using binary mode you'd use rb instead of the r you'd use on a UNIX-compatible system.

Moving the pointer within a file

In order to solve the problem of accessing a specific location within a file, we'll need a way to adjust the file pointer. What is a file pointer? It's the marker used by PHP to keep track of where the currently executing file operations take place. We will be using the fseek() function to accompblish this.

PHP Constant File Offset

SEEK_SET - Starting from the beginning of the file
SEEK_CUR - Starting from the current pointer location
SEEK_END - Starting from the end of the file

// use one of your files here, of course...
$file = 'images/dart.gif';
$hf = fopen($file, 'r') or die("Can't open $file for reading");
$bin = fread($hf, filesize($file));
fclose($hf);
$asc = '$img_data = "';
for ($i = 0, $len = strlen($bin); $i < $len; ++$i) {
if ($i && !($i % 16))
$asc .= "\" .\n\t\"";
$asc .= '\\x' . bin2hex(substr($bin, $i, 1));
}
$asc .= '";';

// $asc now contains the hex-coded binary file.
// Example (a real gif - try it)

Below are some examples on reading and writing to bindary file.

Dealing with Forms

Working with forms with PHP is a breeze, since all the name are automatically given the name variable in PHP all you have to do is reference the file in the script.

At one point all you had to do was use the dollar sign followed by the name of the variable. But because of the security concerns the register_globals is turned off by default so now you have to use _POST[varname].

So to try the code below copy the form.html and getform.php code and save it. Put both in the HtDocs directory and type the following in your web browser.

http://127.0.0.1/form.html

Enter in the information in the input boxes and click the submit button. The script will return the first name, middle name, last name and phone number.

================== form.html ============================< /FONT >

<html><head><title>Working with forms</title></head>

<body>

Your First name: <input type="text" name= "Fname"><br>< /FONT>

Your Middle Name: <input type="text" name= "Mid"><br>< /FONT>

Your Last name: <input type="text" name= "Lname"><br>< /FONT>

Your Phone number: <input type = "text" name = "pNum"><br> < /FONT>

</form>

</body>

</html>

===================== getform.php =========================< /FONT >

<?php

$testFname = $_POST[FirstName];

$testLname = $_POST[Lname];

$testMid = $_POST[Mid];

$testPhone = $_POST[pNum];

Include, require Function

The include or require fuctions can come in handy. Say you have a connection script that connects you to the database, why would you have to write this information every time you want to connect to the databse. Why don't you just create a connection script and included it when every you want to connect to the database. This will save you key stroks.

Using include()

============== conn.php ==================< /FONT >

<?php

$connect =new COM("ADODB.Connection");

$connect->Provider = "MSDASQL";

if ( ! $connect ) die( "Couldn't connect to Database" );

$connect->Open("php");

=============== testinc.php ==================< /FONT >

<html><head><title>Pulling records from the database</title></head>

<body>

<?php

include("conn.php");

$rs = $connect-> Execute("Select * From People");

$i = 0;

$fld0 = $rs->Fields(0);

$fld1 = $rs->Fields(1);

$fld2 = $rs->Fields(2);

while (!$rs->EOF) {

$i += 1;< /FONT>

print "$fld0->value $fld1->value $fld2->value<BR>";

$rs->MoveNext(); /*updates fld0, fld1, fld2 !*/

}

$rs->Close();

</body>

</html>

Now we can use the $connect in the conn.php file to pull the records from the database.

==================== testinc.php ==========================< /FONT >

<?php

$test = "1234"

==================== include-test.php =========================< /FONT >

<body>

<?php

include ("testinc.php");

echo "This is what's in the variable of test: $test";

</body></html>

When you run include-test.php the script will print out 1234.

Using require()

Say the file you need the variable from is called file2.php, I would

insert the following line into file1.php:

<?php

require("file2.php");

echo $var;

Then in file2.php you would have to have a line similar to this:

<?php

$var = "Teacher";

When you run the file1.php it will print Teacher .

Connecting to MySQL w/PHP

The one good thing about PHP is that it comes with the functions that will make accessing MySQL a snap. However you are not limited to just MySQL you can access just about any database with PHP. For this portion of the tutorial we will be using code to connect to MySQL and Access, while I wouldn't recommend using Access for Internet, it really isn't designed for that. You can us it for stand-a-lone purposes(more on that later).

If you don't have MySQL setup on your system you can download it for free at the link below, its easy to install. You can also create two bat files, one to start the server and one to close it.

http://www.mysql.com/downloads/mysql-4.0.html

Open up notepad and copy the code below. Save the file to your desktop as start.bat - remember when saving a file type other then txt in notepad you must use quotes.

C:\mysql\bin\mysqld

Create another file with the code below and call it stop.bat and save it to your desktop.

C:\mysql\bin\mysqladmin -u root shutdown

Now when ever you want to start mySql all you have to do is double click on the start.bat file and click the stop.bat when you want to shut down the server.

To connect to the database you first must tell PHP what the database is, the user name and password if any you need to access the database. When accessing a MySQL database you will be using mysql_connect, to make the connection, mysql_select_db to select the database, mysql_query to send your Select statements to the database and the mysql_fetch_row to get the result of the Select statement. Below is the code that would connect to the database, select all the records in the Phonelist table and print then to the web page using the mysql_fetch_row.

------------------------- php-mysql.php -----------------------------------

< ?php

// open connection to database

$connection = mysql_connect("localhost", "user", "pw") or die ("Unable

to connect!");

// select database

mysql_select_db("phonebook") or die ("Unable to select database!");

// execute query

$query = "SELECT * FROM phonelist";

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

// iterate through rows and print column data

while ($row = mysql_fetch_row($result))< /FONT >

{

echo "$row[0] $row[1] $row[2] $row[3] $row[4] <br>";

}

// get and print number of rows in resultset

echo "< br>< br>[" . mysql_num_rows($result) . " rows returned]";

// close database connection

mysql_close($connection);

Below is the results of the Select statement from above.

-------------------------------- results -------------------------

100 Paul Rods 408 555-1212

101 Sandy Bray 408 552-1212

103 Mark Greeson 415 225-1212

104 Martha Bobellet 303 955-1212

105 Sam Briggs 415 445-1212

106 Kelly Preston 313 950-1212

107 Jamie Summers 303 295- 1212

[8 rows returned]

Adding to MySQL Database

Last time we went over connecting to the database and pulling and displaying records. This time will we go over adding items to the database, we will be using the same functions as we did when we connected to the database. The only difference this time will be in the Statement, last time we used the

"Select" statement this time around we will be using the Insert Into statement.

---------------------------- mysql-insert.php ------------------------------

< ?php

// open connection to database

$connection = mysql_connect("localhost", "user", "ps") or die ("Unable to connect!");

// select database

mysql_select_db("phonebook") or die ("Unable to select database!");

//Since all variables pulled from the web page are strings we have to

//change the IDNum variable to an integer, because the ID in the database

//is setup to accept integers if we were to try and enter the ID as is

//we would get an error message (you cant enter strings in a table that

//was setup to accept integer.

$chgID = "$_POST[IDNum]";

settype($chgID, "intger");

// execute query

$query = "Insert INTO phonelist(ID, FName, LName, PhoneNum) values ($chgID, '$_POST[Fname]', '$_POST[Lname]', '$_POST[pNum]')";

$result = mysql_query($query) or die ("Error in query: $query. " .mysql_error());

if ($restult) print "<br><br> The information has been added to the Database.";

// close database connection

mysql_close($connection);

? >

------------------------------ mysql-insert.html --------------------------------

< html>< head>< title>Entering data in a MySQL database< /title>< /head>

< body>

< form action="mysql-insert.php" method= "post">< /FONT>

Your ID Number: < input type= "text" name= "IDNum"><br>< /FONT>

Your First name: < input type="text" name= "Fname"><br>< /FONT>

Your Last name: < input type="text" name= "Lname"><br>< /FONT>

Your Phone number: < input type = "text" name = "pNum"><br> < /FONT>

< input type="submit">< /FONT>

< /form>

< /body>

< /html>

Remember earlier when we went over dealing with forms? This is the same thing we take the information from the web page input tags and add the information to the database using the _POST variables.

There are two things you might have noticed, one we had to change the ID from a string to a integer (using settype). And two, we did not put the single quote around the $chgID variable in the Insert Into statement. This is because you only put quotes around strings, if you are dealing with numbers you leave off the quotes.

Connecting to an Access Database w/PHP

Earlier we connected and added to MySQL database, this time around we will be connected and adding to an Access database. As I explained before PHP comes with variable already setup to connect and work with MySQL but with COM we can also connect to Access using ADODB. You will notice that the SQL statements are the same the only difference is that we used a different connection string. Which is why when dealing with databases if you use standard SQL statements you can work with just about any Database.

------------------------------ access-con.php ---------------------------------

< html>< head>< title> Connecting to an Access Database< /title>< /head>

< body>

< ?php

$connect =new COM("ADODB.Connection");

$connect->Provider = "MSDASQL";

if ( ! $connect ) die( "Couldn't connect to Database" );

$connect->Open("php");

$rs = $connect-> Execute("Select * From People");

$i = 0;

$fld0 = $rs->Fields(0);

$fld1 = $rs->Fields(1);

$fld2 = $rs->Fields(2);

while (!$rs->EOF) {

$i += 1;< /FONT>

print "$fld0->value $fld1->value $fld2->value<BR>";

$rs->MoveNext(); /*updates fld0, fld1, fld2 !*/

}

$rs->Close();

< /body>

< /html>

We use COM to open an ADO connection the an Access Database, what is COM you might ask? COM stands for Component Object Model and is a way for programs to use services from each other. ADO is then an API wrapper around ODBC (Open DataBase Connectivity).

You can learn more about COM on Microsoft's web site.

http://www.microsoft.com/com/

PHP the wrap up!

Ok so you're probably thinking what does PHP have to do with VB? Well like Perl PHP is also a command line scripting language, which means unlike JavaScript, VBScripts or ASP it doesn't require a web browser. So you can create scripts that can then be run on your computer like batch files or even stand-a-lone progams. You can run a script from with in VB using the Shell or ShellExecute command, of course you have to accossate the php.exe file with the php extenstion in windows.

retVal = ShellExecute(0, sTopic, sFile, """c:\vb programs\vb-php\getdir.php""", 0&, 1)

retVal = Shell("php c:\vb\vb-php\getdir.php",1)

You will notice that I have quotes inside of quotes, this si because the directory path has a space in it. "VB Programs". So you can create PHP files that can be executed using VB, below is the above script. It can be run from VB and the results of that script can be loaded into a text file your VB application.

On the second example I use the Shell command, since the shell command takes only one argument I have to call the php compiler (just remember php.exe must be setup in the autoexec.bat file in the path) then I supply the directory path to the php file I want to run.

So you can create PHP files that can be executed using VB, below is the above script. It can be run from VB and the results of that script can be loaded into a text file your VB application.

---------------------------------- getdir.php ---------------------------------

<?php

//test.php command line script

exec("dir >> dir.txt");

--------------------- Code to load the results in the text box ---------------

Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd _

As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As _

String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

______________________________________________________________________________________

Private sub GetPHPResutls()

Dim sTopic As String

Dim sFile As String

Dim sParams As Variant

Dim retVal

Dim i As Long

sTopic = "open"

sFile = "c:\httpd\php\php.exe"

sParams = """c:\vb programs\vb-php\getdir.php"""

retVal = ShellExecute(0, sTopic, sFile, sParams, 0&, 1)

'Now wait for the information to be written to file and then put

'that information in to the text box

i = 0

Do Until i = 1000000< /FONT >

i = i + 1

Loop

'Now put the contents of dir.txt in to Text1 text box

Dim intFile As Integer

Dim LentFile

intFile = FreeFile

Open App.Path & "\dir.txt" For Input As intFile

If LentFile < 0 Then Exit Sub

LentFile = LOF(intFile) - 2

Text1 = Input(LentFile, #intFile)

Close #intFile

End Sub

Once the above script is run the contents below will be displayed in the text box. In

this case it printed the contents of the dirctory that the php script was in.

-------------------------------------- results of scripts ---------------------------

Volume in drive C has no label

Volume Serial Number is 114C-0AF6

Directory of C:\VB Programs\VB-PHP

. <DIR> 05-01-03 11:53a .

.. <DIR> 05-01-03 11:53a ..

HELLO PHP 125 04-28-03 3:00p hello.php

FORM1 FRM 2,864 05-12-03 5:21p Form1.frm

PROJECT1 VBP 587 05-12-03 5:21p Project1.vbp

PROJECT1 VBW 49 05-10-03 4:31p Project1.vbw

GETDIR PHP 86 05-05-03 1:26p getdir.php

FORM1 FRX 6 05-12-03 5:21p Form1.frx

DIR TXT 0 05-12-03 5:21p dir.txt

7 file(s) 3,717 bytes

2 dir(s) 35,768.31 MB free

Volume in drive C has no label

Volume Serial Number is 114C-0AF6

Directory of C:\VB Programs\VB-PHP

. <DIR> 05-01-03 11:53a .

For more resources on PHP check out the links below.

php FAQ

PHP faqts

PHP Code examples

PHP Code Examples

PHP-GTK

PHP-GTK - Allows you to create stand-a-lone PHP applications

Lastly the web site below will allow you to retrive content via HTTP or PHP file from VB.

http://www.vbip.com/winsock/winsock_http_08_01.asp

http://www.vbip.com/comment.asp?744 - This update will allow you to pull PHP

Phase two XML

XML is short for Extensible Markup Language. Because XML is stored in plain text it is software and hardware independent. So it makes sharing information over the internet a lot easier. XML can be used to store data in files or databases, so you can create applications to retrieve and display the data. You can even use XML to create internet application for hand held devices using WML.

Over the next two weeks we will be going over XML and not only how to work with it over the internet, but how to use it with VB. But before we start writing VB programs to work with XML, the first thing we will do is go over the XML language. Like I explained in the beginning you will not need any special software, remember XML uses plain text so all you'll need is notepad or any text editor. You must also have IE4 and up since well we be using it to view our scripts, I was also able to view the XML scripts in Netscape 4.6. However it will not let you now if there are any errors in the scripts like IE5+ does.

XML The Basics

The syntax rules of XML are very simple and very strict. The rules are very easy to learn, and very easy to use. Because of this, creating software that can read and manipulate XML is very easy to do.

XML like HTML uses tags, however unlike HTML you make up the tags. Because of this you must be very careful because XML is case sensitive.

Example:

In the above example the first note is different then the second so be careful, also be careful not to mix and match the tags.

Example:

Also the above example will cause a error. It is illegal in XML not to have a closing tag! All XML documents must have a root element and all XML documents must contain a single tag pair to define a root element. All other elements must be within this root element.

<root>

<child>

</child>

</root>

One final note, unlike HTML with XML, the white space in your document is not truncated.

Our first XML script, so what does it all mean?

=========================== xmlCar.xml ==============================< /FONT >

<?xml version="1.0" encoding= "ISO-8859-1"?>< /FONT>

<cars>

<car>

<make>Buick</make>

<model>LeSabre</model>

</car>

<car>

<make>Chevrolet</make>

<model>Venture</model>

</car>

</cars>

The first line in the document - the XML declaration - defines the XML version and the character encoding used in the document. In this case the document conforms to the 1.0 specification of XML and uses the ISO-8859-1 (Latin-1/West European) character set.

The next line describes the root element of the document (like it was saying: "this document is cars")

The next 3 lines describe the child elements of the root. and the last line (</cars>) defines the end of the root element.

Entities, elements and attributes oh my!

Entity

XML structural construct. A file, database record, or another item that contains data. The primary purpose of an entity is to hold content�not structure, rules, or grammar. Each entity is identified by a unique name and contains its own content, from a single character inside the document to a large file that exists outside the document. The function of an XML entity is similar to that of a macro definition.

Element

XML structural construct. An XML element consists of a start tag, an end tag, and the information between the tags, which is often referred to as the contents. Each element has a type, identified by name, sometimes called its "generic identifier" (GI), and may have a set of attribute specifications. Each attribute specification has a name and a value. An instance of an element is declared using <element> tags.

Attribute

XML structural construct. A name-value pair, separated by an equals sign, included inside a tagged element that modifies certain features of the element. All attribute values, including things like size and width, are in fact text strings and not numbers. For XML, all values must be enclosed in quotation marks.

Ok, so you described entities, elements and attributs. How about an example of what they look like.

Element - would look like this:

<Book>

<Title>XML Programming for VB</Title> <---------- Element

</Book>

Attribute - Would look like this:

<Title>XML Programming for VB</Title>

</Book>

Entity - Would look like this:

An example of using entities to substitute entities for text strings are:

<!ENTITY BookName "XML programming for VB">

Now you can use the entity &BookName ; in a document and wherever you refer to it, the entire string of "XML programming for VB" will be substituted. In VB, this is similar to using the constant.

Another example of an Entity:

<!ENTITY writer "Jane Joyce.">

<!ENTITY copyright "Copyright XML101.">

XML example:<author>&writer;&copyright;</author>

The above would replace the &writer with "Jane Joyce." with out the quotes. And &copyright

would be replaced with "Copyright XML101.".

There are 5 predefined entity references in XML:

< < less than

> > greater than

& & ampersand

' ' apostrophe

" " quotation mark

Entity references always start with the "&" character and end with the ";" character.

What is CDATA?

In a document, a CDATA section instructs the parser to ignore most markup characters. It might contain characters that the XML parser would ordinarily recognize as markup (< and &, for example). In order to prevent this, a CDATA section can be used.

Between the start of the section, <![CDATA[ and the end of the section, ]]>, all character data is passed directly to the application, without interpretation. Elements, entity references, comments, and processing instructions are all unrecognized and the characters that comprise them are passed literally to the application.

What is DTD

DTD stands for Document Type Declarations, it provides information for the parser in which to check the validity of an XML document. More generally, declarations allow a document to communicate meta-information to the parser about its content. Meta-information includes the allowed sequence and nesting of tags, attribute values and their types and defaults, the names of external files that may be referenced and whether or not they contain XML, the formats of some external (non-XML) data that may be included, and entities that may be encountered.

The document Type declaration can contain the following:

document name

reference to an external DTD (Document Type Definition)

markup declaration (internal DTD)

parameter entity references

Below is what a DTD would look like.

film.dtd

<!DOCTYPE film [

<!ENTITY COM "Comedy">

<!ENTITY SF "Science Fiction">

<!ELEMENT film (title+,genre,year)>

<!ELEMENT title (#PCDATA)>

<!ATTLIST title

xml:lang NMTOKEN "EN"

id ID #IMPLIED>

<!ELEMENT genre (#PCDATA)>

<!ELEMENT year (#PCDATA)>

In addition to element names, the special symbol #PCDATA is reserved to indicate character data. The moniker PCDATA stands for parseable character data .

What is ETD

Element type declarations(EDT) identify the names of elements and the nature of their content.

A typical element type declaration looks like this:

<!ELEMENT oldjoke (burns+, allen, applause?)>

Valid vs Well-Formed Documents

Well-Formed

A well-formed XML document means each element bound by start tag, end tag all elements are correctly nested plus a small number of other constraints XML documents should always be well-formed. For many applications (e.g. to display a document), no need to check the DTD

Valid

a valid document a DTD is supplied (internal or external) and the document instance conforms to it.

XML DOM Parser and XML Validator

In order to read, update, create and manipulate an XML document, you need an XML parser. The Microsoft XML parser is a COM component that comes with Microsoft Internet Explorer 5.0. If I'm not mistaken IE5 comes with MSXML 2.0 I'm using MSXML 3.0 but you can download the latest version form Microsoft, MSXML 4.0 at the link below.

MSXML 4.0

http://tinyurl.com/aw2g

MSXML SDK 4.0

http://tinyurl.com/c2e6

XML parser can be used to load an XML document into the memory of your computer. When the document is loaded, it's information can be retrieved and manipulated by accessing the

Document Object Model (DOM).

A total of 13 node types are currently supported by the Microsoft XML parser. The following table lists the most commonly used node types:

Node Type Example

Document type <!DOCTYPE food SYSTEM "food.dtd">

Processing instruction <?xml version= "1.0"?>< /FONT>

Element <drink type= "beer">Carlsberg</drink>< /FONT>

Attribute type= "beer"< /FONT>

Text Carlsberg

Load XML content in to text box

The first thing we will do is load an XML file in to a text box. Below is the XML file that we will be using. Also below are several examples of how to load a single or multiple elements or the whole file.

The XML file we are using is a Well-Formed file the way we know that is it does not include a DTD file.

I have commented the code so you will know what's going on. So start up VB and put a text box and a command button and the form. Make sure the text box is large enough to display the data and make sure that Multiline is set to true. Also reference the MSXML 3.0 or the version you have installed. To create the XML file copy the XML code below and save it as xmlCars.xml.

Each of the codes below can be cut and pasted in to the Command1 click event and tested.

========================== xmlCars.xml ==== =====================

<cars>
<car>
<make>Buick</make>
<model>LeSabre</model>
<year>1987</year>
</car>
<car>
<make>Chevrolet</make>
<model>Venture</model>
<year>1999</year>
</car>
</cars>

==============================================================

Option Explicit
'First we declare the MSXML objects
Dim xmlfile As New MSXML2.DOMDocument
Dim nList As MSXML2.IXMLDOMNodeList
Dim n As MSXML2.IXMLDOMNode
_________________________________________________________________________

'This code loads the full XML file in to the txt text box
txt = xmlfile.xml

'This code loads the model portion of the car xml file.
'it will display LeSabre and Venture
txt = xmlfile.documentElement.childNodes(0).childNodes(1).xml

'This code loads the make portion using the actual name
'it will display Buick
Set n = xmlfile.selectSingleNode("/cars/car/make[text()='Buick']")
txt = n.xml

'This code will load all the child and subchild in the text box
Set nList = xmlfile.selectNodes("/cars/car[year]")
For Each n In nList
txt = txt & vbCrLf & n.xml
Next

'This code will only load the Make portion of the xml file in to the text box
Set nList = xmlfile.documentElement.getElementsByTagName("make")
For Each n In nList
txt = txt & vbCrLf & n.xml
Next

Private Sub Form_Load()
'First we load teh XML file xmlCars.xml in to memory
xmlfile.Load (App.Path & "\xmlCars.xml")
End Sub

Load XML contents in to text boxes

Last time we loaded contents of an XML file in to the text box. But it included the XML tags as well. This time we will use the "Values" option to only retrieve the text between the tags. Once again I have commented the code so you will not what's going on.

So start VB and add 5 text boxes to the form the names are as follows.

txtFirstName
txtLastName
txtStreet
txtCity
txtState
txtZip

The XML file we will be using is: values.xml

< Values>
< FirstName>Rod< /FirstName>
< LastName>Stephens< /LastName>
< Street>1234 Programmer Place< /Street>
< City>Bugsville< /City>
< State>CO< /State>
< Zip>80276< /Zip>

========================================================
Option Explicit
Private m_AppPath As String

Private Sub Form_Load()
' Get the application's startup path.
m_AppPath = App.Path
If Right$(m_AppPath, 1) <> "\" Then m_AppPath = m_AppPath & "\"

' Load the values.
LoadValues
End Sub

Private Sub Form_Unload(Cancel As Integer)
' Save the current values.
SaveValues
End Sub

' Load saved values from XML.
Private Sub LoadValues()
Dim xml_document As DOMDocument
Dim values_node As IXMLDOMNode

' Load the Values.xml document in to memory.
Set xml_document = New DOMDocument
xml_document.Load m_AppPath & "Values.xml"

'If the file doesn't exist, then
'xml_document.documentElement is Nothing.
If xml_document.documentElement Is Nothing Then
' The file doesn't exist. Do nothing.
Exit Sub
End If

' Find the Values section.
Set values_node = xml_document.selectSingleNode("Values")

' Read the saved values and add them to the text boxes.
txtFirstName.Text = GetNodeValue(values_node, "FirstName", "???")
txtLastName.Text = GetNodeValue(values_node, "LastName", "???")
txtStreet.Text = GetNodeValue(values_node, "Street", "???")
txtCity.Text = GetNodeValue(values_node, "City", "???")
txtState.Text = GetNodeValue(values_node, "State", "???")
txtZip.Text = GetNodeValue(values_node, "Zip", "???")
End Sub

' Return the node's value.
Private Function GetNodeValue(ByVal start_at_node As IXMLDOMNode, _
ByVal node_name As String, _
Optional ByVal default_value As String = "") As String
Dim value_node As IXMLDOMNode

Set value_node = start_at_node.selectSingleNode(".//" & node_name)
If value_node Is Nothing Then
GetNodeValue = default_value
Else
GetNodeValue = value_node.Text
End If
End Function

' Save the current values.
Private Sub SaveValues()
Dim xml_document As DOMDocument
Dim values_node As IXMLDOMNode

' Create the XML document.
Set xml_document = New DOMDocument

' Create the Values section node.
Set values_node = xml_document.createElement("Values")

' Add the Values section node to the document.
xml_document.appendChild values_node

' Create nodes for the values inside the
' Values section node.
CreateNode values_node, "FirstName", txtFirstName.Text
CreateNode values_node, "LastName", txtLastName.Text
CreateNode values_node, "Street", txtStreet.Text
CreateNode values_node, "City", txtCity.Text
CreateNode values_node, "State", txtState.Text
CreateNode values_node, "Zip", txtZip.Text

' Save the XML document.
xml_document.save m_AppPath & "Values.xml"
End Sub

' Add a new node to the indicated parent node.
Private Sub CreateNode(ByVal parent As IXMLDOMNode, _
ByVal node_name As String, ByVal node_value As String)
Dim new_node As IXMLDOMNode

' Create the new node.
Set new_node = parent.ownerDocument.createElement(node_name)

' Set the node's text value.
new_node.Text = node_value

' Add the node to the parent.
parent.appendChild new_node
End Sub

Create XML File from Database

The last couple of examples took an existing XML file and loaded it in to VB text boxes. The next few examples will take a database and create and XML file. The code below will take the contents of the Customer table and create a XML file called SampleXML.

We do this using MSXML and ADO so make sure that you reference both MSXML and ADO.

===================================================================
Option Explicit

Public Sub Main()
'Define ADO related variables
Dim dbcon As ADODB.Connection
Dim comsql As ADODB.Command
Dim rs As ADODB.Recordset
Dim conn As String
Dim sSQL As String

'Define XML related variables
Dim XMLFile2 As String
Dim XMLDoc As New MSXML2.DOMDocument

'Assign values to files
XMLFile2 = App.Path & "\SampleXML.xml"

'Setup database connection
conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\vb Programs\vbxml\customer.mdb"
Set dbcon = New ADODB.Connection

'Open Database
dbcon.Open conn
Set comsql = New ADODB.Command

comsql.ActiveConnection = dbcon

'Build SQL statement
sSQL = "Select CustName, CustAdd, CustCity, CustState, CustZip, CustPhone From Customer"
comsql.CommandText = sSQL
Set rs = comsql.Execute()

'Now save the records from the above sql statement to a file.
rs.save XMLFile, adPersistXML

'Clear db objects
rs.Close
dbcon.Close
Set rs = Nothing
Set dbcon = Nothing
Set comsql.ActiveConnection = Nothing
End Sub

When run the above code will create a XML file displaying all the customers and information. The other thing you will notice is that not only does it show the XML information but it also show the schema (DTD) information, making it a Valid form instead of just a Well-Formed XML file.

You can download the XML file below.

Importing XML into Database

This is another example of taking a XML file and added the records to a Database. In this example it take all the records in the Books.xml file and adds them to the Books database.

I this example the database and the table "tblBooks" along with all the field names have already been setup in the Database. However you can create the database using ADOX.Catalog and create the tables using the ADO's Create Table statement, then add the records to the database.

Once again I have commmented each line of code to give a better understanding of what's going on. Once again you must add the MSXML and ADO referance to your program.

The Books.xml file can be download below.
Books.xml

==============================================================================
Public Sub Main()
'Declare the MSXML 3.0 and ADO 2.5
Dim objXMLDOM As New MSXML2.DOMDocument30
Dim objNodes As IXMLDOMNodeList
Dim objBookNode As IXMLDOMNode
Dim objADORS As New ADODB.Recordset
Dim objADOCnn As New ADODB.Connection

'load the source XML file (Books.xml) into an MSXML DOMDocument object
objXMLDOM.async = False
'Check to make sure that Books.xml exists, if not then print error message
If Not objXMLDOM.Load("C:\vb programs\vbxml\Books.xml") Then
MsgBox "Error: " & objXMLDOM.parseError.reason
Exit Sub
End If

'See if there are any records to be inserted. It will check for all
'elements between the < book>< /book>
Set objNodes = objXMLDOM.selectNodes("//book")
If objNodes.length < = 0 Then
MsgBox "No records to be inserted!"
Exit Sub
End If

'connect to the database
objADOCnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\vb programs\vbxml\Books.mdb"

'create a blank tblBooks recordset and start adding rows.
objADORS.Open "SELECT * FROM tblBooks", objADOCnn, _
adOpenKeyset, adLockOptimistic

If Not objADORS.Supports(adAddNew) Then
MsgBox "RecordSet does not support adding new records!"
End If

'Now we're going to add all the records from the Books.xml file into the database.
On Error Resume Next
With objADORS
For Each objBookNode In objNodes

.AddNew
.Fields("Author") = _
objBookNode.selectSingleNode("author").nodeTypedValue

.Fields("BookTitle") = _
objBookNode.selectSingleNode("title").nodeTypedValue

.Fields("Genre") = _
objBookNode.selectSingleNode("genre").nodeTypedValue

.Fields("Price") = _
objBookNode.selectSingleNode("price").nodeTypedValue

.Fields("PublishDate") = _
objBookNode.selectSingleNode("publish_date").nodeTypedValue

.Fields("Description") = _
objBookNode.selectSingleNode("description").nodeTypedValue
.Update

Next objBookNode
End With
End Sub

The PHP and XML Finally

On this example we will pull records from the Nwind database under the Contacts table and create a XML document. With this one example we remove all the Schema data and just add the contacts records. To see what the XML file looks like before the schema data is removed download the contacts.xml file. To see what the file looks like when the schema data is remove see ContactsData.xml

===============================================================================
Private Sub Form_Load()
Dim objADORS As New Recordset
Dim objXMLDOM As New MSXML2.DOMDocument
Dim schemaNode As MSXML2.IXMLDOMNode

objADORS.Open "SELECT ContactName From Customers", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\Program Files\DevStudio\VB\Nwind.mdb"

'Save the recordset as a DOMDocument object
objADORS.Save objXMLDOM, adPersistXML

'Updating the generated XML document using MSXML
With objXMLDOM

'Removing schema node and hence unused namespace declarations
'First select the Schema node and then call removeChild
.setProperty "SelectionNamespaces", _
"xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'"
Set schemaNode = .selectSingleNode("//s:Schema")

With .documentElement
.removeChild schemaNode

.removeAttribute "xmlns:s"
.removeAttribute "xmlns:dt"
End With

'Using MSXML DOM Save method to save the updated document
.Save "c:\ContactsData.xml"
End With

objADORS.Close
Set objADORS = Nothing
Unload Me
End Sub

The first parameter to Recordset Save method is a DOMDocument object. Once the Recordset XML is loaded in this DOMDocument object, we then select the schema node and remove it (by calling removeChild), and then remove the namespace declarations attributes and finally'save the XML document using MSXML Save method. This method has another small benefit: even if the file c:\ContactsData.xml already exists, it is overwritten and no error is produced (unlike as in ADO Recordset Save method, which generates an error if the file already exists).

The above code creates the following XML document(c:\ContactsData.xml) which you can download at the link below.

ContactsData.xml

==============================================================================
Private Sub getData()
Dim objADORS As New Recordset

objADORS.Open "SELECT ContactName From Customers", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\Program Files\DevStudio\VB\Nwind.mdb"

objADORS.Save "c:\Contacts.xml", adPersistXML

objADORS.Close
Set objADORS = Nothing
Unload Me

End Sub

The above code connects to the Northwind sample database. Next, we open a Recordset containing ContactName field from the Customers table and save the Recordset as the XML document file named c:\Contacts.xml. The second parameter to the Save method is important here � instead of using ADO's native Advanced Data TableGram(ADTG)format, we are asking it to save the Recordset as the XML stream. Make sure that c:\Contacts.xml files does not already exists, else you'll get an error while saving the Recordset as XML

You can download the Contacts.xml file below

Contacts.xml

Conclusion

This will conclude VB: Working with PHP and XML, while I only touched on what can be done with VB and PHP and XML I hope it was enough to spark some intrest. Below you will find more resources on PHP and XML.

XML Resources

XML Introduction

Learn XML

The MSXML DOM

XML Articles

I hope you have enjoyed this months HandOn Project VB: Working with PHP and XML, may be it will spark some interest in PHP and or XML and how you can incorporate it in to VB.

String	Access Mode
r	Access the file for reading only, start access from the beginning of the file.
r+	Access the file for both reading and writing, start access from the beginnin of the file
w	Access the file for writing only, if the file exists, erase all contents. If file does not exist an attempt well be made to create the file. In either case the file access at the beginning.
w+	Access the file for both reading and writing, if the file exists erase all contents. If the file does not exist an attempt will be made to create it. In either case access to the file is at the beginning
a	Open the file for writing only. If the file doesn't exist then an attempt will be made to create it. if the file does exsit then access well be at the end of the file
a+	Open the file for reading and writing. If the file does not exsit then an attempt will be make to create it. If the file does exsit then access well be at the end of the file