Call to MySQL SP from Ajax/PHP returns null-ish values

Question

I've been pounding on this for a few days, so time to ask for help. I'm trying to use Ajax/PHP/MySQL to show only a subset of a table based on the user's selections in dropdown. The PHP code calls a MySQL stored procedure. The call I'm constructing is right, and if I echo it out and then copy it and run it as is from the phpMyAdmin MySQL console, I get exactly the results I expect. But from the PHP code that's called by Ajax, I instead see this result (echoed in Firebug, after I JSON_encode it):

{"current_field":null,"field_count":null,"lengths":null,"num_rows":null,"type":null}

The relevant part of the page itself is:

    <script>
    function updateActions() {
    var results = '';
    var success = false;
    var selectedIssues = getIssues();
    var fnargs = "GetActions|'" + selectedIssues + "'";

    $.ajax({
        url: 'retrievedata.php',
        type: "POST",
        async:false,
        data: {"functionname":"getactions", "arguments":fnargs},
        dataType: "JSON",
        complete: function (obj, textStatus) {
                      if( (obj.error != '') ) { 
                        alert(JSON.parse(obj));
                        $("#testresult").text(textStatus);
                      }
                      else {
                        $("#testresult").text("Error");
//                      console.log(obj.error);
                      }
                      success = true;
                },
         error: function(textStatus, errorThrown) {
             success = false;
             $("#testresult").text('Error occurred: '.textStatus);
         }            
        })

    };
    </script>

Two notes. First, the getIssues script it calls returns the expected value. Second, I haven't actually written the right code to process the result once I get it. Still trying to get the right result back to the page.

Page retrievedata.php looks like this:

<?php

    include "dbfns.php";

    $aResult = array();
    $returnval = 'before tests';

    if( !isset($_POST['functionname']) ) { 
        $aResult['error'] = 'No function name!'; 
        }

    if( !isset($_POST['arguments']) ) { 
        $aResult['error'] = 'No function arguments!'; 
        }

    if( !isset($aResult['error']) ) {

        $functionName = $_POST['functionname'];
        $argsarray = explode('|', $_POST['arguments']); 
        $argcount = count($argsarray);
        $returnval = 'before switch';

        switch($_POST['functionname']) {
            case 'getactions':
               if( $argcount < 2 ) {
                   $returnval = 'too few arguments';
               }
               else {
                   $returnval = 'in else';
                   $returnval = getactions($argsarray[0], $argsarray[1]);
               }
               break;

            default:
               $returnval = 'function not found';
               break;
        }

    }

    return $returnval;
    ?>

The relevant portions of dbfns.php (with identifying data and credentials removed, of course) are:

<?php

function connect2db() {
    $hostname = "XXX";
    $username = "XXX";
    $password = "XXX";
    $database = "XXX";
    $conn = mysqli_connect($hostname, $username, $password, $database);

    if( $conn == false ) {
         echo "Connection could not be established.<br />";
         die( print_r( myslqi_connect_error(), true));
    }

    return $conn;
}

function getactions($spname, $params, $errorstring = 'Unable to retrieve requested data') {
    $conn = connect2db();
    $query = "CALL ".$spname."(".$params.")";
    echo $query."\r\n";
    $result = mysqli_query($conn, $query);

    if ($result == false) {
        $errmessage = mysqli_error($conn);
        $allresult = $errmessage;
        echo $errmessage;
        die( print_r( mysql_error(), true));
    }
    else {
        echo "In else case\r\n";    
        $allresult = json_encode($result);              
    }           

    echo $allresult;
    return $allresult;
}

?>

I have another PHP function in retrievedata that calls the same MySQL SP, but not from Ajax and it returns the expected result, so I'm pretty confident that the SP does what I expect.

I think there must be something I don't get about how to do all this from Ajax.

Edit: Just want to add that I've tried success rather than complete in the ajax call, and _GET rather than _POST. No change in results.

Does the other function that work use connect2db also (wondering if user doesn't have execute rights on sp?) — Rick Hodder, Jan 23 '17 at 16:14
Yes, the other function is just a variant of getactions. The calling code is identical. — Tamar E. Granor, Jan 23 '17 at 19:53

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

That looks like it's serializing the result object from mysqli_query(). I'm not sure what it does internally, but it may not return the actual resulting data until you enumerate/fetch the results.

See this example on one way to convert it to a JSON result.

edited May 23 '17 at 11:53

Community

1
1

answered Jan 25 '17 at 12:40

Paul Mrozowski

6,604
9
34
47

1

_For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object._ He needs to fetch the result. `$allresult = json_encode(mysqli_fetch_assoc($query));` – Daniel W. Jan 25 '17 at 12:46
Wrapping the parameter in mysqli_fetch_assoc before calling json_encode did the trick. (Apologies for the first wrong response; messed the parameter up.) – Tamar E. Granor Jan 25 '17 at 15:55

Call to MySQL SP from Ajax/PHP returns null-ish values

1 Answers1