473,287 Members | 1,395 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Complex database - having problems getting data updated

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
2 3531
improvcornartist
303 Expert 100+
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
rustyc
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

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

Similar topics

116
by: Mike MacSween | last post by:
S**t for brains strikes again! Why did I do that? When I met the clients and at some point they vaguely asked whether eventually would it be possible to have some people who could read the data...
0
by: Rob Cheshire | last post by:
Hi to all, I need some help deciding on the best database system for our specific application. OPTIONAL INFO: We currently have 2 separate projects that need to be updated from dos-based dBase. ...
8
by: Steve Jorgensen | last post by:
Mailing List management is a good example of a case where my conundrum arises. Say there is a m-m relationship between parties and groups - anyone can be a member of any combintation of groups. ...
5
by: Karl | last post by:
Hi C# experts, I am to C# and .NET. I am writing a database application using C# and Dataset. In Sql server, I have a Acount table which contains over 100,000 records. This table will be...
2
by: Ville Mattila | last post by:
Hi there, I will post my question to this group too bacause the .data group seems to be rather quiet. I've been playing with VB.NET and ADO for a week now and find the different data handling...
4
by: JM | last post by:
Hi, I am an old programmer who is only just getting back into it after about 10 years, and for the life of me I can not work out what I am doing wrong. Firstly, I've recently downloaded and...
18
by: surfrat_ | last post by:
Hi, I am having the following problems in getting Microsoft Visual Studio 2005 Professional to link to an Access .mdb database. Please help me to sort this out. Problem 1: The Microsoft...
3
by: Malenfant | last post by:
I'm currently working on my first relatively complex C# application and I've run into some problems as to the best way to develop the interface. The design spec calls for a treeview on the...
1
by: AllBeagle | last post by:
Hello Everyone, I'm trying to build a web-based application for a client of mine and I keep finding holes in my design, so I could use some guidance if anyone has any to offer. Let me try to...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.