The invoices table consists of all the invoices, while the companies table links the company name to the company ID number. The table invoicelineitems contain all the line items for each invoice. They are all added up, added to the mileage and expenses, and then totaled for this example.
<?php
$conn = mysql_connect("localhost") or die("Unable to connect to SQL server");
mysql_select_db("Invoicing") or die("Unable to select database");
$query = "SELECT * FROM invoices";
$currentInvoices = mysql_query($query, $conn) or die(mysql_error());
header("Content-Type: text/xml");
// The header line forces XML format to the browser
echo("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n");
echo("<invoices>\n");
// This starts the XML data with the header and the top level entry. There can be only one top level entry in XML.
while ($row = mysql_fetch_assoc ($currentInvoices)) {
$query = "SELECT comp_name FROM companies WHERE comp_id = " . $row["bill_to"];
$result = mysql_query($query, $conn) or die(mysql_error());
$row["bill_to_name"] = mysql_result($result, 0, "comp_name");
// This code will go to the companies table, and get the company name that matches the bill_to on the
invoice.
// The company name is then appended to the $row array with the bill_to_name as the array item name.
$query = "SELECT SUM(hours * rate) AS total FROM invoicelineitems WHERE invoice_no = " . $row["invoice_number"];
$result = mysql_query($query, $conn) or die(mysql_error());
$row["total_amount"] = formatDollar(mysql_result($result, 0, "total") + ($row["mileage"] * $row["mileage_rate"])+$row["expense_total"]);
// This code sumes up hours times the hourly rate from the invoicelineitems table
// It then adds that to the mileage times the milage rate and adds it to the expenses for the main
invoice.
// Again, it is added to the $row array.
echo ("<invoice id=\"". $row["invoice_number"] ."\">\n");
// This will start the entry for each invoice. Remember, each opening tag requires a closing
tag.
foreach (array_keys($row) as $item) {
// The array_keys lets us automatically generate the XML tags from the column
names in the MySQL table.
// This saves MUCH manual coding and contributes towards re-usable code.
echo ("<$item>\n");
echo ("<text>\n");
echo (htmlspecialchars($row["$item"]));
// Dumps the data from the cell to the browser. The htmlspecialcharacters
helps to ensure valid XML data formatting.
echo ("\n</text>\n");
echo ("</$item>\n");
}
echo ("</invoice>\n");
}
echo("</invoices>\n");
?>
This should show how easy it is to quickly dump data from a MySQL table quickly to XML format. This is good for reporting, or even migrating database information
from one database to another.