bindparam on recordset not filtering as expected

<?php

$Existing_Rooms = new WA_MySQLi_RS("Existing_Rooms",$local_i,0);

$Existing_Rooms->setQuery("SELECT bridge_event_room.room_id FROM bridge_event_room LEFT JOIN 7_room ON bridge_event_room.room_id = 7_room.room_id WHERE bridge_event_room.event_id = ? GROUP BY 7_room.room_id");

$Existing_Rooms->bindParam("i", "".(isset($_GET['event'])?$_GET['event']:"")  ."", "-1"); //WAQB_Param1

$Existing_Rooms->execute();

print_r($Existing_Rooms)

?>

<?php

$Church = new WA_MySQLi_RS("Church",$local_i,0);

$Church->setQuery("SELECT 2_department.church FROM 2_department LEFT OUTER JOIN 3_program ON 2_department.department_id = 3_program.department LEFT OUTER JOIN 4_calendar ON 3_program.program_id = 4_calendar.program WHERE 4_calendar.calendar_id = ?");

$Church->bindParam("i", "".(isset($_GET['calendar'])?$_GET['calendar']:"")  ."", "-1"); //WAQB_Param1

$Church->execute();

?>

<?php

$Available_Rooms = new WA_MySQLi_RS("Available_Rooms",$local_i,0);

$Available_Rooms->setQuery("SELECT 7_room.* FROM 7_room LEFT OUTER JOIN bridge_event_room ON 7_room.room_id = bridge_event_room.room_id LEFT OUTER JOIN 6_event ON bridge_event_room.event_id = 6_event.event_id LEFT OUTER JOIN 5_event_category ON 6_event.event_category = 5_event_category.event_category_id LEFT OUTER JOIN 4_calendar ON 5_event_category.calendar = 4_calendar.calendar_id LEFT OUTER JOIN 3_program ON 4_calendar.program = 3_program.program_id LEFT OUTER JOIN 2_department ON 3_program.department = 2_department.department_id WHERE 2_department.church = ? AND 7_room.room_id != ? GROUP BY 7_room.room_id");

$Available_Rooms->bindParam("i", "".($Church->getColumnVal("church"))  ."", "-1"); //WAQB_Param1

$Available_Rooms->bindParam("i", "".($Existing_Rooms->getColumnVal("room_id"))  ."", "-1"); //WAQB_Param2

$Available_Rooms->execute();

print_r($Available_Rooms)

?>

