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) 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