claudeless
|
| Posted: 09/28/2005, 2:06 PM |
|
Example
Field1 = Fname
Field2 = Lname
I would like to do this
FullName = Fname . ' ' . Lname
How do I do this
here is the sql statement
insert into table (Fname, LName, FullName) Values
('{Fname}', '{LName}', '{FullName}')
Thanks
|
|
|
 |
Alvaro Cobo
|
| Posted: 09/28/2005, 3:17 PM |
|
Hi:
If you are using MySql you could try the CONCAT_WS function:
For example
insert into table (Fname, LName, FullName) Values
('{Fname}', '{LName}', CONCAT_WS(" ", '{Fname}', '{LName}'))
I guess in other DBMS there might be an equivalent of this function.
Hope this helps,
Alvaro
"claudeless" <claudeless@forum.codecharge> escribió en el mensaje
news:5433b05cd012ed@news.codecharge.com...
> Example
>
> Field1 = Fname
> Field2 = Lname
>
> I would like to do this
>
> FullName = Fname . ' ' . Lname
> How do I do this
>
> here is the sql statement
>
> insert into table (Fname, LName, FullName) Values
> ('{Fname}', '{LName}', '{FullName}')
>
>
> Thanks
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
claudeless
|
| Posted: 09/29/2005, 8:05 PM |
|
No luck.. this is the error
Invalid SQL: INSERT INTO player (TeamID, TierID, PStatus, FirstName, LastName, Address1, Address2, City, State, Zip, Number, Pos, Hphone, WPhone, CPhone, Email, DOB, LeagueID, RegionID, FullName, DateCreated) VALUES (, , , 'test', 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '', '', 10000, 10000001, CONCAT_WS(" ",'test','tesgtt'), Curdate())
MySQL Error: 1064 (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' , , 'test', 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '', '',' at line 1)
Session halted.
|
|
|
|
Walter Kempees
|
| Posted: 09/30/2005, 6:37 AM |
|
Within the Concat_ws() you are mixing the double and single quotes, why?
You can even do multiple concat_ws's, but use the single quotes only!
concat_ws(' ', person.initials, concat_ws('-',
person.name_spouse,concat_ws('', person.extras,' ',person.name ) ) )
"claudeless" <claudeless@forum.codecharge> schreef in bericht
news:5433cab8fb3fb0@news.codecharge.com...
> No luck.. this is the error
>
> Invalid SQL: INSERT INTO player (TeamID, TierID, PStatus, FirstName,
> LastName,
> Address1, Address2, City, State, Zip, Number, Pos, Hphone, WPhone, CPhone,
> Email, DOB, LeagueID, RegionID, FullName, DateCreated) VALUES (, , ,
> 'test',
> 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '', '', 10000,
> 10000001, CONCAT_WS(" ",'test','tesgtt'), Curdate())
> MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
> that
> corresponds to your MySQL server version for the right syntax to use near
> ' , ,
> 'test', 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '',
> '',' at
> line 1)
> Session halted.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Damian Hupfeld
|
| Posted: 09/30/2005, 6:42 AM |
|
I spent an hour on this and couldnt get it!
$names->ds->SQL =
str_replace("INSERT INTO names (id, fname, lname)
Values ('{id}', '{fname}', '{lname}')",
"INSERT INTO names (id, fname, lname, fullname)
Values ('{id}', '{fname}', '{lname}', CONCAT_WS(' ', '{fname}',
'{lname}'))",
$names->ds->SQL);
I dont get any errors but I dont get anything in the fullname field either.
Anyone else?
"claudeless" <claudeless@forum.codecharge> wrote in message
news:5433cab8fb3fb0@news.codecharge.com...
> No luck.. this is the error
>
> Invalid SQL: INSERT INTO player (TeamID, TierID, PStatus, FirstName,
> LastName,
> Address1, Address2, City, State, Zip, Number, Pos, Hphone, WPhone, CPhone,
> Email, DOB, LeagueID, RegionID, FullName, DateCreated) VALUES (, , ,
> 'test',
> 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '', '', 10000,
> 10000001, CONCAT_WS(" ",'test','tesgtt'), Curdate())
> MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
> that
> corresponds to your MySQL server version for the right syntax to use near
> ' , ,
> 'test', 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '',
> '',' at
> line 1)
> Session halted.
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Damian Hupfeld
|
| Posted: 10/02/2005, 2:15 AM |
|
Hiya Walter,
I tried the above problem and could not get the solution right. I did use
only single quote marks. I think that Claudeless used doubles because that
is what the docos say - but they are not using it inside an existing set of
doubles.
CONCAT_WS(' ', '{fname}', '{lname}')
Damian
PS Im good - hope you are too!
"Walter Kempees" <kempe819@planet.nl> wrote in message
news:dhjf37$9ek$1@news.codecharge.com...
> Within the Concat_ws() you are mixing the double and single quotes, why?
>
> You can even do multiple concat_ws's, but use the single quotes only!
>
> concat_ws(' ', person.initials, concat_ws('-',
> person.name_spouse,concat_ws('', person.extras,' ',person.name ) ) )
>
>
>
>
> "claudeless" <claudeless@forum.codecharge> schreef in bericht
>news:5433cab8fb3fb0@news.codecharge.com...
>> No luck.. this is the error
>>
>> Invalid SQL: INSERT INTO player (TeamID, TierID, PStatus, FirstName,
>> LastName,
>> Address1, Address2, City, State, Zip, Number, Pos, Hphone, WPhone,
>> CPhone,
>> Email, DOB, LeagueID, RegionID, FullName, DateCreated) VALUES (, , ,
>> 'test',
>> 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '', '',
>> 10000,
>> 10000001, CONCAT_WS(" ",'test','tesgtt'), Curdate())
>> MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
>> that
>> corresponds to your MySQL server version for the right syntax to use near
>> ' , ,
>> 'test', 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '',
>> '',' at
>> line 1)
>> Session halted.
>> ---------------------------------------
>> Sent from YesSoftware forum
>> http://forums.codecharge.com/
>>
>
>
|
|
|
|
Walter Kempees
|
| Posted: 10/02/2005, 3:02 AM |
|
Damian, Yep doing all right, as wel as can be expected ;-P
I'll take the tread from your last reply.
"Damian Hupfeld" <damian.hupfeld@itng.com.au> schreef in bericht
news:dho8fv$c3s$1@news.codecharge.com...
> Hiya Walter,
>
> I tried the above problem and could not get the solution right. I did use
> only single quote marks. I think that Claudeless used doubles because that
> is what the docos say - but they are not using it inside an existing set
> of doubles.
>
> CONCAT_WS(' ', '{fname}', '{lname}')
>
> Damian
>
> PS Im good - hope you are too!
>
>
> "Walter Kempees" <kempe819@planet.nl> wrote in message
>news:dhjf37$9ek$1@news.codecharge.com...
>> Within the Concat_ws() you are mixing the double and single quotes, why?
>>
>> You can even do multiple concat_ws's, but use the single quotes only!
>>
>> concat_ws(' ', person.initials, concat_ws('-',
>> person.name_spouse,concat_ws('', person.extras,' ',person.name ) ) )
>>
>>
>>
>>
>> "claudeless" <claudeless@forum.codecharge> schreef in bericht
>>news:5433cab8fb3fb0@news.codecharge.com...
>>> No luck.. this is the error
>>>
>>> Invalid SQL: INSERT INTO player (TeamID, TierID, PStatus, FirstName,
>>> LastName,
>>> Address1, Address2, City, State, Zip, Number, Pos, Hphone, WPhone,
>>> CPhone,
>>> Email, DOB, LeagueID, RegionID, FullName, DateCreated) VALUES (, , ,
>>> 'test',
>>> 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '', '',
>>> 10000,
>>> 10000001, CONCAT_WS(" ",'test','tesgtt'), Curdate())
>>> MySQL Error: 1064 (You have an error in your SQL syntax; check the
>>> manual that
>>> corresponds to your MySQL server version for the right syntax to use
>>> near ' , ,
>>> 'test', 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '',
>>> '',' at
>>> line 1)
>>> Session halted.
>>> ---------------------------------------
>>> Sent from YesSoftware forum
>>> http://forums.codecharge.com/
>>>
>>
>>
>
>
|
|
|
|
Walter Kempees
|
| Posted: 10/02/2005, 3:35 AM |
|
First thing first.
The Concat_ws is supremely helpfull in situations where you have to add 2
fields together resulting in a third, where no programming language is
available.
In other words straight SQL on the Db.
We use it all the time when reporting rows from a database in report and
browses (Grid).
(No discussion plse:) we always let the database engine do things to data as
opposed to doing it in PhP.
On a table named "addnames" containing besides an autonum primary_key field
ID,
Fname, Lname, Fullname
example #1
this works in straight SQL
INSERT into addnames (Fname, Lname, FullName) Values
('Damian','Hupfield',Concat_ws('-',Fname, Lname));
resulting in
1, Damian, Hupfield, Damian-Hupfield.
In CCS I would therefore substitute the Values with {Fname} and {Lname} but
let SQL handle the Concat!
Another approach would off course be (a way to reach th previous goal) to
Let CCS do the Insert of Fname and Lname and after succesful insert do an
update :
update addnames set FullName=Concat_ws('*', Fname, Lname)
If you would like to do it in CCS using PhP, why not do a $Fullname= $Fname
.. $Lname
(using the right CCS syntax of course.
Second:
My previous example documents the problem we have in Holland where a persons
name consist of
Initials like T.
Firstname Troy
prefix_to_Lastname of
Lastname Rome
Maidenname Pompeď only filled if gender is Female
Then when creating letterheads and so on we have to first see if Maidenname
is filled and gender is Female and the do a proper concatenate
combining multiple concat_ws() constructions.
Oh I almost forgot if an argument is blank then the concat first argument
will NOT be used!
concat_ws(' ', Initials, concat_ws('-', Maidenname, concat_ws('',
prefix_toLastname, ' ', Lastname ) ) ) as Name
resulting in: T. Pompeď-of Rome
if Maindenname is blank
resulting in: T. of Rome
Hope this does it for all of us.
Greetings Walter
"Damian Hupfeld" <damian.hupfeld@itng.com.au> schreef in bericht
news:dhjfbl$9i2$1@news.codecharge.com...
>I spent an hour on this and couldnt get it!
>
> $names->ds->SQL =
> str_replace("INSERT INTO names (id, fname, lname)
> Values ('{id}', '{fname}', '{lname}')",
> "INSERT INTO names (id, fname, lname, fullname)
> Values ('{id}', '{fname}', '{lname}', CONCAT_WS(' ', '{fname}',
> '{lname}'))",
> $names->ds->SQL);
>
> I dont get any errors but I dont get anything in the fullname field
> either. Anyone else?
>
>
>
>
>
> "claudeless" <claudeless@forum.codecharge> wrote in message
>news:5433cab8fb3fb0@news.codecharge.com...
>> No luck.. this is the error
>>
>> Invalid SQL: INSERT INTO player (TeamID, TierID, PStatus, FirstName,
>> LastName,
>> Address1, Address2, City, State, Zip, Number, Pos, Hphone, WPhone,
>> CPhone,
>> Email, DOB, LeagueID, RegionID, FullName, DateCreated) VALUES (, , ,
>> 'test',
>> 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '', '',
>> 10000,
>> 10000001, CONCAT_WS(" ",'test','tesgtt'), Curdate())
>> MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
>> that
>> corresponds to your MySQL server version for the right syntax to use near
>> ' , ,
>> 'test', 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '',
>> '',' at
>> line 1)
>> Session halted.
>> ---------------------------------------
>> Sent from YesSoftware forum
>> http://forums.codecharge.com/
>>
>
>
|
|
|
|
Walter Kempees
|
| Posted: 10/02/2005, 3:38 AM |
|
Insert into table (Fname, LName, FullName) Values ('{Fname}','{Lname}',
Concat_ws(' ', Fname, Lname);
"claudeless" <claudeless@forum.codecharge> schreef in bericht
news:5433b05cd012ed@news.codecharge.com...
> Example
>
> Field1 = Fname
> Field2 = Lname
>
> I would like to do this
>
> FullName = Fname . ' ' . Lname
> How do I do this
>
> here is the sql statement
>
> insert into table (Fname, LName, FullName) Values
> ('{Fname}', '{LName}', '{FullName}')
>
>
> Thanks
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Walter Kempees
|
| Posted: 10/02/2005, 3:44 AM |
|
Quote :
$names->ds->SQL =
str_replace("INSERT INTO names (id, fname, lname)
Values ('{id}', '{fname}', '{lname}')",
"INSERT INTO names (id, fname, lname, fullname)
Values ('{id}', '{fname}', '{lname}', CONCAT_WS(' ', '{fname}',
'{lname}'))",
$names->ds->SQL);
What's this doing?
I would use
$names->ds->SQL ="INSERT INTO names ( id, fname, lname ) Values ( '{id}',
'{fname}', '{lname}', CONCAT_WS(' ', fname, lname) )";
$names->ds->SQL;
And I'm not commenting on filling the id field which is not needed if
autonum primary
greetz
"Damian Hupfeld" <damian.hupfeld@itng.com.au> schreef in bericht
news:dhjfbl$9i2$1@news.codecharge.com...
>I spent an hour on this and couldnt get it!
>
> $names->ds->SQL =
> str_replace("INSERT INTO names (id, fname, lname)
> Values ('{id}', '{fname}', '{lname}')",
> "INSERT INTO names (id, fname, lname, fullname)
> Values ('{id}', '{fname}', '{lname}', CONCAT_WS(' ', '{fname}',
> '{lname}'))",
> $names->ds->SQL);
>
> I dont get any errors but I dont get anything in the fullname field
> either. Anyone else?
>
>
>
>
>
> "claudeless" <claudeless@forum.codecharge> wrote in message
>news:5433cab8fb3fb0@news.codecharge.com...
>> No luck.. this is the error
>>
>> Invalid SQL: INSERT INTO player (TeamID, TierID, PStatus, FirstName,
>> LastName,
>> Address1, Address2, City, State, Zip, Number, Pos, Hphone, WPhone,
>> CPhone,
>> Email, DOB, LeagueID, RegionID, FullName, DateCreated) VALUES (, , ,
>> 'test',
>> 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '', '',
>> 10000,
>> 10000001, CONCAT_WS(" ",'test','tesgtt'), Curdate())
>> MySQL Error: 1064 (You have an error in your SQL syntax; check the manual
>> that
>> corresponds to your MySQL server version for the right syntax to use near
>> ' , ,
>> 'test', 'tesgtt', 'dsfsdaf', 'dfdsf', 'sdfsdf', 'df', , 34, , , , , '',
>> '',' at
>> line 1)
>> Session halted.
>> ---------------------------------------
>> Sent from YesSoftware forum
>> http://forums.codecharge.com/
>>
>
>
|
|
|
|
Walter Kempees
|
| Posted: 10/02/2005, 3:52 AM |
|
Quote Walter Kempees:
Insert into table (Fname, LName, FullName) Values ('{Fname}','{Lname}',
Concat_ws(' ', Fname, Lname);
there is one ')' missing in above statement.
Insert into table (Fname, LName, FullName) Values ('{Fname}','{Lname}', Concat_ws(' ', Fname, Lname) );
|
|
|
|
Damian Hupfeld
|
| Posted: 10/02/2005, 6:44 AM |
|
Aaargghh!!!
Another hour gone by!
First I struggled to be sure WHERE to be adding the CODE and believe that it
should be
Before execute Insert
Next I dont get what I think I should:
a) When I run the following SQL directly:
INSERT INTO names (fname, lname, fullname)
Values (123, 321, CONCAT_WS(' ', fname,
lname))
I get:
123 | 321 | 123 321
Which is correct.
b) When I run this in CCS
$names->ds->SQL =
"INSERT INTO names (fname, lname, fullname)
Values ('{fname}', '{lname}', CONCAT_WS(' ', fname,
lname))";
I get:
{fname} | {lname} | {fname} {lname}
which is no good
c} When I run this in CCS
$names->ds->SQL =
"INSERT INTO names (fname, lname, fullname)
Values ({fname}, {lname}, CONCAT_WS(' ', fname,
lname))";
I get:
syntax error
which is no good
d} When I run this in CCS
$names->ds->SQL =
"INSERT INTO names (fname, lname, fullname)
Values ({fname}, {lname}, CONCAT_WS(' ', fname,
lname))";
I get:
| |
(ie Nothing)
which is no good
e} When I run this in CCS
$names->ds->SQL =
"INSERT INTO names (fname, lname, fullname)
Values ('fname', 'lname', CONCAT_WS(' ', fname,
lname))";
I get:
fname | lname | fname lname
which is no good
----------------------
I really dont know where to go next. The SQL command is fine. How/Where to
implement in CCS - I dont know.
Damian
<WalterKempees@forum.codecharge (Walter Kempees)> wrote in message
news:5433fbbd966dc6@news.codecharge.com...
> Quote Walter Kempees:
> Insert into table (Fname, LName, FullName) Values ('{Fname}','{Lname}',
> Concat_ws(' ', Fname, Lname);
>
>
>
> there is one ')' missing in above statement.
>
>
> Insert into table (Fname, LName, FullName) Values ('{Fname}','{Lname}',
> Concat_ws(' ', Fname, Lname) );
>
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
Walter Kempees
|
| Posted: 10/02/2005, 8:59 AM |
|
Yee Whiz!!!!
To save another hour I would do the update in the after insert success.
update names set fullname = concat_ws('',fname, lname) where id =
get_last_inserted_id
or if the table is not too big do it without the wher, updating all rows
just to be safe (kidding)
After that we'll find out where to put this statement.
I don't really know just of the back off my head but we'll figure it out.
But it's Sunday 17..0 pm and my prtners is eager to get cooking and so on,
so ..... later, siorry.
|
|
|
|
Walter Kempees
|
| Posted: 10/02/2005, 9:13 AM |
|
Are you using a generated RecordForm ?
If Yes, make it so that it does the insert for fname and lname, generate
take the insert copy it, set the record from to use custom insert drop the
copied insert in there add the concat_ws.
Just an idea, oepsssss she seen me, kitchen utilities flying around
me.................
later
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 10/02/2005, 3:25 PM |
|
Äfter dining:"
I build a small test, and did not use the concat_ws() function.
Instead I realized that having a Record Form with Fname and Lname as editable fields, the best way to fill the records FullName with the Fname+' ' + Lname would be to have Fullname as a Hidden Field on the RecordForm, Event On Validate would then contain:
//addnames1_FullName_OnValidate @41-42B120CE
function addnames1_FullName_OnValidate()
{
$addnames1_FullName_OnValidate = true;
//End addnames1_FullName_OnValidate
//Custom Code @42-3B04D9C2
// -------------------------
global $addnames1;
// Write your own code here.
$addnames1-> FullName ->SetValue($addnames1->Fname->GetValue() .' '. $addnames1->Lname->GetValue() );
// -------------------------
//End Custom Code
//Close addnames1_FullName_OnValidate @41-2ED6B1F5
return $addnames1_FullName_OnValidate;
}
//End Close addnames1_FullName_OnValidate
Which would habdle both the INSERT and the UPDATE correctly.
I came to this line of thinking by my own statement saying concat_ws() is great for doing things on the DB directly. Which we are ot because we are letting the user insert/update the record info.
No Iám not chickening out, I did try to put the concat_ws inside the INSERT statement but non of the events is letting me do this.
SO, I stick with my previous post: the only way I can see to do ti using the concat_ws is by firing an UPDATE after the INSERT and after the UPDATE (= generated).
Any thoughts?
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
 |
|
peterr
Posts: 5971
|
| Posted: 10/03/2005, 2:03 AM |
|
Couple ways to accomplish this:
1. In the Custom Insert property add FullName field with an "Expression" type parameter and the following value:
$GLOBALS["FormName"]->Fname->GetValue() . " " . $GLOBALS["FormName"]->Lname->GetValue()
2. In the Custom Insert property add FullName field with an "Expression" type parameter and any value. Then use the following BeforeBuildInsert event to modify/assign the value:
global $Form;
$FormName->ds->cp["FieldName"]->SetValue( $FormName->Fname->GetValue() . " " . $FormName->Lname->GetValue() );
The above code assumes that your control names are Fname and Lname, not Field1 and Field2 as in your example. Table field names are not important and not used here, except for FullName.
FormName is the name of the record form.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Damian Hupfeld
|
| Posted: 10/03/2005, 4:24 AM |
|
"wkempees" <wkempees@forum.codecharge> wrote in message
news:543405e64be6ce@news.codecharge.com...
<snip>
> No Iám not chickening out, I did try to put the concat_ws inside the
> INSERT
> statement but non of the events is letting me do this.
<snip>
That makes me feel SO much better! I tried and tried and tried some more and
I couldnt get it! If WK cant do it I dont feel so bad!
Damian
|
|
|
|
Walter Kempees
|
| Posted: 10/03/2005, 5:04 AM |
|
Damian:
Grin (assuming there's no sarcasm '-) )
Check out Peterr's answer.
Still not using concat_ws, but a nice one, I learned something from it
anyway.
But still think that as it's Inserting and updating records, the approach
would be to do something maintainable like the hidden field.
Or a trigger on the Db, which is more Oracle and the likes.
Anyway, we did exchange a lot of text again and that's been a while.
"Damian Hupfeld" <damian.hupfeld@itng.com.au> schreef in bericht
news:dhr4cl$jft$1@news.codecharge.com...
>
> "wkempees" <wkempees@forum.codecharge> wrote in message
>news:543405e64be6ce@news.codecharge.com...
> <snip>
>
>> No Iám not chickening out, I did try to put the concat_ws inside the
>> INSERT
>> statement but non of the events is letting me do this.
>
> <snip>
>
> That makes me feel SO much better! I tried and tried and tried some more
> and I couldnt get it! If WK cant do it I dont feel so bad!
>
> Damian
>
>
|
|
|
|
BW Malcolm
|
| Posted: 11/25/2005, 11:06 PM |
|
|
|
|
|
|
