displaying SQL results on webpage

Discussion in 'Programming' started by Chenks, Aug 6, 2013.

Share This Page

  1. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    OK, got a bit of a conundrum here.

    I've got an SQL query that i wan't to display on a webpage.
    The problem I have is getting it to display the results in the format that is required.

    It's essentially to be a weekly schedule of jobs, split into days, and grouped by each installation team. The number of jobs per day per team can vary from none to 3 or 4.
    The SQL query pull of the job data from the base in a big list (as i would expect) - no problems there.

    The conundrum is getting it to display on the webpage in the specific format - which is this.

    https://www.dropbox.com/s/vm9auew6wdvkl0k/installs.JPG

    The webserver is a IIS windows server, but has PHP installed, so can use ASP/VB or PHP.

    This is the query.

    Code:
    select Pers_Name, PER_Data7, Call_Ref, dbo.dateonly(Scheduled_Date_Time) as sched_date, Link_to_Contract_Header, Add1, Add2, Post_Code, Call_Type_Description, LUCFC_Description, Call_Status_Description
    from Calls with (nolock)
    	inner join Clients with (nolock) on Link_to_Client=Client_Ref
    	left join Personnel with (nolock) on Last_Allocated_To=Pers_Ref
    	left join LU_Call_Types with (nolock) on Call_Type=Call_Type_Code
    	left join Personnel_More with (nolock) on Last_Allocated_To=PER_Link_to_Pers_Ref
    	left join LU_Call_Fault_codes with (nolock) on call_fault_code_1=LUCFC_Code
    	left join LU_Call_Status with (nolock) on Last_Event_Status=Call_Status_Code
    where dbo.dateonly(Scheduled_Date_Time) between '5 August 2013' and '11 August 2013'
    	and Call_Type in ('BC','IN')
    	and PER_Data7 is not NULL
    order by sched_date, PER_Data7
    "PER_Data7" is the field that holds the team name (ie Team 1, Team 2 etc).
    "sched_date" is the date of the job.

    So.... how to tackle this?

    I have a working version that displays just a 1 day schedule.
     
  2. latency

    latency Existentialist

    Joined:
    Jul 1, 2004
    Messages:
    8,780
    Likes Received:
    0
    If you've got a version that'll do 1 day, you could repeat it seven times, outputting into an array for each team, and then you'll have 11 arrays, which can easily be echo'ed as table rows?
     
  3. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    the 1 day version isn't the same layout at the one required for the week though.
    the daily one is listed horizontally, whereas the weekly one is wanted vertically (if that makes sense) - it uses a different query too.

    this is what the daily version looks like (which will be scrapped)
    https://dl.dropboxusercontent.com/u/1499080/installs2.JPG
     
  4. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    so how would you do it ?
     
  5. latency

    latency Existentialist

    Joined:
    Jul 1, 2004
    Messages:
    8,780
    Likes Received:
    0
    Hmm. Thinking about this further, I'd do it in multiple queries, one for each team.

    I'd "SELECT (required fields) FROM `calls` WHERE PER_Data7 = 'Team 1' AND `sched_date` BETWEEN (start of range) AND (end of range);"

    That gives you a list of calls for the team in an array, then start a table, and in each cell, loop over the array, and if the date of the call matches the date of the cell, echo the call details.

    Repeat that for each team, starting a new row for each team, and you'll have the table layout you're after.

    Only disadvantage is one query per team, but since there's ten teams that's not the end of the world, and it's potentially a lot simpler than grabbing all the data in one go, and then having to sift through it.
     
  6. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    that's the choices i was going thru in my head... either a query for each day or a query for each team.

    not sure how to go about using an array though.
    for the 1 day schedule i had, i was just using a simple DO WHILE loop which gave me a row for each job. obviously the layout for this week schedule is different though.
     
  7. latency

    latency Existentialist

    Joined:
    Jul 1, 2004
    Messages:
    8,780
    Likes Received:
    0
    I'd have a bash at writing this for you, but making up an example database with some test data in it is a chore and a half... can you PM me an SQL dump of some example data? Then I'd be willing to give it a whirl.
     
  8. Chenks

    Chenks Registered Trader

    Joined:
    Jan 5, 2002
    Messages:
    3,741
    Likes Received:
    0
    PM sent