Updating Database

I had a few hours between meetings and classes: just enough time to add a new table to the database. It stores information about institutions and will be part of the new author and new resource screens.

I also “fixed” the delete publisher, delete periodical, etc. screens to that they now delete the item as well as the resource associated with this. This is how it SHOULD work so you don’t end up with a resource assigned to a non-existent publisher or periodical. But it is also very scary and I’m wondering if it should just delete the publisher or periodical and then leave it up to the user to edit the resource and pick a different publisher.

I’ve been using the database pretty regularly and have a nice stash of resources, quotes and notes. I’m keeping a list of stuff to fix that ranges from making longer text fields for data entry to how to change the page title that shows up as Login to Noter on every page, and finishing up the edit resource page. OR I may just take the time to create separate book and journal data entry pages.

A Quick Update

Well, I’ve solved the biggest problem I thought I had: the need for a None selection in the editbook pull-down menus. I added an echo statement that echoed “None Selected” when the periodical_id was 0. I had to put this before the while statement that created the pull-down menu or I got “None Selected before each periodical title.

I’ll admit to being pretty pleased with myself…it wasn’t as hard as I thought.

Programming Update

I’ve been actively using the database for a paper I have to write as well as just general data entry for resources I’ve had marked for ages just waiting for the creation of this database. It seems the best way to troubleshoot and think about what else I would like it to be able to do. Here’s a brief list:

  • Add an institution field for the authors: and speaking of institution, it really should be a separate table where I can pick off a list so the data entry is consistent for the field. There is an institution field in the resource table as well and I could use the same table.
  • Continue to add fields to the resource update field (editbook.php). I added pull-down menus for periodicals and types. I based them on the checkboxes for keywords and authors but in this case, it’s a one-to-one relationship. It took a little while to get the lists to appear and then to get them to show the correct periodical and type. I’ve solved all that but the continuing problem is that when you’re in the edit screen, the first periodical and publisher appear and when you press submit, their id numbers get written to the resource table. I could solve it through the database but creating a row called “None Selected” and choose that each time I enter a new resource but I wonder if there’s a way to do it with the code? I tried echoing “None Selected” when there was no id match but then the pull-down menu just displayed that instead of the periodicals and types. I’m wondering if I can write an if/else statement that says that if no match exists then the list should display “None Selected” but also providing all the choices.
  • quotequery.php passes the id of the resource but is directed to the edit quotes page so the wrong quote appears. In the quotequery.php page, the edit/delete/add choices functions refer to the resource.
  • Create a Views page that includes the various queries I’ve developed. And I need to continue to hone my formatting skills by incoporating html code and php echo statements.

There’s plenty more but that’s a good start…I’m going to watch Part II of The Fellowship of the Ring tonight and do some coding and then data entry.

Things I’ve Learned:

Choosing the right data storage type is important. I choose smallint for my quotespage field only to discover that it goes to 127. So, when I started entering page numbers that were larger than that, they defaulted to 127.

There are lots of other formatting issues.

Login Script Added

As much as I like to be a citizen of the world, I’m pretty sure someone deleted stuff from my database. So, I found an easy script on the web to require a login and password. I think I still need to do some encryption but for now hopefully this will stall any would-be hackers. I want to start using the database for a lit review I’m writing.

Two more queries

I am teaching a Dreamweaver workshop this evening so I have to head out soon but wanted to post links to two more database queries that I’ve developed.

Author/Resource
: Lists the authors and resources
Resource/Author: Lists the resources and authors

I struggled with the author query (even though I had already successfully created the resource query) because I was matching the ids of the author and resource so I wasn’t getting the right author/resource combination.

I’m also doing some behind-the-scenes troubleshooting to make sure that edits and deletes are not just being made in the main tables but also in the lookup tables. Last night, I discovered that, while the keyword edit/delete was working, the author was not so the links were still in the lookup table. I believe everything is working now but it’s just a reminder that even though things appear to working on the surface, the integrity of the database has to be maintained.

All She Wants to Do is Code!

I am coding every chance I get and moving right along on the project (but oh is there PLENTY of work still to do). The basic data entry pages are done. I am going to just create a generic resource entry page that includes all the fields in the resource table. Later I can make specific pages for each type of resource.

Right now I am working on nested queries and starting to try to add in formatting. Here are a few queries of the database:

Quote/Resource Query
: This query shows the quotes first and then the resources. It passes the quote id so you can use the options to edit or delete quotes as well as add notes related to quotes (rather than resources).

Resource/Quote Query: This query shows the resource first and then the quotes. It passes the resource id so you can use the options to edit or delete the Resource as well as add quotes or notes related to the resource (rather than the individual quote).

Show All Notes: You can use this search interface to see notes related to specific resources or keywords. OR if you don’t choose anything, you can see all the notes in the database including those related to resources AND to quotes. You can also edit or delete notes from this screen.

I am currently working on the queries to show all the notes related to a specific quote. It is a two-step process. You start by searching for quotes related to resources and then click the View Notes button. I still need to add the options for editing notes to this page.

And…someone added a resource to my database! I guess I better start thinking about a password protected version to use for myself. Actually, with the Apache server on my laptop, I could use run MY version locally.

More Database News

I have gotten past several hurdles to have at least a skeleton of a functioning database. You can add quotes related to resource and also add notes related to either quotes or resources. I’m also working on the page design and thinking about other things I want to add like a “show quotes” link in the Resource list that shows up when you do a search. And I also need a “show notes” button in the Quotes list that shows up when you search.

There are several pages still to be done: a quotes edit and delete and a notes edit and delete.

Then I need to start working on querying: what results do I want? And what do I want to be able to do with them when I get them (edit, delete, etc.)

I also need to work on the pages for different types of resources. I’ve got all the fields created in my database. I need to add them into the resource data entry pages. So…lots to do but I think I know it’s doable.

Monday Morning Update

Hard to believe it’s been two weeks since I posted anything at all but it’s not for lack of work. I decided I needed some academic work so have been reading about php. I have tinkered with the database, adding fields based on RefWorks. I have also added a page for adding quotes and, hopefully this afternoon, will put one together for adding notes. But I took some time to work with queries and now have two nested queries that use a function to show all the resources related to an author and all the authors related to a resource. Here’s the resource with author query that returns all the authors associated with a book. And here’s the author with resource query that returns authors with all the resource associated with them. I used a query from the Williams and Lane book as a basis for these.