nested queries, multiple tables, 3 scripts (oh my!)

[flamingo]

hi

i've posted before with another issue (host changed connection syntax, and has thrown off all the scripts), which i've partially solved, but i'm still having a couple of other things not quite right.....i have 3 scripts (i inherited them) that pick data from an access database, the first (front.php) has drop down boxes and radio buttons (drop down boxes fetch choices from the database) which determine the parameters of the search, then the actual search (runquery), then the output formatted (data.php).

i got the front.php working and this is a snippet of it:

 

<dd class="style9"><label><input type='checkbox' name='bylanguage'/>Language:</label>

<select name='lang'><br/>

 

<?php

$db_conn = new COM("ADODB.Connection");

$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./F2F.mdb").";";

$db_conn->open($connstr);

$query= $db_conn->execute("SELECT DISTINCT LanguageSpoken FROM Members_LanguagesSpoken ORDER BY LanguageSpoken");

while (!$query->EOF){

$languageid = $query->Fields("LanguageSpoken");

echo "<option value='".$languageid->value."'>".$languageid->value."</option>";

$query->MoveNext();

}

$db_conn->Close();

?>

</select><br/>

<dd class="style9"><label><input type='checkbox' name='bytopic'/>Topic of interest:</label>

<select name='topic'><br/>

 

<?php

$db_conn = new COM("ADODB.Connection");

$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./F2F.mdb").";";

$db_conn->open($connstr);

$query= $db_conn->execute("SELECT DISTINCT Topic FROM Memberexperience_all ORDER BY Topic");

#$topic_query = odbc_exec($db_conn,$query);

            while (!$query->EOF){

$topicid = $query->Fields("Topic");

echo "<option value='".$topicid->value."'>".$topicid->value."</option>";

$query->MoveNext();

}

$db_conn->Close();

?>

</select><br/>

</dl>

 

<dl class="style3">

<dt class="style9">Sort by:</dt>

<dd class="style9"><label><input type='radio' name='sortby' value='LastName' checked/>Last Name</label></dd>

<dd class="style9"><label><input type='radio' name='sortby' value='City'/>City</label>

</dd>

</dl>

<span class="style3">

<input type='submit' value='Get List'/>

 

this is displaying properly.....the runquery code i haven't touched (yet) as it didn't involve the connection string so i think it may be okay (they changed from odbc_connect to com(), which is why i've had to rewrite and change all my syntax, if you all think i need to look at that as well, speak up!)

 

the output code is where one issue is.....i have a section of it working, but i can't figure out how to do the next part (a query within the query, looking at a 2nd table for more output), entire code (so far) below (this basically works as it does go get PART of the data and makes it look nice, but not all, and not filtered at all, but this is as far as i've gone with that script as i cannot get the 2nd part syntax correct of the other query that i need), i tried doing (select table1.field1, table1.field.3, etc) and that didn't work:

 

<?php

function runquery($passedQuery="")

{

$db_conn = new COM("ADODB.Connection");

$connstr = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=". realpath("./F2F.mdb").";";

 

$db_conn->open($connstr);

$query = $passedQuery;

echo "";

$query=$db_conn->execute("SELECT First_Name, Last_Name, City, State, Zip_Code, Pub_Phonenumber, Pub_address, ContactPreference, ParticipationList FROM Members_contactinfo WHERE Inactive=false");

 

while (!$query->EOF){

$contact_info = $query->Fields("First_Name");

$contact_info1 = $query->Fields("Last_Name");

$contact_info2 = $query->Fields("City");

$contact_info3 = $query->Fields("State");

$contact_info4 = $query->Fields("Zip_Code");

$contact_info5 = $query->Fields("Pub_Phonenumber");

$contact_info6 = $query->Fields("Pub_address");

$contact_info7 = $query->Fields("ContactPreference");

$contact_info8 = $query->Fields("Pub_Email");

$contact_info9 = $query->Fields("ParticipationList");

$contact_info10 = $query->Fields("Pub_Phone");

echo "Name:  ";

echo $contact_info->Value." ";

echo $contact_info1->Value."<br/>\n";

echo "Address Info: "; 

echo $contact_info2->Value.", ";

echo $contact_info3->Value."  ";

echo $contact_info4->Value."<br/>\n";

echo "Phone Number (if public): ";

// Phone Number, if it's public

    if($contact_info10->value=1)

    {

      echo $contact_info5->Value."<br/>\n";

      }

    else

    {

    echo"<br/>";

    }

echo "Email (if public): ";

echo $contact_info6->Value."<br/>\n";

echo "Participates in: ";

echo $contact_info9->value."<br/><br/>\n";

$query->MoveNext();

}

$db_conn->Close();

}

?>

 

and just for argument's sake, this is the part of the OLD code that worked for years, along with the next section that i need to incorporate into the above code (2nd part of nested query, getting the fields from the family members table to display in the same output as each of their other information):

 

<?php

 

define("DSN", "uapdata");

