Is there any way to show an array in MySQL like this? ("One"=>"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);

can i just encode the array and insert it in the mySQL table without doing anything else? — EternalGrey, Apr 28 '19 at 17:36
Just insert the result of `$serializedArray = json_encode($r9);`, although please use [prepared statements](https://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php). — Nigel Ren, Apr 28 '19 at 17:38
You misunderstanding the point of serialize/unserialize, you need to use serialize when you storing it in db so, do `serilize($r9)` store in the db, next day when you want that array back to php do, select data from db, and do `unserialize($db['arr'])` — George G, Apr 28 '19 at 19:15
@miile7 the answer i accepted was the one that worked for me, sorry for not responding before, i was turning something in for work. — EternalGrey, May 01 '19 at 22:52

miile7 · Accepted Answer · 2019-04-28T19:27:08.827

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()).

Is there any way to show an array in MySQL like this? ("One"=>"1")

1 Answers1