Trouble with AJAX array interacting with MySQL table

Question

On my webpage I have a number of buttons that relate to table columns in a MySQL database (the id of the buttons and the table column names are exactly the same).

What I would like to do is capture the ids in an array and via AJAX, send back an array of values associated with those table columns with the same names. The AJAX call is no problem, however I'm struggling to understand how to convert the data for use with PHP in my server code.

My code is as follows:

jQuery:

var username = USERNAME;
var array = [1, 2, 3, 4, 5];

$.ajax ({
    cache: false,
    url: PHP_FILE,
    dataType: "json",
    type: "post",
    data: {username: username, array: array},
    success: function(data) {
        console.log(data);
    }
});

PHP:

<?php
    $dbc = DATABASE CONNECTION;

    $username = mysqli_real_escape_string($dbc, trim($_POST['username']));
    $auth = mysqli_real_escape_string($dbc, trim($_POST['array']));

    $array = json_decode($auth, true);

    $query = "SELECT auths.".$array." FROM details INNER JOIN auths USING (code) WHERE un = '".$username."'";
    $data = mysqli_query($dbc, $query);
    $row = mysqli_fetch_array($data);

    echo(json_encode($row));
?>

Presumably json_decode is not the right route, however I'm struggling to understand what the alternative would be.

json encode your input array and post over the json'd string instead of an actual array. — Dave
– Dave, Commented Nov 22, 2013 at 16:06

Josué · Accepted Answer · 2013-11-28 03:10:15Z

2

Your JS part needs to be updated

Replace this block:

var $username = USERNAME;
var $array = array(1, 2, 3, 4, 5);

with:

var username = 'USERNAME';
var values = [1, 2, 3, 4, 5];

For the PHP part try with this (assuming you're using PHP >= 5.3):

<?php

$username = trim($_POST['username']);
$fields = isset($_POST['array']) && is_array($_POST['array']) ? $_POST['array'] : array();

$dbc = mysqli_connect ('HOST', 'USERNAME', 'PASSWORD', 'DATABASE', 3306);

$username = mysqli_real_escape_string($dbc, $username);
$fields = array_map(function ($field) use($dbc) {
    $field = mysqli_real_escape_string($dbc, trim($field));
    return 'authors.'.$field;
}, $fields) ;
$fields = implode(',', $fields);
$query = sprintf("SELECT %s FROM details INNER JOIN auths AS authors USING (code) WHERE un = '%s'", $fields, $username);

$rows = array();
if($result = mysqli_query($dbc, $query)) {
    while($row = mysqli_fetch_assoc($result)) {
        $rows[] = $row;
    }
}

echo json_encode($rows);

NOTE: You should apply extra validations assure the username and fields are not empty, as well as receiving incorrect field names as query will throw errors if any of these happens and might expose information about your code and/or database.

edited Nov 28, 2013 at 3:10

answered Nov 22, 2013 at 17:06

Josué

1184 bronze badges

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

2 Comments

Ryan Over a year ago

Thanks for your answer. Using this solution, console returns an empty array, which is better than null as I was getting back before, but it's still not quite there. I checked to ensure that the array is being populated prior to AJAX call, which it is, therefore I assume there is a failure somewhere within the PHP script itself?

Ryan Over a year ago

Ah, I got it: authors should have read auths, the name of the MySQL database. Great answer, works perfectly, thanks so much.

Patato · Accepted Answer · 2013-11-22 16:49:52Z

JQuery

        var username = USERNAME;
        var array = new Array(1, 2, 3, 4, 5);


        $.ajax ({
            cache: false,
            url: PHP_FILE,
            dataType: "json",
            type: "post",
            data: {username: $username, array:  JSON.stringify(array)},
            success: function(data) {
                console.log(data);
            }
        });

PHP

<?php
    $dbc = DATABASE CONNECTION;

    $username = mysqli_real_escape_string($dbc, trim($_POST['username']));
    $auth = mysqli_real_escape_string($dbc, trim($_POST['array']));

    $array = json_decode($auth, true);

    $query = "SELECT auths.".$array." FROM details INNER JOIN auths USING (code) WHERE un = '".$username."'";
    $data = mysqli_query($dbc, $query);
    $row = mysqli_fetch_array($data);

    echo(json_encode($row));
?>

Krish R · Accepted Answer · 2013-11-24 10:05:41Z

0

Can you try this,

        var $username = 'USERNAME';
        var $array = new Array(1, 2, 3, 4, 5);


        $.ajax ({
            cache: false,
            url: PHP_FILE,
            dataType: "json",
            type: "post",
            data: {username: $username, array: $array},
            success: function(data) {
                console.log(data);
            }
        });

edited Nov 24, 2013 at 10:05

answered Nov 22, 2013 at 16:42

Krish R

22.7k7 gold badges54 silver badges60 bronze badges

2 Comments

Ryan Over a year ago

If I prefix the variables with a dollar sign in that way, I get a '$username variable not found' error message.

Krish R Over a year ago

$username = "your username value here";

Collectives™ on Stack Overflow

Trouble with AJAX array interacting with MySQL table

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related