How do I turn the content of a MySQL table into a series of html documents?
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$6 Answers
The first thing you need to do is get a web server going. Like I said, you can use any old junk computer that you have. If you don't have a spare, you can usually pick one up for cheap on ebay or even just buy another drive and place that in an existing machine for a temporary fix. Or, rent a webserver online for a month(be careful when choosing hosting company...some don't allow you to create local files).
After you have the machine, you need to install linux on it. More specifically, you'll be going after a LAMP setup (this stands for Linux, Apache, MySQL, PHP). Fortunately, there are TONS of resources for setting these up from scratch on the net. Here is what you'll need to do:
Here is how to install the full LAMP package in one shot. It explains things fairly clearly and has screen shots. It should take anywhere from 15-30 minutes depending on the computer you use and how quick you read! =)
http://www.debianadmin.com/ubuntu-lamp-server-installation-with-screenshots.html
NOTE: You won't have to do the part about making a static IP address...your just doing this temporarily...
Make sure you write down the IP address of your Ubuntu machine before you get off of it. To find the IP, type the following at your command prompt(it will be the number next to address):
ifconfig
This part is optional, but will make your life easier. Your going to install PHPMyAdmin. This is a program which simplifies the process of dealing your mysql on your web server. You can find the steps I've included here at http://www.howtoforge.com/ubuntu_debian_lamp_server . But the Linux install isn't as nice, so I just use this for the phpmyadmin part. Type the following at the command prompt:
apt-get install phpmyadmin
nano /etc/apache2/apache2.conf
NOTE: This will open NANO...a basic text editor in linux...and open the apache2.conf file...the configuration file for apache. This will have a LOT of stuff in it, and look really confusing, but we are only changing one line. Remove the # from the line that says:
Include /etc/phpmyadmin/apache.conf
Now type to restart apache:
/etc/init.d/apache2 restart
phpmyadmin should now be installed. You can check this on your windows machine. Go to http://UBUNTUIP/phpmyadmin where UBUNTUIP is the IP address of your Ubuntu box(you found that above with the ifconfig command).
Now you can create a new database for your file to go into:
http://www.smartwebby.com/PHP/database_table_create.asp
Now, just import your mysql file with phpmyadmin. This is a video walkthrough...it's probably the easiest part of this whole thing:
http://www.dewahost.com/tutorials/cpanelx/importsqlfile.html
Now, write a PHP script which will go through your table and make each row into a HTML file. Place this file in /var/www on your webserver. To get there, you can type "cd /var/www" without the quotes. If possible, create this directly on the webserver using nano...that will keep you from having to FTP it and move around and such. All of the lines that start with "//" are just comments. Feel free to leave them out when typing, they are just there so you know what's going on. Also, all of the lines that begin with "echo" just output information on the page your looking at so you know how far along it is. This entire thing is surrounded by ...but that screws up the formatting in the answer...so add one at the beginning, and the closing bracket at the end of the file.
EDIT: Ok...I just blew up Mahalo by putting the script in the answer. So, it is now on a webpage, just follow the link for the code:
http://www.calvinnhobbes.com/RandomThings/WriteHTMLfromMySQLfile.txt
Um...my bad Mahalo guys...didn't mean to set off every hack alarm and SQL injection protection you have on this page...=(
Once the script is complete, you can then copy all of your HTML files off wherever you want them. Or connect to each on your windows box to make sure they look like you want them to. This is all off the top of my head in the middle of the night, so let me know if you get any errors.
If all this seems too complicated, then you can buy some software to convert it for you. Look into:
http://www.convert-in.com/sql2xls.htm
or
http://www.sharewareriver.com/product.php?id=1477
I have never used either of those...so I'm not sure how good they are.
Other side notes:
If you absolutely can't get another hard drive or machine for linux, you can try installing these on windows. I have noticed some problems with it in the past though...but this is a simple enough script it "SHOULD" still work. Note that the install will be quite a bit more complicated though since you will need to install apache, PHP and MySQL all seperately.
http://www.ampsoft.net/webdesign-l/how-to-install-apache-php-mysql.html
or
http://www.php-mysql-tutorial.com/wikis/php-tutorial/installing-php-and-mysql.aspx
One other option is using Wubi to install Ubuntu inside of windows...but I've never tried it:
http://seogadget.co.uk/the-ubuntu-installation-guide/
*crosses fingers and hopes the code doesn't trash the formatting and presses submit*
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$Other options include (but not limited to) Java, Perl, .Net (VB.Net/C#), ASP, Cold Fusion, etc..). It might be best to ask your web host what they support. Most likely, they'll offer either PHP (Linux hosting) or .NET/ASP (Windows Hosting).
If you already have programming experience, but just no SQL experience, skip ahead to after my programming example.
The pages themselves would be broken into a few parts. One part where the actual connection to the database is made, and any SQL queries are passed to the MySQL server. Those results come back into variables or arrays to be included in the content later on.
The next part of the pages would be the HTML itself, or the template that the dynamic data will be displayed with in.
The last part is actually embedded within the HTML code and that would be the display of the variables...
A sample page would look like this. (actual code not used, but will be used to encapsulate pseudo-code from HTML code.
Connect to the MySQL database
SQL = The SQL commands to retrieve the page data
Results = Execute the command, SQL
?>
Welcome to the page about . This page shows
information from the database on .
-----
You can sort of see in the above example that a connection is made to the database. A SQL command is sent to MySQL and it puts the response in a container called "Results". Then in the middle of the HTML, you can see a few places where "Results" is referenced along with the name of the field in the database you want displayed.
This is a very rough example of how a dynamic page would work. If it makes any sense at all, I recommend the following sites/links:
http://www.php.net -- PHP Web Site. Contains all the resources you need to program in PHP.
http://www.php.net/manual/en/book.mysql.php - Link to the MySQL section of the PHP manual. Lists all the commands required to interact with a MySQL server.
The MySQL website is http://www.mysql.com/
http://sqlcourse.com/ or http://www.w3schools.com/sql/default.asp offer tutorials to teach SQL in general. Most SQL servers use similar syntax. There's occasionally small differences, but most of these can be searched for online. For instance:
SELECT * FROM WHERE id = 20;
will work in Microsoft SQL Server, Access, PostgreSQL and MySQL (and probably oracle too, but I dont have any first-hand experience). This command will get (select) all columns (*) from the table where the value of the id column is the number 20. If this were HTML page "20.html" or page.php?id=20 then that command would bring up the 20th record (assuming there's a column called ID)
Get it?
If you have any questions, feel free to contact me (jaypoc AT google's mail service) or through Mahalo.
If you don't have any experience programming or what I said above is out of your league then I recommend hiring someone else to accomplish it or outsource it.
- - -
One alternative that is a bit tricky, but probably MUCH easier (though not as good)...
Download and install the MySQL ODBC Drivers in your Windows PC
Create a DSN (ODBC Connection) on your computer to the MySQL DB
Create a template/layout in Microsoft Word as a Mail Merge "catalog"
Save the pages as HTML
It's also complicated, but some thrift web searches will walk you through doing each of these steps if your not a programmer.
PHP: http://www.php.net
PHP/MySQL: http://www.php.net/manual/en/book.mysql.php
MySQL Site: http://www.mysql.com/
SQL Tutorial 1 : http://sqlcourse.com/
SQL Titorial 2 : http://www.w3schools.com/sql/default.asp
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$Copy and paste that code into a new file. Call the new page something like: read.php
Ensure that you change the username, password, database and table name.
These things can be found on lines 3 and 6. You need to replace:
mysql_host
mysql_user
mysql_password
my_database
These things are unique to your server and database.
Browse to the the file (read.php) on your web server. You'll either be presented with a descriptive error message or the contents of the table.
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$
@afluke : This could be a bit complicated. You only have the .sql data file?
If so, the easiest way to get the data into html is going to be to import the sql into mysql on a server with php installed. This is just about any server at any hosting company. Then, copying-and-pasting the code above into a new file will result in the database dump that you're looking for.
It's possible to parse the sql file directly, however you would need to code a custom processor that will parse the sql data into html. This would be a fair bit tougher than simply extracting it properly using PHP.
Oh, just as a quick note...
The code that I referenced will produce a proper html table of the data. Microsoft Excel understands this. In your browser, you can select all of the data and copy-and-paste it into Excel. Excel will automatically split the data apart into the proper columns and rows so that you can perform searches, calculations, lookups whatever.
This is handy for non-sql folks.
Thank you, Rob. But to clarify. I only have the table, no code. Just a *.sql file with the table's content. The web app is long gone.
The SQL file sonds like a dump of the data from MySQL directly. You may want to import it back into MySQL which can be freely downloaded at http://dev.mysql.com/downloads/mysql/5.1.html#downloads
Available for Windows, Linux and Mac OS X.
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$
Assuming the .sql file is text and not binary. Open the file in a text editor where you can run macros. Two text editors with macro support that come to mind are Notepad++ (http://notepad-plus.sourceforge.net/uk/site.htm) and TextPad (http://www.textpad.com/). Once you open your file, press the record button (should be a red dot in toolbar). At this moment, the macro is recording everything you do.
Let's say your data is something like:
column1a, column2a, column3a
column1b, column2b, column3b
You can convert the first row into HTML, for example:
< tr >< td >column1a< /td > < td > column2a < /td > < td >column3a < /td > < /tr >
column1b, column2b, column3b
You can now stop the macro (should be the black square).
Now you can run the macro for each line in the file. You can also specify to run the macro till the end of file - which will save you time if you have many rows.
I don't know much about the data and what you are trying to accomplish. I hope this information helps you in accomplishing your task.
personal experience.
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$
Thanks, csandoval! This is more in line with what I'm looking for. I was able to get the data into BBEdit and sort the content down to one row per line. Although I'm more likely to use s.
However, and I certainly should have been clearer with my question, I need each row turned into a separate html document. So this means splitting the the text document into several dozen new documents. I tried writing using Automator to write a quick Applescript to do it, but its not a simple task and I really don't have the time to craft something specific.
Just hoping for a software solution, I guess. Not a scriptable one.
<%@ Page Language="VB" MasterPageFile="lssMasterPage.master" Title="Write File" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.OleDb" %>
You can leave an optional "tip" with Mahalo's virtual currency, Mahalo Dollars. If you are asking a difficult question that might require some research, or if you'd like a wide variety of feedback, a higher tip often leads to more answers to your question.
M$
Protected Sub Process_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sql As String
Dim sb As StringBuilder = New StringBuilder
sql = "select id,username,title,message from messages where parentid=-1 order by id desc"
Dim dr As OleDbDataReader
cmdSelect = New OleDbCommand(sql, dbconn)
dr = cmdSelect.ExecuteReader
Do While dr.Read
'Clear the string builder
sb.Remove(0, sb.Length)
sb.Append(...Insert your HTML ...)
OutputFile(sb.ToString, dr("id"))
Loop
dr.Close()
End Sub
Sub OutputFile(ByVal sHTML As String, ByVal sFileName As String)
Dim StreamWriter1 As System.IO.StreamWriter = New System.IO.StreamWriter(Server.MapPath(sFileName & ".htm"))
StreamWriter1.WriteLine(sHTML)
StreamWriter1.Close()
End Sub
Thanks, nativenerd. This is great, but its an issue of time. I'm not afraid of learning something new, but my work doesn't give me the time to invest in this, although it might end up being the only answer. I'm going to ask around the office to see if anyone is familiar with what you describe since I am interested in the "mysql to excel" option. This seems more reasonable than learning how to write a script to do it.