PC pointed out that the licence text file was showing the wrong version number, so I updated that and rebuilt the installer.
Robin S and Joyce P came by to talk about two levels of service they'll offer for departments: cms-based and template-based. The cms-based is part of a much more comprehensive package including needs asssessment, focus testing etc. etc. intended for the largest, high profile offices. There are a couple of dozen offices in that queue. The template-based service is less ambitious and will be used by most departments. At some point in the future a cms-based solution for typical departments may be offered.
Went through how we'd likely hack the templates to make the navigational items and other shared content php includes. The two sites we'll likely start with are the HCMC site and the Faculty site, but I'll have to see what other projects are in the queue.
Sat in on a meeting between Stew and two folks from the WebCoor office, about the new templates for departmental site design. We'll probably make use of them first of all on our own site.
Worked through a range of bugs, and also a couple of new feature requests, and I think I have the whole thing basically working, producing three tables with (as far as I can tell) the correct results (based on comparison of single-ethnicity grouping results against results from the original code for the same ethnicity. The last remaining bugs were due to the necessity for a lot of our calculations to include zero-total records, because of cross-ethnicity, cross-city, and cross-year comparisons, where the original code could discard the zero counts earlier in the process.
Having done this once, I could now rewrite it to use half the code. One thing I now realize, that took a while to emerge from the process, is that the root of the data structure is actually an array of years; each year contains ethnicity groupings, and city tract sets. Right now, my data structure has two separate arrays of ethnicity groupings and city tract sets, which have a lot of their internal organization done by year, but a year-indexed array would be a better organizational principle. If the site gets a lot of attention and use, it will be worth rewriting on that basis for simplicity. But right now, it seems to work (although I suspect there may still be the odd edge case which can break it).
Follow-up to this.
This is basically a wrap-up post. The nature of the application was quite simple, but I had to learn everything from the ground up, which was extremely time-consuming.
After converting the data to sql (including developing a simple schema) I created a MySQL view which is the sole point of contact for the php code querying it.
As far as features go, the php app presents a single search box from which every useful field in the DB is queried.
All words are assumed to be connected by an OR operator, but +- and "" are supported.
Hits are presented in a thumbnail gallery which is weighted by relevancy - the more hits inside of a given record, the more relevant it is and the closer to the front page it goes. The gallery is paged, with each thumbnail providing a quick view of the complete record (with some info truncated to fit) on hover. On click, a larger version is displayed along with a link that takes the user to a full record view.
The current version lives here: I have a feature-complete version of the GRS media database ready for use here:
http://lettuce.tapor.uvic.ca/~gregster/grs/final/ . I do not know where it will ultimately live.
Impossible to estimate the amount of time I spent on this - it was 50/50 learning and coding. "Minutes Worked" is just a guess.
Repurposed bits of David's code so that about five fields show up by default for each record, and all the rest are hidden by default and show up only if user clicks a button.
Also tidied up behaviour if no results are returned, made a few other modifications to code to normalize behaviour and presentation.
Spent the whole day on this, and I've got as far as making the new D-Table output. The other data is also being calculated, but not output yet. Here are some of the annoyances and hurdles:
- Temporary tables in MySQL cannot be addressed twice in one operation, so they're useless for our purposes. Therefore I have to create full tables to do our queries on, and delete them explicitly at the end. This has the useful side-effect, though, that they're still around to be examined if the process dies in the middle.
- It turns out that my original plan for calculating values on a much-reduced table consisting only of those ethnicities we're grouping will not really work; the other ethnicities need to be there, so that total values can be calculated involving tracts when there may not be any residents from one of our groupings in that tract. Therefore the temporary table construction process is much more complicated than I thought, involving a union of the summed/grouped tract rows for our grouped ethnicities with all of the rows which are NOT from those ethnicities which are grouped. The resulting tables can be very large, of course, amounting to almost full copies of the original tables, and querying them takes a while, so it then makes sense (and saves a lot of time) to create indexes on them, just as on the regular tables. However, once this is done, the processing speed is considerably enhanced.
- My new calculations, which are based entirely on reconstructed tables which mimic the originals, along with calculation code based on JD's, are showing up results which are basically the same as those in my original complicated D-table calculation, which JS-R thought might be off. We need to look at this. But the fact that I've done the same calculations two completely different ways and gotten the same result suggests that the original might have been right after all.
- The ordering of the D-table cells may not be right yet (either that or the ordering of my original table wasn't right). In the second column group of a two-city result table, the order of row data for the ethnicities is switched around compared to the original table. That shouldn't be too hard to figure out, though.
- Instead of using negative indices for the grouped ethnicities, I've elected instead to start them from 10,000, which is way above the possible limits in the existing data. It makes for easier access and sorting.
Stayed a bit late to get this working, but I'd been out of the office for an hour and a half in the morning, so we're square. I'm really looking forward to getting this finished off in the next few days; the code needs a lot of old and debugging functions removing from it, once I don't need them any more, and then it will be easier to read.
Added those three fields to the interface. Took me a while to get gender working. When a gender was specified, the gender field in each record appeared. When the gender was not specified, no value appeared in the gender field for each record. I had been asking to display the p_sex field from the exams table (which should have been an integer). When I asked instead to display the gender_text field from the genders table, everything worked perfectly, even though the gender_text value is obtained by a lookup from the p_sex value. I don't get it.
The next two fields went in no problem.