I'm a newbie with WAMP on Windows7 ; using HTML5 on Chrome browser plus jQuery and a PHP script. WAMP is up and running. I'm not using 'root' and have setup an account named 'my_user' with password 'my_password'. Through phpMyAdmin I believe I have set the privs correctly for my_user to have access to the taskmanager database. I am able to update things via the mysql console window.
This is a screenshot of my users page.
The connection in PHP code is successful and I'm able to replace the UPDATE code with SELECT code to verify that things are working. It's just that nothing actually gets updated.
$host = "localhost"; // 127.0.0.1 port = 55394 asof 3/9/17
$username = "my_user";
$password = "my_password";
$database = "taskmanager";
try {
$db = new mysqli( $host, $username, $password, $database);
if ( ! $db ) {
echo "Error: call to mysqli with $host, $username, $password, $database failed";
exit;
}
}
catch (Exception $e) {
echo "Exception Message: " . $e->getMessage();
exit;
}
if ($db->connect_error > 0) {
die('Unable to connect to database [' . $db->connect_error . ']');
}
I call this PHP script via an ajax POST call from my JavaScript code.
// console.log( JSON.stringify(dataStore));
$.ajax({
url : "taskmanagerSaveTasks.php" ,
type : 'POST',
cache: false,
data : {'whichUser' : forThisUser, 'whichDay': onThisDay, 'jsonData' : JSON.stringify(dataStore) },
success : function(res) {
// Successfully sent data
console.log(res);
},
error: function(err) {
// Unable to send data
console.log(err);
}
});
I log my UPDATE command string to a text file, for debug purposes and this is what I see.
UPDATE elapsed, users, tasks
SET elapsed=28000
WHERE elapsed.day='2017-03-10' AND elapsed.taskid=1 AND tasks.taskid=elapsed.taskid
AND users.name='jimladeroute' AND users.userid=tasks.userid ;
success affected=0
You see? The affected list is always zero.
The variable $strSQL is what I'm using to try and do the update. I searched stackoverflow for an existing solution but I don't see one where things pass ok (no query error) and yet nothing gets affected in the database via PHP.
Again, I can update the data using the UPDATE from the SQL COMMAND LINE and from the phpAdmin window. Just can't do it via my own php script.
I changed the setup of strSQL variable to not use the heredoc method, and I didn't see any differences in the result (still no updates to the database).
And here is how I process the inputs.
<?php
$whichDay = date('Y-m-d'); // this gets you today's date - but on the server which could be in a different timezone!
$whichUser = 'anybody'; // there is no anybody user unless one is created in the database ; maybe allow this for testing?
$data = [];
if(isset($_POST['whichUser'])) {
$whichUser = $_POST['whichUser'];
}
if(isset($_POST['whichDay'])) {
$whichDay = $_POST['whichDay'];
}
if(isset($_POST['jsonData'])) {
$contents = $_POST['jsonData'];
$data = json_decode( $contents );
}
Here is the full text of the PHP script.
<?php
$whichDay = date('Y-m-d'); // this gets you today's date - but on the server which could be in a different timezone!
$whichUser = 'anybody'; // there is no anybody user unless one is created in the database ; maybe allow this for testing?
$data = [];
if(isset($_POST['whichUser'])) {
$whichUser = $_POST['whichUser'];
}
if(isset($_POST['whichDay'])) {
$whichDay = $_POST['whichDay'];
}
if(isset($_POST['jsonData'])) {
$contents = $_POST['jsonData'];
$data = json_decode( $contents );
}
// $contents equals [{"idnum":"1","category":"HOUSEHOLD","title":"DISHES","elapsed":"10000"},{"idnum":"2","category":"HOUSEHOLD","title":"CLEAN FLOORS","elapsed":"10000"}]
$filename = "taskdata.json.save.txt";
$nbytes = file_put_contents( $filename, "data=".$contents );
$host = "localhost"; // 127.0.0.1 port = 55394 asof 3/9/17
$username = "my_user";
$password = "my_password";
$database = "taskmanager";
try {
$db = new mysqli( $host, $username, $password, $database);
if ( ! $db ) {
echo "Error: call to mysqli with $host, $username, $password, $database failed";
exit;
}
}
catch (Exception $e) {
echo "Exception Message: " . $e->getMessage();
exit;
}
if ($db->connect_error > 0) {
die('Unable to connect to database [' . $db->connect_error . ']');
}
$safeUser = mysqli_real_escape_string( $db, $whichUser) ;
$safeDay = mysqli_real_escape_string( $db, $whichDay) ;
$mystring = '';
foreach ( $data as $jsonObj ) {
$taskid = $jsonObj->{'idnum'} ;
$elapsedMS = $jsonObj->{'elapsed'}; // in milliseconds
$mystring = $mystring . "=================\r\n" ;
$selStrSQL = <<<SQL1
SELECT * FROM elapsed AS e, tasks AS t, users AS u
WHERE e.day='$safeDay' AND e.taskid=$taskid AND t.taskid=e.taskid
AND u.name='$safeUser' AND u.userid=t.userid ;
SQL1;
/*
$strSQL = <<<SQL
UPDATE elapsed, users, tasks
SET elapsed=$elapsedMS
WHERE elapsed.day='$safeDay' AND elapsed.taskid=$taskid AND tasks.taskid=elapsed.taskid
AND users.name='$safeUser' AND users.userid=tasks.userid ;
SQL;
*/
$strSQL = "UPDATE elapsed, users, tasks SET elapsed=" . $elapsedMS . " WHERE elapsed.day='" . $safeDay ."' AND elapsed.taskid=" . $taskid . " AND tasks.taskid=elapsed.taskid AND users.name='" . $safeUser . "' AND users.userid=tasks.userid ;" ;
// $mystring = $mystring . $strSQL . "\r\n" ;
$result = $db->query( $strSQL );
if ( $result === false ) {
echo "Error updating record: " . $db->error ;
$mystring = $mystring . " error " . $db->error . "\r\n" ;
} else {
echo "Record update successfully affected=" . $db->affected_rows . "\r\n";
$mystring = $mystring . " success affected=" . $db->affected_rows . "\r\n" ;
}
}
$nbytes = file_put_contents( "taskdata.sql.txt" , $mystring );
$db->close();
?>
Here is the modified PHP code - now using prepared statements. Doing this did not resolve the problem. Things are still not getting updated in the database.
$safeUser = mysqli_real_escape_string( $db, $whichUser) ;
$safeDay = mysqli_real_escape_string( $db, $whichDay) ;
$mystring = '';
$strSQL = "UPDATE elapsed, users, tasks SET elapsed=? WHERE elapsed.day=? AND elapsed.taskid=? AND tasks.taskid=elapsed.taskid AND users.name=? AND users.userid=tasks.userid" ;
if ( !($stmt = $db->prepare( $strSQL ))) {
echo "Prepare failed: (" . $db->errno . ") " . $db->error ;
$mystring = $mystring . " Prepare failed " . $db->errno . ") " . $db->error . "\r\n";
}
foreach ( $data as $jsonObj ) {
$taskid = $jsonObj->{'idnum'} ;
$elapsedMS = $jsonObj->{'elapsed'}; // in milliseconds
$catname = $jsonObj->{'category'};
$title = $jsonObj->{'title'};
$mystring = $mystring . "=================\r\n" ;
$mystring = $mystring . "UPDATE using $taskid, $catname, $title, elapsed=$elapsedMS \r\n" ;
if ( !$stmt->bind_param( "isis", $elapsedMS, $safeDay, $taskid, $safeUser ) ) {
echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error ;
$mystring = $mystring . " Binding parameters failed " . $stmt->errno . ") " . $stmt->error . "\r\n" ;
}
if ( !$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error ;
$mystring = $mystring . "execute failed " . $stmt->errno . ") " . $stmt->error . "\r\n";
}
$mystring = $mystring . " affected_rows=" . $db->affected_rows . "\r\n" ;
}
$nbytes = file_put_contents( "taskdata.sql.txt" , $mystring );
$db->close();
