MySQL Arbitarary string prefix in select statement

Discussion in 'Web Design & Coding' started by LordOfLA, May 1, 2006.

  1. LordOfLA

    LordOfLA Godlike!

    Messages:
    7,027
    Location:
    Maidenhead, Berkshire, UK
    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
     
  2. Geffy

    Geffy Moderator Folding Team

    Messages:
    7,805
    Location:
    United Kingdom
    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;
     
  3. Mainframeguy

    Mainframeguy Debiant by way of Ubuntu Folding Team

    Messages:
    3,763
    Location:
    London, UK
    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...
     
  4. LordOfLA

    LordOfLA Godlike!

    Messages:
    7,027
    Location:
    Maidenhead, Berkshire, UK
    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
     
  5. LordOfLA

    LordOfLA Godlike!

    Messages:
    7,027
    Location:
    Maidenhead, Berkshire, UK
    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 :)