I have a new website that I’m setting up – I’m using Wordpress as a CMS. It’s a stock install of the current version of Wordpress (3.4.2) plus a couple of plug-ins I’ve installed that are not particularly related to this project.
I’ve created a MySQL table in the Wordpress database for a particular state’s divorce records – it’s in a table in the Wordpress database for the site.
What I want is what I would call a ‘search screen’ or 'search engine' for this data (I’m a Linux application programmer by day and don’t do much if any PHP).
Besides having pages describing where the data comes from and a page about the project the search function is going to be the heart of this website.
Here is the schema for the table from a recent mysqldump:
CREATE TABLE IF NOT EXISTS `divorces` (
`file_number` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`husbands_name` text COLLATE utf8_unicode_ci NOT NULL,
`husbands_age` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
`wifes_name` text COLLATE utf8_unicode_ci NOT NULL,
`wifes_age` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
`number_of_minor_children` int(11) NOT NULL,
`marriage_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`divorce_date` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`county_code` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
`county_name` varchar(80) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
I will provide 50 rows or so of data to the winning bidder (along with the url of my website), but the data in general looks like:
INSERT INTO `divorces` (`file_number`, `husbands_name`, `husbands_age`, `wifes_name`, `wifes_age`, `number_of_minor_children`, `marriage_date`, `divorce_date`, `county_code`, `county_name`) VALUES
('026484', 'ACKERS LARRY DALE ', '30', 'SONDRA IRENE ', '30', 1, '5/8/1964', '11/12/1968', '188', 'POTTER ');
The table at present is about 3 million rows and I’m not done importing the data yet. I expect the table to be double that size before I’m done with my imports.
At present I’m on shared hosting but I’ll probably move this to some kind of VPS or dedicated hosting once I see what the performance is.
You may use any plug-in or custom code you like, but the solution must be safe from SQL injection. Ideally I’d like something that uses a shortcode that I could place on a Wordpress page but that’s not a requirement.
Other observations/possible problems:
I’ve not stated what the form inputs to the search should be – I will leave that to you.
You will notice that I have no indexes on this table yet and not only that but I have nothing that makes a particular row unique. I’ll take suggestions for indexes to speed the search of course.
Also – if I were to search for Sondra Ackers (from the sample data above) I probably wouldn’t find her. Parsing the husbands_name field is likely to be non-trivial since we don’t know where the last name ends and the first name begins.
I am open to datatype changes on the table but I can’t really change the structure because I’m importing tab and fixed-width data into this database. As you may or may not expect I would love to have a field that would uniquely identify a row that I haven’t been able to figure out how to do that and still be able to import my delimited data without writing some code to do the inserts instead of just using a simple command line statement.
Of course I am open to suggestions of course but the solution must fit within my Wordpress website.
If you have any questions please ask before bidding on my project. I think I’ve made this about as clear as I can but you never know.
The solution in PHP
Instructions on how to install (I have ssh and sftp access)