Xavier
|
| Posted: 05/30/2002, 7:06 AM |
|
Hello,
I've a record form to insert private informations about a generic user in
table "users". Two of these are user's company code and user's company name.
I've stored all company code and names in a different table "companies".
Codes and names are uniques: there aren't two different codes for the same
name nor different names for the same code.
When I insert I get user's company code with a listbox. The listbox shows
all companies' name and submits a code. Ok.
Than I'd like to avoid another listbox to get the company name too.
In other words, I'd like a trigger that writes in table "users" reading from
table "companies" and selecting by a value got from the listbox.
I suppose I must use _After Insert_ Event, but I don't know the sintax to do
it mergeing php and Sql. Maybe something as:
$fldfieldname = "select id, company_name from companies where company_code =
$fldusercompany_code";
$sSQL="update users set "fieldname=" . tosql($fldfieldname, "Text") ;
???
Please help.
Xavier
|
|
|
 |
RonB
|
| Posted: 06/02/2002, 9:54 AM |
|
"Xavier" <nobody@nowhere.invalid> schreef in bericht
news:ad5bm1$kef$1@news.codecharge.com...
> Hello,
> I've a record form to insert private informations about a generic user in
> table "users". Two of these are user's company code and user's company
name.
> I've stored all company code and names in a different table "companies".
> Codes and names are uniques: there aren't two different codes for the same
> name nor different names for the same code.
> When I insert I get user's company code with a listbox. The listbox shows
> all companies' name and submits a code. Ok.
> Than I'd like to avoid another listbox to get the company name too.
> In other words, I'd like a trigger that writes in table "users" reading
from
> table "companies" and selecting by a value got from the listbox.
> I suppose I must use _After Insert_ Event, but I don't know the sintax to
do
> it mergeing php and Sql. Maybe something as:
>
> $fldfieldname = "select id, company_name from companies where company_code
=
> $fldusercompany_code";
> $sSQL="update users set "fieldname=" . tosql($fldfieldname, "Text") ;
>
> ???
> Please help.
>
> Xavier
>
>
>
>
>
Xavier,
As a general rule you do not store the same information more then once in a
relational database.
If I understand you correctly you have a table in wich company codes and
company names are stored.
That is enough. In the user table just store company code(inserted with the
listbox you made that shows company name but inserts company code)
Now if you would want a grid that shows user info and you want to see the
company name rather then the company code you join let's say user_company
to company_code with the join option when a field is a label.
Storing the same data twice will quickly lead to data inconsistency, wich is
about the worst thing that can happen to a releational database. If you want
to see both company code and company name in the user grid use sql.
Statement could look like this:
select user_name,user_company, company_name from user, company
where user_company=company_code
Good database design will pay of and is always a good thing to think about
before starting a project. It could save you a lot of work later.
Ron
|
|
|
|
|
