Nested loop querying mySQL, writing to XML

[StumblingCow]

//Dangit, noticed something.. testing!

 

 

Hi!

 

I'm querying a table with markers in a loop. Inside that loop is a nested loop, which takes the ID of the marker and checks if images in another table use that markerID. Code below. The markers output fine, but each of them has only the first image with markerID=1, regardless of their own ID!

 

PHP

<?php require_once('library/mysqllogin.php'); ?>
<?php


if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_geotagger, $geotagger);
$query_legendlist = "SELECT * FROM marker ORDER BY markerID ASC";
$legendlist = mysql_query($query_legendlist, $geotagger) or die(mysql_error());
$row_legendlist = mysql_fetch_assoc($legendlist);
$totalRows_legendlist = mysql_num_rows($legendlist);

$query_imagelist = "SELECT * FROM image WHERE imageID=" . $row_legendlist['markerID'] . " ORDER BY markerID ASC";
$imagelist = mysql_query($query_imagelist, $geotagger) or die(mysql_error());
$row_imagelist = mysql_fetch_assoc($imagelist);
$totalRows_imagelist = mysql_num_rows($imagelist);

$myFile = "viewer_xml/marker.xml";
$fh = fopen($myFile, 'w') or die("can't open file");

$stringData ="<markers>";
fwrite($fh, $stringData);

if($totalRows_legendlist > 0){

do {
$stringData =
	"
	<marker>
		<markerID>" . $row_legendlist['markerID'] . "</markerID>
		<markerName>" . $row_legendlist['markerName'] . "</markerName>
		<markerIcon>" . $row_legendlist['markerIcon'] . "</markerIcon>
		<markerX>" . $row_legendlist['markerX'] . "</markerX>
		<markerY>" . $row_legendlist['markerY'] . "</markerY>
	<linkedImages>";
	fwrite($fh, $stringData);
if($totalRows_imagelist > 0){
	do {
	$stringData =
	"
	<image>
	<imageID>" . $row_imagelist['imageID'] . "</imageID>
	<imageName>" . $row_imagelist['imageName'] . "</imageName>
	<imageThumb>" . $row_imagelist['imageThumb'] . "</imageThumb>
	<imageFull>" . $row_imagelist['imageFull'] . "</imageFull>
	<imageDate>" . $row_imagelist['imageID'] . "</imageDate>
	<imageInfo>" . $row_imagelist['imageInfo'] . "</imageInfo>
	</image>";
	fwrite($fh, $stringData);

	} while ($row_legendlist = mysql_fetch_assoc($imagelist));
}
	$stringData ="
	</linkedImages>
	</marker>";
	fwrite($fh, $stringData);
	} while ($row_legendlist = mysql_fetch_assoc($legendlist));
}
$stringData ="
</markers>";
fwrite($fh, $stringData);

mysql_free_result($legendlist);
mysql_free_result($imagelist);
?>

 

This is the XML output

<markers>
	<marker>
		<markerID>1</markerID>
		<markerName>derp</markerName>
		<markerIcon>BTNmarker01</markerIcon>
		<markerX>604</markerX>
		<markerY>171</markerY>
	<linkedImages>
	<image>
	<imageID>1</imageID>
	<markerID>1</markerID>
	<imageName>Karolinenplatz</imageName>
	<imageThumb>thumbs/karoplatz01.jpg</imageThumb>
	<imageFull>images/karoplatz01.jpg</imageFull>
	<imageDate>1</imageDate>
	<imageInfo>This is a placeholder text</imageInfo>
	</image>
	</linkedImages>
	</marker>
(and so on..)

 

The "image" table has only three pictures, and all of them use markerID=1.

 

And lastly, what I actually expected..

<markers>
	<marker>
		<markerID>1</markerID>
		<markerName>derp</markerName>
		<markerIcon>BTNmarker01</markerIcon>
		<markerX>604</markerX>
		<markerY>171</markerY>
	<linkedImages>
	<image>
	<imageID>5</imageID>
	<imageName>blah</imageName>
	<imageThumb>whatever.jpg</imageThumb>
	<imageFull>something</imageFull>
	<imageDate>something</imageDate>
	<imageInfo>something</imageInfo>
	</image>
	<image>
	<imageID>7</imageID>
	<imageName>something</imageName>
	<imageThumb>something</imageThumb>
	<imageFull>something</imageFull>
	<imageDate>something</imageDate>
	<imageInfo>something</imageInfo>
	</image>
	<image>
	<imageID>12</imageID>
	<imageName>something</imageName>
	<imageThumb>something</imageThumb>
	<imageFull>something</imageFull>
	<imageDate>something</imageDate>
	<imageInfo>something</imageInfo>
	</image>
	</linkedImages>
	</marker>
	<marker>
		<markerID>5</markerID>
		<markerName>hans!</markerName>
		<markerIcon>BTNmarker07</markerIcon>
		<markerX>656</markerX>
		<markerY>234</markerY>
	<linkedImages/>
	</marker>
	<marker>
		<markerID>6</markerID>
		<markerName>top</markerName>
		<markerIcon>BTNmarker07</markerIcon>
		(ETC)

 

What's wrong? Oh and if you couldn't tell from the code, my PHP knowledge is rather small, so this method might be quite hamfisted :|

[StumblingCow]

Well, and now the edit post button vanished! Testing is over but I still didn't figure out what's wrong