Welcome to WebmasterWorld Guest from 34.201.121.213

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Importing html to mysql database

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

     
8:01 pm on Jan 5, 2005 (gmt 0)

New User

10+ Year Member

joined:Jan 4, 2005
posts:5
votes: 0


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.

11:27 pm on Jan 5, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


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?

1:26 am on Jan 6, 2005 (gmt 0)

New User

10+ Year Member

joined:Jan 4, 2005
posts:5
votes: 0


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.

1:00 pm on Jan 6, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12548
votes: 2



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.

4:55 pm on Jan 6, 2005 (gmt 0)

New User

10+ Year Member

joined:Jan 4, 2005
posts:5
votes: 0


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

6:04 pm on Jan 6, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


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.

10:03 pm on Jan 6, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12548
votes: 2


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]';
10:11 pm on Jan 6, 2005 (gmt 0)

Administrator

WebmasterWorld Administrator jatar_k is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 24, 2001
posts:15756
votes: 0


try coop's first, I am talking alternative if that doesn't work
10:54 pm on Jan 6, 2005 (gmt 0)

New User

10+ Year Member

joined:Jan 4, 2005
posts:5
votes: 0


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.