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.

it is not advisable to use mysql_* methods, anyways try to use mysql_error() and know what was the error thrown from sql while inserting the values — Ayyappan Sekar, Aug 19 '13 at 13:16
Cast to int for IDs at the very least: $userid = (int) $_REQUEST['userid'];. Also look into preventing SQL injections. — James Poulson, Aug 19 '13 at 13:24
userid gets inserted but the svalueid does not get's inserted and shows 0 @JamesPoulson — iPhnQ, Aug 19 '13 at 13:31

Rob · Accepted Answer · 2013-08-19 14:10:56Z

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

?>

RiggsFolly · Answer 2 · 2013-08-19 13:48:37Z

up vote 1 down vote

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.

answered Aug 19 '13 at 13:48

RiggsFolly
23.9k93253

+1 I agree that if these were strings, he should be escaping them like you've outlined here. I assumed that he meant them to be numeric (because although his keys were strings, the values were not, and he doesn't even appear to be using the keys at all), so I suggested validating the values as is_numeric. But the concept is the same: Protect himself against SQL injection and incidental SQL errors. – Rob Aug 19 '13 at 14:02

@Rob He said they were JSON objects so I that means they have to be strings! – RiggsFolly Aug 19 '13 at 14:47

add a comment |

asked	2 years ago
viewed	1003 times
active	2 years ago

current community

your communities

more stack exchange communities

PHP MySQL using JsonString (Array) to insert record

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged php mysql json or ask your own question.

Hot Network Questions

current community

your communities

more stack exchange communities

PHP MySQL using JsonString (Array) to insert record

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged php mysql json or ask your own question.

Related

Hot Network Questions