SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question How do I display parts of queried data in different sections of output?

    I'm hoping someone can help. Here's my problem.

    Lets say I'm building a report where I query SQL for data and display the individual lines and show a sum at the bottom...

    Example:

    Code:
    <%
        do until rsData.EOF
    %>
            <tr>
                <td><%=rs("NAME")%></td>
                <td><%=rs("SALES")%></td>
            </tr>
    
    <%
        TTL_SALE = TTL_SALE + rs("SALES")
        rsData.MoveNext
            loop
        rsData.Close
    %>
    
            <tr>
                <td>TOTAL</td>
                <td><%=TTL_SALE%></td>
            </tr>
    The output would be like:

    JOHN 5
    JANE 6
    MARK 7
    TOTAL 18

    Now lets say I want to show the TOTAL at the top, before the detail.

    TOTAL 18
    JOHN 5
    JANE 6
    MARK 7


    So how does one go about getting the sum appear at the top before the detail, without having the query the SQL server twice ... once for the TOTAL and again for the detail? Is it possible to setup the SQL query as a function, or a way to pull the SQL data into cache to display in different parts of a page?

    Thanks!!

  2. #2
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    One way would be to build your intended output in a string variable (rather than echoing straight out), then echo out the total section then echo out the string variable.

    Code:
    <%
    sStr = ""
    do until rsData.EOF
        sStr = sStr & "        <tr>"
        sStr = sStr & "            <td>" & rs("NAME")& "</td>"
        sStr = sStr & "            <td>" & rs("SALES") & "</td>"
        sStr = sStr & "        </tr>"
        TTL_SALE = TTL_SALE + rs("SALES")
        rsData.MoveNext
    loop
    rsData.Close
    %>
            <tr>
                <td>TOTAL</td>
                <td><%=TTL_SALE%></td>
            </tr>
    <%
    Response.Write sStr
    %>
    Ian Anderson
    www.siteguru.co.uk

  3. #3
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Holy smokes! Now that I see how you did it, it makes it super easy!! THANK YOU!!!

    Is there any other way of doing it?

  4. #4
    SitePoint Wizard siteguru's Avatar
    Join Date
    Oct 2002
    Location
    Scotland
    Posts
    3,629
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <%
    sStr = ""
    do until rsData.EOF
        sStr = sStr & "    <tr>" &_
        "        <td>" & rs("NAME")& "</td>" &_
        "        <td>" & rs("SALES") & "</td>" &_
        "    </tr>"
        TTL_SALE = TTL_SALE + rs("SALES")
        rsData.MoveNext
    loop
    rsData.Close
    %>
        <tr>
            <td>TOTAL</td>
            <td><%=TTL_SALE%></td>
        </tr>
    <%Response.Write sStr%>
    Using &_ is a way of telling the script that the string is continued on the next line.
    Ian Anderson
    www.siteguru.co.uk

  5. #5
    SitePoint Member
    Join Date
    Jun 2013
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have some code that I use in websites to enter values ​​in the array, and store in session. Eu ut This code is very useful for shopping cart in loja virtual:

    <%

    Function VetorMonta(Acao,Valor)
    'Usamos o case para manipular a ação da função
    Select Case Trim(Acao)
    Case "Incluir"
    'Guarda na variavel Vetor o conteudo da Session
    Vetor = Session("GuardaVetor")
    'Verifica se a Variavel Vetor é um Array, caso nao for entao definimos ela um Array
    If Not IsArray(Vetor) Then Vetor = Array() End if
    'Verifica se o Valor que esta sendo inserido já esta no Vetor se estiver entao nao inseri para nao haver duplicidades do vetor
    If InStr(Join(Vetor), Valor) = 0 Then
    'Este comando ira preservar o vetor e adciona + 1 valor
    ReDim preserve Vetor(UBound(Vetor)+1)
    Vetor(Ubound(Vetor )) = Valor
    'Coloca o conteudo da variavel vetor dentro da Session
    Session("GuardaVetor") = Vetor
    End if
    End Select 'Fim do Case
    End Function

    Function Incluir_Vetor
    'Executa a função que ira criar uma posição do vetor, basta passar a acao e o valor
    Call VetorMonta("Incluir",Replace(Request("Valor_Vetor"),"'",""))
    Call VetorMonta("Incluir",Replace(Request("tamanho"),"'",""))
    End Function


    Function VisualizaValoresVetor
    Response.Write "<table border='2'>"
    Response.Write "<tr><td>id</td><td>valor</td>"
    Response.Write "</tr>"

    For x = 0 To ubound(session("GuardaVetor")) 'ira fazer um laço mostrando todos os vetores criados
    Response.Write "<tr><td>" & x & "</td>"
    Response.Write "<td>" &session("GuardaVetor")(x) & "</td>"
    Response.Write "</tr>"
    Next
    Response.Write "</table>"

    'Verifica se a Session tem alguma posição, se tiver mostra a opção de apagar todos os vetores
    If ubound(session("GuardaVetor")) >= 0 Then
    Response.Write "<br>" &"<a href='vetor.asp?action=LimpaVetor'>Apagar Tudo</a>" & "<br>" 'Imprime o Vetor na tela
    End if

    End Function


    'USAMOS CASES PARA MANUPULARAS CHAMADAS DAS FUNÇÕES
    useraction=request("action")
    select case useraction
    Case "Incluir_Vetor"
    'Chama a function que ira incluir um valor para o vetor
    Call Incluir_Vetor
    End Select
    %>
    <table width="100%">
    <form name="form" method="post" action="?action=Incluir_Vetor">
    <tr>
    <td><input name="Valor_Vetor" type="text" id="Valor_Vetor"> </td>
    <td><input name="tamanho" type="text" >
    <input type="submit" name="Submit" value="Incluir no Vetor"></td>
    </tr>
    </form>
    </table>
    <%
    'Chama a função que vizualiza todos os vetores criados
    VisualizaValoresVetor
    %>

    I so almost all of my sites. Hope that helps!
    Last edited by Mittineague; Jun 22, 2013 at 11:52. Reason: removing unnecessary links


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
  •