Casi todos los días tengo la tarea de insertar datos JSON en una base de datos relacional a través de PHP, al igual que con los datos JSON, algunos registros tienen ciertas columnas mientras que otros no, y esto tiende a ser un problema cuando se inserta en una tabla.
Si estoy insertando varios miles de estudiantes, un registro podría verse como
{"name": "Billy Jackson", "Height": 172, "DOB" : "2002-08-21"}Sin embargo, no es seguro que la altura o el DOB estén establecidos en ningún registro, lo que hago actualmente es algo así como
<?php foreach($records as $json){ $name = addslashes($json['name']); if(isset($json['Height']){ $height = $json['Height']; } else{ $height = "NULL" } if(isset($json['DOB']){ $dob = $json['DOB']; } else{ $dob = "NULL" } } $db->query("INSERT INTO table (name, height, dob) VALUES ('$name', $height, '$dob')");Como puede ver, esto no es elegante ni funciona para varios tipos, campos como DOB no aceptan NULL, ni enumeraciones.
¿Existe una solución integrada más elegante, solo para intentar insertar en columnas donde existe el valor en el JSON?
¿Es esto algo que manejan las declaraciones preparadas?
EDITAR
digamos que el registro de ejemplo anterior no tenía establecido el DOB, la declaración de inserción se vería así
"INSERTAR EN la tabla (nombre, altura, fecha de nacimiento) VALORES ('Billy Jackson', 172, 'NULL')"
Lo cual falla, si $dob se establece en nulo ($dob = null) si no está establecido, entonces la declaración de inserción se ve así
"INSERTAR EN la tabla (nombre, altura, fecha de nacimiento) VALORES ('Billy Jackson', 172, '')"
que falla
¿Por qué incluso incluir la columna dob? porque algunos registros tienen un dob y quiero que se incluyan en el inserto
Cadena vacía '' no es lo mismo que null . La cadena tampoco es "null" . Dado que su consulta cita explícitamente el contenido de la variable $dob , está citando la cadena null de modo que se convierta en "null" , que definitivamente no es nula. :)
Para evitar la necesidad de meterse con comillas (y la inyección SQL), querrá usar una declaración preparada, algo como esto:
$db->prepare('INSERT INTO table (name, height, dob) VALUES (?, ?, ?)');Luego, cuando vincula los valores, PHP se encargará automáticamente de qué campos necesitan comillas y cuáles no.
También tenga en cuenta que puede atajar esto:
if (isset($json['Height']){ $height = $json['Height']; } else { $height = "NULL" }En solo esto:
$height = $json['Height'] ?? null;Lo que eliminaría un montón de su código y haría que su vinculación fuera algo como esto:
$stmt->bind_param( 'sis', $json['name'], $json['Height'] ?? null, $json['dob'] ?? null );Debe comenzar por abordar los problemas en el diseño de su mesa.
Todas las columnas que DEBEN tener datos deben configurarse como NOT NULL y establecer un valor predeterminado, si corresponde. Puede que no sea apropiado tener un valor predeterminado para User Name , por ejemplo, así que no establezca uno.
Todas las columnas que PODRÍAN tener datos deben configurarse para aceptar NULL , con un valor predeterminado establecido según corresponda. Si no hay datos, el valor correcto generalmente debe ser NULL y debe establecerse como predeterminado.
Tenga en cuenta que las columnas DATE y ENUM pueden aceptar NULL si están configuradas correctamente.
Una vez que tenga las definiciones de columna correctas, puede generar una consulta INSERT basada en los valores reales que encuentre en su archivo JSON. Las reglas de integridad de datos que establezca en la definición de su tabla garantizarán que se ingresen los valores apropiados para cualquier fila que se cree con valores faltantes, o que la fila no se cree si faltan datos "imprescindibles".
Esto lleva a un código como este, basado en declaraciones preparadas por PDO:
$json = '{"name": "Billy Jackson", "Height": 172, "DOB" : "2002-08-21"}'; $columnList = []; $valueList = []; $j = json_decode($json); foreach($j as $key=>$value) { $columnList[] = $key; // interim processing, like date conversion here: // eg if $key == 'DOB' then $value = reformatDate($value); $valueList[] = $value; } // Now create the INSERT statement // The column list is created from the keys in the JSON record // An array of values is assembled from the values in the JSON record // This is used to create an INSERT query that matches the data you actually have $query = "INSERT someTable (".join(',',$columnList).") values (".trim(str_repeat('?,',count($valueList)),',').")"; // echo for demo purposes echo $query; // INSERT someTable (name,Height,DOB) values (?,?,?) // Now prepare the query $stmt = $db->prepare($query); // Execute the query using the array of values assembled above. $stmt->execute($valueList);Nota: es posible que necesite ampliar esto para manejar la asignación de claves JSON a nombres de columna, cambios de formato en campos de fecha, etc.