I have a query that uses a LEFT JOIN to combine two tables named members and specialmem. The common field is memno, which uniquely identifies each member. The specialmem table stores data as many-to-many. The members table currently has 8 master records (one for each "member").
The query correctly outputs a set of 11 unique records. But, the fields I use on my display page unfortunately show as 2 copies of the record whose memno is 0101001, as well as 3 copies of the record whose memno is 0101002, plus the 6 remaining records. Adding DISTINCT to the query doesn't help because the 11 records are actually unique, and if I display the contents of the specialty column (which I don't) you would see that it is that column that makes those "duplicate" records unique.
So, I think I need some filtering code to only display the data of the records for each unique memno (plus some other fields). But, I do need all 11 records to be in the basic recordset to allow me to search on the specialty field whenever necessary.
The query currently is:
$query_rs_members = sprintf("SELECT
members.memno,
members.stage_last,
members.stage_first,
members.e_color,
members.h_color,
members.age_L,
members.weight,
members.height_ft,
members.height_in,
members.SAG,
members.AFTRA,
members.AEA,
members.union_other,
members.phone1,
members.ph1_type,
members.phone2,
members.ph2_type,
members.email1,
members.website,
members.thumb_loc,
members.pic_loc,
agents.agent_name,
agents.agent_phone,
managers.manager_name,
managers.manager_phone,
specialmem.specialty,
members.category,
members.span_prof,
members.ital_prof,
members.french_prof,
members.port_prof,
members.city,
members.state,
members.region,
members.singer
FROM (managers INNER JOIN
(agents INNER JOIN members ON agents.agent_ID = members.agent_ID) ON
managers.manager_ID = members.manager_ID)
LEFT JOIN specialmem ON
members.memno = specialmem.memno
WHERE
(members.age_L LIKE '%s')
AND (members.weight BETWEEN '%s' and '%s')
AND (members.height_ft BETWEEN '%s' and '%s')
AND (members.height_in BETWEEN '%s' and '%s')
AND (members.stage_last LIKE '%s%%')
AND (members.category LIKE '%s')
AND (members.region LIKE '%s')
AND (members.e_color LIKE '%s%%')
AND (members.h_color LIKE '%s%%')",
$ageL_rs_members, $wL_rs_members, $wH_rs_members,
$ftL_rs_members, $ftH_rs_members, $inL_rs_members,
$inH_rs_members, $stageL_rs_members,
$category1_rs_members, $reg1_rs_members,
$eye1_rs_members, $hair1_rs_members);
$query_limit_rs_members = sprintf("%s LIMIT %d, %d", $query_rs_members, $startRow_rs_members, $maxRows_rs_members);
$rs_members = mysql_query($query_limit_rs_members, $connHP4_php) or die(mysql_error());
$row_rs_members = mysql_fetch_assoc($rs_members);
I think I want to loop thru the records one at a time, comparing the memno of the current record to the previous one, and if they have the same memno, then DO NOT display that record. This comparison would continue until all duplicate memno were filtered out, leaving one display for each memno in the recordset.
It was suggested to me that I do the following:
while (($row = mysql_fetch_array($result))){
if ($memno != $row["memno"])
{
$memno = $row["memno"];
}
//add more code here
$memno = $row["memno"];
} //end while loop
My PHP skills are extremely limited, and I don't understand how this works. I looked it up in the mysql manual, but was only confused by it. Can someone tell me what this does, and how I can use it with my query? Thanks.
