nesting queries within another query's Switch statement?

imbedding queries within the switch statement of another query

[DebW]

I am trying to replace over 500 -- and growing, pages related to physical communities my site represents. On this dynamic document, with help from this group, I've set up the dynamic Visitors' menu (of inter-document links to different types of visitors' services details (images, contact info, and descriptions) listed further down in the document) based on what services are available from my db, for each specific community.

The menu and the named headers were both created with a switch statement.

Now, I am trying to repeat the same switch to provide headers associated with each link, and then imbed inside a second query/switch statement to capture the details for each row captured. In other words, the first query's (menu) links to the second query's named headers, and I want a third query to run within the second query's switch statements, to capture the detailed records associated with each of the different headers. I created the third query as a function (called "ContactsInfo()", to reduce the filesize.

I am not getting any syntax errors, but the page chokes on the function and stops the second query at the point where the function is encountered.

Is there a rule against imbedding queries?

Here is the code for the 2nd query (which works without the function):

$query = "SELECT DISTINCT `Category` FROM `Contacts` WHERE `Category` NOT IN ('Individual Member', 'Group Member', 'Visitor Source', 'Trail Project', 'Restoration Project', 'Event Project', 'Other', '') AND `PriSt` = '$State' AND `Stop` = '$Stop' ORDER BY `Category` ASC";
$result = mysql_query($query)
or die ("No Visitors' Services Found.");

while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
$Category = $row["Category"];
switch ($Category)
{
case 'Activity':
echo ("<a name=\"Do\"><h4>Recreation</h4></a>");
ContactsInfo();
echo "<hr/>";
break;
case ...
default:
echo ("Sorry, TMRT has no Visitors' Services on record for this Stop.");
}

}

Here is the function:

function ContactsInfo()
{
global $Category, $State, $Stop, $GrpName, $NameFirst, $NameMI, $NameLast, $ImageName, $PriSt, $Stop, $sMailStreet1, $sMailStreet2, $sMailCity, $sMailState, $sMailZip, $eMail, $vPhone1, $vPhone2, $fPhone, $URL, $Description;
$query2 = 'SELECT `GrpName`, `NameFirst`, `NameMI`, `NameLast`, `ImageName`, `sMailStreet1`, `sMailStreet2`, `sMailCity`, `sMailState`, `sMailZip`, `eMail`, `vPhone1`, `vPhone2`, `fPhone`, `URL`, `Description` FROM `Contacts` WHERE `Category` = $Category AND `PriSt` = $State AND `Stop` = $Stop ORDER BY `GrpName` ASC';
$result2 = mysql_query($query2)
or die ("Couldn't execute query.");

while ($row2 = mysql_fetch_array($result2,MYSQL_ASSOC))
{
extract ($row2);
$ImageName= $row22["ImageName"];
$GrpName= $row2["GrpName"];
$NameFirst= $row2["NameFirst"];
$NameMI= $row2["NameMI"];
$NameLast= $row2["NameLast"];
$ImageName= $row2["ImageName"];
$sMailStreet1= $row2["sMailStreet1"];
$sMailStreet2= $row2["sMailStreet2"];
$sMailCity= $row2["sMailCity"];
$sMailState= $row2["sMailState"];
$sMailZip= $row2["sMailZip"];
$eMail= $row2["eMail"];
$vPhone1= $row2["vPhone1"];
$vPhone2= $row2["vPhone2"];
$fPhone= $row2["fPhone"];
$URL= $row2["URL"];
$Description= $row2["Description"];
echo "<div id=\"TourPhoto\">";
if ($ImageName!= '')
{
echo "<img src=\"../SiteImages/$ImageName\">";
}
else
{
echo "<img src=\"../SiteImages/StopAboard.JPG\">";
}
echo "</div>"; // end of TourPhoto
echo "<div id=\"TourContact\">";
if ($GrpName!= '')
{
echo "$GrpName<br/>";
}
else
{
echo "$NameFirst $NameMI $NameLast<br/>";
}
if ($eMail!= '')
{
echo "$eMail<br/>";
}
echo "$sMailStreet1<br/>";
if ($sMailStreet2!= '')
{
echo "$sMailStreet2<br/>";
}
echo "$sMailCity, $sMailState $sMailZip<br/>" ;
echo "Ph. $vPhone1<br/>";
if ($vPhone2!= '')
{
echo " or $vPhone2<br/>";
}
if ($fPhone!= '')
{
echo "Fx. $fPhone<br/>";
}
if ($URL!= '')
{
echo "Web: $URL<br/>";
}
echo "</div>"; // end of TourContact

echo "<div id=\"TourDesc\">";
if ($Description!= '')
{
echo "$Description<br/>";
}
echo "</div>"; // end of TourDesc
}
}

[edited by: jatar_k at 9:11 pm (utc) on Oct. 3, 2006]
[edit reason] fixed sidescroll [/edit]

[eelixduppy]

Hello.

There aren't any rules against this, although you generally want to try to avoid it for obvious reasons. It seems that you can just combine your queries into one:

$query = "SELECT * FROM `Contacts` WHERE `Category` NOT IN ('Individual Member', 'Group Member', 'Visitor Source', 'Trail Project', 'Restoration Project', 'Event Project', 'Other', '') AND `PriSt` = '$State' AND `Stop` = '$Stop' ORDER BY `Category` ASC"; 

[DebW]

eelixduppy;

Thanks for the reply. Your suggestion allows the page to fully produce, with:
1. a warning: "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ...Test.php on line 242"

2. It now repeats the headers for as many detail records that exist, though none of the detail records information is displayed.

Going back to the books..... 8¦

I suppose this is the fun part of PHP? ;)

[coopster]

Can be :)

It's all part of learning the language ... any language actually. There is a thread in our PHP Forum Library [webmasterworld.com] that you might find very helpful when Troubleshooting [webmasterworld.com].

[DebW]

Thanks, coopster -- I've bookmarked the troubleshooting page to remind myself, for the next time.

FYI -- I did have to go back to the query within the query, but got it to work by changing the variable names -- after I discovered it was choking on overlapping variables.

Deb