Cannot get this to work
I've done this before and it worked fine but now...
I have two tables with corresponding recordsets
The Parent is countries (Only two The US and the UK)
The Child is regions (States for the US and counties for the UK
I get the Countries to display OK but the regions remain blank.
This is the code for the page. Could someone cast their eye over it and tell me where I'm going wrong please?
php:
<?php error_reporting(E_ALL);
ini_set('display_errors', '1');?>
<?php require_once('Connections/localhost.php'); ?>
<?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_localhost, $localhost);
$query_rsCountry = "SELECT * FROM country";
$rsCountry = mysql_query($query_rsCountry, $localhost) or die(mysql_error());
$row_rsCountry = mysql_fetch_assoc($rsCountry);
$totalRows_rsCountry = mysql_num_rows($rsCountry);
mysql_select_db($database_localhost, $localhost);
$query_rsRegion = "SELECT * FROM regions";
$rsRegion = mysql_query($query_rsRegion, $localhost) or die(mysql_error());
$row_rsRegion = mysql_fetch_assoc($rsRegion);
$totalRows_rsRegion = mysql_num_rows($rsRegion);
?>
<?php
if ($row_rsRegion) {
echo "<SC" . "RIPT>\n";
echo "var WAJA = new Array();\n";
$oldmainid = 0;
$newmainid = $row_rsRegion["FKcountryID"];
if ($oldmainid == $newmainid) {
$oldmainid = "";
}
$n = 0;
while ($row_rsRegion) {
if ($oldmainid != $newmainid) {
echo "WAJA[".$n."] = new Array();\n";
echo "WAJA[".$n."][0] = '".WA_DD_Replace($newmainid)."';\n";
$m = 1;
}
echo "WAJA[".$n."][".$m."] = new Array();\n";
echo "WAJA[".$n."][".$m."][0] = "."'".WA_DD_Replace($row_rsRegion["regionID"])."'".";\n";
echo "WAJA[".$n."][".$m."][1] = "."'".WA_DD_Replace($row_rsRegion["regionName"])."'".";\n";
$m++;
if ($oldmainid == 0) {
$oldmainid = $newmainid;
}
$oldmainid = $newmainid;
$row_rsRegion = mysql_fetch_assoc($rsRegion);
if ($row_rsRegion) {
$newmainid = $row_rsRegion["FKcountryID"];
}
if ($oldmainid != $newmainid) {
$n++;
}
}
echo "var rsRegion_WAJA = WAJA;\n";
echo "WAJA = null;\n";
echo "</SC" . "RIPT>\n";
}
function WA_DD_Replace($startStr) {
$startStr = str_replace("'", "|WA|", $startStr);
$startStr = str_replace("\\", "\\\\", $startStr);
$startStr = preg_replace("/[\r\n]{1,}/", " ", $startStr);
return $startStr;
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<script type="text/javascript">
<!--
function WA_ClientSideReplace(theval,findvar,repvar) {
var retval = "";
while (theval.indexOf(findvar) >= 0) {
retval += theval.substring(0,theval.indexOf(findvar));
retval += repvar;
theval = theval.substring(theval.indexOf(findvar) + String(findvar).length);
}
retval += theval;
if (retval == "" && theval.indexOf(findvar) < 0) {
retval = theval;
}
return retval;
}
function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
if(!x && d.getElementById) x=d.getElementById(n); return x;
}
function WA_UnloadList(thelist,leavevals,bottomnum) {
while (thelist.options.length > leavevals+bottomnum) {
if (thelist.options[leavevals]) {
thelist.options[leavevals] = null;
}
}
return leavevals;
}
function WA_FilterAndPopulateSubList(thearray,sourceselect,targetselect,leaveval,bottomleave,usesource,delimiter) {
if (bottomleave > 0) {
leaveArray = new Array(bottomleave);
if (targetselect.options.length >= bottomleave) {
for (var m=0; m<bottomleave; m++) {
leavetext = targetselect.options[(targetselect.options.length - bottomleave + m)].text;
leavevalue = targetselect.options[(targetselect.options.length - bottomleave + m)].value;
leaveArray[m] = new Array(leavevalue,leavetext);
}
}
else {
for (var m=0; m<bottomleave; m++) {
leavetext = "";
leavevalue = "";
leaveArray[m] = new Array(leavevalue,leavetext);
}
}
}
startid = WA_UnloadList(targetselect,leaveval,0);
mainids = new Array();
if (usesource) maintext = new Array();
for (var j=0; j<sourceselect.options.length; j++) {
if (sourceselect.options[j].selected) {
mainids[mainids.length] = sourceselect.options[j].value;
if (usesource) maintext[maintext.length] = sourceselect.options[j].text + delimiter;
}
}
for (var i=0; i<thearray.length; i++) {
goodid = false;
for (var h=0; h<mainids.length; h++) {
if (thearray[i][0] == mainids[h]) {
goodid = true;
break;
}
}
if (goodid) {
theBox = targetselect;
theLength = parseInt(theBox.options.length);
theServices = thearray[i].length + startid;
var l=1;
for (var k=startid; k<theServices; k++) {
if (l == thearray[i].length) break;
theBox.options[k] = new Option();
theBox.options[k].value = thearray[i][l][0];
if (usesource) theBox.options[k].text = maintext[h] + WA_ClientSideReplace(thearray[i][l][1],"|WA|","'");
else theBox.options[k].text = WA_ClientSideReplace(thearray[i][l][1],"|WA|","'");
l++;
}
startid = k;
}
}
if (bottomleave > 0) {
for (var n=0; n<leaveArray.length; n++) {
targetselect.options[startid+n] = new Option();
targetselect.options[startid+n].value = leaveArray[n][0];
targetselect.options[startid+n].text = leaveArray[n][1];
}
}
for (var l=0; l < targetselect.options.length; l++) {
targetselect.options[l].selected = false;
}
if (targetselect.options.length > 0) {
targetselect.options[0].selected = true;
}
}
//-->
</script>
</head>
<body>
<form action="" method="post" name="form1" id="form1">
<table>
<tr>
<th scope="row">country</th>
<td><select name="country" id="country" onchange="WA_FilterAndPopulateSubList(rsRegion_WAJA,MM_findObj('country'),MM_findObj('regions'),1,0,false,': ')">
<option value="">Please choose</option>
<?php
do {
?>
<option value="<?php echo $row_rsCountry['countryID']?>"><?php echo $row_rsCountry['countryName']?></option>
<?php
} while ($row_rsCountry = mysql_fetch_assoc($rsCountry));
$rows = mysql_num_rows($rsCountry);
if($rows > 0) {
mysql_data_seek($rsCountry, 0);
$row_rsCountry = mysql_fetch_assoc($rsCountry);
}
?>
</select></td>
</tr>
<tr>
<th scope="row">region</th>
<td><select name="regions" id="regions">
<option value="">Choose country first</option>
<?php
do {
?>
<option value="<?php echo $row_rsRegion['regionID']?>"><?php echo $row_rsRegion['regionName']?></option>
<?php
} while ($row_rsRegion = mysql_fetch_assoc($rsRegion));
$rows = mysql_num_rows($rsRegion);
if($rows > 0) {
mysql_data_seek($rsRegion, 0);
$row_rsRegion = mysql_fetch_assoc($rsRegion);
}
?>
</select></td>
</tr>
<tr>
<th scope="row"> </th>
<td> </td>
</tr>
</table>
</form>
</body>
</html>
<?php
mysql_free_result($rsCountry);
mysql_free_result($rsRegion);
?>
This is the SQL
--
-- Table structure for table `country`
--
CREATE TABLE IF NOT EXISTS `country` (
`countryID` varchar(30) NOT NULL,
`countryName` varchar(30) NOT NULL,
PRIMARY KEY (`countryID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Table structure for table `regions`
--
CREATE TABLE IF NOT EXISTS `regions` (
`regionID` int(11) NOT NULL AUTO_INCREMENT,
`FKcountryID` int(2) NOT NULL DEFAULT '1',
`regionName` varchar(128) NOT NULL,
`regionAbbr` varchar(8) DEFAULT NULL,
PRIMARY KEY (`regionID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
Thanks in advance.
