Note: This was installed on Mac OS X 10.5 (Leopard)
The other night was discussing with a friend one of his processes at work, which was very manual, and trying to find ways to automate it. A few times I said, “a few SQL queries would probably help out”. Somehow (though I didn’t resist) I was assigned the task of creating a MySQL database and a website to assist with this project.
First step was to get MySQL installed on my Mac. I chose to do a manual install verses the MySQL dmg package. I had to track down the mysql-5.1.33.tar.gz file because the file that downloaded was only 4KB. In addition, I learned how to view ‘hidden’ files on a Mac – TextWrangler simplified it for me.
The directions went smoothly after finding the initial tar and hidden files… up until I hit the “A Note about Security” section. I couldn’t find the my.cnf file. In my search for this my.cnf file I came across a site that described how to open up Apache for PHP use and I figured it’d come in handy.
I decided to worry about the security step later (and a later step created the my.cnf for me). Next step was to get a gui interface so I could create a database, create a table and load it up with data. I downloaded and installed the MySQL GUI Tools. As a previous Windows only user, MySQL Administrator was unfamiliar at first, but I quickly found the table structure and made my rows within a table I called “products”.
I now had my table, so I switched over to the MySQL Query Browser. After some changes to get an auto-incrementing primary key, and to change the date field into a time-stamp field, I inserted data into my table using SQL scripts.
INSERT INTO products
(serialNum, errorCode, data) VALUES(‘123456’, ‘Some Error number 1′, ’09/01/2009’ )
INSERT INTO products (serialNum, errorCode) VALUES(‘123456’, ‘Some Error number 1’ )
Next step was to display these data points into a web browser. I enabled Apache on Leopard via System Preferences -> Sharing ->Web Sharing. Placed a simple .html into the user–>Sites folder, visited http://localhost/username/ and bang, an error. “Can’t find the server”. So, I moved the username.conf file to the proper location for Apache2 to try to resolve the issue. No luck (but I have a feeling it one less problem in the future). Finally checked the console’s error messages and the solution was to create a folder for the log files.
Success! The simple html page displayed as beautifully as “Hello World” can. An IT professional’s favorite words. I redirected to the DisplayData.php page, which I had already created using SQL queries to retrieve all the data and display it in a simple html table. Then with a little tweaking of the table structure to make it look just the way wanted, I was ready for the next step.
Obtain specific requirements of the page/task:
- Can you get a php page that takes in variables posted to it? – A Perl script pass variables to the php page
- and populate the db with the info?
- date = X – (is this a timestamp date or a date entered by the user? Perl script.)
- IP = X– (This will need to be a varchar(15)
- Data in coma sep. values = key, value, key, value (explode is a possibility) – a “key” is a column heading. Need to find out how to automatically/dynamically add columns to tables when the column doesn’t pre-exist.
- need to match keys (column headings) and overwrite if the date is the same
Had problems with reloading the process.php page. It would add another record to the database. Finally used header and GET to redirect to a different php page and pass the info for displaying on the final page.