So last night after dinner, I proceeded to rebuild my database. I dumped everything into Access, and created new tables from there. Why Access? Because I can *see* what is happening in Access, whereas in MySQL I have to take extra steps to see my results. The rebuilding wasn’t really as bad as I thought it would be, as I only needed relatively simple Access queries to manipulate my data.

From there, I uploaded the tables into MySQL, and proceeded to rebuild my php files to query the database.

With a minimum amount of bickering and quibbling, Michael and I were able to do that, although he kept trying to make one query much harder than it needed to be. Needless to say, by the time we finished up last night it was past my bedtime, which is why I didn’t have a post ready for this morning.

But the important thing is the new database is up and all public queries are working.

Now I just have to rebuild the administrative back end to allow me to add, edit, and delete books from the database. Hopefully now that the tables have better relationships, the rebuilding will be easier to do.

Hopefully.

So, many years ago, what is now my on-line book database started out life as a simple Excel workbook.

When I had to learn MS Access several years ago, I took that Excel file, and tried to convert it into an Access file. It worked… ok. But at least got me comfortable with the basics of Access.

A year or so later I started working with the woman who was helping me re-write our Access classes, and as an exercise she helped me again convert my Excel file into an Access database, and this time I had a better idea of what I was going, and built queries and life was seemingly good.

Well, when I wanted to learn MySQL, I took that Access database and imported it into MySQL, and that’s how my book database began. I’ve used that database to learn to write queries of various types, and all kinds of PHP/MySQL manipulations. And I’ve gone on to write other databases since then.

Well, I’ve been tying to learn more of MySQL, and been playing with my database and been running into a LOT of trouble. It’s been frustrating, but I’ve mostly been able to get things to work. And as a matter of course the more I learn the more I discover that I should have done things differently, and gone back and changed this in light of this.

Well, yesterday I had a problem I couldn’t figure out, so I had Michael look at it. He was playing with the queries, and then asked to look at the database structure. Since he was driving, I was looking at everything from a different angle, and I suddenly realized the whole damned database had been set up wrong.

That’s right. Instead of having foreign keys liking the databases together, I repeated every bit of data in multiple tables.

(deep breath)

So I’ve dumped the entire thing back into Access, and I’m going to see if I can actually repair the damage.

Wish me luck.

I’ve spent the last month working on my PHP/MySQL skills.

Now I fully admit I’m not a master of the subject, but I’ve managed to beat my book database into submission.

There are still things I want to learn to do, such as making changes to multiple selected records, but I’m pretty sure I’ll eventually figure it out.

Meanwhile, I invite you to peruse my library database, and to make suggestions as to improvements. (Yes, I need to eventually make a single search form that includes genre with the other fields or allows you to select multiple genres, but that’s coming. Eventually.)