Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2016
    Posts
    2

    Unanswered: Unable to UPDATE mysql table from a function, although successful query response

    I am using PHPMyAdmin for my database. When a button on my html page is clicked, the following is executed:
    Code:
    var stopID = 10; // Sample
    var stopPOV = "129.29,158.58"; // Sample
    
    $.ajax({
        url: "getFromDB.php",
        type: "post",
        dataType: 'json',
        data: {
            action: "setStopPOV",
            stopID : stopID,
            stopPOV : stopPOV
        },
        success: function(data) {
            //alert(data);
        }
    });
    My getFromDB.php is such:
    PHP Code:
    <?php
        
    require_once('connect.php');
        require_once(
    'db_functions.php');

        if (isset(
    $_POST["action"]) && !empty($_POST["action"])) {
            
    $action $_POST["action"];

            switch (
    $action) {
                case 
    "setStopPOV":
                    
    $stopID $_POST['stopID'];
                    
    $stopPOV $_POST['stopPOV'];

                    
    setStopPOV($stopID$stopPOV);
                    break;
            }
        }
    ?>
    Finally, setStopPOV(...) function inside db_functions.php is as below:
    PHP Code:
    <?php
        
    function setStopPOV($stopID$stopPOV) {
            global 
    $dbc;        // Set in connect.php

            
    $query  "UPDATE stop ";
            
    $query .= "SET STOP_POV = '{$stopPOV}' ";
            
    $query .= "WHERE STOP_ID = '{$stopID}'";

            
    $result = @mysqli_query($dbc$query) or die("Error updating record: " mysqli_error($dbc));

            if (
    $result) {
                
    file_put_contents('function_result.txt'"Record updated successfully" PHP_EOLFILE_APPEND);
            } else {
                
    file_put_contents('function_result.txt'"Error updating record" PHP_EOLFILE_APPEND);
            }
        }
    ?>
    This outputs 'Record updated successfully' info the txt file, but nothing is updated in the database.

    However, if getFromDB.php is like this:
    PHP Code:
    <?php
        
    require_once('connect.php');
        require_once(
    'db_functions.php');

        
    // NEW CODE ADDED START
        
    $id 10;
        
    $pov "129.29,158.58";

        
    $qry  "UPDATE stop ";
        
    $qry .= "SET STOP_POV = '{$pov}' ";
        
    $qry .= "WHERE STOPID = '{$id}'";

        
    $result = @mysqli_query($dbc$qry) or die("Error updating record: " mysqli_error($dbc));

        if (
    $result) {
            
    file_put_contents('function_result.txt'"Record updated successfully" PHP_EOLFILE_APPEND);
        } else {
            
    file_put_contents('function_result.txt'"Error updating record" PHP_EOLFILE_APPEND);
        }
        
    // NEW CODE ADDED FINISH

        
    ...

        
    // The rest is previous code as shown above
        
    if (isset($_POST["action"]) && !empty($_POST["action"])) {
            
    $action $_POST["action"];

            switch(
    $action) {
                
    // Code
            
    }
        }
    ?>
    And I go to the page directly (i.e. localhost/getFromDB.php), The txt file has 'Record updated successfully' AND the database is updated!

    It is important to note, that my db_functions.php contains other functions which retrieve info from the DB and they work as expected. For example, this is one such function in the db_functions.php:
    PHP Code:
    function getRouteList() {
        global 
    $dbc;

        
    $query  "SELECT RTE_NAME, TRAV_DIR ";
        
    $query .= "FROM route";

        
    $result = @mysqli_query($dbc$query) or die("Error in Selecting " mysqli_error($dbc));

        
    // Do whatever I want with the retrieved data

    I have tried everything to get the update to work, but I haven't been able to. What am I missing?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I would expect this to be a data error ie you are not getting the values you expect
    So go through each section of code step by step and prove you are getting what you think you should be.

    My immediate suspiscion is that either the if or switch in getFromDB.php means the update is never called. So prove all values
    Make certain to turn up error logging to the highest value
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can prove the actual update works by calling setStopPOV function from some test code (making certain you provide suitable values). this is basic debugging skills. I found PHP to be an absolute pain to debug until I started using integrated debuggers. although integrated debuggures make loife easier you can still go back to old school techniques and write values to log files
    during development always do something with if's or uncatered for switch statements
    so I tend to write to the screen of logfile

    if (boolean expression)
    'do soemthing
    else
    write to logifle (boolean expression failed, values were....
    endif

    on a switch use the default to do somethign similar

    just in temrs of code style I tend to leave if then else's in once code is proven to demonstrate to the poor sap followoing on from me that its deliberate

    eg
    if ($age >=18)
    { //do something
    //} else //don't care as we are only EVER interested in people over 18...
    //{
    }
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Mar 2016
    Posts
    2
    I figured it out. One query has 'STOP_ID' and the other has 'STOPID'

  5. #5
    Join Date
    Mar 2016
    Posts
    3
    It might be that you get a good query result because the query is executed properly, but it was not the query you wanted to have been executed.
    In such cases I would try to output for debugging the $query string, just for checking.

    My theory is that $stopID is empty and the SQL statement did not find the record you wanted to update.
    Maybe there is an extra space in your ajax code? In front of the colons when you pass on the parameters for the action?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •