By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,718 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Complex database - having problems getting data updated

P: 5
Well, here's my first post in this forum (other than saying 'HI' over in the hi forum ;-)

As I said over there:

... for a little side project at home, I'm writing a ham radio web site in uby/Rails. I started it in Perl and gave up on Perl as I went from the 'display the database information on the web page' to the 're-display the information from the database and allow the user to update the database using the web page' stage and realized I had more work to do than I had just finished doing.

Now I've gotten it from 'show form with lots of things to fill out' through 'save the data from the forms in the database' and 'retrieve said info from db and display on web page' all the way to 'try to save the changed data into the db' - and I'm running in to problems now.

Now, I'll continue over here, and apologize up front for its length, but I figured that a single post is probably better than a bunch of little ones which don't really give the full flavor of how far off into the weeds I've gotten :-).

Let's say that I have the following data layout:

Expand|Select|Wrap|Line Numbers
  1. hams:
  2.    id int
  3.    name varchar(100)
  4.    gps_coords varchar(50)
  5.    geographic_location_id (references geographic_locations below)
  6.    general_comments varchar(100)
  7.  
  8. geographic_locations:
  9.    id int
  10.    full_title varchar(255)
  11.    icon    varchar(128)
  12.    num_hams  int
  13.  
  14. ham_secrets:
  15.    id int
  16.    date_last_updated date
  17.    password varchar(100)
  18.    ham_id (references hams above)
  19.  
  20. callsign_lists
  21.    id int
  22.    ham_id (references hams above)
  23.    callsign varchar(20)
  24.    sequence int
  25.  
  26. phone_numbers
  27.    int id
  28.    phone_number varchar(30)
  29.    ham_id (references hams above)
  30.    publish int (0 means do not publish, 1 means do)
  31.  
  32. email_addresses
  33.    int id
  34.    ham_id (references hams above)
  35.    sequence int
  36.    publish int  # zero if want private, 1 to publish on the web page
  37.    ...stuff you'd expect here...
  38.  
(In reality, there is a little more stuff up there in some of those tables, but I figure I'd rather make this shorter than longer :-)

Other tables which reference hams are email_addresses, pending_verifications,
ham_expertise_entries (which also references expertise_items), and a few other
things for future expansion.

So, for example, I might register and it would look something like this:

the database entries for me would be:
Expand|Select|Wrap|Line Numbers
  1. hams:
  2.    id     = 1
  3.    name rusty c
  4.    gps_coords ""
  5.    geographic_location_id 3
  6.    general_comments ""
  7.  
  8. ham_secrets:
  9.    id       1
  10.    date_last_updated (today)
  11.    password "a password of some kind"
  12.    ham_id 1
  13.  
  14. callsign_lists
  15.    id       1
  16.    ham_id 1
  17.    callsign "N7IKQ"
  18.    sequence 1
  19.  
If I had another callsign, say "W1XYZ", it would be sequence 2, id 2, all other fields the same

phone_numbers - I didn't choose to enter any phone numbers, so this table has no entries referencing me.

Expand|Select|Wrap|Line Numbers
  1. email_addresses (two entries):
  2.    id 1
  3.    ham_id 1
  4.    sequence  1
  5.    publish 0
  6.    ...stuff you'd expect here...
  7.  
  8.    id 2
  9.    ham_id 1
  10.    sequence 2
  11.    ...stuff you'd expect here with my email address and comments...
  12.  
Ok, so now you see that I've learned how to make a 'good' database ;-) (or whatever they call it when you don't duplicate info)

The process is - user goes to web site, and a navigate to a page that displays
all the above in one nice page. They fill in the fields they want to (and leave the others blank) and hit 'submit'. Their info gets saved, a new 'pending verification' entry gets created, and an email is sent to each of the email addresses with a unique number and a web page address.

The user then goes to that address, enters the unique number and their email address, and the fields get re-displayed for them to verify and approve.

They verify and hit 'approve' (or whatever I called the button), and their updates are made and they are now 'published' on the site.

So, what does the ruby/rails code look like?

Probably hosed - here's some background info and snippets:

I have 2 controllers - one I call 'elmers' and one I call 'registration'. You talk to the 'elmers' controller up until the registration verification step.

elmers_controller.rb:
Expand|Select|Wrap|Line Numbers
  1. class ElmersController < ApplicationController
  2.  
  3. def registration_page  # this displays the page that they fill out initially
  4.    # get the tables of items that can appear (kinds of email (home, etc),
  5.    # different special interests (over 100 at this time), etc)
  6.    @emailtypes = EmailType.find(:all)
  7.    @subjects = ExpertiseItem.find(:all, :conditions => "id > 1")
  8.    @phonetypes = PhoneNumberType.find(:all)
  9.    @geolocs = GeographicLocation.find(:all)
  10. end
  11.  
  12. def process_registration # this is the ruby that gets run when they say 'submit'
  13. #  to the above-generated page
  14.    @ham = Ham.new(params[:ham])
  15.    @ham.active = 1
  16.    @ham.ok_to_publish = 1
  17.    @ham.general_comments = @params[:webpage][:general_comments]
  18.    @ham.private_comments =  @params[:webpage][:private_comments]
  19.    if (@ham.save)
  20.       hamid = @ham.id
  21.       hamsecrets = HamSecret.new(:ham_id => hamid, ... etc etc etc...)
  22.       hamsecrets.save!
  23.       if (@params[:callsign][:callsign1] != "")
  24.          callsignlist = CallsignList.new(:ham_id =>hamid, :callsign => @params[:callsign][:callsign1], :sequence = 1)
  25.          callsignlist.save!
  26.       end
  27.       ... repeat above code for 3 other possible callsigns....
  28.       emailtypes = EmailType.find(:all)
  29.       priority = 2
  30.       # now, do each of the possible email types, using the emailtypes table to control.
  31.       emailtypes.each do |emailtype|
  32.          emat = "email_addr_#(emailtype.email_name)"
  33.          # the above builds something like "email_addr_primary"
  34.          if (params[:"#(emat)"][:addr] != "")
  35.             emailSecretNo = rand(655360)
  36.             # build string with, e.g. "email_addr_primary_comments"
  37.             comments = "#(emat)"+"_comments"
  38.             # if they have selected the 'publish' checkbox, set pub to 1
  39.             pub = 0
  40.             if ("yes" == params[:#(emat)"][:publish] )
  41.                  pub = 1
  42.             end
  43.             # create and save the new email address entry
  44.             email = EmailAddress.new(:ham_id => hamid, :emailaddr => params[:"#(emat)"][:addr], :type_of_addr => emailtype.email_name, :publish => pub, :priority => priority, :comments => params[:"(comments)"][:comments])
  45.             email.save!
  46.             # if this kind of email address is an INTERNET-style email address (we can have non-internet addresses (ham email and ICQ, for example)
  47.             if (1 == emailtype.send_email)
  48.                   ... create 'pending verification' db entry and send them email.....
  49.             end
  50.             priority += 1
  51.          end
  52. # phone numbers and postal addresses are handled in almost exactly the same way, which I won't show here
  53. #     now, get the expertise items they have selected, their comments, and save them in db
  54.       1.upto(200) do |exno|
  55.     if (params[:"subject_#{exno}"])
  56.       if (params[:"subject_#{exno}"][:expertise])
  57.             if ("yes" == params[:"subject_#{exno}"][:expertise])
  58.               exp = HamExpertiseEntry.new(:comments => params[:"subject_#{exno}"][:comments], :expertise_id => exno, :ham_id => hamid)
  59.               exp.save!
  60.         end
  61.       end
  62.     end
  63.       end
  64.    end
  65. # whew!  That SHOULD be the end of process_registration
  66. end # end of elmers controller
  67.  
  68.  
Yikes.

So, my registration_page.rhtml says (at least in part):

Expand|Select|Wrap|Line Numbers
  1.  
  2. <%= start_form_tag(:action => "process_registration") %>
  3.  
  4. <H2>Required:</H2>
  5.  
  6. <P>
  7.  
  8. <STRONG>Name:</STRONG> <%=text_field("ham", "name", "size" => 60) %> (always published)
  9. <BR>
  10. <BR>
  11. <center>Email address(es) - must enter 'primary email' as a minimum.  The rest are optional.</center>
  12. <table BORDER=1>
  13. <tr>
  14. <% for email in @emailtypes %>
  15. <% emailname = email.email_name %>
  16.   <TD> <STRONG> <%= email.type_of_email %>:</STRONG><br>
  17. <%= text_field("email_addr_#{emailname}", "addr", "size" => 50) %><br>
  18.   <TD><DL><DD>
  19.     <%= check_box("email_addr_#{emailname}", "publish", {}, "yes", "no") %>    <STRONG>Publish</STRONG>
  20.     <td>    <dd>Comments: <%= text_field("email_addr_#{emailname}_comments", "comments", "size" => 80) %>
  21. </td></tr>
  22. <% end %>
  23. </TABLE>
  24. Remember - we <b>strongly</b> advise against publishing any of the above (well, ok, other than home page ;-).<p>
  25. <BR><STRONG>Personal Callsign(s) (Government-issued Amateur or MARS only, please):</STRONG>  (always published)<P>
  26. <%= text_field("callsign", "callsign1", "size" => 20 ) %>
  27. <%= text_field("callsign", "callsign2", "size" => 20 ) %>
  28. <%= text_field("callsign", "callsign3", "size" => 20 ) %>
  29. <%= text_field("callsign", "callsign4", "size" => 20 ) %>
  30. <P>
  31. <STRONG>Please enter a password:</STRONG>
  32. <%= text_field("ham_secrets", "password", "size" => 100) %>
  33. <br>
  34. (Note that this password should NOT be the same as anything you use anywhere else,....!)
  35.  
  36. <P>
  37. <H3>Categories/Area(s) of Expertise</H3>
  38.  
  39. (Please select all that apply; if any are not listed, select "OTHER"
  40. and describe them in the text message at the bottom.  ....
  41. <p><b>This information is ALWAYS published!</b>  (I mean, good grief, that's the whole point of this exercise, eh?  ;-)
  42.  
  43. <P>
  44. <% @numExpertise = 0 %>
  45. <% for subject in @subjects %>
  46. <% subjid = "subject_#{subject.id}" %>
  47. <%= check_box(subjid, "expertise", {}, "yes", "no") %> <b><%= subject.description %></b>
  48. <br> Additional comments: <%= text_field(subjid, "comments", "size" => 80) %>
  49. <br> <% @numExpertise = @numExpertise + 1 %>
  50. <% end %>
  51. <hr>
  52. </DL>
  53.  
  54. <HR>
  55.  
  56. <H2>Optional (Fill out as much or as little as you want):</H2>
  57. <P>
  58. <H3>Telephone Numbers (Be sure to include area and country codes):</H3>
  59.  
  60. <table BORDER=1>
  61. <TR>
  62. <% for phone in @phonetypes %>
  63.   <%  phtype = phone.type_of_phone %>
  64.   <TD> <STRONG> <%= phtype %>:</STRONG>
  65.     <br><%= text_field("phone_no_#{phtype}", "phone_number", "size" => 50) %><br>
  66.   <TD><DL><DD>
  67.     <%= check_box("phone_no_#{phtype}", "publish", {}, "1", "0") %>    <STRONG>Publish</STRONG>
  68.     <td>    <dd>Comments: <%= text_field("phone_no_#{phtype}", "comments", "size" => 80) %>
  69. </td></tr>
  70. <% end %>
  71. </TABLE>
  72.  
  73. <P>
  74. <H3>Location for the purposes of providing a 'geographic location' search capability - .....)</H3>
  75. <%= collection_select("ham", "geographic_location_id", @geolocs, "id", "full_title") %>
  76.  
  77. <P>
  78. <H3>Location (Either full mailing address or city, state/province, country; Remember that your name has already been given above and should not be retyped here):</H3>
  79. <P>
  80. <DL><DD> <%= radio_button("postal", "publish", "none") %>Keep everything Private</dd>
  81.   <DD>     <%= radio_button("postal", "publish", "cs"  ) %>Publish only City/State/Zip/Country</dd>
  82.   <DD>     <%= radio_button("postal", "publish", "all") %>Publish Entire Address information (including Street) (not wise)</dd>
  83.     </DL>
  84.  
  85. <STRONG>Street Line 1:</STRONG> <%= text_field("postal", "street1", "size" => 80) %><br>
  86. <STRONG>Street Line 2:</STRONG> <%= text_field("postal", "street2", "size" => 80) %><br>
  87. <STRONG>City:</STRONG> <%= text_field("postal", "city", "size" => 50) %><br>
  88. <STRONG>State:</STRONG> <%= text_field("postal", "state", "size" => 50) %><br>
  89. <STRONG>Postal Code:</STRONG> <%= text_field("postal", "postalcode", "size" => 10) %><br>
  90. <STRONG>Country:</STRONG> <%= text_field("postal", "country", "size" => 50) %><br>
  91. <STRONG>Comments:</STRONG> <%= text_field("postal", "comments", "size" => 50) %><br>
  92.  
  93. <br>
  94. (Note - ...blah blah blah...)
  95. <p><STRONG>If you wish it to be public, please enter your GPS coordinates</strong>
  96. <%= text_field("ham","gps_coords", "size" => 50) %>
  97. <p><STRONG>If you wish it to be public, please enter your Maidenhead Grid Locator</strong>
  98. <%= text_field("ham","grid_square", "size" => 20) %>
  99.  
  100. <P><STRONG>Please provide any additional information, ...
  101.  
  102. <P><%= text_area("webpage", "html", "cols" =>70, "rows" => 20 ) %>
  103.  
  104. <P><H3>Here is your opportunity to put private information into the database.  The following information will NOT be published on the web pages created by this site:</H3>
  105. <p><%= text_area("webpage", "privatecomments", "cols" =>70, "rows" => 10 ) %>
  106. <p>
  107. <P>(Please be patient, and press Send button only once.  You will receive acknowledgement when the form is successfully submitted, which may take up to a minute or two.)
  108.  
  109. <P><%= submit_tag("Submit") %>
  110.  
  111. <%= end_form_tag %>
  112.  
  113. <P>
  114.  
  115. <HR>
  116.  

Yow! I'm not sure I want to put up all the rest of the thing - I'll try to make it smaller than the above!

So, when they come back, the end up running 'verify_data_and_confirm', which basically just grabs all the stuff from the database. The rhtml associated with it looks a lot like the above (I think I cut-and-pasted it and added whatever was needed), which I will skip for brevity purposes!

When they click 'confirm' after updating whatever they want to change, we run the following (again, I've removed a bit):

Expand|Select|Wrap|Line Numbers
  1. def confirm_registration_and_update_info
  2.    #note - this is basically process_registration from elmers_controller
  3.     @haminfo = get_haminfo
  4.     @hamsecrets = @haminfo.get_secrets
  5.     hamid = @haminfo.get_hamid
  6.     emailaddr = @haminfo.get_email
  7.     # update ham record
  8.     @ham = Ham.find(hamid)
  9.     @ham.active = 1
  10.     @ham.ok_to_publish = 1
  11.     @ham.general_comments = params[:webpage][:html]
  12.     @ham.private_comments = params[:webpage][:privatecomments]
  13.     if (@ham.save)
  14.       # update callsigns
  15.       callsignlist = CallsignList.find_by_hamid(hamid)
  16.       0.upto(3) do |i|
  17.         csname = "callsign#{i+1}"
  18.         if (@params[:callsign][csname] != "")
  19.           callsign = @params[:callsign][:callsign1]
  20.           if (callsignlist[i])
  21.             callsignlist[i].callsign = @params[:callsign][:callsign1]
  22.           else
  23.             callsignlist[i] = CallsignList.new(:ham_id => hamid, :callsign => callsign, :sequence=>i+1)
  24.           end
  25.           # note that this date is actually wrong - we should look this up in the
  26.           # callsign database, or ask them...
  27.           callsignlist[i].save!
  28.         else
  29.           # here if empty in form, make sure nothing in database!
  30.           if (callsignlist[i])
  31.             # oops, there is!  Nuke it!
  32.             callsignlist[i].delete!
  33.           end
  34.         end
  35.       end
  36.       # process email - but only the one we sent email to!
  37.       email = EmailAddress.find_by_hamid_and_addr(hamid, emailaddr)
  38.       email.date_of_next_verify = 3.months.from_now
  39.       email.date_last_verified = Time.now
  40.       email.save!
  41.       # ok, now its possible for them to CHANGE the email info, so we have to handle
  42.       # that here also. (Which means they could end up deleting the one they just
  43.       # verified, which sounds dumb but is possible)
  44.       emailtypes = EmailType.find(:all)
  45.       emailtypes.each do |emailtype|
  46.         emat = "email_addr_#{emailtype.email_name}"
  47.         if (params[:"#{emat}"][:addr] != "")
  48.             # something in the field - find it in the db and update it.
  49.             email = EmailAddress.find_by_hamid_and_type(hamid, emailtype.email_name)
  50.             if (email)
  51.               if (email.date_of_next_verify < Date.today)
  52.                 email.date_of_next_verify = Time.now
  53.               end
  54.               email.publish = params[:"#{emat}"][:publish]
  55.             email.comments = params[:"#{emat}"][:comments]
  56.           else
  57.             puts "Email address writing?"
  58.               puts params[:"#{emat}"][:comments]
  59.             email = EmailAddress.new(:ham_id => hamid, :emailaddr => params[:"#{emat}"][:addr], :type_of_addr => emailtype.email_name, :publish => params[:"#{emat}"][:publish], :priority => priority, :date_of_next_verify => Time.now, :active => 1, :comments => params[:"#{emat}"][:comments])
  60.         end
  61.         email.save!
  62.       end
  63.       # phone_numbers, postal_addresses, pending_verifications
  64.       ... lots of code just like the above.....
  65.     else
  66.       puts "blew it"
  67.       redirect_to(:action => "error" )
  68.     end
  69.  
  70.   end
  71.  
Now I have discovered the 'build' method in my internet searching, and I bet there's a MUCH better way to do all the above, so that's why I'm here.

The problems I'm having are with getting the info back into the database - the above doesn't seem to do the job. But I'll bet my approach is wrong, so:

Any suggestions for improvement, any info on 'build' and how it might help, whatever (shoot, I might even accept flames, at this point!).

In case it matters, I'm using RoR on Linux (Debian).
Sep 6 '07 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 303
I had a similar problem updating my database. I needed to set some variables in code rather than from a form field, something like
Expand|Select|Wrap|Line Numbers
  1. @ham.active = 1
but using that format, it wouldn't update the record. What I had to use instead was
Expand|Select|Wrap|Line Numbers
  1. params[:ham][:active] = 1
  2. @ham.update_attributes(params[:ham])
Sep 7 '07 #2

P: 5
Ooh! And, if I read that right, that also answers how to get the info out of the web page and into the variable! (In other words, that's probably why I'm having trouble getting everything back into the database) Cool, thanks - I'm off playing now! ;-)

(I'll report back if that works, and how it looks, later. If someone else comes along and thinks I'm still in the weeds, feel free to (gently) hit me with a clue stick :-)

rc
Sep 7 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.