doug_mize
Posts: 12
|
| Posted: 04/09/2008, 9:43 PM |
|
Hello!
I'm having trouble filtering out my listbox. All I need it to do is query a single table with this simple WHERE condition:
$query="SELECT listname FROM lists WHERE lists.custnum='$user'";
so that my listbox only displays records that match a certain user number.
Sounds simple enough. But when I try and put this into the VQB it doesn't work.
I read this post:
http://forums.codecharge.com/posts.php?post_id=88961&s_...d=listbox+where+
and used the VQB to make an expression. All it did was select the first listname with the proper user#. All of the other user's lists were in there too. Obviously this isn't going to work because I need to listbox to list only the list names of individual customers, not every list for every customer.
Then I found the section in the HELP files called:
"Dynamically Modify the WHERE and ORDER BY Clauses of a ListBox" and it gave me this PHP code:
1. In the Properties Window for the Listbox, set the Source Type property to Table/View.
2. Add the Before Build Select event to the Listbox.
3. Within the event, add the code below:
function Tasks_Status_DataSource_BeforeBuildSelect(& $sender) {
global $Tasks;
$Tasks->Status->DataSource->Where = " status_id >2";
$Tasks->Status->DataSource->Order = " status_id DESC";
which I modified to:
function delete_list_selectbox_DataSource_BeforeBuildSelect(& $sender) {
global $delete_list;
$delete_list->selectbox->DataSource->Where = " selectbox_custnum='200'";
$delete_list->selectbox->DataSource->Order = " selectbox_listname DESC";
}
Where delete_list is the name of my form and selectbox is the name of my listbox. As you can see I even hard coded the customer number as a test and got nowhere.
Why in the heck won't it simply display the list names by the user#???
I did everything per the instructions but to no avail. At this point I'm going crazy! Any help would be appreciated.
Doug
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 04/11/2008, 8:32 AM |
|
doug_mize
Question....What field type is selectbox_custnum??
2nd Question....Have you successfully executed this query directly in MySQL first??...Does it work??
|
|
|
|
doug_mize
Posts: 12
|
| Posted: 04/14/2008, 10:46 AM |
|
selectbox_custnum isn't a field in my listbox. custnum is a field from my custlist table which is where I'm puling the data (listname) that populates my listbox. The customer number (custnum) is merely the field that is checked against a session variable called $user (user # from a cookie) to determine which custom list names (listname) to populate the listbox with.
If my listbox WHERE condition was working properly it would only let the listbox display custom lists from a particular customer by their customer number (custnum).
And yes, my simple WHERE condition works perfectly outside CCS. I worked up my first version by hand without a single hitch and in about 10 minutes. Now I'm completely at a loss as to why CCS doesn't seem to process WHERE conditions in a straight forward manner.
I guess the question now is: Where do I input my custom WHERE conditions if not in the VQB? Because in the VQB I'm forced to choose parameter session, form, application, and so on when I'm actually trying to simply input my WHERE condition I have no idea what to set the parameter as.
How hard can this be? Look at it:
$query="SELECT listname FROM lists WHERE lists.custnum='$user'";
It seems so simple yet it somehow alludes me as to how to set this simple WHERE condition in CCS.
I hope my rambling sheds some light on my situation. Thanks for the help.
Doug
|
|
|
|
wkempees
|
| Posted: 04/19/2008, 3:37 AM |
|
First off:
You are giving too much information.
You are obviously irritated something simple is not working, which I
respect.
That said:
click the listbox in the design mode.
In it's properties see datasource [...], click that to go to the VQB.
In VQB, (in table mode, not SQL mode) it should show:
SELECT
FROM
WHERE
in a tree like fashion in the left pane.
click the SELECT and make sure you have 2 fields in your SELECT, one for the
lisbox bound value and one to display, most cases (id, listname) or
(listname,listname)
next click the WHERE
make sure it reflects your custnum field as a filter and 'user' type
Session as it's parameter.
That should really be it!
No BeforeBuildSelects or any other event code should be needed.
I assume you are using you $user Session value for a purpose, not using the
standard available UserID.
Have fun,
Walter
|
|
|
|
wkempees
|
| Posted: 04/19/2008, 3:44 AM |
|
Some answers to your questions:
> And yes, my simple WHERE condition works perfectly outside CCS. I worked
> up my
> first version by hand without a single hitch and in about 10 minutes. Now
> I'm
> completely at a loss as to why CCS doesn't seem to process WHERE
> conditions in a
> straight forward manner.
It does........
> I guess the question now is: Where do I input my custom WHERE conditions
> if
> not in the VQB? Because in the VQB I'm forced to choose parameter
> session,
> form, application, and so on when I'm actually trying to simply input my
> WHERE
> condition I have no idea what to set the parameter as.
In PhP, Form and Application type are handled as a CCGetFromGet or
CCGetFromPost thereby 'reading' your form variables, you just need to make
sure you accyratly name them.
Type Session, will do a CCGetSession(), reading you variable from the
Session, again accuratly name them. See Tips and Solutions,'Enumerate
Parameters for SERVER, SESSION etc' for a way to check available (used)
variables.
>
> How hard can this be? Look at it:
>
> $query="SELECT listname FROM lists WHERE lists.custnum='$user'";
>
> It seems so simple yet it somehow alludes me as to how to set this simple
> WHERE
> condition in CCS.
>
Not hard, see my previous post, sit back relax and try again, sure it works!
Walter
(Took some extra time, just to get you reassured with CCS, hope it helped)
|
|
|
|
wkempees
|
| Posted: 04/19/2008, 3:55 AM |
|
responding to the other related post, you can always have a Where clause and
set it to an expression in which case you can enter any code you might deem
correct.
|
|
|
|
|
