Importing html to mysql database

import, html, database,php,mysql,phmyadmin,quotes

[Lorelle]

I'm preparing to convert my "old" non-dynamic html website to php and mysql. I finally found a mediumly fast way to turn the html pages into raw data, but it has html codes within the content that I want to stay within the database.

For example, I have DIV and CLASS codes with quote marks (div id="fred" - p class="red") that I want to stay.

I've read through the mysql documentation and understand that I can set the fields-terminated-by, fields-enclosed-by and lines-terminated by with characters, but I can't find which characters are allowed. I also am not sure about doubling the quotes in order to keep them ("") or adding slashes (\"), or what.

I did a test run with phpmyadmin to add a simple four field test text file to a table I set up with phpmyadmin. I tried changing from the default field separators of ";" to "^" and record ends of "#". Nothing.

PHPMyAdmin reports:

SQL-query : 
LOAD DATA LOCAL INFILE '#*$!/tmp/phpRIYxId' INTO TABLE `junktable` FIELDS TERMINATED BY '^' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '#' 
MySQL said: 
The used command is not allowed with this MySQL version

The problem could be with the upload file process, but I still would like to know how to deal with quotes within my "fields" and what choices do I have for field separators and line ends.

I have a lot of data to add to the database and I want to prepare it right for importing. Thanks.

[jatar_k]

Welcome to WebmasterWorld Lorelle,

well when you insert data there are a bunch of ways to do it

creating and understanding the data to be inserted is the most important part.

>> I finally found a mediumly fast way to turn the html pages into raw data

What do you mean by raw data?
What exact format does it have?

[Lorelle]

The data is basic. Web page stuff to be generated by php. Author, keywords, description, and content stuff. The content contains html codes. I need it for the layout.

<div id="fred><p>Blah Blah <a class="external" href="link.html">Link blah</a>....

that kind of thing.

How do I treat the quotes around the codes so they don't interfere with the import, and what are my choices for field and line breaks.

The code for which I tried to import the data is listed in the post above.

I read somewhere in all the millions of pages I've been reading on this subject for the past three weeks that I need FILE permission for something, and maybe this is one of the somethings. I went through my list of permissions and don't see a FILE to be selected. Is this why I might not be able to import my data to MySQL?

Thanks for the help. I've got a lot of work to do on this, especially stripping the "non-essential" (to the database) HTML code from everything to put it into the database. I want to prepare the data properly so it will whizbang right in there - no stress....ha ha.

[coopster]

The used command is not allowed with this MySQL version

Which version of MySQL are you running? LOCAL is available in MySQL 3.22.6 or later.

[Lorelle]

I just checked and it's phpMyAdmin 2.4.0 and MySQL 4.0.18.

The command line was generated through phpMyAdmin. I didn't "write" it, just filled in the blanks and the program wrote it out. Ergo, it should work....such a thought! ;-)

[jatar_k]

so since you are using LOAD DATA, can I assume that you have it all in a csv type file?

If so you can easily have php open/read/insert into mysql.

is that the case? If so I will flush out my answer after.

[coopster]

Realizing that jk is going to show you another route, you may be forced to anyway. I am willing to bet that you aren't allowed to run the LOAD DATA INFILE command because either you don't have the privileges or the option is not enabled upon mysql startup. It' more than likely the latter and you can check it by running this command:

SHOW VARIABLES LIKE 'local_infile [dev.mysql.com]';

[jatar_k]

try coop's first, I am talking alternative if that doesn't work

[Lorelle]

I ran SHOW VARIABLES LIKE 'local_infile';
and the answer is local-infile ON

Does that help?

The file I am trying to upload looks like this and is in a file called "test.txt"

"sally"^"rose"^"blue"^"green"#
"fred"^"orange"^"yellow"^"pink"#
"phil"^"blue"^"gold"^"black"#"lori"^"purple"^"red"^"yellgreen"#

I left the last line as one line to test the end record but I also tried the file with everything on one line.

The file structure is set up:

Field  Type    Null
name  varchar(20)  No
color1 varchar(20)  No
color2 varchar(20)  No
color3 varchar(20)  No

This should work like a charm so I'm not sure what's going on.