Cleopatra
Posts: 73
|
| Posted: 04/14/2010, 8:06 AM |
|
Hi everyone,
I'm thinking of concatenating 2 records from one table to insert into another record from another table.
what event or code should I use?
All help is appreciated.
_________________
php newbie |
 |
 |
andy
Posts: 183
|
| Posted: 04/18/2010, 9:35 AM |
|
Hi Cleoptra
I'm not sure the exact way in which you want to concatenate 2 records into one but here is one way you might go about it:
Depending on whether you are using an editable grid or a record, then you will access the control (field) values in a slightly different way, but in general terms you need to
identify which records are to be concatenated
save the page by submitting the form
in the aftersubmit event custom code access these newly saved records, assign their values to variables, then update the other table with the concatenated values
Here's an example and these are my assumptions:
You are just concatenating one field ("field1"):
The primary keys of the two records you with to concatenate is an integer and the primary keys are say "2" and "4"
You are retrieving data from a table called "sourcetable"
You are saving the concatenated product to a table called "concattable"
global $dbConnection1;
//create the database connection
$db = new clsDBConnection1();
//access the recently saved values
$field1rec1 = CCDLookUp("field1","sourcetable","keyfield=2",$DBConnection1);
$field1rec2 = CCDLookUp("field1","sourcetable","keyfield=4",$DBConnection1);
//Update table
$db->query("UPDATE concattable set field1 = $field1rec1+$field1rec2 WHERE keyfield = 1");
//close the database connection
$db->close();
You will probably need to dynamically access the keyfields, but this gives you an idea of the principle.
I haven't checked the code...
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/19/2010, 6:53 AM |
|
Hi Andy,
Sorry if I wasn't very clear on what I'd like to do. What I'm trying to do is to concatenate 2 fields.
For example if the user types in the first field: his/her (job) title and in the second one : his/her full name. Then I would like to store those two in one record of another table ( userinfo), So other users can view it in a grid like this: Msc. John Doe -> the stored title and name
This is just a example. I'll try what you've suggested, thank you so much for your reply.
_________________
php newbie |
|
|
|
datadoit
|
| Posted: 04/19/2010, 7:54 AM |
|
Sounds like you'll have a record form that get's it's information from
two other tables. Correct?
So in the record form's Before Show, you'll go and get that information
to populate your form.
When someone clicks submit on this form, you'll then need to have custom
code in Before Update to write the information back out to the two tables.
Not as daunting as it sounds. Just a little bit of typing.
|
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/19/2010, 8:32 AM |
|
Actually, it's a record form that works with one table and I would like to concatenate 2 of the columns into a different column to be able to view it later on.
_________________
php newbie |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 04/19/2010, 11:23 AM |
|
no luck yet, but still working on it.
when using a SQL query, the statement is unique and only works for those specific records where you put the keyfield value. How can you do it for all the record values in those 2 columns?
_________________
php newbie |
|
|
|
saseow
Posts: 744
|
| Posted: 04/19/2010, 11:52 PM |
|
Cleopatra, perhaps this will help:
In the record form where you enter the data for the first time, select the AfterExecuteInsert event and write your code there. As an example, the following will update EVERY record in the users table:
//Update the userinfo field
$db = new clsDByourconnectionname();
$sql = "update users set userinfo = CONCAT_WS(' -> ', RPAD(users.full_name,LENGTH(users.full_name),' '), LPAD(users.job_title,LENGTH(users.job_title),' '))";
$db->query($sql);
$db->close();
Hope this helps.
Trevor
|
|
|
|
Waspman
Posts: 948
|
| Posted: 04/20/2010, 12:26 AM |
|
Quote Cleopatra:
Actually, it's a record form that works with one table and I would like to concatenate 2 of the columns into a different column to be able to view it later on.
Why not concat later on? 
_________________
http://www.waspmedia.co.uk |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 05/03/2010, 6:38 AM |
|
Really need help with the concatenation, guys.
This is for a project I' m working on at school.
I have a textarea and a textbox of which the content is stored in 2 separate fields in 1 database table.
How can I store them in one field in order to use it later.
I've tried a few methods and also the ones suggested, but still no luck.
_________________
php newbie |
|
|
|
ckroon
Posts: 869
|
| Posted: 05/03/2010, 8:16 AM |
|
Cleopatra.
I am confused.. if you have the data in a table.. you can present it on a page.. and concatenate the two fields there.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 05/03/2010, 8:32 AM |
|
Maybe, I'm looking too far for a solution.
How would you concatenate the two fields?
_________________
php newbie |
|
|
|
ckroon
Posts: 869
|
| Posted: 05/03/2010, 8:58 AM |
|
It's as simple as presenting the data in Labels and putting the two fields right next to each other.
:)
It's so easy to overthink this stuff.
_________________
Walter Kempees...you are dearly missed. |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 05/05/2010, 4:53 AM |
|
Yeah, I get what you're saying, Ckroon.
But the way I would like to do it.......hmm, storing the text of those two fields in one column of the database. Isn't there any other way ?
_________________
php newbie |
|
|
|
Waspman
Posts: 948
|
| Posted: 05/05/2010, 5:37 AM |
|
You're already storing it?
_________________
http://www.waspmedia.co.uk |
|
|
|
Cleopatra
Posts: 73
|
| Posted: 05/05/2010, 5:42 AM |
|
Okay, I found a something that works for me.
I added a hidden field to my form and then kept the controlsources of the two fields, which I wanted to concatenate, empty. Next I added the following custom code for the OnValidate event:
//------------------------------------
$Container->Hiddenfield->SetValue($Container->field1->GetValue().
$Container->field2->GetValue());
//------------------------------------
Works like a charm!!
Don't know why I didn't try this earlier, how silly of me. Anyway it wasn't my idea, I saw it in another post.
Thanks everyone for trying to help, really appreciate it.
_________________
php newbie |
|
|
|
