PHP sql UPDATE not working

Question

I'm trying to update a table in my database, put my update query does not work. I have checked the code many times, but I can't see anything wrong with it. I'm calling a php-file and sending data over to it with javascript. Any ideas?

This is my php-file:

include 'db-connect.php';

$db->autocommit(false);

$postdata = file_get_contents("php://input");
$request = json_decode($postdata);

$id = $db->real_escape_string($request->ID);
$from = $db->real_escape_string($request->From);
$to = $db->real_escape_string($request->To);
$from_ampm = $db->real_escape_string($request->FromAMPM);
$to_ampm = $db->real_escape_string($request->ToAMPM);

$sql = "update OpeningHours set From = '$from', To = '$to', FromAMPM = '$from_ampm', ToAMPM = '$to_ampm' ";
$sql .= "where ID = '$id'";
$res = $db->query($sql);

$message = array();

if($db->affected_rows <= 0)
{
    $db->rollback();
    $message['error'] = "Could not edit. Contact IT manager.";
    echo json_encode($message);
    die();
}

$db->commit();
$db->close();
$message['error'] = "";
$message['success'] = "Edit Success!";
echo json_encode($message);

And this is the javascript part:

var request = $http({
                method: "post",
                url: "updateOpeningHoursItem.php",
                data: {
                    ID: $scope.openingHoursID,
                    From: $scope.from,
                    To: $scope.to,
                    FromAMPM: $scope.fromAMPM,
                    ToAMPM: $scope.toAMPM
                },
                headers: { 'Content-Type': 'application/x-www-form-urlencoded' }
            });

            request.success(function (data) {

                if(data.error === "")
                {
                    $scope.emptyOpeningHoursFields();
                    $scope.resetOpeningHoursEditForm();

                    swal("Success", data.success, "success");
                }
                else
                {
                    swal("Ops!", data.error, "error");
                }
            });

You might get a better response if you define more specifically what "not working" means. — Mike
– Mike, Commented Jun 2, 2015 at 4:54

piyush · Accepted Answer · 2015-06-02 05:04:59Z

2

Hello,

        $host = 'localhost'; 
        $user = 'root';
        $password = '';
        $database = 'youdatabasename';
        $con = mysql_connect($host,$user,$password);
        $res = mysql_select_db($database,$con);

        $postdata = file_get_contents("php://input");
        $request = json_decode($postdata);

        $id = $db->real_escape_string($request->ID);
        $from = $db->real_escape_string($request->From);
        $to = $db->real_escape_string($request->To);
        $from_ampm = $db->real_escape_string($request->FromAMPM);
        $to_ampm = $db->real_escape_string($request->ToAMPM);

        $sql = "UPDATE OpeningHours set From = '".$from."', To = '".$to."', FromAMPM = '".$from_ampm."', ToAMPM = '".$to_ampm."' where ID = '".$id."'";
        $result = mysql_query($sql) or die(mysql_error());

        if($result==0)
        {
            $message['error'] = "Could not edit. Contact IT manager.";
            echo json_encode($message);
            die();
        }

Please try with this code might be work if there any missing something.

answered Jun 2, 2015 at 5:04

piyush

5545 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

solid_luffy Over a year ago

Thanks, but this will not work. Check Bjoern's answer

Bjoern · Accepted Answer · 2015-06-02 04:57:44Z

1

You used reserved words in your UPDATE statement, therefore you have to escape them properly using backticks.

Change your SQL statement to

$sql = "UPDATE OpeningHours 
        SET `From` = '".$from."', 
            `To` = '".$to."', 
            `FromAMPM` = '".$from_ampm."', 
            `ToAMPM` = '".$to_ampm."'
        WHERE `ID` = '".$id."'";

answered Jun 2, 2015 at 4:57

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

Comments

M J · Accepted Answer · 2015-06-02 05:43:19Z

1

You have also used reserved words in you query. Put them in backticks. You can debug this by echo your query. The query should be like this.

$sql = "UPDATE OpeningHours set From = '".$from."', To = '".$to."', FromAMPM = '".$from_ampm."', ToAMPM = '".$to_ampm."' where ID = '".$id."'";
    $result = mysql_query($sql) or die(mysql_error());

edited Jun 2, 2015 at 5:43

answered Jun 2, 2015 at 5:17

M J

3,0414 gold badges26 silver badges36 bronze badges

1 Comment

solid_luffy Over a year ago

'$from' is completely valid, in case you didn't know that. Check Bjoern's answer to see what I did wrong :)

Collectives™ on Stack Overflow

PHP sql UPDATE not working

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related