CODEWITHSUNDEEP
phpmysql
ConnorL
ConnorL

Reputation: 227

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

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        
    }

Upvotes: 0

Views: 94

Answers (2)

chris85
chris85

Reputation: 23892

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.

Upvotes: 2

Scalable
Scalable

Reputation: 1681

Two Things.

  1. Escape the data provided by user , that will take care of any quotation .
  2. 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.

Upvotes: 0

Related Questions