PHP Mysql update statement is not working

[gemerson]

I have tried backticks and different syntaxs for the mysql query and nonthing is fixing the problem. Each time I do insert with values of select from it fails and it writes an error to my custom log. Here is my code:  Line 120 has my sql insert statement and right after that is my query that i changed from mysql to mysqli because mysql is depricated.

<?php
 

$hostname = "XXXXXXXXXXXXXX";
$username = "XXXXXXXXXXXXXX";
$dbName = "XXXXXXXXXXXXXX";

// lets prepare some files to capture what is going on.
$incomingJson = 'json.txt';
//$fullArray = 'fullArray.txt';  // needed if you enable the debugging secton below
$sqlErrorLog = "sqlErrors.txt";
$resultLog = "Results.txt";
// initialize the string with a blank value
$string = "";

//These variable values need to be changed by you before deploying
$password = "XXXXXXXXXXXXXX";
$tableName = "Statistics";
$tableName2 = "queuedRows";
$yourfield = "charactersName";

//Connecting to your database
mysql_connect($hostname, $username, $password) or die ("Unable to
connect to database! Please try again later.");

mysql_select_db($dbName)or die("cannot select DB");

// start SEND data
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {

        //capture incoming data
        error_reporting(1);
        $sig = $_POST["sig"];
        $jsondata = $_POST["params"];

        // this line captures the sent data so you can figure out what you need to send back.
        file_put_contents($incomingJson,$jsondata);

        // this line tells the application that the data send was successful.
        echo '{"Status":"Success"}';

        // convert JSON to an array
        $array = json_decode($jsondata, TRUE);

        /*
        // formats the array to view it easier
        $results = print_r($array,true);
        file_put_contents($fullArray,$results);
        */

        //get the total number of objects in the array
        $arrlength = count($array['Children']['1']['Properties']);

        // set while loop index
        $i = 0;
        
        //loop through array node and get row values
        while ($i < $arrlength ) {

            // get row value
            $value = $array['Children']['1']['Properties'][$i]['Value']."\n";

            // convert delimited string to an array
            $arrayPieces = explode("|", $value);

            
            $rowName = $arrayPieces[0];  
            $charactersName = $arrayPieces[1];
            $highscoreFeet = $arrayPieces[2];
            $charactersFitnessLevel = $arrayPieces[3];
            $worstJump = $arrayPieces[4];
            $totalTrainingTime = $arrayPieces[5];
            $startingDate = $arrayPieces[6];
            
            $i++;
        }

            
            // construct SQL statement
            if($startingDate != "1/1/1") {
            $sql="INSERT INTO `$tableName`(`charactersName`, `highscoreFeet`, `charactersFitnessLevel`, `worstJump`, `totalTrainingTime`, `startingDate`)VALUES('$charactersName', '$highscoreFeet', '$charactersFitnessLevel', '$worstJump', '$totalTrainingTime', '$startingDate')";
            
            $result=mysqli_query($sql);
            file_put_contents($resultLog, $result, FILE_APPEND);
            
                  if($result){
                      // if successful do nothing for now.
                  }
      
                  else {
      
                      // if failure, write to custom log
                      $sqlError = "Error writing to statistics {$charactersName}\n";
                      file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND);
                  
                  //if($startingDate != "1/1/1") {
                  $sql="UPDATE $tableName SET charactersName = $charactersName, highscoreFeet = $highscoreFeet, charactersFitnessLevel = $charactersFitnessLevel, worstJump = $worstJump, totalTrainingTime = $totalTrainingTime, startingDate = $startingDate WHERE charactersName = $charactersName";
                  
                  // insert SQL statement
                  $result=mysqli_query($sql);
                  file_put_contents($resultLog, $result, FILE_APPEND);
                  }
                  // catch any errors
                  if($result){
                      // if successful do nothing for now.
                  }
      
                  else {
      
                      // if failure, write to custom log
                      $sqlError = "Error updating to statistics {$charactersName}\n";
                      file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND);
                  }
             }//end of insert if statement to add a character (line 81)
            
    //search for the character name instead of adding
            if($startingDate == "1/1/1") {
            $sql="INSERT INTO $tableName2(charactersName, highscoreFeet, charactersFitnessLevel, worstJump, totalTrainingTime, startingDate, timeOfCreation) SELECT ('charactersName', 'highscoreFeet', 'charactersFitnessLevel', 'worstJump', 'totalTrainingTime', 'startingDate', getdate() FROM $tableName WHERE charactersName = $charactersName)";
            
            // insert SQL statement
            $result=mysqli_query($sql);
            file_put_contents($resultLog, $result, FILE_APPEND);
            // catch any errors
            if($result){
                // if successful do nothing for now.
            }

            else {

                // if failure, write to custom log
                $sqlError = "Error writing to QueuedRows {$charactersName}\n";
                file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND);
            }
            }
            
            $sql="DELETE FROM queuedRows WHERE timeOfCreation < DateAdd(mi, -5, GetDate())";
            
            // insert SQL statement
            $result=mysqli_query($sql);
            file_put_contents($resultLog, $result, FILE_APPEND);

            // catch any errors
            if($result){
                // if successful do nothing for now.
            }

            else {

                // if failure, write to custom log
                $sqlError = "Error deleting from Queuedrows\n";
                file_put_contents($sqlErrorLog, $sqlError, FILE_APPEND);
            }


            

            
    } // end of POST (line 30)

    // start GET data
    if ($_SERVER['REQUEST_METHOD'] === 'GET') {

        // initialize the JSON body variable
        $jsonBody="";

        // get table contents
        $query = mysql_query("SELECT * FROM queuedRows");

        // construct an array to hold the data we pull from mySQL
        $rows = array();

        // loop through the table and drop the data into the array
        while($row = mysql_fetch_assoc($query)) {
            $rows[] = $row;       
        }

        // get the number of rows in the array. We need this in the JSON return
        $arrlength = count($rows);

        // set while loop index
        $i = 0;

        //loop through array node and get row values
        while ($i < $arrlength ) {
            
            // tables we are capturing
            $charactersName = $rows[$i]['cha$ractersName'];
            $highscoreFeet =$rows[$i]['highscoreFeet'];
            $charactersFitnessLevel = $rows[$i]['charactersFitnessLevel'];
            $worstJump = $rows[$i]['worstJump'];
            $totalTrainingTime = $rows[$i]['totalTrainingTime'];
            $startingDate = $rows[$i]['startingDate'];

            // table row numbers. our index starts at 0, so we want to increment it by 1 to get valid row numbers.
            $tableRow = $i+1;

            // construct the JSON return from our data
            $jsonString = '{"Name":"'.$tableRow .'","Value":"|'.$charactersName.'|'.$highscoreFeet.'|'.$charactersFitnessLevel.'|'.$worstJump.'|'.$totalTrainingTime.'|'.$startingDate.'|"},';

            // append the JSON return with the new data
            $jsonBody=$jsonBody.$jsonString;

            // increase index and loop again if not at end of array.
            $i++;            
        }

        // construct the JSON response

        // this is the header of the JSON return. It will have to be adjusted to match whatever your app is expecting. We have to define this here to get the row count above.
        $jsonHeadher='{"Properties":[],"Name":"id948725_headers","Children":[{"Properties":[{"Name":"rowCount","Value":'.$arrlength.'},{"Name":"columnCount","Value":6},{"Name":"0-1-name","Value":"charactersName"},{"Name":"0-1-type","Value":1},{"Name":"0-2-name","Value":"highscoreFeet"},{"Name":"0-2-type","Value":2},{"Name":"0-3-name","Value":"charactersFitnessLevel"},{"Name":"0-3-type","Value":2},{"Name":"0-4-name","Value":"worstJump"},{"Name":"0-4-type","Value":2},{"Name":"0-5-name","Value":"totalTrainingTime"},{"Name":"0-5-type","Value":2},{"Name":"0-6-name","Value":"startingDate"},{"Name":"0-6-type","Value":1}],"Name":"id948725_headers","Children":[]},{"Properties":[';
        
        // this is the footer of the JSON return. Again it will have to be adjusted     to match whatever your app is expecting.
        $jsonFooter='],"Name":"id948725","Children":[]}]}';

        // removes an extra comma that the loop above leaves behind
        $jsonBody=rtrim($jsonBody, ",");

        // constructing the full JSON return
        $returnedJson=$jsonHeadher.$jsonBody.$jsonFooter;
        
        // write the JSON data so the app can read it.
        echo $returnedJson;    

    } // end of get

