Simple SQL Update Statement Syntax

Question

Attempting to update a record within my MySQL database with PHP. I am a PHP noob so I'm assuming I'm missing something obvious.

My new code (update-task.php) - it submits and redirects but the record is not updated.

<?php
$data = array(
    'id' => $_POST['id'],
    'TaskName' => $_POST['TaskName'],
    'ClientName' => $_POST['ClientName'],
    'AssignedTo' => $_POST['AssignedTo'],
    'DueDate' => $_POST['DueDate'],
    'TimeLogged' => $_POST['TimeLogged'],
    'Notes' => $_POST['Notes'],
    'Urgent' => $_POST['Urgent']
);

require('mysql.php');
$db = new mysql;
$db->connect('localhost', 'my_user', 'my_pass;', 'my_db');
$db->query("UPDATE `my_table` SET TaskName='" . mysql_real_escape_string($data['TaskName']) . "',
    ClientName='" . mysql_real_escape_string($data['ClientName']) . "',
    AssignedTo='" . mysql_real_escape_string($data['AssignedTo']) . "',
    DueDate='" . mysql_real_escape_string($data['DueDate']) . "',
    TimeLogged='" . mysql_real_escape_string($data['TimeLogged']) . "',
    Notes='" . mysql_real_escape_string($data['Notes']) . "',
    Urgent='" . mysql_real_escape_string($data['Urgent']) . "' WHERE id='" . mysql_real_escape_string($data['id']) . "'
");

header('Location:saved.htm');

My form looks like this (view-task.php)

<?php
$id = $_GET['id'];
require('mysql2.php');
$db = new mysql;
$db->connect('localhost', 'my_user', 'my_pass;', 'my_db');
$query = $db->query("SELECT * FROM `table_name` WHERE id = '" . $id ."'");
while($result = $db->fetch_array($query)) {
    $data = $result;
}
?>

<form action="update-task.php" method="post" id="form1" name="form1">

    <label>Task Name:</label>
    <input name="TaskName" type="text" id="TaskName" size="30" value="<?php echo $data['TaskName']?>"/>

    <label>Client</label>
    <input name="ClientName" type="text" id="ClientName" size="30" value="<?php echo $data['ClientName']?>"/>

    <label>Assigned To</label>
    <input name="AssignedTo" type="text" id="AssignedTo" size="30" value="<?php echo $data['AssignedTo']?>"/>

    <label>Due Date</label>
    <input name="DueDate" type="text" id="DueDate" size="30" value="<?php echo $data['DueDate']?>"/>

    <label>Time Logged</label>
    <input type="button" value="Start Count" onClick="timedCount()">
    <input type="text" value="<?php echo $data['TimeLogged']?>" id="TimeLogged" name="TimeLogged">
    <input type="button" value="Stop Count" onClick="stopCount()">
    <input type="button" value="Clear Timer" onClick="clearTimer()">

    <label>Notes</label>
    <input name="Notes" type="textarea" id="Notes" size="30" value="<?php echo $data['Notes']?>"/>

    <label>Urgent</label>
    <input name="Urgent" type="text" id="Urgent" size="30" value="<?php echo $data['Urgent']?>"/>

    <input type="submit" value="Update" class="AddButton">

</form>

Contents of mysql.php

<?php
class mysql {
    var $querynum = 0;
    var $querylist = '';

    function connect($dbhost, $dbuser, $dbpw, $dbname, $pconnect=0) {
        $die = false;

        if($pconnect) {
            @mysql_pconnect($dbhost, $dbuser, $dbpw) or ($die = true);
        } else {
            @mysql_connect($dbhost, $dbuser, $dbpw) or ($die = true);
        }

        if($die) {
            die(mysql_error());
        }

        mysql_select_db($dbname) or die(mysql_error());
    }

    function fetch_array($query, $type=MYSQL_ASSOC) {
        $query = mysql_fetch_array($query, $type);
        return $query;
    } 

    function query($sql) {
        $query = mysql_query($sql) or die(mysql_error());
        $this->querynum++;
        $this->querylist .= "$sql <br />";
        return $query;
    }

    function result($query, $row) {
        $query = mysql_result($query, $row);
        return $query;
    }

    function num_rows($query) {
        $query = mysql_num_rows($query);
        return $query;
    }

    function insert_id() {
        $id = mysql_insert_id();
        return $id;
    }

    function fetch_row($query) {
        $query = mysql_fetch_row($query);
        return $query;
    }
    function affected_rows() {
        $query = mysql_affected_rows();
        return $query;
    }
    function close() {
        mysql_close();
    }
}


?>

@Saty I tried this but still get error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VALUES( 0, 'Task 2', 'Client Test', 'Person', '04/14/2017', '0', 'First Task ' at line 1" — josefresco
– josefresco, Commented Apr 14, 2017 at 13:50
Learn about prepared Statements to prevent sql injection and it makes your statement more readable — Jens
– Jens, Commented Apr 14, 2017 at 14:53
Remove the closing bracked steht the end of the update statement — Jens
– Jens, Commented Apr 14, 2017 at 14:58

Asylzat · Accepted Answer · 2017-04-14 14:46:30Z

1

If you are updating data, so then you have uniq id, post a code from mysql.php mysql update query works as "UPDATEmy_tableSET WHERE id='1'". It looks you have a query error

   <?php

$data = array(
    'id' => $_POST['id']
    'TaskName' => $_POST['TaskName'],
    'ClientName' => $_POST['ClientName'],
    'AssignedTo' => $_POST['AssignedTo'],
    'DueDate' => $_POST['DueDate'],
    'TimeLogged' => $_POST['TimeLogged'],
    'Notes' => $_POST['Notes'],
    'Urgent' => $_POST['Urgent']
);

require('mysql.php');
$db = new mysql();
$db->connect('localhost', 'my_user', 'my_pass;', 'my_db');
$db->query("UPDATE `my_table` SET TaskName='" . mysql_real_escape_string($data['TaskName']) . "',
    ClientName='" . mysql_real_escape_string($data['ClientName']) . "',
    AssignedTo='" . mysql_real_escape_string($data['AssignedTo']) . "',
    DueDate='" . mysql_real_escape_string($data['DueDate']) . "',
    TimeLogged='" . mysql_real_escape_string($data['TimeLogged']) . "',
    Notes='" . mysql_real_escape_string($data['Notes']) . "',
    Urgent='" . mysql_real_escape_string($data['Urgent']) . "' WHERE id='" . mysql_real_escape_string($data['id']) . "'
)");
header('Location:saved.htm');

?>

form

<?php
$data['id'] = $_GET['id'];
require('mysql.php');
$db = new mysql();
$db->connect('localhost', 'my_user', 'my_pass;', 'my_db');
$query = $db->query("SELECT * FROM `my_table` WHERE id = '" . $data['id'] ."'");
while($result = $db->fetch_array($query)) {
    $data = array_merge($data, $result);
}
?>


<form action="update-task.php" method="post">

    <label>Task Name:</label>
    <input name="TaskName" type="text" id="TaskName" size="30" value="<?= $data['TaskName']?>"/>

    <label>Client</label>
    <input name="ClientName" type="text" id="ClientName" size="30" value="<?= $data['ClientName']?>"/>

    <label>Assigned To</label>
    <input name="AssignedTo" type="text" id="AssignedTo" size="30" value="<?= $data['AssignedTo']?>"/>

    <label>Due Date</label>
    <input name="DueDate" type="text" id="DueDate" size="30" value="<?= $data['DueDate']?>"/>

    <input name="id" type="hidden" id="id" value="<?= $data['id']?>"/>

    <label>Time Logged</label>
    <input type="button" value="Start Count" onClick="timedCount()">
    <input type="text" value="<?= $data['TimeLogged']?>" id="TimeLogged" name="TimeLogged">
    <input type="button" value="Stop Count" onClick="stopCount()">
    <input type="button" value="Clear Timer" onClick="clearTimer()">

    <label>Notes</label>
    <input name="Notes" type="textarea" id="Notes" size="30" value="<?= $data['Notes']?>"/>

    <label>Urgent</label>
    <input name="Urgent" type="text" id="Urgent" size="30" value="<?= $data['Urgent']?>"/>

    <input type="submit" value="Update" class="AddButton">

</form>

edited Apr 14, 2017 at 14:46

answered Apr 14, 2017 at 13:54

Asylzat

2297 silver badges19 bronze badges

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

7 Comments

josefresco Over a year ago

Posted contents of mysql.php How do I pull in the ID of the current record?

Asylzat Over a year ago

you can pass your id by url or post $_GET $_POST, if you want to update so then you have a uniq key by which you can update. Queries on how to, you can read in here w3schools.com/php/php_mysql_update.asp

josefresco Over a year ago

This all sounds good, but I don't know how to implement. The w3schools link shows a hardcoded ID- when I view a task I use the following view-task.php?id=1

Asylzat Over a year ago

your url is "www.example.com?id=2" and code: $id = $_GET["id"]; $sql = "UPDATE MyGuests SET lastname='Doe' WHERE id=$id";

josefresco Over a year ago

I replaced my code with your new version above (thanks!) - I'm now getting the following error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 8"

|

Pwntus · Accepted Answer · 2017-04-14 13:52:58Z

0

Your syntax is wrong. You need to use SET with a WHERE clause to identify what to actually update.

SQL update syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

answered Apr 14, 2017 at 13:52

Pwntus

1211 silver badge6 bronze badges

1 Comment

josefresco Over a year ago

I've seen this reference, however I do not know how to modify my code. Can you get me started?

Collectives™ on Stack Overflow

Simple SQL Update Statement Syntax

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related