PHP MySQL using JsonString (Array) to insert record

Question

I want to make a PHP file to insert array data in my server table. As i have multiple values selected so i have taken an array and created jsonstring from my iphone application to post value in PHP file and insert data in server table. I don't know what i am doing wrong and the value is not getting inserted in the server DB PHP/MYSQl.

I am getting Error message = "An error occurred.";

I have also tried declaring a variable with same array(used json_encode and decode also) data which i want to insert but shows same error.

<?php

$response = array();

if (isset($_REQUEST['userid']) && isset($_REQUEST['svalueid'])) {
    $userid = $_REQUEST['userid'];
    $svalueid = json_decode($_REQUEST['svalueid']);

    include 'connect.php'; 

    $db = new DB_CONNECT();

    $result = mysql_query("SELECT userid, svalueid FROM users WHERE userid = '$userid'");

    if (mysql_num_rows($result) > 0) {

        $result = mysql_query("DELETE FROM uses WHERE userid = '$userid'");

        foreach($svalueid as $value) {
            $result = mysql_query("INSERT INTO users(id, userid, svalueid) VALUES('','$userid', '$value')");
        }
    } else {
        foreach($svalueid as $value) {
            $result = mysql_query("INSERT INTO users(id, userid, svalueid) VALUES('','$userid', '$value')");
        }
    }

    if ($result) {
        $response["success"] = 1;
        $response["message"] = "successful.";
        echo json_encode($response);
    } else {
        $response["success"] = 0;
        $response["message"] = "An error occurred.";
        echo json_encode($response);
    }
} else {
    $response["success"] = 0;
    $response["message"] = "Field's missing";
    echo json_encode($response);
}
?>

To check if i am getting correct string i used this php file ..

<?php
$response = array();

$userid = $_REQUEST['userid'];
$test="'svalueid' : [
    '0' : 1,
    '1' : 2
]";
$xyz = json_encode($test);
$vb = json_decode($test);
$pbk=array("1"=>"abc","2"=>"cck");
$pk=json_encode($pbk);
$ck=json_decode($pk);

include 'db_connect.php'; 

$db = new DB_CONNECT();


echo "<pre>";
print_r($_REQUEST['svalueid']);
echo "<br>";
print_r($vb);
echo "<br>";
print_r($ck);
echo "<br>";
echo "hi";die; 

if ($result) {
    $response["success"] = 1;
    $response["message"] = "Success.";
    echo json_encode($response);
} else {
    $response["success"] = 0;
    $response["message"] = "An error occurred.";
    echo json_encode($response);
}

} else {
    $response["success"] = 0;
    $response["message"] = "Value is missing";
    echo json_encode($response);
}
?>

The response i get in my application on console shows this :

    response: <pre>{
  \"sidselected\" : {
    \"0\" : 1,
    \"1\" : 2
  }
}<br><br>stdClass Object
(
    [1] => abc
    [2] => cck
)
<br>hi

Value that i have in userid = 1 and svalueid = "svalueid" : { "0" : 1, "1" : 2 }in jsonString is like this

json string going on server is {
  "svalueid" : {
    "0" : 1,
    "1" : 2
  }
}

i am not able to insert the values 1 and 2 in my DB table.

Is the format of svalueid correct ? Please help how should i enhance my php code.

Answer 1

I'd personally be inclined to go all-in on the JSON request, including both the userid and the svalueids part of the JSON request. I'd also make svalueids an array, not a dictionary (unless, of course, you were actually going to use the keys passed).

For example, I might make the request like so:

{
    "userid"    : 1,
    "svalueids" : [4, 5]
}

For example, if generating the request in iOS:

NSURL *url = [NSURL URLWithString:@"http://your.url.here.com/submit.php"];
NSMutableURLRequest *request = [NSMutableURLRequest requestWithURL:url];

NSDictionary *requestDictionary = @{@"userid"    : @1,
                                    @"svalueids" : @[@4, @5]};

NSData *requestData = [NSJSONSerialization dataWithJSONObject:requestDictionary options:0 error:nil];

[request setHTTPBody:requestData];
[request setHTTPMethod:@"POST"];
[request addValue:@"text/json; charset=utf-8" forHTTPHeaderField:@"Content-Type"];

NSOperationQueue *queue = [[NSOperationQueue alloc] init];
[NSURLConnection sendAsynchronousRequest:request queue:queue completionHandler:^(NSURLResponse *response, NSData *data, NSError *error) {
    if (error) {
        NSLog(@"%s: error: %@", __FUNCTION__, error);
        return;
    }
    if (data) {
        NSDictionary *responseDictionary = [NSJSONSerialization JSONObjectWithData:data options:0 error:nil];
        NSLog(@"responseDictionary=%@", responseDictionary);
    }
}];

You could then have PHP to parse that JSON request (where the JSON formatted request is the actual HTTP body):

<?php

$handle = fopen("php://input", "rb");
$raw_post_data = '';
while (!feof($handle)) {
    $raw_post_data .= fread($handle, 8192);
}
fclose($handle);

// I'm expecting a request in the form of
//
// {"userid":1, "svalueids":[1,2]}

$request_data = json_decode($raw_post_data, true);
if (is_null($request_data)) {
    echo json_encode(array("success" => false, "message" => "Request not valid formed JSON"));
    exit;
}

// get userid

$userid = $request_data["userid"];

if (is_null($userid) || !is_numeric($userid)) {
    echo json_encode(array("success" => false, "message" => "Userid not provided or not numeric"));
    exit;
}

// get array of svalueids

$svalueids = $request_data["svalueids"];

if (!is_array($svalueids)) {
    echo json_encode(array("success" => false, "message" => "svalueids array not provided"));
    exit;
}

$dbuserid = ...;
$dbpassword = ...;
$dbdatabase = ...;
$con = mysqli_connect("localhost", $dbuserid, $dbpassword, $dbdatabase);

// Check connection
if (mysqli_connect_errno())
{
    echo json_encode(array("success" => false, "message" => mysqli_connect_error()));
    exit;
}

foreach ($svalueids as $svalueid) {
    if (!is_numeric($svalueid)) {
        echo json_encode(array("success" => false, "message" => "svalueid is non- numeric"));
        mysqli_close($con);
        exit;
    }

    $sql = "INSERT INTO users(userid, svalueid) VALUES ({$userid}, {$svalueid})";

    if (!mysqli_query($con, $sql))
    {
        echo json_encode(array("success" => false, "message" => mysqli_error($con)));
        mysqli_close($con);
        exit;
    }
}

mysqli_close($con);

echo json_encode(array("success" => true));

?>

Answer 2

You have double quotes all over the place in svalueid = "svalueid" : { "0" : 1, "1" : 2 } this is likely your problem.

try

$fixedValue = mysql_real_escape_string($value);

$result = mysql_query("INSERT INTO users(userid, svalueid) VALUES('$userid', '$fixedValue')");

Also if ID is a AUTO_INCREMENT field you dont need to add it to the INSERT query, it is implied and will be incremented and inserted automatically.

Apply this logic to all your INSERTS and UPDATES.

Also should have error processing code after each mysql_query() and it should be displaying mysql_erno() and mysql_error() at least. Thats would de-mistify your errors as they happen.