?>
 

Edited July 23, 2016 by Psycho

[benanamen]

You have a bigger problem. You just gave the entire world login credentials to your server. Bad move!

 

Besides that, your code is all kinds of wrong. Too much for me to want to get into. I will leave that for others.

Edited July 23, 2016 by benanamen

[mac_gyver]

i changed from mysql to mysqli

 

 

um. no you didn't. you still have several mysql_ statements in your code and the mysqli_ statements you do have are incorrect, which would be throwing php errors, if you had php's error_reporting/display_errors/log_errors settings set up correctly. you need make use of the php.net documentation for any php coding you do, so that you will know what each statement requires.

 

also, you should not be manually building JSON encoded data. there's a function to do that - json_encode().

[gemerson]

How to delet a topic?

I just noticed the info.

Pleas help!!!

[mac_gyver]

you would need to use the 'Report' link/button in the lower right hand corner of the first post and ask for a mod/admin to edit out the information.

 

you need to go and change your username and password in any case. this thread has been view by 20+ members/guests, plus has probably already been indexed by more than one search engine.

 

if you put your database credentials in a separate .php file and require it into your code, you won't ever be faced with problem of your credentials ending up in code you are posting.

 

edit: you should also be using prepared queries to supply data values to the sql queries. unfortunately, php messed up when producing the msyqli class and it is a pain in the rear to use with prepared queries. if you can, forget about using msyqli and use PDO instead.

Edited July 23, 2016 by mac_gyver

[benanamen]

Your credentials were also emailed to the 198 followers of this particular forum.