Creating XML document using PHP with MySQL PDO database information

Other Questions MySQL
1

I’m trying to build an XML file using PHP and pull data from my MySQL database through PDO. I have a database table called Categories that stores different category names like Travel, Education, etc. My goal is to create XML elements for each category dynamically.

<?php
$document = new DomDocument("1.0", "UTF-8");
session_start();
require("database.php");

$currentUser = $_SESSION['username'];

$mainReport = $document->createElement("Report");
$mainReport = $document->appendChild($mainReport);

$monthlyData = $document->createElement("MonthlyData");
$monthlyData = $mainReport->appendChild($monthlyData);

$query = $database->prepare("SELECT Amount FROM Transactions WHERE TransactionType='Expense' AND UserID = (SELECT UserID FROM Members WHERE Username = '$currentUser')");
$query->execute();
$amounts = $query->fetchAll(PDO::FETCH_ASSOC);

$grandTotal = 0;
foreach ($amounts as $record) {
    $grandTotal = $grandTotal + $record["Amount"];
}

echo "$grandTotal";

$categoryQuery = $database->prepare("SELECT categoryTitle FROM ExpenseCategories WHERE UserID = (SELECT UserID FROM Members WHERE Username = '$currentUser')");
$categoryQuery->execute();
$categories = $categoryQuery->fetchAll();

foreach ($categories as $record) {
    echo "<div>" . $record['categoryTitle'] . "</div>";
    $categoryNode = $document->createElement($record['categoryTitle']);
    $categoryNode = $monthlyData->appendChild($categoryNode);
    $amountNode = $document->createElement("Amount", "$150");
    $amountNode = $categoryNode->appendChild($amountNode);
}

$totalSection = $document->createElement("TotalSection");
$totalSection = $monthlyData->appendChild($totalSection);
$finalAmount = $document->createElement("Amount", '$grandTotal');
$finalAmount = $totalSection->appendChild($finalAmount);

$document->formatOutput = true;
$xmlContent = $document->saveXML();
$document->save("report.xml");
?>

The issue I’m facing is that when I try to add the calculated total value to the XML elements, it doesn’t seem to work properly. The calculation part works fine but inserting that value into the XML structure is giving me trouble. Any suggestions would be really helpful!

2

your xml structure looks good, but you’re hardcoding $150 for every category instead of calculating the real amounts. you’ll need separate queries to pull the actual totals for each categoryTitle - don’t just echo static values into those xml nodes.

3

Your XML generation breaks because you’re hardcoding $150 for every category instead of calculating actual amounts. Here’s the fix: after you get your categories, loop through each one and run SELECT SUM(Amount) FROM Transactions WHERE CategoryID = ? AND UserID = ? to grab the real totals. Use those results in your createElement call, not the static value.

Also, you’re mixing echo statements with XML generation - those div echoes will break your output if you’re serving this as XML. Kill those debug echoes or comment them out once it’s working.

I’ve built similar reporting tools and you’ve got to nail your data aggregation before touching the XML structure.

4

Your total value insertion isn’t working because of this line: $finalAmount = $document->createElement("Amount", $grandTotal); - you’re passing the literal string ‘$grandTotal’ instead of the actual variable. Just remove the quotes so it’s $document->createElement("Amount", $grandTotal);. I ran into this exact same thing building financial reports last year and it took me forever to catch.

Also, you’ve got a security issue with that SQL query. You’re using prepared statements but then sticking $currentUser directly in the query string, which kills the whole point. Try parameter binding instead: "SELECT Amount FROM Transactions WHERE TransactionType='Expense' AND UserID = (SELECT UserID FROM Members WHERE Username = ?)" then bind it with $query->execute([$currentUser]);. Way more secure and cleaner code.