Solved

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

Posted on 2008-10-10
2
796 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
How do I use framesets in Ruby on Rails? 3 570
Routing in Rails 3 3 680
Rails 3.2 directory to upload files 3 775
What is 'f.hidden_field' for? 1 295
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

757 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

19 Experts available now in Live!

Get 1:1 Help Now