MySQL seems to pull error when long text

[Timms]

Cant figure this out the fact long text should be able to hold an extreme amount of data from what i hold so i figure there is something else wrong. never the less my code is very basic and i cant see why this would pull an error.. if i put a short peice of text in it works fine, inserts in in to the mysql data base and displays it on the website but if i put a long peice on there it will just pull out the next error and do nothing... here is my code for inserting.

name comment and usermail is user input but user e mail is not used in the db just purly for sending thank you message and the comment in the mysql data base is set to long text so im really stuck and need an answer.

The first error is the one i am getting that says "OoOoOoPpPpPsSsSsS there was an error! please contact the webmaster and notify him on this! <a href="mailto:welsh-kurtuss@hotmail.com">welsh-kurtuss@hotmail.com</a> Thanks!"

$host="host"; // Host name
$username="user"; // Mysql username
$password="pass"; // Mysql password
$db_name="DB"; // Database name
$tbl_name="guestbook"; // Table name
$name = $_POST['name'];
$comment = $_POST['comment'];
$useremail = $_POST['email'];
if (!empty($_POST['name']) || (!empty($_POST['comment']))) {
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect server ");
mysql_select_db("$db_name")or die("cannot select DB");
$datetime=date("d-m-y H:i:s"); //date time ... this is not submitted by the user!

$sql="INSERT INTO $tbl_name(name, comment, datetime)VALUES('$name', '$comment', '$datetime')";
$result=mysql_query($sql);

//check if query successful
if($result){
echo "Thank you for your comment! You will automaticly be redirected...";
header('Refresh: 5; URL=../');
}

else {
echo 'OoOoOoPpPpPsSsSsS there was an error! please contact the webmaster and notify him on this! <a href="mailto:welsh-kurtuss@hotmail.com">welsh-kurtuss@hotmail.com</a> Thanks!';
}

mysql_close();
header('Refresh: 4; URL=../');
} else {
   // empty variable
   die('Please go back and fill out all information<br />Alternativly if you are having problems you can submit your review to <a href="mailto:cerris@chulse.co.uk">cerris@chulse.co.uk</a> or contact the webmaster <a href="mailto:welsh-kurtuss@hotmail.com">welsh-kurtuss@hotmail.com</a><br /><br />You can copy the message below if you would like to submit via e mail:<br />' . $comment);
} 

[ApacheTech]

As a debug, try this:

FIND:

$result=mysql_query($sql);

//check if query successful
if($result){
echo "Thank you for your comment! You will automaticly be redirected...";
header('Refresh: 5; URL=../');
}

else {
echo 'OoOoOoPpPpPsSsSsS there was an error! please contact the webmaster and notify him on this! <a href="mailto:welsh-kurtuss@hotmail.com">welsh-kurtuss@hotmail.com</a> Thanks!';
} 

REPLACE WITH:

error_reporting(E_ALL);
try {
    $result=mysql_query($sql);
    echo "Thank you for your comment! You will automaticly be redirected...";
    header('Refresh: 5; URL=../');
} catch (Exception $e) {
    die('Error with INSERT statement:' . $e->getMessage() . '\n');
} 

That might give you more insight into the errors you're facing.

[CoursesWeb]

Hi
Try insert and select data with PHP PDO, or MySQLi.
Example with PDO:

<?php
// Connection data (server_address, database, name, poassword)
$hostdb = 'localhost';
$namedb = 'DB';
$userdb = 'username';
$passdb = 'password';

try {
  // Connect and create the PDO object
  $conn = new PDO("mysql:host=$hostdb; dbname=$namedb", $userdb, $passdb);
  $conn->exec("SET CHARACTER SET utf8");      // Sets encoding UTF-8

  // Define an insert query
  $sql = "INSERT INTO `$tbl_name` (`name`, `comment`, `datetime`)
    VALUES ('$name', '$comment', '$datetime')";
  $count = $conn->exec($sql);

  $conn = null;        // Disconnect
}
catch(PDOException $e) {
  echo $e->getMessage();
}

// If data added ($count not false) displays the number of rows added
if($count !== false) echo 'Number of rows added: '. $count;
?>

You can find documentation about PDO, or MySQLi in the php manual, on php.net

[Timms]

Ah thanks for the replys guys i just found out the cause!

it apears that when people add such a thing like ' it pulls out an error!

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's life. Then a sharing of her understanding and deeply personal spiritual lesson'

Any idea how i would go about fixing this?

i did a complete change of code never the less the code is very similar

so ' is the culperate!

im guessing mysql filters ' to stop people adding html code to it but if im wrong correct me.

[Timms]

ok so i found the answer that i need to escape the ' quotes in the user submited comment

my attempt is $dtl = str_replace("'","'","\'");

but replaces the whole thing so how to i just replace the quote rather than the whole thing?

[CoursesWeb]

Hi
Try use the addslashes() or mysql_real_escape_string() functions. See on php.net examples.

[roars1111]

This is similar to MvG's answer, but it doesn't require gawk 4 and thus uses -F as suggested in that answer. It also shows a technique for listing the desired fields and iterating over the list. This may make the code easier to maintain if there is a large list.

#!/usr/bin/awk -f
BEGIN {
    col_list = "colour shape size" # continuing with as many as desired for output
    num_cols = split(col_list, cols)
    FS = OFS = ","
}

NR==1 {
    for (i = 1; i <= NF; i++) {
        p[$i] = i # remember column for name
    }
    # next # enable this line to suppress headers.
}

{
    delim = ""
    for (i = 1; i <= num_cols; i++) {
        printf "%s%s", delim, $p[cols[i]]
        delim = OFS
    }
    printf "\n"
}