Member Avatar for phouse512

Hello all, I'm working on a comment script to produce a string of XML to be used for one of my ajax applications. However, I cannot figure out how to produce the xml. Assume that the passing of the parent_id variable is working for now.

Here's the code I'm using to export the XML.

<?php
    
	$parent_id = $_GET['parent_id'];
	
	$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
	$comment_query = "SELECT * FROM comments WHERE parent_id = '$parent_id' ORDER BY post_time ASC";
	$comment_data = mysqli_query($dbc, $comment_query);
	$comment_rows = mysqli_fetch_array($comment_data);
	
	
	xmlOutput = '<?xml version="1.0"?>';
	xmlOutput .= '<comments>';
	
	while($comment_rows){
		xmlOutput .= '<comment>';
		xmlOutput .= '<comment_id>'. $comment_rows['comment_id'] .'</comment_id>';
		xmlOutput .= '<poster_id>'. $comment_rows['poster_id'] . '</poster_id>';
		
		//Get username
		$username_query = "SELECT username FROM wwit_user WHERE user_id = '$comment_rows['poster_id']'";
		$username_data = mysqli_query($dbc, $username_query);
		$username_rows = mysqli_fetch_array($username_data);
		
		xmlOutput .= '<poster_name>'. $username_rows['username'] .'</poster_name>';
		xmlOutput .= '<comment_text>'. $comment_rows['comment_text'] .'</comment_text>';
		xmlOutput .= '<comment_time>'. $comment_rows['post_time'] .'</comment_time>';
		xmlOutput .= '</comment>';
	}
	xmlOutput .= '</comments>';
	header('Content-Type: application/xml;');
	echo xmlOutput;
?>

Thanks!

  • 3 Contributors
  • 2 Replies
  • 107 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by hielo

Dani AI

Generated

The code in the first post is on the right track but shows several common pitfalls that cause broken XML, SQL errors and security problems. Typical issues here are: building the XML by fragile string concatenation, not fetching rows correctly inside the loop, doing a per-row username query (N+1 problem), missing input validation (SQL injection risk), and not handling encoding/headers (UTF-8 and the XML declaration). As noted, returning a simpler payload (JSON or a compact delimited string) is often easier; if XML is required, use safer tools.

Use one parameterized query with a JOIN to fetch comments plus usernames in a single pass, validate parent_id, and set the connection charset to UTF-8. Example SQL (use with a prepared statement):

SELECT c.comment_id, c.poster_id, u.username, c.comment_text, c.post_time
FROM comments AS c
LEFT JOIN wwit_user AS u ON c.poster_id = u.user_id
WHERE c.parent_id = ?
ORDER BY c.post_time ASC

Prefer prepared statements over manual escaping; see mysqli prepared statements. Set charset with mysqli::set_charset to avoid mangled characters: mysqli::set_charset.

Generate XML with an XML-aware API instead of raw concatenation. XMLWriter or DOMDocument will correctly escape text nodes and let you stream output (important for large result sets). See XMLWriter and DOMDocument. If you must escape by hand, use htmlspecialchars(..., ENT_XML1, 'UTF-8') for text nodes: htmlspecialchars. Always send the charset-aware header Content-Type: application/xml; charset=utf-8 and ensure no output (including BOM or whitespace) precedes the XML declaration: header.

As suggested, fetch rows inside a proper loop and check every DB call for errors (log mysqli_error when needed). For best results: single JOIN query, prepared statements, XMLWriter/DOM to emit well-formed XML, and explicit charset handling — or switch to JSON for simpler client parsing.

Recommended Answers

All 2 Replies

Member Avatar for MooGeek

why do you have to choose XML? I think its pretty darn easy if you just output the whole comment and then let the AJAX do the work. for example

|Darwin/November 15 1988/12:21AM/Hey!
|Anna/November 15 1988/12:41AM/What?

anyway, if you insist, this might help you

Member Avatar for hielo

try:

<?php
	$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die("Unable to connect to the db server");

	$parent_id = mysqli_real_escape_string($dbc, $_GET['parent_id']);

	$comment_query = "SELECT comment_id,poster_id,comment_text,post_time FROM comments WHERE parent_id = '$parent_id' ORDER BY post_time ASC";
	$comment_data = mysqli_query($dbc, $comment_query);
	
	$xmlOutput = '<?xml version="1.0"?>';
	$xmlOutput .= '<comments>';
	
	while( $comment_rows = mysqli_fetch_assoc($comment_data) ){
		//Get username
		$username_query = sprintf("SELECT username FROM wwit_user WHERE user_id = '%s' LIMIT 0, 1", mysqli_real_escape_string($dbc, $comment_rows['poster_id']);
		$username_data = mysqli_query($dbc, $username_query);
		$username_rows = mysqli_fetch_assoc($username_data);

		$xmlOutput .= '<comment>';
			$xmlOutput .= '<comment_id>'. htmlentities($comment_rows['comment_id'],ENT_QUOTES) .'</comment_id>';
			$xmlOutput .= '<poster_id>'. htmlentities($comment_rows['poster_id'],ENT_QUOTES) . '</poster_id>';
			$xmlOutput .= '<poster_name>'. htmlentities($username_rows['username'],ENT_QUOTES) .'</poster_name>';
			$xmlOutput .= '<comment_text>'. htmlentities($comment_rows['comment_text'],ENT_QUOTES) .'</comment_text>';
			$xmlOutput .= '<comment_time>'. htmlentities($comment_rows['post_time'],ENT_QUOTES) .'</comment_time>';
		$xmlOutput .= '</comment>';
		mysqli_free_result($username_data);
	}
	mysqli_free_result($comment_data);
	$xmlOutput .= '</comments>';
	header('Content-Type: application/xml;');
	echo $xmlOutput;
exit;
?>
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.