CODEWITHSUNDEEP
phpjquerymysqlajaxjson
Ryan
Ryan

Reputation: 787

Trouble with AJAX array interacting with MySQL table

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.

Upvotes: 1

Views: 422

Answers (3)

Josu&#233;
Josu&#233;

Reputation: 118

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.

Upvotes: 2

Krish R
Krish R

Reputation: 22711

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);
            }
        });

Upvotes: 0

Patato
Patato

Reputation: 1472

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));
?>

Upvotes: 0

Related Questions