RetrievingDataFromMySQLWithPHP
From OpenLaszlo
This is working code for retrieving simple data from a MySQL 5.0 database with PHP 5.0, and outputting it as XML for OL to use.
It has been written to work with the tutorial in the OpenLaszlo 4.2 Developers Guide.
Contents |
[edit]
The example database table
Create your database using whatever MySQL tool you're comfortable with, then create the table mentioned in the developers guide.
As a guide, this SQL will create the right kind of table, though it is done very simply and has no speed optimising features turned on:
CREATE TABLE contact (email varchar(40) PRIMARY KEY, first_name varchar(20), last_name varchar(20), phone varchar(20));
Add two rows of example data, so we have something to test with. This will work, but feel free to add your own instead if you want:
INSERT INTO contact (email, first_name, last_name, phone) VALUES ('some_email@example.com', 'Justin', 'Clift', '123456789');
INSERT INTO contact (email, first_name, last_name, phone) VALUES ('some_other_email@example.com', 'Anonymous', 'Person', '987654321');
[edit]
Reading data from the database, and presenting it as XML
Equivalent to the getcontacts.jsp example in the OpenLaszlo 4.2 Developers Guide.
[edit]
A version that presents the elements as attributes
<?php
// Connect to the database
global $link;
$link = mysqli_connect("mysql_server", "username", "password", "database");
// Verify the connection worked
if (!$link)
{
printf("Connection to database failed: %s\n", mysqli_connect_error());
exit();
}
// Prepare the database query
$stmt = mysqli_prepare($link, "SELECT email, first_name, last_name, phone FROM contact");
// Run the database query
mysqli_stmt_execute($stmt);
// Bind the result columns to PHP variables
mysqli_stmt_bind_result($stmt, $email, $first_name, $last_name, $phone);
// Create a new XML document in memory
$xw = new xmlWriter();
$xw->openMemory();
$xw->startDocument('1.0','UTF-8');
// Start the outer data container
$xw->startElement('phonebook');
// Fetch values
while (mysqli_stmt_fetch($stmt))
{
$xw->startElement('contact');
// Write out the data as attributes
$xw->writeAttribute('firstName', $first_name);
$xw->writeAttribute('lastName', $last_name);
$xw->writeAttribute('email', $email);
$xw->writeAttribute('phone', $phone);
$xw->endElement();
}
// End container
$xw->endElement();
// End the document
$xw->endDocument();
// Output the document
header('Content-Type: text/xml');
print $xw->outputMemory(true);
// Close the database statement
mysqli_stmt_close($stmt);
// Close the database connection
mysqli_close($link);
?>
[edit]
Same thing, but this presents the elements as nodes
Probably not what you want, as it's slower to process, but mentioning it here for completeness.
<?php
// Connect to the database
global $link;
$link = mysqli_connect("mysql_server", "username", "password", "database");
// Verify the connection worked
if (!$link)
{
printf("Connection to database failed: %s\n", mysqli_connect_error());
exit();
}
// Prepare the database query
$stmt = mysqli_prepare($link, "SELECT email, first_name, last_name, phone FROM contact");
// Run the database query
mysqli_stmt_execute($stmt);
// Bind the result columns to PHP variables
mysqli_stmt_bind_result($stmt, $email, $first_name, $last_name, $phone);
// Create a new XML document in memory
$xw = new xmlWriter();
$xw->openMemory();
$xw->startDocument('1.0','UTF-8');
// Start the outer data container
$xw->startElement('phonebook');
// Fetch values
while (mysqli_stmt_fetch($stmt))
{
$xw->startElement('contact');
// Write out the data elements
$xw->writeElement ('firstName', $first_name);
$xw->writeElement ('lastName', $last_name);
$xw->writeElement ('email', $email);
$xw->writeElement ('phone', $phone);
$xw->endElement();
}
// End container
$xw->endElement();
// End the document
$xw->endDocument();
// Output the document
header('Content-Type: text/xml');
print $xw->outputMemory(true);
// Close the database statement
mysqli_stmt_close($stmt);
// Close the database connection
mysqli_close($link);
?>
[edit]
Verify it's working
To verify the PHP code is working correctly, access it directly from a browser:
http://HOSTNAME/getcontacts.php
It should give a result (in Firefox) like:
-<phonebook> <contact firstName="Justin" lastName="Clift" email="some_email@example.com" phone="123456789"/> <contact firstName="Anonymous" lastName="Person" email="some_other_email@example.com" phone="987654321"/> </phonebook>
[edit]
OpenLaszlo code
This is the OpenLaszlo code from the Developers Guide, (very slightly) adjusted to call the PHP script above instead of a JSP file:
<canvas height="200" width="100%" bgcolor="#D4D0C8">
<dataset name="dset" src="http://HOSTNAME/getcontacts.php" request="true" type="http"/>
<class name="contactview" extends="view" visible="false" x="20" height="120">
<text y="10">First Name:</text>
<edittext name="firstName" datapath="@firstName" x="80" y="10"/>
<text y="35">Last Name:</text>
<edittext name="lastname" datapath="@lastName" x="80" y="35"/>
<text y="60">Phone:</text>
<edittext name="phone" datapath="@phone" x="80" y="60"/>
<text y="85">Email:</text>
<edittext name="email" datapath="@email" x="80" y="85"/>
</class>
<simplelayout axis="y"/>
<view>
<simplelayout axis="y"/>
<text onclick="parent.newContact.setAttribute('visible', !parent.newContact.visible);">New Entry...</text>
<contactview name="newContact" datapath="new:/contact">
<button width="80" x="200" y="10">Add
<handler name="onclick">
parent.datapath.updateData();
var dp=canvas.datasets.dset.getPointer();
dp.selectChild();
dp.addNodeFromPointer( parent.datapath );
parent.setAttribute('visible', false);
parent.setAttribute('datapath', "new:/contact");
</handler>
</button>
</contactview>
</view>
<view datapath="dset:/phonebook/contact">
<simplelayout axis="y"/>
<view name="list" onclick="parent.updateContact.setAttribute('visible', !parent.updateContact.visible);">
<simplelayout axis="x"/>
<text datapath="@firstName"/>
<text datapath="@lastName"/>
<text datapath="@phone"/>
<text datapath="@email"/>
</view>
<contactview name="updateContact">
<button width="80" x="200" y="10">Update
<handler name="onclick">
parent.parent.datapath.updateData();
</handler>
</button>
<button width="80" x="200" y="40">Delete
<handler name="onclick">
parent.parent.datapath.deleteNode();
</handler>
</button>
</contactview>
</view>
</canvas>
[edit]
A simpler example
Please note though, that the above code is simply copied from the Developers Guide, and is specific to the things being taught there.
If you just want a simple example, then this may be clearer:
<canvas height="400" width="400"> <dataset name="dset" src="http://HOSTNAME/getcontacts.php" request="true" type="http"/> <window title="Phone book entries" width="600" height="250" resizable="true" closeable="true"> <simplelayout axis="y"/> <view datapath="dset:/phonebook/contact"> <view name="list"> <text datapath="@firstName"/> <text datapath="@lastName"/> <text datapath="@phone"/> <text datapath="@email"/> <simplelayout axis="x"/> </view> </view> </window> </canvas>