$Existing_Rooms prints out:
[Results] => Array
(
[0] => Array
(
[room_id] => 1 This one was filtered out correctly
)

[1] => Array
(
[room_id] => 2
)

[2] => Array
(
[room_id] => 35



$Available_Rooms prints out
[Results] => Array
(
[0] => Array
(
[room_id] => 2 This should have been filtered out.
[room_value] => 80
[room_text] => Congo Room
[room_default_color] => #2C4217
)

[1] => Array
(
[room_id] => 3
[room_value] => 20
[room_text] => Baby Bay
[room_default_color] => #66CCCC
)

[2] => Array
(
[room_id] => 4
[room_value] => 30
[room_text] => Baby Reef
[room_default_color] => #4D5AB3
)

[3] => Array
(
[room_id] => 5
[room_value] => 50
[room_text] => Rainforest
[room_default_color] => #397D02
)

[4] => Array
(
[room_id] => 6
[room_value] => 90
[room_text] => Pipeline
[room_default_color] => #5c5c5c
)

[5] => Array
(
[room_id] => 7
[room_value] => 40
[room_text] => Toddler Grove
[room_default_color] => #4EBF85
)

[6] => Array
(
[room_id] => 8
[room_value] => 10
[room_text] => Welcome Hut
[room_default_color] => #98950b
)

[7] => Array
(
[room_id] => 10
[room_value] => 9
[room_text] => Floating
[room_default_color] => #8b95c3
)

[8] => Array
(
[room_id] => 33
[room_value] => 0
[room_text] => Administrative Help
[room_default_color] => #355E13
)

[9] => Array
(
[room_id] => 34
[room_value] => 0
[room_text] => test room
[room_default_color] => #355E13
)

[10] => Array
(
[room_id] => 35 This should have been filtered out.
[room_value] => 0
[room_text] => test room
[room_default_color] => #355E13
)

)

Seems like I did everything correctly, or did I miss something?

Let's concentrate on just the first one... You say the second two records should be filtered out and not return?

Why don't you add "bridge_event_room.event_id" to the select statement so that we can verify the data in the database is correct and that the filter is the problem. I'd start with that.

I attached a screenshot of the results from the first query (with the bridge_event_room.event_id added). Also, I've broken it down to the absolute minimum of what I'm trying to do, but still get the same results. I've also tried changing the sql in the second query with all variations of !=, <>, NOT IN. I think NOT IN is probably the most accurate for what I'm doing, but not really sure.

<?php

$Existing_Rooms_Test = new WA_MySQLi_RS("Existing_Rooms_Test",$local_i,0);

$Existing_Rooms_Test->setQuery("SELECT bridge_event_room.* FROM bridge_event_room WHERE bridge_event_room.event_id = 5 GROUP BY bridge_event_room.room_id");

$Existing_Rooms_Test->execute();?>

As you can see from the screenshot, this outputs room_id's 1, 2, 33

<?php

$Available_Rooms_Test = new WA_MySQLi_RS("Available_Rooms_Test",$local_i,0);

$Available_Rooms_Test->setQuery("SELECT 7_room.* FROM 7_room WHERE 7_room.room_id NOT IN (?)");

$Available_Rooms_Test->bindParam("il", "".($Existing_Rooms_Test->getColumnVal("room_id"))  ."", "-1"); //WAQB_Param1

$Available_Rooms_Test->execute();

print_r($Available_Rooms_Test)

?>

This one still gives me the same result as before. It filters the first room_id (#1) correctly, but displays everything else.

See private message section

I'm afraid I'm not understanding the issue still. What is appearing in the list that you don't expect to see?

The screen shot appears to show the correct values. Since you are filtering with a parameter, the test in Dreamweaver may not be accurate.

Would it be possible to send a URL to view the problem live and FTP information?

I can push it to the live server, but it will take a while (Trying to entertain company and work. lol). In the mean time a better explanation is:

The 7_room.room_id column contains records 1-10, 33, 34

Query1 returns room_id 1, 2, 33

Query2 should return room_id's 3-10, 34 (because they are NOT IN Query1)

Instead Query 2 is giving me room_id's 2-10, 33, 34. I'm 99.9% certain the data is correct, and I know what Query 1 is giving me because I left the static value of 5 instead of the variable after the test, and I still got the same result.

I also attached screenshots of the two tables. 7_room has then entire table in the screenshot, and bridge_event_room is sorted by event_id, so all of the pertinent data is in the screenshot.

See private message section.

I see your problem now.

You are binding the parameter to: $Existing_Rooms_Test->getColumnVal("room_id")

and you are expecting that to have all of the rows from the previous recordset. Really that would only contain the first row, which is why the first row is the only one being filtered out.

You would have to use a different function to get a comma separated list of all rows to filter them out. If you added this to the top of the page:

<?php

function getAllColumns($rs,$col) {

      $allColumns = "";

      for ($x=0; $x<sizeof($rs->Results); $x++) {

                if ($allColumns) $allColumns .= ", ";

        $allColumns .= $rs->Results[$x][$col];

      }

      return $allColumns;

}

?>

Then you could bind the parameter to: getAllColumns($Existing_Rooms_Test,"room_id")

Another solution, is to just nest the SQL like:

SELECT 7_room.* FROM 7_room WHERE 7_room.room_id NOT IN (SELECT room_id FROM bridge_event_room WHERE bridge_event_room.event_id = 5 GROUP BY bridge_event_room.room_id)

Thanks Ray that works!! I had to add the $ before allcolumns on the last line of your function, but after that worked great.

I don't think the nested query will work when I add the joins back in from my original post will it?

Yes, you could do a nested query with joins and parameters for each, but it would get rather complex and might even be less efficient than separate recordsets at some point.