Mysql is not updating records in Database

Question

Please help as i have an issue. I have a callback script, that another website is sending me some data, and mysql is not updating the records. I didn't mind securing my code yet, because it's not that important at this testing stage. My code is:

    $dbhost = 'localhost';
    $dbuser = 'user';
    $dbpass = 'pass';

    $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

    $dbname = 'dbname';
    mysql_select_db($dbname);
    $postData = $_POST; //GET ALL POST DATA 

    //GET ALL DATA FROM POST AND PREPARE IT FOR SQL
    $mId = $postData['id'];
    $mDone = date('Y-m-d H:i:s',$postData['donedate']);
    $mStatus = $postData['status'];
    $mText = $postData['txtstatus'];
    if ($mText == 'DELIVRD')
        $mText = 'DELIVERED'; //Correction of test status
    $mPhone = $postData['receiver'];

    //ADD TO DB
    if ($postData['type'] == 1){ //success
        $sql = mysql_query("UPDATE message_details SET doneDate='$mDone', status='$mText' WHERE contact='$mPhone' AND msgId='$mId'");
       echo "UPDATE message_details SET doneDate='$mDone', status='$mText' WHERE contact='$mPhone' AND msgId='$mId'"
    }elseif ($postData['type'] == 2) {//FAILED
        $sql = mysql_query("UPDATE message_details SET doneDate='$mDone', status='FAILED' WHERE contact='$mPhone' AND msgId='$mId'");
echo "UPDATE message_details SET doneDate='$mDone', status='FAILED' WHERE contact='$mPhone' AND msgId='$mId'"
    }

Echoing the mysql query and then running it manual in my DB, works fine. I get around 10 requests per second. Why doesn't it work when this code is running

Thanks

EDIT: i added this line in the code: file_put_contents('errors.txt', mysql_error($conn).'\n',FILE_APPEND); and the return is:

\n\n\n\n\n\n\n\n\n\n\n

So no errors from the mysql

Use ```echo mysql_error($conn)``` to get an error from the failed query and post results here please — John Svensson, Oct 02 '15 at 08:49
Well there is alot of things wrong with your code.. First of all I want to point out that using **mysql_*** functions is deprecated and will be removed soon.. So please start using **mysqli_*** or **PDO** instead. While you are doing that, you should start learning and using prepared statements... And last but not least, you rlly need to build in some error handling.. — Naruto, Oct 02 '15 at 08:49
Have you looked in the `php error log` for the message `Error connecting to mysql` — RiggsFolly, Oct 02 '15 at 08:51
Security cannot be an after thought. You are doubling your work load. YOu will have to sit down and convert all those mysql_* into mysqli* — e4c5, Oct 03 '15 at 07:23

score 0 · Answer 1 · edited May 23 '17 at 10:26

If the query is sound, as you say it is because you ran it independantly, then it must be either the connect or select database that has an error. You need to get into the habit of testing the results of database access calls.

As I assume this code has no associated browser page, then you need to check for errors and send the error code somewhere that you can see it, something like this for example. Or regularly check your standard server logs for errors.

$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = 'pass';

$conn = mysql_connect($dbhost, $dbuser, $dbpass)
if ( ! $conn ) {
    file_put_contents('admin_error.log', 
                      mysql_error() . "\n", 
                      FILE_APPEND);
    exit;
}

$dbname = 'dbname';
if ( ! mysql_select_db($dbname) ) {
    file_put_contents('admin_error.log', 
                      mysql_error() . "\n",
                      FILE_APPEND);
    exit;
}

Of course here comes the standard warning:

You should not be using the mysql_ database access extension for new code. It has been deprecated for years and will dissapear completely in PHP7 due out late 2015. Instead use the mysqli_ or PDO extensions. See this post to help in that decision if nothing else its quite a good read.

Mysql is not updating records in Database

1 Answers1