[RESOLVED] Searching with Multiple Form Fields and Multiple Tables

[emptypixels]

I have a form for searching against the database with the following fields:
Program Name, Country, Length of Service, Size of Program, Financial Arrangement, ect.

The database tables are set up as follows

TABLE tbl_program
COLUMNS program_id, name

TABLE program_x_country
COLUMNS program_id, country_id

TABLE tbl_length_service
COLUMNS program_id, length_service_id

TABLE tbl_program_size
COLUMNS program_id, program_size_id

TABLE tbl_financial_arrangements
COLUMNS program_id, financial_arrangements_id

I would like the user to fill in as much information as they like to narrow down which programs are shown to them on the resulting page. I've separated the info across different tables because these programs that users are searching against may have multiple locations, financial arrangments, etc.

I've tried to build in conditional JOIN statments using PHP, but after I join the tbl_program TABLE with the tbl_length_service TABLE, I end up with multiple instances of each program name since there are multiple instances of that program_id in the tbl_length_service TABLE.

I'd like all form fields to remain optional, and I'd like the results to display all programs that match only once.

My code is listed below.
Hopefully someone can help me sort this out, let me know what I'm doing wrong, or point me in the wrong direction.

global $querycount;
$querycount = 0;

$subprogram_name = $POST['program_name'];
$subvolunteer_time = $POST['volunteer_time'];
$query = "SELECT * FROM ";
// If the Program Name field was not blank
if(!empty($subprogram_name)) {
$query .= " tbl_program ";// run against the Program Table
$querycount++; // query count is +1
}

if(!empty($subvolunteer_time)) {
if ($querycount > 0) {
$query .= ", ";
}
$query .= "tbl_length_service ";
$querycount++; // query count is +1
}
$query .= " WHERE ";
if ($querycount > 0) {
$query .= " tbl_program.program_id = tbl_length_service.program_id AND";
}
if(!empty($subprogram_name)) {
$query .= " name LIKE '%$subprogram_name%' ";
}
if(!empty($subvolunteer_time)) {
if ($querycount > 0) {
$query .= " AND ";
}
$query .= " length_service_id = '$subvolunteer_time' ";
}
$numresults = mysql_query($query);

[bogu]

SELECT * 
FROM tbl_program t1
LEFT JOIN program_x_country t2 USING(program_id)
LEFT JOIN tbl_length_service t3 USING(program_id)
LEFT JOIN tbl_program_s t4 USING(program_id)
LEFT JOIN tbl_financial_arrangements t5 USING(program_id)
WHERE 
	t1.name LIKE '%$subprogram_name%'
	AND t3.length_service_id = '$subvolunteer_time'
GROUP BY t1.program_id

this how u should join your tables, the where condition it should be generate on what the user search for ...

emptypixels wrote:

I'd like all form fields to remain optional, and I'd like the results to display all programs that match only once.

for this u should use group by ...

SELECT SYNTAX
JOIN SYNTAX

[emptypixels]

Thanks Bogu!
Exactly what I was looking for.