I have a MySQL database, 'astdpen_members', with four tables: Members, AreasOfExpertise, IndustryExperience, SoftwareSkills.
All tables in the database have the same primary key (named MemberID).
I am trying to develop a 'drop down' query menu.
The page with the 'drop down' menus would eventually look like this ( http://www.astd-pensacola.org/images/query_screen.gif ), but presently I am only building one select at a time:
So far, I have the query page (also located here: http://www.astd-pensacola.org/testform.php ) with the following form elements:
<form action="results.php" method="post">
<select name="aoesearch">
<option value="choose" selected>Choose One</option>
<option value="adult_learning">Adult Learning</option>
<option value="analysis_impact">Analysis (Impact)</option>
<option value="analysis_job">Analysis (Job)</option>
<option value="analysis_needs">Analysis (Needs)</option>
<option value="analysis_task">Analysis (Task)</option>
<option value=" business_development">Business Development</option>
<option value="computer_based_training_cbt">Computer-based Training (CBT)</option>
<option value="change_management">Change Management</option>
<option value="coaching_and_mentoring_training">Coaching and Mentoring Training</option>
</select>
<input name="Submit" type="submit" id="Submit" value="Go">
</form>
Notice that the select name of this form is "aoesearch".
So with this example I am trying to query the table, AreasOfExpertise. I have figured out how to join the tables: Members, AreasOfExpertise, and obtain part of the results I need by matching the corresponding form input value from testform.php with the field in AreasOfExpertise with the same name, and then echo the results into <div> tags formated with CSS below:
Select an item, then Click 'Go' to see the results: http://www.astd-pensacola.org/testform.php
mysql_select_db('astdpen_members');
$query = "select AreasOfExpertise.MemberID, FirstName, LastName, Occupation, Company, Bio, Phone, Fax, Email, Photo
from Members, AreasOfExpertise
where AreasOfExpertise.MemberID = Members.MemberID
and ".$aoesearch." = 1
order by LastName asc";
$result = mysql_query($query);
$query_result_handle = mysql_query ($query)
or die ('The query failed! table_name must be a valid table name that exists in the database specified in mysql_select_db');
$num_of_rows = mysql_num_rows ($query_result_handle)
or die ("The query: '$query' did not return any data");
for ($count = 1; $row = mysql_fetch_row ($query_result_handle); ++$count) {
// Create container & content around results
echo '<div id="container">';
echo '<div id="content">';
// Fetch member photo from the database
echo '<div id="photo">';
echo $row[9];
echo '</div>';
// Fecth member info
echo '<div id="memberinfo">';
// Fetch member name
echo '<div id="name">';
echo "<b>$row[1]</b>";
echo ' ';
echo "<b>$row[2]</b>";
echo '</div>';
// Fetch member occupation
echo '<div id="occupation">';
echo "<b>Occupation:</b> $row[3]";
echo '</div>';
// Fetch member company
echo '<div id="company">';
echo "<b>Company:</b> $row[4]";
echo '</div>';
// Fetch bio
echo '<div id="bio">';
echo $row[5];
echo '</div>';
// End Fetch member info
echo '</div>';
// Fetch contact info
echo '<div id="contactinfo">';
// Fetch phone
echo '<div id="phone">';
echo "<b>Phone:</b> $row[6]";
echo '</div>';
// Fetch fax
echo '<div id="fax">';
echo "<b>Fax:</b> $row[7]";
echo '</div>';
// Fetch email
echo '<div id="email">';
echo "<b>Email:</b> $row[8]";
echo '</div>';
// End Fetch contact info
echo '</div>';
// Fetch Qualifications
echo '<div id="qual">';
// Fetch Areas of Expertise Results
echo '<div id="aoeresults">';
echo '<div class="header">Areas of Expertise</div>';
echo $row[--H E L P--];
echo '</div>';
// Fetch Industry Experience Results
echo '<div id="ieresults">';
echo '<div class="header">Industry Experience</div>';
echo $row[--H E L P--];
echo '</div>';
// Fetch Software Skills Results
echo '<div id="softresults">';
echo '<div class="header">Software Skills</div>';
echo $row[--H E L P--];
echo '</div>';
// End Fetch Qualifications
echo '</div>';
// End container & content
echo '</div>';
echo '</div>';
echo '<br />';
echo '<br />';
}
?>
So far this query returns all of the members that match field in the AreasOfExpertise table with the drop down menu search criteria. If you tried the example links above you would notice that the results page returns all of the data, but doesn't return anything for the three columns at the bottom of each result on the results.php page, 'Areas Of Expertise', Industry Experience', and 'Software Skills'.
My question: Is there a way to combine this query with something else (like a multidimensional array) so that I could query the other tables at the same time and return the results of the other tables into the last three empty $row containers above('Under //Fetch Qualifications' ???) or some other variable perhaps? This would populate the three columns at the bottom of each result on the results.php page, 'Areas Of Expertise', Industry Experience', and 'Software Skills'.
Unlike the fields of the Members table which only contain text data about each member, the three tables: AreasOfExpertise, IndustryExperience, SoftwareSkills, each have a "1" or "0" in each MemberID row for true or false. Each of the fields in this table correspond/match each <item> value identified in the form.
What is the best way to accomplish this?
I have been told that you can do this in cold fusion using a query of queries, but how would I do something like this with php / mysql?
Thanks in advance!
Replik8