define("USERNAME", "");

define("PASSWORD", "");

 

 

function runquery($passedQuery=""){

 

  $link = @odbc_connect(DSN, USERNAME, PASSWORD) or exit();

 

  $query= $passedQuery;

 

  $output = "";

 

  $contact_info_query = odbc_exec($link,$query);

 

  while($contact_info = odbc_fetch_array($contact_info_query)){

    // Print Name and Address

    $output.= "<strong>Name:</strong> ".$contact_info["First_Name"]." ".$contact_info["Last_Name"]."<br/>\n";

    //echo $contact_info["Address"]."<br/>\n";

    $output.= "<strong>City, State, Zip:</strong> ".$contact_info["City"].

      ", ".$contact_info["State"].

      " ".$contact_info["Zip_Code"]."<br/>\n";

    $output.= "<br/>\n";

 

 

    // Phone Number, if it's public

    if($contact_info["Pub_Phone"]){

      $output.= "<strong>Phone:</strong> ".$contact_info["Pub_Phonenumber"]."<br/>\n";

    }

//contact preference

if($contact_info["ContactPreference"]){

  $output.= "<strong>Contact Preference:</strong> ".$contact_info["ContactPreference"].

"<br/>\n";

    }

    // Email Address, if it's public

    if($contact_info["Pub_Email"]){

      $output.= "<strong>Email:</strong> "."<a href='mailto:".$contact_info["Pub_address"]."'>".

$contact_info["Pub_address"]."</a><br/>\n";

 

    $output.= "<br/>\n";

}

//participation

if($contact_info["ParticipationList"]){

$output.= "<strong>Participates in:</strong> ".$contact_info["ParticipationList"]."<br/>\n";

}

 

   

    // Family Members

    $output.= "<table>\n".

  "<th colspan='3' align='left'>".

      "Family Member(s) with Special Needs".

      "</th>\n";

    $query = "SELECT * FROM Members_Age WHERE Network_ID=".

      $contact_info["Network_ID"];

    $memberdata_query = odbc_exec($link,$query);

    while($memberdata = odbc_fetch_array($memberdata_query)){

      $output .= "<tr><td width='100px'>Age: ".floor($memberdata["Expr1"]).

    "</td><td width='200px'>Gender: ".$memberdata["Gender"].

    "</td><td width='400px'>Special Need: ".

    $memberdata["SpecialNeed"].

    "</td></tr><br/>\n";

    }

    $output .= "</table>\n".   

    "<br/>\n".

"<ul>\n".

"<br/><strong>Language(s) Spoken:</strong><br/>\n";

$query= "SELECT * FROM Members_LanguagesSpoken WHERE Network_ID=".

$contact_info["Network_ID"];

$lang_query = odbc_exec($link,$query);

while($language = odbc_fetch_array($lang_query)){

$output .= "<li>".$language["LanguageSpoken"]."</li>\n";

}

$output .= "</ul>\n".

"<ul>\n".

"<strong>Experience:</strong><br/>\n";

    $query = "SELECT * FROM Memberexperience_all WHERE Network_ID=".

      $contact_info["Network_ID"];

    $exp_query = odbc_exec($link,$query);

    while($experience = odbc_fetch_array($exp_query)){

      $output .= "<li>".$experience["Experience"]."</li>\n";

    }

$output .="</ul>\n".

"<br/>\n".

 

 

then how to get it ordered by city or last name (radio button choice), or search based on the first page's choices from the drop down list (category to filter by), this is a snippet of the runquery code:

 

<?php

 

require("data.php");

 

import_request_variables("p","p_");

 

$runquery = 0;

$query = "SELECT * FROM Members_contactinfo".

" WHERE Inactive=false";

 

if(!isset($p_searchtype)){

  $p_searchtype="Undef";

}

 

switch($p_searchtype){

case "allmembers":

$runquery = 1;

break;

case "restricted":

if( isset($p_byage)){

if(is_numeric($p_StartAge) && is_numeric($p_EndAge) ){

$query.=

" AND Network_ID IN".

" (SELECT Network_ID FROM Members_Age".

" WHERE Expr1 >=".$p_StartAge.

" AND Expr1 <=".$p_EndAge.")";

$runquery = 1;

} else {

$runquery = 0;

break;

}

}

 

 

if(isset($p_disability)){

  if($p_disab==""){

  $runquery=0;

break;

} else{

$query.=

" AND Network_ID IN".

" (SELECT Network_ID FROM Members_FamilyMemberDisabilityCategory".

" WHERE DisabilityCategory LIKE '".$p_disab."%')";

$runquery =1;

}

}

 

once again, any help is greatly appreciated, i've looked all over the internet and now am the proud owner of 2 books, none of which really adresses these in particular cuz it's all about MySQL most of the time (and this isn't!) i'm just trying to work with what i have and i know it's not the best way to do this, but i work for a non profit and can just hack away at what was already done, thank you for looking!