MySQL Arbitarary string prefix in select statement

LordOfLA

Godlike!
Joined
2 Feb 2004
Messages
7,026
Hey!

Anyone know if I can have mysql 5 arbitrarily prefix a value from a table in a select statement?

I had thought that something like:

Code:
SELECT CONCAT('maildir:',pop) AS home FROM users WHERE username = "email@domain.com"

would do the trick but I just get nothing out of mysql.

Pointers in the right direction would be appreciated :D
 
I too would also expect that to work.

you tried it with the email address in single quotes?

a good way to test it would be to try
SELECT CONCAT('maildir:', pop) AS home FROM users;
 
there's no chance this is in a php script and you have perhaps ommitted \"maildir:\" is there? The only other thing I can think of that pop is returning as null - in which case the whole finction evaluates to null (you never get just "maildir:" returned, but then I can't see why you would want to either...
 
nah not php, configuration for dovecot imap/pop3 server :)

For the moment I've manually specified a default location for maildir locations, but since that's stored in the database I'd like it to figure it out for itself :p

However I need to prefix "maildir:" to the value in the database so that insted of /usr/local/mail/domain/user_part_of_full_mail_address/Maildir it gets maildir:/usr/local/mail/domain/user_part_of_full_mail_address/Maildir
 
Well I don't know what I was doing wrong last night but the following works now:

Code:
SELECT CONCAT('maildir:',pop) AS mail, uid, gid FROM users WHERE username = %u"

%u gets expanded by dovecot to be the full username as supplied at pop3/imap4 login and is a full email address.

My guess is that I was supposed to use mail and not home. But works now :)
 

Members online

No members online now.

Latest profile posts

Also Hi EP and people. I found this place again while looking through a oooollllllldddd backup. I have filled over 10TB and was looking at my collection of antiques. Any bids on the 500Mhz Win 95 fix?
Any of the SP crew still out there?
Xie wrote on Electronic Punk's profile.
Impressed you have kept this alive this long EP! So many sites have come and gone. :(

Just did some crude math and I apparently joined almost 18yrs ago, how is that possible???
hello peeps... is been some time since i last came here.
Electronic Punk wrote on Sazar's profile.
Rest in peace my friend, been trying to find you and finally did in the worst way imaginable.

Forum statistics

Threads
62,015
Messages
673,494
Members
5,621
Latest member
naeemsafi
Back