[SOLVED] DOM, MYSQL, and PHP loop

[ewalsh]

I am tryiing to create XML for Google Maps, and I am using DOM to create the XML nodes. The query I am running returns entries for individual meetings, but since there are sometimes rows where all of the data is the same except for the time information of that meeting, I am using an if statement to loop the DOM to create multiple text nodes for the same element (<schedule></schedule>). Unfortunately the dom sees this loop as a new row and is creating a parent node <meeting> around each listing of a new <schedule>) instead of just around each meeting as a whole including multiple listings of <schedule>. See XML output below. What I would like to see is the <meeting> node before the <name> element, and after the last listing of <schedule>. This xml is created from 3 mysql rows being put through the php shown below.  Also, I have attached the data coming out of mysql. I hope this clarifies things a bit. If this cannot be done with DOM any othey suggestions would be appreciated.

 

 

<?xml version="1.0"?>

<meetings>

<meeting>

<name>San Carlos Group</name>

<address>Grace Presbyterian<BR>7470 Hickory Dr<BR>Fort Myers, FL 33912</address>

<lat>26.459139</lat>

<lng>-81.825691</lng>

<schedule>Mo 08:00:00 CD</schedule>

</meeting>

<meeting>

<schedule>We 08:00:00 CSt</schedule>

</meeting>

<meeting>

<schedule>Fr 08:00:00 OSp</schedule>

</meeting>

</meetings>

 

 

 

$global_dbh = mysql_connect($db_host, $db_name, $db_pass)

OR die("Could not connect to database");

mysql_select_db($db_db, $global_dbh)

OR die("Could not select database");

 

function display_meeting($db_connection)

{

$query = "SELECT MEETINGS.PRIME_UID,NAME,FELLOW,STREET1,STREET2,CITY,STATE,ZIP,COUNTRY,LAT,LON,DAY,TIME,TYPE,OPEN

FROM MEETINGS, ADDRESS, MARKERS, WAW

WHERE MARKERS.PRIME_UID = WAW.MARK_UID

and MEETINGS.PRIME_UID = WAW.MEET_UID

and ADDRESS.MARK_UID = MARKERS.PRIME_UID

and CURRENT = 'Y'

order by MEETINGS.PRIME_UID";

 

$result_id = mysql_query($query, $db_connection)

OR die(mysql_error());

 

 

$dom = new DOMDocument("1.0");

$node = $dom->createElement("meetings");

$node = $dom->appendChild($node);

//header("Content-type: text/xml");

 

$old_meeting_id = 0;

 

while ($row_array = mysql_fetch_row($result_id))

{

$outer_table = 'meeting';

$outer = $dom->createElement($outer_table);

 

$meeting_id = $row_array[0];

 

if ($meeting_id != $old_meeting_id){

 

$meeting_address = $row_array[3]."<BR>".$row_array[4]."<BR>".$row_array[5].", ".$row_array[6]." ".$row_array[7];

 

    $child = $dom->createElement("name");

    $child = $outer->appendChild($child);

    $value = $dom->createTextNode($row_array[1]);

    $value = $child->appendChild($value);

   

    $child = $dom->createElement("address");

    $child = $outer->appendChild($child);

    $value = $dom->createTextNode($meeting_address);

    $value = $child->appendChild($value);

   

    $child = $dom->createElement("lat");

    $child = $outer->appendChild($child);

    $value = $dom->createTextNode($row_array[9]);

    $value = $child->appendChild($value);

   

    $child = $dom->createElement("lng");

    $child = $outer->appendChild($child);

    $value = $dom->createTextNode($row_array[10]);

    $value = $child->appendChild($value);

   

   

  $old_meeting_id = $meeting_id;

  }

    $meeting_sched = $row_array[11]." ".$row_array[12]." ".$row_array[13];

    $child = $dom->createElement("schedule");

    $child = $outer->appendChild($child);

    $value = $dom->createTextNode($meeting_sched);

    $value = $child->appendChild($value);

  $outer = $node->appendChild($outer);

 

 

} // while

$node = $dom->appendChild($node);

echo $dom->saveXML();

}

 

display_meeting($global_dbh);

?>

 

[attachment deleted by admin]

[fenway]

What do you have now, what do you want it to do?

