9 table join, getting an extra iteration of last result?

new7media

I am doing a table join that includes 9 tables in all. Scary! Anyway, I actually got this working (!), but here's the thing. We want to provide people with a search form that includes 4 pull-down menus. Those are the only means to search the database.

One of the options in each pull down will be "All". If someone selects "All" then we don't want to search on something distinct in that field.

All of the pulldowns pass a numeric value. What I cooked up to handle "All" is I set the value of All to zero. Then in my script that handles the search form input, I put this PHP code:

if (form submitted) {

  if ($pulldown1 == '0') {
     $equation = "!=";
  } else {
     $equation = "=";
  }
  Begin the dbase query.....

Then in my MySQL query I put the part of the query particular to this pulldown menu together like this:

  ... AND field $equation '$pulldown1' AND....

Wonder of all wonders, this worked! Sort of! I get the results I should get, but I also get one more instance of the last result. So if the results were supposed to be "Result One" and "Result Two" I am getting:

Result One

Result Two

Any ideas why this is happening? I have been thinking maybe it's how i'm joining all of those tables -- I am not using any kind of descriptive like LEFT JOIN, etc. Could that be it? I'm not finding anything on this. Any help is appreciated!

Pig

I have no idea, but it sure would help to see the query and the database structure.

Rather than try and mess with the operator, just leave out or alter the sql statement. Also, since 0 means 'false', you might want to go with a different choice. Maybe just leave at 'any'

$sql = "SELECT * FROM table1 WHERE ";
if( $POST['field1'] != "any" ) $sql .= "field1='" . $POST['field1'] . "'";

new7media

Hi and thanks for your response. I tried your fix, and I also tried my code again replacing the zero with "any". All three tries resulted in that extra display of the last record. Here's the dbase and query info.

The database includes 9 tables:

4 of the tables are lookup tables and include 3 fields -- type_id, lookup_id, type. The type is just a label.

Another 4 tables are "data" tables -- this is where the info from the lookup tables is linked to a particular record. The fields are -- data_id, lookup_id, record_id.

The 9th table has a bunch of fields and is the main table for the application. For the query below, the field to note is just record_id.

Here's my entire script with the big ol' table join in there:


if ($submit) {

if ($pulldown1 == '0') { // or any...
	$equation = "!=";
} else {
	$equation = "=";
}  

// there would be a total of 4 if (pulldownX) statements

$result = mysql_query
("SELECT * 
FROM
 main_table,
 data_table_1,
 data_table_2,
 data_table_3,
 data_table_4,
 lookup_table_1,
 lookup_table_2,
 lookup_table_3,
 lookup_table_4,
WHERE
data_table_1.lookup_id = lookup_table_1.lookup_id AND
data_table_2.lookup_id = lookup_table_2.lookup_id AND
data_table_3.lookup_id = lookup_table_3.lookup_id AND
data_table_4.lookup_id = lookup_table_4.lookup_id AND
data_table_1.record_id = main_table.record_id AND
data_table_2.record_id = main_table.record_id AND
data_table_3.record_id = main_table.record_id AND
data_table_4.record_id = main_table.record_id AND
data_table_1.lookup_id $equation '$pulldown1' AND
data_table_2.lookup_id $equation '$pulldown2' AND
data_table_3.lookup_id $equation '$pulldown3' AND
data_table_4.lookup_id $equation '$pulldown4'", $db);


 $row_count = mysql_num_rows($result);
 if ($row_count == 0) {
 	echo ("0 Rows Returned\n");
} else {
	while ($row = mysql_fetch_array ($result)) {
		$record_id = $row['record_id'];
		$title = $row['title'];
		echo ("<p>$record_id) $title</p>");
	}
}
} // end the first if ($submit)

new7media

A little bit more info:

When I perform a search where $pulldown1 = X, or it equals a particular piece of info in the database (eg., $pulldown1 = '2'), the query returns the right results and doesn't repeat anything.

But when i try the query where I say if $pulldown1 = 'any' then either don't include this variable in the search, or include $pulldown1 != 'any' in the search, I get the repeated result.

Lastly, I tried hardcoding in all possible options for pulldown1 like this (let's say there are 3 possible options):


if ($pulldown1 == 'any') { 
    $search_pulldown1 = "data_table_1.lookup_id = '1' OR data_table_1.lookup_id = '2' OR data_table_1.lookup_id = '3'";
 } else { 
    $search_pulldown1 = "data_table_1.lookup_id = ' " . $pulldown1 ."'";
}

Then in the query i just show $search_pulldown1. When searching on 'any' this query takes an eternity to finally retun an error "can't save result set". But searching on an actual option returns the right result.

Any help - APPRECIATED. Thanks!