close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

bindparam on recordset not filtering as expected

Thread began 5/26/2015 12:02 pm by Jason | Last modified 5/27/2015 4:07 pm by Ray Borduin | 1149 views | 9 replies |

Jason

bindparam on recordset not filtering as expected

php:
<?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?

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post

Jason

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:
<?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:
<?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.

Sign in to reply to this post

Jason

See private message section

Sign in to reply to this post

Ray BorduinWebAssist

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?

Sign in to reply to this post

Jason

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.

Sign in to reply to this post

Jason

See private message section.

Sign in to reply to this post

Ray BorduinWebAssist

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:
<?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)
Sign in to reply to this post

Jason

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?

Sign in to reply to this post

Ray BorduinWebAssist

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.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...