Form not submitting if text field is too long/large (PHP & MYSQL)

[DanglyWhale]

Hi there,

 

I did have an old account but my login credentials have gone missing so this is a fresh account.

 

I have the below code which works almost perfectly... (I realise it's not encrypted yet).

 

My issue is, that if a user makes a long comment in the "comments" field, then none of the data is inserted in to the database, but the user is still correctly redirected to the thank you page (and a confirmation email is triggered as per the insert script sent).  If the comment is short (eg "Thanks") it all gets inserted nicely... 

 

The "Comments" field is a "text" type row with utf8_general_ci collation within my MYSQL database. 

 

Any ideas whats going wrong here?  What settings in my database or in my code should I change to allow a longer comment/text field?  I always thought "text" field types supported 21k characters?!  So it must be in the code, no?  I wish I was better at PHP! 

 

Thank you!

 

Tom

<?php
session_start();

// Database variables
$localhost = "000.000";
$username = "xxx";
$password = "xxx";
$database = "awards01";

// Establish our connection to the database 
mysql_connect($localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

// Include PHPMailer for confirmation mail 
require("../../utils/PHPMailer/class.phpmailer.php");
require("../../utils/PHPMailer/class.smtp.php");
$mail = new PHPMailer();

$FirstName = mysql_escape_string($_POST['FirstName']);
$LastName = mysql_escape_string($_POST['LastName']);
$Email5 = mysql_escape_string($_POST['Email']);
$Email = strtolower($Email5);
$Tel = mysql_escape_string($_POST['Tel']);
$Ref = mysql_escape_string($_POST['Ref']);
$Comments = mysql_escape_string($_POST['Comments']);
$Address1 = mysql_escape_string($_POST['Address1']);
$Address2 = mysql_escape_string($_POST['Address2']);
$Town = mysql_escape_string($_POST['Town']);
$County = mysql_escape_string($_POST['County']);
$PostCode = mysql_escape_string($_POST['PostCode']);

// To protect MySQL injection 
$FirstName = stripslashes($FirstName);
$LastName = stripslashes($LastName);
$Email = stripslashes($Email);
$Tel = stripslashes($Tel);
$Ref = stripslashes($Ref);
$Comments = stripslashes($Comments);
$Address1 = stripslashes($Address1);
$Address2 = stripslashes($Address2);
$Town = stripslashes($Town);
$County = stripslashes($County);
$PostCode = stripslashes($PostCode);

// Insert into database 
$PostAwards01 = "INSERT INTO details01 (`FirstName`, `LastName`, `Email`, `Tel`, `Ref`, `Comments`, `Address1`, `Address2`, `Town`, `County`, `PostCode`, `Paid`, `date`) VALUES ('$FirstName','$LastName','$Email','$Tel','$Ref','$Comments','$Address1','$Address2','$Town','$County','$PostCode','No',CURRENT_TIMESTAMP)";
mysql_query($PostAwards01);

// Create & Send email 
$mail->IsSMTP();                                  
$mail->Host = "smtp.mydomain.co.uk";  
$mail->SMTPAuth = true; 
$mail->Username = "enquiries@mydomain.co.uk"; 
$mail->Password = "xxx"; 

$mail->From = "enquiries@mydomain.co.uk";
$mail->FromName = "mydomain";
				
$mail->AddAddress($Email);
$mail->addBCC("abc@mydomain.co.uk");
$mail->addBCC("xyz@mydomain.co.uk");

$mail->Subject = "Subject title here";
$mail->IsHTML(true);

$mail->AddEmbeddedImage('../img/EmailSig.jpg', 'logoimg', 'logo.jpg');

$mail->Body = "
				<p>Email body</p>
				";

$mail->Send();

// Redirect to thank you page
header("location:../ThankYou.php");

// Close connection 
mysql_close($database);
?>

[Muddy_Funster]

Y'all need to stop using mysql.  you should right now take this opportunity to re-write that using PDO (or mysqli if you really want).

[DanglyWhale]

Thanks for the reply,

 

What's wrong with MYSQL?  

 

I would still need a physical database somewhere - does PDO replace that? What is PDO?

 

I would classify myself as PHP/MYSQL retarded. I know a little, enough to understand I know nothing.  I think a lot of fellow noobs would appreciate you elaborating on what PDO is, how it can be used, if it's used as well as MYSQL, etc etc. 

 

Thanks!

[Muddy_Funster]

The mysql_ extension has been depreciated for quite a while now, and as of the current release of PHP (v7) it's no longer supported at all.

 

PDO is a newer library (well it's really an abstraction but that's not important) that does all the same things mysql_ did - only better.  As I said, another option is mysqli_ (the i is for improved) which is also acceptable, but I personally prefer PDO because it works with more than just mysql.

 

I have posted a simple example of PDO use in another thread, but for ease I'll post another here with some comments for you:

 

$con = new PDO('mysql:host=yourDBHost;dbname=nameOfDB', 'dbUserName','dbUserPassword');
//that establishes the connection to the database
$sql = "SELECT some, fields, that, you, want FROM tablename WHERE condition = :match";
//that names a base query string to use, notice that the value that the condition has to match is entered as :match - the : notates a parameter that needs to be bound
$stmt = $con->prepare($sql);
//that prepares the query sting statement for parameter binding
$stmt->bindParam(':match', $yourVarToMatch, PDO::PARAM_INT)
//that binds the value of $yourVarToMatch to the placeholder :match set in the query string and tells the PDO class that it is to be an integer.
$stmt->execute();
//execute the statement
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
//fetch all the results into the $results variable in the form of an associative array
$errors = $stmt->errorInfo();
//pass any error info into the $errors variable - errorInfo() returns an array.
if($errors[0] != '00000'){
var_dump($errors);
}   // errorInfo() will return a value of '00000' as the first item in the array if the query was a success, if this isn't the case we want to know what wen't wrong
else{
var_dump($results);
} // if everything wen't OK let's see the results we got back

That's all there is to it.  Obviously the var_dumps() are just for debugging, you can handle the contents of $results and $errors like you would any other variable. 

 

There are other nuances of PDO, such as when run a query that does not require any parameters to be bound you would do things a bit differently, and there are a number of options that you can - and in a production setting should - set during the initial new PDO() call, but that should be enough to get you started with it.

[DanglyWhale]

The mysql_ extension has been depreciated for quite a while now, and as of the current release of PHP (v7) it's no longer supported at all.

 

PDO is a newer library (well it's really an abstraction but that's not important) that does all the same things mysql_ did - only better.  As I said, another option is mysqli_ (the i is for improved) which is also acceptable, but I personally prefer PDO because it works with more than just mysql.

 

I have posted a simple example of PDO use in another thread, but for ease I'll post another here with some comments for you:

$con = new PDO('mysql:host=yourDBHost;dbname=nameOfDB', 'dbUserName','dbUserPassword');
//that establishes the connection to the database
$sql = "SELECT some, fields, that, you, want FROM tablename WHERE condition = :match";
//that names a base query string to use, notice that the value that the condition has to match is entered as :match - the : notates a parameter that needs to be bound
$stmt = $con->prepare($sql);
//that prepares the query sting statement for parameter binding
$stmt->bindParam(':match', $yourVarToMatch, PDO::PARAM_INT)
//that binds the value of $yourVarToMatch to the placeholder :match set in the query string and tells the PDO class that it is to be an integer.
$stmt->execute();
//execute the statement
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
//fetch all the results into the $results variable in the form of an associative array
$errors = $stmt->errorInfo();
//pass any error info into the $errors variable - errorInfo() returns an array.
if($errors[0] != '00000'){
var_dump($errors);
}   // errorInfo() will return a value of '00000' as the first item in the array if the query was a success, if this isn't the case we want to know what wen't wrong
else{
var_dump($results);
} // if everything wen't OK let's see the results we got back

That's all there is to it.  Obviously the var_dumps() are just for debugging, you can handle the contents of $results and $errors like you would any other variable. 

 

There are other nuances of PDO, such as when run a query that does not require any parameters to be bound you would do things a bit differently, and there are a number of options that you can - and in a production setting should - set during the initial new PDO() call, but that should be enough to get you started with it.

 

Thank you for this explanation. Do i need to install anything on my server to use PDO, or is it part of the PHP library?

 

Lastly, do you have any insight in to my initial query: why putting too much text in a text box is causing the insert not to work?

 

Thank you, again.

[mac_gyver]

 A TEXT column has a maximum length of 65,535.

 

php's mysql extension, i.e. the mysql_xxxxxx() functions, are obsolete and have been removed from the latest php version. the PDO extension/class (which is the best choice to use as a replacement) and the mysqli extension/class (turns out is not the best choice to use, thanks php) are the available replacements when using php with a msyql database.
 
the problem you are currently having may be triggering an error, and having error handling in your code for the execution of the sql query would result in useful information being produced.

 

you ALWAYS need to handle any errors that statements can produce. when debugging problems, you would want to display errors. on a live server, you would want to log errors. the switch between displaying and logging errors can be done automatically if you use php itself to handle the errors, since php's display_errors and log_errors settings will control what happens with the error messages. you would also need to set php's error_reporting to E_ALL, in both cases, so that all errors will be reported.

 

the easiest way of handling errors is to use exceptions, which both the PDO and msyqli extensions/classes supports. if you are going to use the PDO class, you would set the error mode to exceptions, either in the connection statement or as a separate statement right after you make the connection. if you don't catch exceptions in your code, using a try/catch block, php will handle the uncaught exception and produce a fatal error that contains the actual error information.

 

some other things to configure for the PDO connection -

 

1) set the character encoding of the connection in the connection statement.

2) set emulated prepared queries to false.

3) set the default fetch mode to assoc, since this will (should) be the most common usage in your code.

 

next, you need to use a prepared query to supply input data to an sql query statement, by putting place-holder(s) into the sql statement where the data is to be used and binding the data to the place holder, either explicitly (which is the best general choice) or implicitly (doesn't work in some cases since all data is treated as a string), and supplying the actual data separately for the execution of the prepared sql statement.

 

using a prepared query will ELIMINATE the need to escape string data, which my also be where the current problem is at. your current code is ineffective as escaping data (there would be a query error, from the error handling), since you are first escaping the data, then undoing that escaping with the stripslashes() statements. the problem is more likely because your longer text has sql special characters in it, such as a quote.
 
 

Edited July 7, 2016 by mac_gyver