PHP/MySQL only updates if the variable is less than a few characters

Question

I've got an update query running so that events in the database can be updated.

For example, the event record table :

table from mysql

Now, when I want to edit the record, I import all the current data from one and show it on a webpage, so that the user can edit the data, as shown:

page with prefilled info

However, if I submit that page and the event description is more than a few characters long it does not update at all. Here is my PHP/MySQL Code:

$event_title=$_POST['event_title'];
$event_desc=$_POST['event_desc'];
$event_date_start = $_POST['event_date_start'];
$event_date_end = $_POST['event_date_end'];
$db = mysql_select_db("millyaca_events", $connection);

mysql_query("UPDATE events set event_title='$event_title', event_desc='$event_desc', event_date_start='$event_date_start', event_date_end='$event_date_end' where unique_ID='$ID'", $connection);

Only just started learning PHP and MySQL so apologies if it's a really stupid mistake.

Here is the complete submit button script:

if (isset($_POST['submit'])) {
    $ID = $_GET['ID'];
    $event_title=$_POST['event_title'];
    $event_desc=$_POST['event_desc'];
    $event_date_start = $_POST['event_date_start'];
    $event_date_end = $_POST['event_date_end'];
    // Establishing Connection with Server by passing server_name, user_id and password as a parameter
    $connection = mysql_connect("localhost", "removed username", "removed password");
    // Selecting Database
    $db = mysql_select_db("millyaca_events", $connection);
    // SQL query to fetch information of registerd users and finds user match.
    mysql_query("UPDATE events set event_title='$event_title', event_desc='$event_desc', event_date_start='$event_date_start', event_date_end='$event_date_end' where unique_ID='$ID'", $connection);
    mysql_close($connection); // Closing Connection
    header("location: https://www.millyacademy.com/admin-zone/events_management/"); // Redirecting To Other Page        
    }

That's only a small section of the code, but it's all that is used to connect and interact with the database, if you need me to copy the full code from when the submit button is pressed just say — ConnorL, Jun 22 '15 at 20:58
I can't really find any obvious mistakes or anything. so maybe adding a little more code for context can help us out. — Gerton, Jun 22 '15 at 20:59
How large is the description column? Can you update the data to it at the DB levl? You are open to SQL injections as well with this code. — chris85, Jun 22 '15 at 21:00
I can edit directly using PHPMyAdmin without any issues :/ that was my first thought. — ConnorL, Jun 22 '15 at 21:00
Does the data you are trying to write have a quote in it?... or could you provide a sample of the failing data? — chris85, Jun 22 '15 at 21:01
Test data was just some plain text "Hello, hello, hello" etc. Only data i've got to work is a 3 characters or less. The 'Event Description' is a box, does that make a difference? — ConnorL, Jun 22 '15 at 21:04
This is half off-topic, but **please** read [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)! — helmbert, Jun 22 '15 at 21:07
Thanks 'helmbert', this page is only accessible after admin login - normal users cannot submit any sort of scripts, but i'll implement it after i've got this working :) — ConnorL, Jun 22 '15 at 21:09
Try adding `echo mysql_errno($connection) . ": " . mysql_error($connection) . "\n";` — chris85, Jun 22 '15 at 21:09
1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ', event_date_start='2015-06-22', event_date_end='2015-06-22'' at line 1 — ConnorL, Jun 22 '15 at 21:19
So it's because i've got an apostrophe! How would I go about handling symbols? — ConnorL, Jun 22 '15 at 21:19
http://php.net/manual/en/function.mysql-real-escape-string.php for your current driver. You should switch to MySQLi or PDO though in the future and use prepared statements. — chris85, Jun 22 '15 at 21:21
_“So it's because i've got an apostrophe! How would I go about handling symbols?”_ – you would read the link @helmbert posted __again__, and this time not make the __totally wrong__ assumption that this was of no concern, just because it was only accessible to admins … — CBroe, Jun 22 '15 at 21:30

score 2 · Accepted Answer · answered Jun 22 '15 at 21:32

From the comments we've debugged this to being an apostraphe/quote in the data being passed to the query. To resolve this with your current DB driver use, mysql_real_escape_string, http://php.net/manual/en/function.mysql-real-escape-string.php.

You should switch to MySQLi or PDO though in the future and use prepared statements.

Here's a functional usage (untested, so maybe not functional?) using your current code.

if (isset($_POST['submit'])) {
    $ID = (int)$_GET['ID']; //force this to an int, or you could also escape
    $event_title= mysql_real_escape_string($_POST['event_title']);
    $event_desc= mysql_real_escape_string($_POST['event_desc']);
    $event_date_start = mysql_real_escape_string($_POST['event_date_start']);
    $event_date_end = mysql_real_escape_string($_POST['event_date_end']);
    // Establishing Connection with Server by passing server_name, user_id and password as a parameter
    $connection = mysql_connect("localhost", "removed username", "removed password");
    // Selecting Database
    $db = mysql_select_db("millyaca_events", $connection);
    // SQL query to fetch information of registerd users and finds user match.
    mysql_query("UPDATE events set event_title='$event_title', event_desc='$event_desc', event_date_start='$event_date_start', event_date_end='$event_date_end' where unique_ID='$ID'", $connection);
    mysql_close($connection); // Closing Connection
    header("location: https://www.millyacademy.com/admin-zone/events_management/"); // Redirecting To Other Page        
    }

It is best to never pass user data directly to your queries.

score 0 · Answer 2 · answered Jun 22 '15 at 21:27

Two Things.

Escape the data provided by user , that will take care of any quotation .
Ensure the db field you are trying to update has enough length.

Also it may be worth skipping the entire POST and do the update using hard coded values to see what is happening.

PHP/MySQL only updates if the variable is less than a few characters

2 Answers2