有没有办法像这样在MySQL中显示数组？ （“一” =>“1”）

Question

I'm trying to save an array in a MySQL table, I serialize it and it shows something like a:3:{s:8:"One";s:1:"1";s:6:"Two";s:2:"2";... but I don't want it like this, I want something like this {One = 1, Two = 2} or something similar without those weird characters "a:4", "s:3", I was trying to look up and I was told to deserialize, but it isn't the solution i'm looking for as it shows something like {1,2}. Is there a way to make it look like I'm saying?

This is what I tried to do to deserialize:

$r9 = array("One"=>"1", "Two"=>"2", "Three"=>"3");


    $serializedArray = serialize($r9);
$decoded = unserialize($serializedArray);
    $respuestaCompleta = $cadena_equipo = implode(",", $decoded);;

    $conn = new mysqli($servername, $username, $password, $dbname);
    $sql = "INSERT INTO encuesta (id, pregunta, respuesta) VALUES ('$id', '$q9', '$respuestaCompleta')";
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }


    mysqli_close($conn);

Answer 1

I think you are looking for the following:

$r9 = array("One"=>"1", "Two"=>"2", "Three"=>"3");

$serializedArray = json_encode($r9); // or use serialize() here

$conn = new mysqli($servername, $username, $password, $dbname);
$sql = "INSERT INTO encuesta (id, pregunta, respuesta) VALUES ('".$id."', '".$q9."', '".$serializedArray."')";
if($conn->query($sql) === true){
    echo "New record created successfully";
}
else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}


mysqli_close($conn);

To get the real array again use this code:

$conn = new mysqli($servername, $username, $password, $dbname);
$sql = "SELECT respuesta FROM encuesta;";
$result = $conn->query($sql);
$row = $result->fetch_array();

$r9 = null;
if(is_array($row) && count($row) > 0){
    $r9 = json_decode($row[0], true); // or unserialize() if serialize was used
}

---

I recommend to use json_encode(). serialize() is a php specific command. This means that other programming languages, which may interact with your database in future, cannot use the data. In addition you json gives you a more genral way. In my opinion it is easier to read. ^{serialize() is more powerful than json_encode(). But you because you are not using objects this is no extra you need.}

In general you are converting your array to a string (with eigher the serialize() or the json_encode() method). The database cannot handle an array because there are no arrays in MYSQL. Also this is a structure of the php language.

The generated string is equal to the array representation. MYSQL can handle strings so this string can then be saved to the database.

If you are loading the value from the database again you will receive the saved string. This string can then be converted back to an array by using the opposite command (json_decode() or unserialize()).