Ill try to make this as short as possible. Most of what I have learned so far has come from RTFM and various other sites such as this one.
What I have is a db of family members with 2 tables as follows:
members:
+----+--------+-------+---------+--------+---------+--------+--------+------------+----------+------+------+----------+------+----------------------------+---------+------+----------+----------+-------+--------------------+--------+----------+
| id | smf_id | blood | f_name | n_name | m_name | maiden | l_name | dob | cob | dod | cod | cemetery | plot | hs | hs_date | col | col_date | city | state | email | f_book | img |
+----+--------+-------+---------+--------+---------+--------+--------+------------+----------+------+------+----------+------+----------------------------+---------+------+----------+----------+-------+--------------------+--------+----------+
relation:
+----+---------+--------+--------+--------+
| id | members | spouse | mother | father |
+----+---------+--------+--------+--------+
At the onset of this project I had everything in one table but decided it would be better to seperate them. I am open to db design changes if thats what it takes.
The problem I am running into is trying to display siblings and children. I only end up displaying the last result of the array.
Here is the php section Im having the issue with.
$id=$_GET['id'];
$q1=mysql_query("SELECT * FROM members WHERE id = $id");
$q2=mysql_query("SELECT * FROM relation WHERE members = $id");
while ($rel = mysql_fetch_row($q2)){
$qs=mysql_query("SELECT id, f_name, l_name FROM members WHERE id = $rel[2]");
$qm=mysql_query("SELECT id, f_name, l_name FROM members WHERE id = $rel[3]");
$qf=mysql_query("SELECT id, f_name, l_name FROM members WHERE id = $rel[4]");
$qsibid=mysql_query("SELECT members FROM relation WHERE (mother = $rel[3] or father = $rel[4]) AND (members != $id)"); //Getting the `member.id` for siblings
while ($sib = mysql_fetch_row($qsibid)){
$qsibn=mysql_query("SELECT id, f_name, l_name FROM members WHERE id = $sib[0]"); //Use those `member.id` to get relevant info of siblings
}
}
$q3=mysql_query("SELECT members FROM relation WHERE mother = $id or father = $id"); //Getting the `member.id` for children
while ($kid = mysql_fetch_row($q3)){
$qkidn=mysql_query("SELECT id, f_name, l_name FROM members WHERE id = $kid[0]"); //Use those `member.id` to get relevant info of kids
}
echo "<div id=\"left_section\">
<div class=\"section_box\">
<div class=\"section_title\">Search</div>
<ul>
<li>
<form method=\"get\" action=\"\">
<input type=\"text\" id=\"s\" name=\"s\" value=\"\" />
<input type=\"submit\" id=\"x\" value=\"Search\" />
</form>
</li>
</ul>
</div>
<div class=\"section_box_last\">
<div class=\"section_title\">Info</div>
<table class=\"flat_two\">
<tr><th>Spouse:</th></tr>";
while ($spo = mysql_fetch_row($qs)){
echo " <tr><td><a href=\"info.php?action=browse_info&id=$spo[0]\">$spo[1] $spo[2]</a><td></tr>";
}
echo " <tr><th>Children:</th></tr>";
while ($kids = mysql_fetch_row($qkidn)){
echo " <tr><td><a href=\"info.php?action=browse_info&id=$kids[0]\">$kids[1] $kids[2]</a><td></tr>";
}
echo " <tr><th>Mother:</th></tr>";
while ($mot = mysql_fetch_row($qm)){
echo " <tr><td><a href=\"info.php?action=browse_info&id=$mot[0]\">$mot[1] $mot[2]</a><td></tr>";
}
echo " <tr><th>Father:</th></tr>";
while ($fat = mysql_fetch_row($qf)){
echo " <tr><td><a href=\"info.php?action=browse_info&id=$fat[0]\">$fat[1] $fat[2]</a><td></tr>";
}
echo " <tr><th>Siblings:</th></tr>";
while ($sibs = mysql_fetch_row($qsibn)){
echo " <tr><td><a href=\"info.php?action=browse_info&id=$sibs[0]\">$sibs[1] $sibs[2]</a><td></tr>";
}
echo " <tr><th>Place of Birth:</th></tr>";
Am I going about this all wrong or am I just missing some key piece of the puzzle?
Also, any other suggestions on how to go about something like this would be greatly appreciated. That just seems like an awful lot of queries for the amount of info I am trying to pull.
Thanks much for and help / constructive criticism.