[ewalsh]

The results currently are:

 

<?xml version="1.0"?>

<meetings>

<meeting>

<name>San Carlos Group</name>

<address>Grace Presbyterian<BR>7470 Hickory Dr<BR>Fort Myers, FL 33912</address>

<lat>26.459139</lat>

<lng>-81.825691</lng>

<schedule>Mo 08:00:00 CD</schedule>

</meeting>

<meeting>

<schedule>We 08:00:00 CSt</schedule>

</meeting>

<meeting>

<schedule>Fr 08:00:00 OSp</schedule>

</meeting>

</meetings>

 

 

I would like it to be:

 

<?xml version="1.0"?>

<meetings>

<meeting>

<name>San Carlos Group</name>

<address>Grace Presbyterian<BR>7470 Hickory Dr<BR>Fort Myers, FL 33912</address>

<lat>26.459139</lat>

<lng>-81.825691</lng>

<schedule>Mo 08:00:00 CD</schedule>

<schedule>We 08:00:00 CSt</schedule>

<schedule>Fr 08:00:00 OSp</schedule>

</meeting>

</meetings>

 

Thanks,

 

Evan

[fenway]

You've opening a meeting element _inside_ the loop...

[ewalsh]

I know it is inside the loop, but I need it to apply for each meeting pulled from the mysql array. If I put the meeting element outside of the array, I only get the start node <meeting> at the very beginning of the XML and appended node </meeting> after the last meeting listing in the XML. While this would work with a single meeting, when multiples are present it fails.

 

Thanks,

 

Evan

[fenway]

I mean inside the loop of meetings is good, but not the schedules.

[ewalsh]

AH, Solved. I kept thinking the issue was with the append child not the create element node. Here is the working solution. Thanks for the help

function display_meeting($db_connection)
{
$query = "SELECT MEETINGS.PRIME_UID,NAME,FELLOW,STREET1,STREET2,CITY,STATE,ZIP,COUNTRY,LAT,LON,DAY,TIME,TYPE,OPEN
FROM MEETINGS, ADDRESS, MARKERS, WAW
WHERE MARKERS.PRIME_UID = WAW.MARK_UID
and MEETINGS.PRIME_UID = WAW.MEET_UID
and ADDRESS.MARK_UID = MARKERS.PRIME_UID
and CURRENT = 'Y'
order by MEETINGS.PRIME_UID";

$result_id = mysql_query($query, $db_connection)
OR die(mysql_error());


$dom = new DOMDocument("1.0");
$node = $dom->createElement("meetings");
$node = $dom->appendChild($node);
//header("Content-type: text/xml");

$old_meeting_id = 0;





while ($row_array = mysql_fetch_row($result_id))
{


$meeting_id = $row_array[0];



if ($meeting_id != $old_meeting_id){

$outer_table = 'meeting';
$outer = $dom->createElement($outer_table);[/color]


	$meeting_address = $row_array[3]."<BR>".$row_array[4]."<BR>".$row_array[5].", ".$row_array[6]." ".$row_array[7];

    $child = $dom->createElement("name");
    $child = $outer->appendChild($child);
    $value = $dom->createTextNode($row_array[1]);
    $value = $child->appendChild($value);
    
    $child = $dom->createElement("address");
    $child = $outer->appendChild($child);
    $value = $dom->createTextNode($meeting_address);
    $value = $child->appendChild($value);
    
    $child = $dom->createElement("lat");
    $child = $outer->appendChild($child);
    $value = $dom->createTextNode($row_array[9]);
    $value = $child->appendChild($value);
    
    $child = $dom->createElement("lng");
    $child = $outer->appendChild($child);
    $value = $dom->createTextNode($row_array[10]);
    $value = $child->appendChild($value);
    
    
   $old_meeting_id = $meeting_id;
  }
    $meeting_sched = $row_array[11]." ".$row_array[12]." ".$row_array[13];
    $child = $dom->createElement("schedule");
    $child = $outer->appendChild($child);
    $value = $dom->createTextNode($meeting_sched);
    $value = $child->appendChild($value);
  	$outer = $node->appendChild($outer);


} // while
	$node = $dom->appendChild($node);
                echo $dom->saveXML();
}

display_meeting($global_dbh);
?>