SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: MySQL CONCAT

  1. #1
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL CONCAT

    Hi guys, I am trying to concatenate the following aliases; monday, tuesday, wednesday, thursday, friday, saturday and sunday. And I am encountering an error like
    Unknown column 'monday' in 'field list'
    So far, this is my query, can anyone help me how to concatenate the days from monday to sunday. Thanks in advance.
    Code:
    SELECT schedule.subjcode, subjdescription,
      if(schedule.monday_am='', char(32), CONCAT('M: ',schedule.monday_am,'-',schedule.monday_pm,char(13))) AS monday,
      if(schedule.tuesday_am='',char(32), CONCAT('T: ',schedule.tuesday_am,'-',schedule.tuesday_pm, char(13))) AS tuesday,
      if(schedule.wednesday_am='',char(32), CONCAT('W: ', schedule.wednesday_am,'-',schedule.wednesday_pm, char(13))) AS wednesday,
      if(schedule.thursday_am='', char(32), CONCAT('TH: ', schedule.thursday_am,'-',schedule.thursday_pm, char(13))) AS thursday,
      if(schedule.friday_am='', char(32), CONCAT('F: ',schedule.friday_am,'-',schedule.friday_pm, char(13))) AS friday,
      if(schedule.saturday_am='', char(32), CONCAT('SAT: ', schedule.saturday_am,'-',schedule.saturday_pm, char(13))) AS saturday,
      if(schedule.sunday_am='', char(32), CONCAT('SUN: ', schedule.sunday_am,'-',schedule.sunday_pm)) AS sunday,
      absences.totalabsences FROM schedule INNER JOIN absences ON absences.subjectcode=schedule.subjcode
      INNER JOIN subjectinfo ON subjectinfo.subjcode=schedule.subjcode

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    is this MySQL?

    if so, please post your create table statements for the two tables and clarify what you are trying to do. I guess you are trying to display 'opening hours' or 'trading hours' like this:

    Mon: 0900 - 1700
    Tues: 1000 - 1600
    etc

    if so (i may well be corrected), I would store my data differently and then format the output in the scripting language.

    bazz

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    what exactly does "concatenate the days from monday to sunday" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    396
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @r937: The purpose of concatenation is for me to place the information in the report designer - Crystal Report.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •