- I have two MySQL tables in the same database, each with data.
- I have a PHP results page that shows the data from the first MySQL table in a dynamic PHP table.
- One of the fields in the first MySQL table is a comma-separated list of IDs that relate to data in the second MySQL table.
- I have a second, nested/embedded dynamic PHP table within the first dynamic PHP table that pulls the data from the second MySQL table when the row ID appears in the comma-separated field of the first MySQL table. (i.e. one of the columns in the first dynamic PHP table contains the second, nested dynamic PHP table with data from the second MySQL table
See the PHP/HTML below:
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$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_TravelSite, $TravelSite);
$query_Route = "SELECT * FROM SavedRoutes WHERE RouteCode = '".$_GET['rc']."'";
$Route = mysql_query($query_Route, $TravelSite) or die(mysql_error());
$row_Route = mysql_fetch_assoc($Route);
$totalRows_Route = mysql_num_rows($Route);
?>
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
</head>
<body>
<p> </p>
<p> </p>
<table border="0">
<tr>
<td>ID</td>
<td>StartPoint</td>
<td>StartPointLatLng</td>
<td>EndPoint</td>
<td>EndPointLatLng</td>
<td>Waypoints</td>
<td>Name</td>
<td>Details</td>
<td>RouteCode</td>
</tr>
<?php do { ?>
<tr>
<td><?php echo $row_Route['ID']; ?></td>
<td><?php echo $row_Route['StartPoint']; ?></td>
<td><?php echo $row_Route['StartPointLatLng']; ?></td>
<td><?php echo $row_Route['EndPoint']; ?></td>
<td><?php echo $row_Route['EndPointLatLng']; ?></td>
<td><p><?php echo $row_Route['Waypoints']; ?></p>
<?PHP $string = $row_Route['Waypoints'];
$slashstring = stripslashes($string);
$trimstring = rtrim($slashstring, "', '");
mysql_select_db($database_TravelSite, $TravelSite);
?>
<p>echo $trimstring = <?PHP echo $trimstring; ?></p>
<table border="0">
<tr>
<td>id</td>
<td>Lng</td>
<td>Lat</td>
<td>Name</td>
</tr>
<?php
mysql_select_db($database_TravelSite, $TravelSite);
$query_Markers = "SELECT * FROM markers WHERE ID IN('".$trimstring."')";
$Markers = mysql_query($query_Markers, $TravelSite) or die(mysql_error());
$row_Markers = mysql_fetch_assoc($Markers);
$totalRows_Markers = mysql_num_rows($Markers);
do { ?>
<tr>
<td><?php echo $row_Markers['id']; ?></td>
<td><?php echo $row_Markers['Lng']; ?></td>
<td><?php echo $row_Markers['Lat']; ?></td>
<td><p>
<select multiple name="waypoints" id="waypoints">
<?php
do {
?>
<option selected value="<?php echo $row_Markers['Lat'] . ", " . $row_Markers['Lng']?>"><?php echo $row_Markers['Lat'] . ", " . $row_Markers['Lng']?></option>
<?php
} while ($row_Markers = mysql_fetch_assoc($Markers));
$rows = mysql_num_rows($Markers);
if($rows > 0) {
mysql_data_seek($Markers, 0);
$row_Markers = mysql_fetch_assoc($Markers);
}
?>
</select>
</p>
<p><?php echo $row_Markers['Name']; ?></p></td>
</tr>
<?php } while ($row_Markers = mysql_fetch_assoc($Markers)); ?>
</table>
<p> </p></td>
<td><?php echo $row_Route['Name']; ?></td>
<td><?php echo $row_Route['Details']; ?></td>
<td><?php echo $row_Route['RouteCode']; ?></td>
</tr>
<?php } while ($row_Route = mysql_fetch_assoc($Route)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($Markers);
mysql_free_result($Route);
?>
Now, when ever I run the page the following happens:
- If there is only one item in the nested table (i.e. only one ID from the second table appears in the comma-separated field of the first table), then it renders perfectly.
- However, if there is more than one (i.e. multiple IDs in the comma-separated field), then it renders the first item out to infinity (and crashes the browser).
What am I doing wrong? Clearly, nested tables is not the right way of doing what I am trying to do! n00b But what is?
