Gossamer Forum: General: Databases and SQL: mySQL longtext

Sep 16, 2003, 12:42 PM

Lee

User (154 posts)

Sep 16, 2003, 12:42 PM

Post #1 of 12

Shortcut

mySQL longtext

I have a mySQL table which I need to export the data from into a new table in another database. The table includes a field which is "longtext". I have written a PHP script to take the data from one table and insert it into the other. The script has worked flawlessly until I came to this table which has a "longtext" field. I cannot get that field to copy regardless of what I try. Any suggestions??? HELP!!!! :)

Sep 16, 2003, 12:45 PM

Andy

Veteran / Moderator (18441 posts)

Sep 16, 2003, 12:45 PM

Post #2 of 12

Shortcut

Re: [Lee] mySQL longtext In reply to

Any particular reason why its a longtext? Surely TEXT could do the job? Or does it hold a *very* long string?

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 16, 2003, 12:46 PM

Lee

User (154 posts)

Sep 16, 2003, 12:46 PM

Post #3 of 12

Shortcut

Re: [Andy] mySQL longtext In reply to

The database holds all the html pages for a county government website. Those pages, at times, do get rather lengthy.

Sep 16, 2003, 12:49 PM

Andy

Veteran / Moderator (18441 posts)

Sep 16, 2003, 12:49 PM

Post #4 of 12

Shortcut

Re: [Lee] mySQL longtext In reply to

Mmmm... what query's etc are you using to try and copy it currently?

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 16, 2003, 12:54 PM

Lee

User (154 posts)

Sep 16, 2003, 12:54 PM

Post #5 of 12

Shortcut

Re: [Andy] mySQL longtext In reply to

$query = "SELECT * FROM profile";
$result = create_db_connection("departments",$query);

while ($row = mysql_fetch_array($result)) {

$query1 = "INSERT INTO department (FIELD1, FIELD2, FIELD3 ...) VALUES (\"$row[field1]\",\"$row[field2]\",\"$row[field3]\" ...)";
$result1 = create_db_connection("new_table",$query1);

} // end while statement

When I remove the last field (which is the longtext field) this script works fine. I thought maybe it had to do with quotation marks and apostraphe's, etc. embedded in the variable; however I have even escaped all those characters and it still errors out.

Sep 16, 2003, 12:58 PM

Andy

Veteran / Moderator (18441 posts)

Sep 16, 2003, 12:58 PM

Post #6 of 12

Shortcut

Re: [Lee] mySQL longtext In reply to

You tried mysql_escape_string() ?

http://es2.php.net/...ql-escape-string.php

Could be some charachters in there that are causing the problems...

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 16, 2003, 12:59 PM

Lee

User (154 posts)

Sep 16, 2003, 12:59 PM

Post #7 of 12

Shortcut

Re: [Andy] mySQL longtext In reply to

yup, even tried that!

man, this is frustrating, but keep the ideas coming Andy ... they're certainly appreciated!

Sep 16, 2003, 1:00 PM

Andy

Veteran / Moderator (18441 posts)

Sep 16, 2003, 1:00 PM

Post #8 of 12

Shortcut

Re: [Lee] mySQL longtext In reply to

Also, why are you doing stuff like;

(\"$row[field1]\",\

???

You can simply do....

$query1 = "INSERT INTO department (FIELD1, FIELD2, FIELD3 ...) VALUES ('$row[field1]','$row[field2]','$row[field3]' ...)";

PHP isn't fussy about using '' around a string (unlike Perl).

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 16, 2003, 1:01 PM

Lee

User (154 posts)

Sep 16, 2003, 1:01 PM

Post #9 of 12

Shortcut

Re: [Andy] mySQL longtext In reply to

heh, thanks.

I have only been involved with PHP for about two months now. Guess once Perl is in your blood, it is hard to get rid of! I will clean the code, but that still isn't the problem! Thanks for that insight.

Sep 16, 2003, 1:04 PM

Andy

Veteran / Moderator (18441 posts)

Sep 16, 2003, 1:04 PM

Post #10 of 12

Shortcut

Re: [Lee] mySQL longtext In reply to

You got any error catching in there?

Try something like...

Code:
$result1 = create_db_connection('new_table',$query1);  

my $error = mysql_error(); 
if ($error) {  
    echo "SQL Error: <font color=red>$error</font>"; exit;  
}

May help to determine a bit more what the SQL error is.

Cheers

Andy (mod)
andy@ultranerds.co.uk

Sep 16, 2003, 1:05 PM

Lee

User (154 posts)

Sep 16, 2003, 1:05 PM

Post #11 of 12

Shortcut

Re: [Andy] mySQL longtext In reply to

Andy:

I retried using mysql_escape_string() and this time it worked. Maybe I had a syntax error or something when I first used that function. Regardless, you have been an awesome help. Thanks a bunch.

Sep 16, 2003, 1:08 PM

Andy

Veteran / Moderator (18441 posts)

Sep 16, 2003, 1:08 PM

Post #12 of 12

Shortcut

Re: [Lee] mySQL longtext In reply to

Glad to hear you got it working :) BTW, I would still recommend that mysql_error() code.. there is always the odd random string that will get through, and cause a MySQL error (as I have found out the hard way...lol).

Cheers

Andy (mod)
andy@ultranerds.co.uk