php ajax works but not on localhost

Question

I recently inherited a project at work and I'm trying to set up a local instance on my localhost so I can work with it and not affect the live site. The problem is that some of the ajax calls are giving me errors on response. id is a user id and in the working instance this returns any row in the permissions table that has an id matching the one in the users table. This is only an issue on localhost. Any advice on why this is happening appreciated. Thanks.

Ajax call

 $.ajax({
    type: 'POST',
    url: '../wwwroot/models/apm/CheckAdmin.php',
    data: {
        eid: id
    },
    dataType: 'json',
    success: function (data) {
        //console.log(data);
        if (data[0].status == 2) {
            ret = true;
            $('#devTimeGrp').show();

            $('#mngTab').show();
            console.log("anAdmin............");
        }
        else {
            ret = false;
            $('#devTimeGrp').show();
            $('#mngTab').hide();
            console.log("notAdmin......");
            //$('#mngTab').show();
        }
    },
    error: function (XMLHttpRequest, textStatus, errorThrown) {
    }
});

PHP

<?php
include("../db-settings.php");

$eid = $_POST['eid'];

echo $eid;

$sql="call CheckAdmin('$eid')";

$result = $mysqli->prepare($sql);

$result->execute();
$result->bind_result($col1, $col2, $col3);

while($result->fetch()){
    $out[] = array( 'id' => $col1,
                    'user' => $col2,
                    'status' => $col3);
}
echo json_encode($out);
$result->close();
?>

Stored procedure

CREATE DEFINER=`myDB`@`%` PROCEDURE `CheckAdmin`(IN p_eid CHAR(36))
BEGIN
SELECT * FROM user_permission WHERE (SELECT id FROM users WHERE uid = p_eid) = user_id;
END

Error

Warning: mysqli_stmt::bind_result(): Number of bind variables doesn't match number of fields in prepared statement

This error is telling you that when you call the bind_result function, you're trying to bind more variables than what actually is returned — IlGala
– IlGala, Commented Oct 23, 2015 at 16:05
Or less variables. BTW, you should use bind_param when making the query, instead of doing variable substitution in the SQL. — Barmar
– Barmar, Commented Oct 23, 2015 at 16:06
I was using the wrong username lol. Thanks for the advice tho. — PaulB
– PaulB, Commented Oct 23, 2015 at 16:44

Barmar · Accepted Answer · 2015-10-23 16:10:17Z

1

Change the procedure so that it only returns the 3 columns you're binding. Also, I recommend using a JOIN instead of the subquery, as MySQL is notoriously poor at optimizing that syntax.

SELECT p.id, p.user, p.status
FROM user_permissions AS p
JOIN users AS u ON u.id = p.user_id
WHERE u.uid = p_eid\

If you can't change the procedure, you need to change the PHP so that it binds as many variables as the number of columns in the user_permissions table.

And to avoid SQL injection problems, use bind_param when making the query:

$sql = "CALL CheckAdmin(?)";
$result = $mysqli->prepare($sql);
$result->bind_param("s", $eid);
$result->execute();

answered Oct 23, 2015 at 16:10

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

3 Comments

PaulB Over a year ago

Thanks, I had already thought of this. Those are the only three columns in the table. Thats why it seems odd to me. It should be returning a matching number of columns and on the web server it works.

Barmar Over a year ago

My guess is your table schema is different on localhost than it is on the server.

PaulB Over a year ago

Thanks, yes I just figured it out and thats pretty close. I was using the wrong username in my stored procedure. Thanks

Imvydao · Accepted Answer · 2015-10-23 16:41:58Z

0

Please try SELECT id,user,status FROM user_permission instead of SELECT *
If the first not working

var_dump($col1); var_dump($col2); var_dump($col3); Maybe a variable is array.

answered Oct 23, 2015 at 16:41

Imvydao

262 bronze badges

1 Comment

PaulB Over a year ago

Thanks, Had already tried this. It turned out to be a wrong username.

Collectives™ on Stack Overflow

php ajax works but not on localhost

Ajax call

PHP

Stored procedure

Error

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Ajax call

PHP

Stored procedure

Error

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related