JoeMann
|
| Posted: 06/11/2003, 2:47 PM |
|
I am using CC, PHP & MySQL.
I have a table with company info, another with contacts as well as 1 for projects.
I then create a project form which has a listbox for company which gets the data from the company table, and one for the contact (contact table), and lets say a notes field.
On that form I would like to filter the contents of the contact table based on the client id, this way only the contacts for that company show up in the listbox. I am stuck at this part, I have tried in the sql lookup within the listbox properties to get this to work with no luck. Here are some sample sql querrys I tried.
where conact_names.client_no = $fldclient_no
where conact_names.client_no = "& $fldclient_no &"
where conact_names.client_no = "$fldclient_no"
where conact_names.client_no = fldclient_no
select client_no, contact_names from contact_names
This just doesn't work.
Any help appreciated,
|
|
|
 |
Mickster
|
| Posted: 06/16/2003, 6:15 AM |
|
I have the same question, any ideas?
Thanks,
Mick
|
|
|
|
Frans
|
| Posted: 06/17/2003, 7:10 AM |
|
Hello,
I asked some help at the Yes support group. Alex came with the following solution.
select club_id,kop from club_main_kop_tbl where user_id=" . get_session("UserID") ."
It's up to you how to design the SQL, the only requirement is that
field list must be like this : field_id,field_value
With this it is almost working.
|
|
|
|
frans
|
| Posted: 06/17/2003, 10:04 AM |
|
This is the answer for this topic.
#
# Table structure for table 'news_header_tbl'
#
CREATE TABLE `news_header_tbl` (
`news_header_id` int(6) NOT NULL auto_increment,
`news_header` varchar(50) default NULL,
`user_id` int(6) default NULL,
PRIMARY KEY (`news_header_id`),
UNIQUE KEY `news_header_id` (`news_header_id`),
KEY `news_header_id_2` (`news_header_id`,`user_id`)
) TYPE=MyISAM;
#
# Dumping data for table 'news_header_tbl'
#
INSERT INTO news_header_tbl VALUES("1","Computer","1");
INSERT INTO news_header_tbl VALUES("2","Hardware","1");
INSERT INTO news_header_tbl VALUES("3","Software","1");
INSERT INTO news_header_tbl VALUES("4","Snowboards","2");
INSERT INTO news_header_tbl VALUES("5","Wax","2");
INSERT INTO news_header_tbl VALUES("6","Ski","2");
#
# Table structure for table 'news_tbl'
#
CREATE TABLE `news_tbl` (
`news_id` int(3) NOT NULL auto_increment,
`news_header_id` int(3) default NULL,
`news` varchar(255) default NULL,
`user_id` int(3) default NULL,
PRIMARY KEY (`news_id`),
UNIQUE KEY `news_id` (`news_id`),
KEY `news_id_2` (`news_id`,`news_header_id`,`user_id`)
) TYPE=MyISAM;
#
# Dumping data for table 'news_tbl'
#
INSERT INTO news_tbl VALUES("1","1","This has something to do with computers","1");
INSERT INTO news_tbl VALUES("2","2","Another story about computers","1");
INSERT INTO news_tbl VALUES("3","3","More computer story","1");
INSERT INTO news_tbl VALUES("4","4","This is a story about wintersports","2");
INSERT INTO news_tbl VALUES("5","5","This is another story about wintersport","2");
INSERT INTO news_tbl VALUES("6","6","Again a story about wintersports","2");
If user_id 1 logs in to the system the listbox should contain the following values from the table news_header_tbl Computer, Hardware and Software
If user_id 2 logs in he should see the following values in the listbox: Snowboards, Wax and Ski.
Open Field Properties/List tab. Fill "Lookup SQL" with SQL
select news_header_id,news_header from news_header_tbl where user_id=" . get_session("UserID") ."
|
|
|
|
|
