Solved

Ruby on Rails - how to display mySQL results on html.erb page

Posted on 2008-10-10
2
798 Views
Last Modified: 2012-05-05
I've attached the code snippets below

At the moment I'm displaying
Merchant | Product        | Request Type        | Success | Failure
22            | PRODUCT1 | CANCEL_CARD    | 8             |     0 |
22            | PRODUCT1 | REQUEST_CARD | 8             |     0 |
22            | PRODUCT2 | CANCEL_CARD    | 6             |     0 |
22            | PRODUCT2 | CANCEL_CARD    | 0             |     2 |
22            | PRODUCT2 | LOAD_CARD         | 1             |     0 |
22            | PRODUCT2 | REQUEST_CARD | 8             |     0 |
22            | PRODUCT3 | CANCEL_CARD    | 8             |    0 |
22            | PRODUCT3 | REQUEST_CARD | 10           |    0 |

Where as I really want the successes and failures on the same row:
Merchant | Product        | Request Type        | Success | Failure
22            | PRODUCT1 | CANCEL_CARD    | 8             |     0 |
22            | PRODUCT1 | REQUEST_CARD | 8             |     0 |
22            | PRODUCT2 | CANCEL_CARD    | 6             |    2 |
22            | PRODUCT2 | LOAD_CARD         | 1             |     0 |
22            | PRODUCT2 | REQUEST_CARD | 8             |     0 |
22            | PRODUCT3 | CANCEL_CARD    | 8             |    0 |
22            | PRODUCT3 | REQUEST_CARD | 10           |    0 |

<h1>Traffic Report</h1>
 

<% form_tag '/requests/traffic_report', :method => :get do%>

<dl>

    <dt>Merchant:</dt>

    <dd><%= select_tag(:merchant, "<option value=\"\">- Select merchant -</option>" + options_from_collection_for_select(Merchant.find(:all), :id, :name, @merchant.to_i)) %></dd>
 

    <dt>From date:</dt>

    <dd><%= calendar_date_select_tag "from_date", @from_date %></dd>
 

    <dt>To date:</dt>

    <dd><%= calendar_date_select_tag "to_date", @to_date %></dd>
 

    <dt>Request Type:</dt>

    <dd><%= select_tag(:request_type, "<option value=\"\">- Select request type -</option>" + options_from_collection_for_select(@request_types, :requestType,:requestType, @request_type)) %></dd>
 

    <dt></dt>

    <dd><%= submit_tag "Search" %></dd>

</dl>

<% end %>
 

<table border="1" style="border-collapse: collapse">

    <tr>

        <th>Merchant</th>

        <th>Product</th>

        <th>Provider</th>

        <th>Request Type</th>

        <th>Success</th>

        <th>Failure</th>

    </tr>
 

<%  if @requests.blank?

%>

    <tr>

        <td colspan="4">Sorry, there are no results</td>

    </tr>

</table>

<%

    else

        for request in @requests

%>

    <tr>

        <td><%=h Merchant.exists?(request.merchant) ? Merchant.find(request.merchant).name : "Merchant "+ request.merchant.to_s%></td>

        <td><%=h request.product  %></td>

        <td><%=h Product.exists?(request.product) ? Product.find(request.product).the_provider.name : "Unknown Provider"%></td>

        <td><%=h request.requestType  %></td>

        <td><%= request.rss == '0' ? request.total : '' %></td>

        <td><%= request.rss == '1' ? request.total : ''%></td>

    </tr>
 

<%      end %>

</table>

<%= will_paginate @requests %>

<%  end %>
 

#=========== snippet from request.rb ======================

    def self.search_traffic( from, to, request_type, merchant, page )

        conditions = self.create_conditions(from, to, request_type, merchant)
 

        Request.paginate_by_sql(["

            select merchant, product, requestType, '0' rss, count(*) total 

            from rb_request 

            where " + conditions + "

                 responseStatusCode=0

            group by merchant, product, requestType
 

            union
 

            select merchant, product, requestType, '1' rss, count(*) total 

            from rb_request 

            where " + conditions + " 

                  responseStatusCode <> 0

            group by merchant, product, requestType
 

            order by merchant, product, requestType, rss

        "], :page => page, :per_page => 10)
 

    end
 

#The above returns something that looks like in mySQL:

+----------+----------+--------------+-----+-------+

| merchant | product  | requestType  | rss | total |

+----------+----------+--------------+-----+-------+

|       22 | PRODUCT1 | CANCEL_CARD  | 0   |     8 | 

|       22 | PRODUCT1 | REQUEST_CARD | 0   |     8 | 

|       22 | PRODUCT2 | CANCEL_CARD  | 0   |     6 | 

|       22 | PRODUCT2 | CANCEL_CARD  | 1   |     2 | 

|       22 | PRODUCT2 | LOAD_CARD    | 0   |     1 | 

|       22 | PRODUCT2 | REQUEST_CARD | 0   |     8 | 

|       22 | PRODUCT3 | CANCEL_CARD  | 0   |     8 | 

|       22 | PRODUCT3 | REQUEST_CARD | 0   |    10 | 

+----------+----------+--------------+-----+-------+

The rss column is basically 1 if it's a failure count and 0 if it's a success count.

Open in new window

0
Comment
Question by:houndour
2 Comments
 
LVL 1

Accepted Solution

by:
JimOser earned 500 total points
ID: 22772410
Your code has:
    order by merchant, product, requestType, rss

So in your view you could do something like:
  <%
    else
        i = 0
        for request in @requests
%>
        ...
<%  if ((@requests[i].merchant == @requests[i+1].merchant)
             &&
            (@requests[i].product == @requests[i+1].product)
             &&
            (@requests[i].request == @requests[i+1].total))
                   
%>
           <td>       <%=   @request[i+1].total %> </td>  <td> <%= @request[i].total %> </td>
                 i = i + 2
<% else %>
                 what you had before
                 <td><%= request.rss == '0' ? request.total : '' %></td>
        <td><%= request.rss == '1' ? request.total : ''%></td>
                 i = i + 1
<% end %>
           
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I recently rediscovered rails when I needed a holiday project and decided to build a management dashboard for the company where I work.  With it being a project done in my free time, I could focus my time on learning the basics rather than trying to…
Article by: narshlob
If you've ever programmed in Ruby and have come across either a proc or a lambda, you might have been wondering what the difference is between the two and when you would use one over the other. This article will try to explain the difference between…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now