Page 1 of 1

Search two or more tables

Posted: Thu Sep 09, 2004 7:42 am
by WLW
I'm not sure if this question belongs in this forum or the PHP Programming forum.

I have a recordset called rs_members, that uses columns from a table called members. From this recordset, I set up simultaneous searches on columns for lastname, gender, eye color, hair color, age plus a few other columns.

The database also contains a junction? table called specialmem, with 2 columns called memno and specialty. Each column can have several instances of the same data, but each record is unique.

My detail page is generated based on the value of memno (which also appears in the rs_members recordset). To use the data in specialmem, I created a 2nd recordset called rsSpecial, and loop the results to be displayed onto the detail page.

So far so good.

My problem is, for example, how can I do a search on the following columns:

gender
age
specialty

...that come from 2 different tables and 2 different recordsets?

Posted: Thu Sep 09, 2004 8:04 am
by CoderGoblin
Why can't you use a JOIN (your junction)....

Code: Select all

SELECT * FROM members,specialmem WHERE (members search options) AND members.memno=specialmem.memno;
to select all members

Code: Select all

SELECT * FROM members,specialmem WHERE (members search options) AND members.memno=$member_no AND specialmem.memno=$member_no;
or

Code: Select all

SELECT * FROM members,specialmem WHERE (members search options) AND members.memno=specialmem.memno AND members.memno=$member_no;
You then have only one recordset to deal with. You will probably have duplicate data but this can be filtered off easily. Change the * to limit which fields are returned

Posted: Thu Sep 09, 2004 8:47 am
by WLW
When I join the tables, I run into another problem, (besides the duplicates).

The resulting display when there is no search being done, instead of showing all the records (including the duplcates added from the join) is only showing records that coincide with the data in the specialmem table.

But, the members table has a memno for every member, but the specialmem only has a memno for those members that say they have "specialties", i.e. some memno's will not be included in the specialmem table.

Posted: Thu Sep 09, 2004 9:07 am
by CoderGoblin
You need to use a LEFT JOIN.

Code: Select all

SELECT <column_names> FROM <Table1> LEFT JOIN <Table2> ON Table1.column = Table2.column WHERE <condition>
.

This performs the join prior to the WHERE clause.

I would recommend you look for a decent tutorial on the subject as it can get confusing. I don't know of one off hand unfortunately.

Posted: Thu Sep 09, 2004 11:04 am
by WLW
The LEFT JOIN worked, so now I just need to be able to remove the duplicates on the displayed page.

Posted: Thu Sep 09, 2004 12:17 pm
by feyd
[mysql_man]distinct[/mysql_man]

Posted: Thu Sep 09, 2004 1:11 pm
by WLW
Technically, there are no duplicate records as such. All are unique. Therefore the DISTINCT parameter has no effect on the output.

Prior to including the new table via LEFT JOIN, there were 8 records in the database, one for each unique memno. After the join there are 11 records. Now, record 2 is a duplicate of record 1, and records 4 and 5 are duplicates of record 3, except for the new specialty column (from the specialmem table).

Thus, when I display the master page of records, visually, you see 2 copies of record 1, 3 copies of record 3 and the remaining 6 records. The data in the specialty column is only meant to be visible on the detail page.

So, I'd like the recordset itself to contain all 11 records (so that the complete set of data can be searched), but the display on the master page to only show 8 records.

I think this may require some PHP code... maybe something that does the following:

...if memno is same as the previous record, do not display that record. The SQL probably should have an "ORDER BY lastname, firstname, memno" to ensure that the duplicate memno are grouped together.

Posted: Fri Sep 10, 2004 10:04 pm
by WLW
A member on another board suggested 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

I don't understand (conceptually) how this code works.

Posted: Sat Sep 11, 2004 12:20 pm
by McGruff
This might help explain some of the issues http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf.

Posted: Sun Sep 12, 2004 6:37 am
by WLW
Thanks for the reference, but it really didn't help my PHP problem.

Can someone, in plain English, explain what each bit of that PHP code is doing? I can't tell what parts of the code need to be changed to suit my situation, or where the code gets placed on my page.

Code: Select all

mysql_select_db($database_connHP4_php, $connHP4_php);
$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%%') AND ((specialmem.specialty = '%s') OR (specialmem.specialty =  '%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,$otherT1_rs_members,$otherT2_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);
What do I need to do to get the mysql_fetch_array code to work with the query above?

My skills at PHP are very minimal at this point.

Thanks for any help.