[wdvltalk] mysql and CONCAT...
Ello all... I've got 2 fields. Depending on the result, one might be NULL, this is intentional. However, if the 2nd IS null, I want to combine it with eh first... eg: field1 | field2 sign up | NULL points claimed | NULL points spent| claimed prize X So in my output I want a new field to be created using CONCAT CONCAT(field1, - , field2) as field1 so I WANT the output of field1 to be: sign up points claimed points spent - claimed prize X but I'm getting a blank field1 if field 2 is null... How can I get round that? -- Give a man a fish and he'll feed himself for a day. Give a man a religion and he'll starve to death praying for a fish. Anon `We are what we pretend to be, so we must be careful what we pretend to be.` Kurt Vonnegut `When a person can no longer laugh at himself, it is time for others to laugh at him.` Thomas Szasz � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
On Tuesday 21 August 2007 10:54:59 Tris wrote: Ello all... I've got 2 fields. Depending on the result, one might be NULL, this is intentional. However, if the 2nd IS null, I want to combine it with eh first... eg: field1| field2 sign up | NULL points claimed| NULL points spent | claimed prize X So in my output I want a new field to be created using CONCAT CONCAT(field1, - , field2) as field1 so I WANT the output of field1 to be: sign up points claimed points spent - claimed prize X but I'm getting a blank field1 if field 2 is null... select if(field2 is null, field1, concat(field1, ' - ', field2)) ... Something like that ought to do it. (This seems like a very strange DB schema... :) ) -- David Precious :: [EMAIL PROTECTED] http://blog.preshweb.co.uk/ :: http://www.preshweb.co.uk/ � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
It's a lot of joins...
From user table, points table, rewards table, statement table...
I just simplified it for the post... ;-p
just finishing off
http://www.tlc-rewards.com/4462Dubli/
On 21/08/07, David Precious [EMAIL PROTECTED] wrote:
On Tuesday 21 August 2007 10:54:59 Tris wrote:
Ello all...
I've got 2 fields.
Depending on the result, one might be NULL, this is intentional.
However, if the 2nd IS null, I want to combine it with eh first...
eg:
field1| field2
sign up | NULL
points claimed| NULL
points spent | claimed prize X
So in my output I want a new field to be created using CONCAT
CONCAT(field1, - , field2) as field1
so I WANT the output of field1 to be:
sign up
points claimed
points spent - claimed prize X
but I'm getting a blank field1 if field 2 is null...
select if(field2 is null, field1, concat(field1, ' - ', field2)) ...
Something like that ought to do it.
(This seems like a very strange DB schema... :) )
--
David Precious :: [EMAIL PROTECTED]
http://blog.preshweb.co.uk/ :: http://www.preshweb.co.uk/
• The WDVL Discussion List from WDVL.COM •
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: wdvltalk@lists.wdvl.com
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
http://www.wdvl.com ___
You are currently subscribed to wdvltalk as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
--
Give a man a fish and he'll feed himself for a day.
Give a man a religion and he'll starve to death praying for a fish.
Anon
`We are what we pretend to be, so we must be careful what we pretend to be.`
Kurt Vonnegut
`When a person can no longer laugh at himself, it is time for others
to laugh at him.`
Thomas Szasz
� The WDVL Discussion List from WDVL.COM �
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: wdvltalk@lists.wdvl.com
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
http://www.wdvl.com ___
You are currently subscribed to $subst('List.Name') as:
$subst('Recip.EmailAddr')
To unsubscribe send a blank email to $subst('Email.UnSub')
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
CONCAT(field1, - , field2) as field1 there are several ways to deal with NULLs here check each field individually -- CONCAT( COALESCE(field1,'') , ' - ' , COALESCE(field2,'') ) however, if field1 is NULL, then the result starts with the dash, so... CONCAT( COALESCE(CONCAT(field1,' - '),'') , COALESCE(field2,'') ) which is esthetically better but rather clumsy my favourite approach -- CONCATE_WS( ' - ' , field1, field2 ) check out CONCAT_WS in the manual for how it works with NULLs ;o) � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
On Tuesday 21 August 2007 11:53:53 Tris wrote: It's a lot of joins... From user table, points table, rewards table, statement table... I just simplified it for the post... ;-p Ah fair enough :) Did the if(...concat(..)..) work? just finishing off http://www.tlc-rewards.com/4462Dubli/ Cool. When PIPEX were using TLC there were some fairly good rewards on offer (and a fair amount of crap ones, too :) ) -- David Precious :: [EMAIL PROTECTED] http://blog.preshweb.co.uk/ :: http://www.preshweb.co.uk/ � The WDVL Discussion List from WDVL.COM � To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] mysql and CONCAT...
Um, here's my finished query:
SELECT statement.timestamp, CONCAT(transferType.descriptionUser, ,
COALESCE(rewards.title, )) as descriptionUser, statement.calcType,
statement.points
FROM `users`
JOIN statement on
statement.userId = users.id
JOIN transferType on
transferType.id = statement.transferType
LEFT JOIN rewards on
rewards.id = statement.rewardId
WHERE users.id = . $_SESSION[userId] .
ORDER BY timestamp ASC
In the last 18 months, I've really got to grips with Joins etc.. Rudy
helped me through some weird probs... now it's getting easier (note,
stil not easy ;-p )
TLC, yeah, must say nothing... if they paid me on time, they'd be a
great company... ;-p
On 21/08/07, David Precious [EMAIL PROTECTED] wrote:
On Tuesday 21 August 2007 11:53:53 Tris wrote:
It's a lot of joins...
From user table, points table, rewards table, statement table...
I just simplified it for the post... ;-p
Ah fair enough :)
Did the if(...concat(..)..) work?
just finishing off
http://www.tlc-rewards.com/4462Dubli/
Cool. When PIPEX were using TLC there were some fairly good rewards on offer
(and a fair amount of crap ones, too :) )
--
David Precious :: [EMAIL PROTECTED]
http://blog.preshweb.co.uk/ :: http://www.preshweb.co.uk/
• The WDVL Discussion List from WDVL.COM •
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: wdvltalk@lists.wdvl.com
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
http://www.wdvl.com ___
You are currently subscribed to wdvltalk as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
--
Give a man a fish and he'll feed himself for a day.
Give a man a religion and he'll starve to death praying for a fish.
Anon
`We are what we pretend to be, so we must be careful what we pretend to be.`
Kurt Vonnegut
`When a person can no longer laugh at himself, it is time for others
to laugh at him.`
Thomas Szasz
� The WDVL Discussion List from WDVL.COM �
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: wdvltalk@lists.wdvl.com
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
http://www.wdvl.com ___
You are currently subscribed to $subst('List.Name') as:
$subst('Recip.EmailAddr')
To unsubscribe send a blank email to $subst('Email.UnSub')
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
[wdvltalk] Site Check Please
I'd appreciate comments on www.healthandsafetyformicrofirms.com if anyone has time. There is no checkout at the moment and we're still uploading data and tidying up some of the data but some initial reactions please. You need to register to get inside the site but at the moment you can do that at no cost - ignore the cost that it shows if you register, it goes nowhere! I'll delete all names from the database before we go live in a few weeks. Tens of thousands of small firms in UK have no HS system even though it is a legal requirement. (Must be in writing if there are five or more employees). Most think it is too costly to bring in professional help to set one up - this is an attempt to tap that market. Thanks for any comments - good bad or indifferent! Regards Peter MacGregor PS - the ads are purely for testing purposes at this stage! The WDVL Discussion List from WDVL.COM To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
Re: [wdvltalk] Site Check Please
Hi Peter, Well, my first look says wa to busy looking. I think maybe there's to much of the same look all over. Perhaps if you break it up a little bit by saying putting a bg color in the right or left cells? I don't think I'd go w/red but maybe - I'd have to play w/it. Just seems to need some breaking up for the visual. The grey on this page might do it: http://healthandsafetyformicrofirms.com/index.php?main_page=indexcPath=4 or a lighter grey. I think I'd also change the light pink to very light grey - the pink is a little hard on my eyes anyway and since this is not strictly a female site - I'd try to get a more uni-sex color combo going. I like the logo :) My feeling when I view it is governmental so if that's what you're shooting for then in my opinion you hit the mark. Question, is this all set in a Zencart template or is the cart separate? Looks like a huge undertaking - I wish you the best of luck! Deb At 02:08 PM 8/21/2007, you wrote: I'd appreciate comments on www.healthandsafetyformicrofirms.com if anyone has time. ÷÷÷ ÷÷÷ ÷ ÷÷÷ Deb Designer @ Digital Mouse Designs com The WDVL Discussion List from WDVL.COM To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: wdvltalk@lists.wdvl.com To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk http://www.wdvl.com ___ You are currently subscribed to wdvltalk as: unknown lmsubst tag argument: '' To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
