How do I get multiple values from the MySQL relational database tables using Ruby on Rails?

I wasn't exactly sure how to word my question, so sorry if the question isn't phrased correctly.

Ill try to give the minimum amount of info needed to help me out. Please let me know edits to make or things to add. Thanks.

So I have a Relational MYSQL database already setup with data in 4 tables. This program is supposed to be a small-scale customer database lookup. I have an HTML page with a form: a text box asking for customer number, and 4 radio buttons to determine what data to retrieve. We're using Ruby on Rails (which I've never used) to access and control the data in the tables.

The 4 radio buttons:

Customer Data
Sales Rep
Orders
Parts

Here's a link to what these table's contain: http://www.cs.uky.edu/~paulp/CS316/tables.txt

The first two radio's were easy to implement since a customerNum is input. For customer data, I set variables to retrieve the values from the customers table. The SalesRep option was also easy. I took the customerNum input, set a variable to be the data in customers and another to be the data in salesreps. I made another variable to be the salesrep id by taking it from customers where customerNum was the input. Then used that sales rep id to display the associated data for the sales rep for that customer.

The 3rd and 4th radio options (orders and parts) has got me stumped! If you look at the orders table, some customerNum's have multiple orders.

My question is essentially "How do I go about displaying all of the orders (and the associated data for each) for a given customer?"

I can't just do what I did for the first 2 radios since each customerNum could have 1 or multiple orders. I'm new to Ruby on Rails and not sure how to go about implementing this.

Ill post some code in a sec. Any suggestions to edits for this post or to help implement this are greatly appreciated. Thanks!

CODE:

index.html.erb

<%
#  index.html.erb
#  First (input) screen for example
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 <html xmlns="http://www.w3.org/1999/xhtml">
<head profile="http://gmpg.org/xfn/11">
<link rel="stylesheet" type="text/css" href="style.css">
<title>The Data Access Program</title>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>
<h1>Spartan Hardware Data Access</h1>
<form action="cs316ruby/result" method="post" >
<h3>Data requested:
<input type="text" name="data" /> </h3>
<ol>
  <li><input type="radio" name="field" value="cu" id="field_cu" checked="checked" />
    Customer data</li>
  <li><input type="radio" name="field" value="sr" id="field_sr" />Sales rep</li>
  <li><input type="radio" name="field" value="or" id="field_o" />Orders</li>
  <li><input type="radio" name="field" value="p" id="field_p" />Parts</li>
</ol>
<input type="hidden" name=<%= request_forgery_protection_token.to_s %>
value=<%= form_authenticity_token %>  />
<input type="submit" value="submit" />
</form>
</body>
</html>

salesrepdisplay.html.erb

<%
# salesrepisplay.html.erb.rb
# View that displays customer's sales rep data for cs316ruby program
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
 <head profile="http://gmpg.org/xfn/11">
<link rel="stylesheet" type="text/css" href="style.css">
<title>The Data Access Program</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>
  <h1>Spartan Hardware Stores Data Access</h1>

  <%  # get the customer data from the customer table
  @custdata = Customer.find(@data)
  # copy the Sales rep field into a variable to retrieve sales rep data
  # if sales rep id = 00, the entered customer has no sales rep
  @sr = @custdata.sales_rep
  @srdata = Salesrep.find(@sr)
  @ln = @srdata.last_name
  @fn = @srdata.first_name
  @rt = @srdata.rate
  @cm = @srdata.commission;
  @cm = sprintf("%7.2f",@cm)
  %>
  <h1>Sales Rep data for customer: <%=@data %></h1>
  <table border=1>
  <tr>
    <td>ID</td>
    <td>Last name</td>
    <td>First name</td>
    <td>Commission</td>
    <td>Rate</td>
  </tr>
  <tr>
    <td><%=@sr%></td>
    <td><%=@ln%></td>
    <td><%=@fn%></td>
    <td><%=@cm%></td>
    <td><%=@rt%></td>
  </tr>
    </table>
  </body>
</html>

custdisplay.html.erb

<%
# custdisplay.html.erb.rb
# View that displays customer data for cs316ruby program
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head profile="http://gmpg.org/xfn/11">
<title>The Data Access Program</title>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>
 <h1>Spartan Hardware Stores Data Access</h1>
 <%  # get the customer data from the customer table
 @custdata = Customer.find(@data)
 # copy each field into a variable for display
 @id = @custdata.id
 @ln = @custdata.last_name
 @fn = @custdata.first_name
 @bl = @custdata.balance;
 @bl = sprintf("%7.2f",@bl) # format as currency
 @cl = @custdata.credit_limit
 @cl = sprintf("%7.2f",@cl) # format as currency
 @sr = @custdata.sales_rep
 %>
 <h1>customer data for customer: <%=@data %></h1>
 <table border=1>
  <tr>
    <td>ID</td>
    <td>Last name</td>
    <td>First name</td>
    <td>Balance</td>
    <td>Credit limit</td>
    <td>Sales rep</td>
    </tr>
  <tr>
    <td><%=@id%></td>
    <td><%=@ln%></td>
    <td><%=@fn%></td>
    <td><%=@bl%></td>
    <td><%=@cl%></td>
    <td><%=@sr%></td>
    </tr>
    </table>
  </body>
</html>

orderdisplay.html.erb

This is basically a copy and paste of the sales rep and customer order pages to start from

<%
# orderdisplay.html.erb
# View that displays customer's order data for cs316ruby program
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head profile="http://gmpg.org/xfn/11">
<title>The Data Access Program</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>
<h1>Spartan Hardware Stores Data Access</h1>

<%  # get the customer data from the customer table
@custdata = Customer.find(@data)
%>
<h1>Order data for customer: <%=@data %></h1>
<table border=1>
  <tr>
    <td>Order Number</td>
    <td>Order Date</td>
    <td>Cost of Order</td>
    </tr>
  <tr>
    <td><%=@sr%></td>
    <td><%=@ln%></td>
    <td><%=@fn%></td>
    </tr>
    </table>
  </body>
</html>

You need to ask a narrower question. (Also ~'s is possessive; plain ~s is for plurals.)

Reviewing your tables.txt, orderlines.id appears to mean orderline.order_id - that is the foreign key of orders, in orderlines.

So if you expressed these as ActiveRecord objects, how would you write in class Order a has_many that points to OrderLine?

CODE:

index.html.erb

salesrepdisplay.html.erb

custdisplay.html.erb

orderdisplay.html.erb

Related Articles