解析字典对象并通过PHP创建MySQL表

Question

Currently, I have this dictionary object in my PHP script that was posted to my PHP implemented server through my iOS app:

{"command":"save","classname":"GameScore","cheatMode":"0","playerName":"SeanPlott","score":"1337"}

Now I need to create a MySQL table based off the data...

Here is my attempt to parse the data but all i get are the objects, not the keys...

foreach( $text as $stuff ) {
    if( is_array( $stuff ) ) {
        echo json_encode($stuff);
        foreach( $stuff as $thing ) {
            echo json_encode($thing);
        }
    } else {
        echo json_encode($stuff);
    }
}

Heres the result... "save""GameScore""0""SeanPlott""1337"

This is what I plan on making as a query

$result = query("INSERT INTO '%s'('%s', '%s', '%s', '%s') 
                    VALUES('%s','%s','%s','%s')", $classname, $key1, $key2, $key3, $key4, 
                                                    $object1, $object2, $$object3, $object4);

But the query should be dynamic, since the dictionary object can have 1 or many keys/objects...

So I figure I need to implement a parsing for loop that generates the query within the array and outputs a string which is the query...

Anyone have any ideas on how to fix my parser?

EDIT: heres my query function to handle MYSQL

function query() {
    global $link;
    $debug = false; 
    $args = func_get_args();
    $sql = array_shift($args);

    for ($i = 0; $i < count($args); $i++) {
        $args[$i] = urldecode($args[$i]);
        $args[$i] = mysqli_real_escape_string($link, $args[$i]);
    }

    $sql = vsprintf($sql, $args);

    if ($debug) print $sql;

    $result = mysqli_query($link, $sql);

    if (mysqli_errno($link) == 0 && $result) {  
        $rows = array();

        if ($result !== true)
            while ($d = mysqli_fetch_assoc($result)) 
                array_push($rows,$d);

        return array('result' => $rows);

    } else {
        return array('error' => 'Database error');
    }
}

EDIT: Took me a while and a lot of help but I completed it... It takes care of the case of spaces in values and adds '`' characters around the keys if there corresponding value contains a space...

$raw_data = '{"command":"save","classname":"GameScore","cheatMode":"0","playerName":"Sean Plott","score":"1337"}';
$data = json_decode($raw_data, true);
$columns = array_slice(array_keys($data), 2);
array_shift($data);
$table_title = array_shift($data);

$values = array();
$num = 0;
foreach($data as $key => $value) {
    if(strpos($value, " ") !== false) $columns[$num] = "`".$columns[$num]."`";
    $num = $num + 1;
    $values[] = (!is_numeric($value)) ? "'".$value."'" : $value;
}

$final_statement = "INSERT INTO " . $table_title . " (".implode(', ', $columns).") VALUES (".implode(', ', $values).")";
echo $final_statement;

If anyone sees any way to optimize this or make it cleaner... please feel free to post something!

Thanks again for everyones input!

Answer 1

You could just use a simple json_decode() and implode() to format it and prepare to insertion. Consider this example:

$raw_data = '{"command":"save","classname":"GameScore","cheatMode":"0","playerName":"SeanPlott","score":"1337"}';
$data = json_decode($raw_data, true);
$columns = array_keys($data); // get the columns

$final_statement = "INSERT INTO `table` (".implode(', ', $columns).") VALUES ('".implode("','", $data)."')";
echo $final_statement;
// outputs: INSERT INTO `table` (command, classname, cheatMode, playerName, score) VALUES ('save','GameScore','0','SeanPlott','1337')

EDIT: Suppose your columns has int types (especially the ones that has numeric values). For whatever reason it doesn't work, because of a mismatch, if so, you could probably do this. Consider this example:

$raw_data = '{"command":"save","classname":"GameScore","cheatMode":"0","playerName":"SeanPlott","score":"1337"}';
$data = json_decode($raw_data, true);
$columns = array_keys($data); // get the columns

$values = array();
foreach($data as $key => $value) {
    // if not numeric, add quotes, if not, leave it as it is
    $values[] = (!is_numeric($value)) ? "'".$value."'" : $value;
}

$final_statement = "INSERT INTO `table` (".implode(', ', $columns).") VALUES (".implode(', ', $values).")";
echo $final_statement;
// outputs: INSERT INTO `table` (command, classname, cheatMode, playerName, score) VALUES ('save', 'GameScore', 0, 'SeanPlott', 1337)
// Note: numbers has no qoutes