How to obtain and insert auto-incremental id from one table in another

Question

How to obtain and insert auto-incremental id from one table in another

Navigation

#1 by (0 votes)

0

I have been asked to do in PHP a script that automatically inserts values from a csv to the database, but although I have some idea of PHP (little thing, I'm learning on the fly) I have no idea of MySQL.

The script works fine, but the responses have a id_pregunta field that remains at 0 after importing them from the csv. So my question is:
How can I extract the id from the table questions and give it to the 4 answers inserted in the table?

I mean that the last 4 answers have the same id_pregunta .

At the moment I have this:

<?php

//conexiones, conexiones everywhere
ini_set('display_errors', 1);
error_reporting(E_ALL);
$db_host = 'localhost';
$db_user = '**';
$db_pass = '**';

$database = '**';
if (!@mysql_connect($db_host, $db_user, $db_pass))
    die("No se pudo establecer conexión a la base de datos");

if (!@mysql_select_db($database))
    die("base de datos no existe");
    if(isset($_POST['submit']))
    {
        //Aquí es donde seleccionamos nuestro csv
         $fname = $_FILES['sel_file']['name'];
         echo 'Cargando nombre del archivo: '.$fname.' <br>';
         $chk_ext = explode(".",$fname);

         if(strtolower(end($chk_ext)) == "csv")
         {
             //si es correcto, entonces damos permisos de lectura para subir
             $filename = $_FILES['sel_file']['tmp_name'];
             $handle = fopen($filename, "r");

             $data = fgetcsv($handle, 1000, ",");
             $sql1 = "INSERT into preguntas(pregunta, pregunta_asturiano, juego, dificultad, url_imagen, url_video) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]')";
             mysql_query($sql1) or die('Error: '.mysql_error());
                while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
                    $sql2 = "INSERT into respuestas(respuesta, respuesta_asturiano, correcta) values('$data[0]','$data[1]','$data[2]','$data[3]')";
                    mysql_query($sql2) or die('Error: '.mysql_error());
                }
           }
         else
         {
            //si aparece esto es posible que el archivo no tenga el formato adecuado, inclusive cuando es cvs, revisarlo para             
//ver si esta separado por " , "
             echo "Archivo invalido!";
         }
    }

?>

mysql php

asked by DarknessDragon 08.10.2018 в 09:47

source

1 answer

Middleware - Laravel Relate tables with string type fields with laravel

score 0 · Answer 1

As you have discovered, the ID of the question you get right after inserting it. But that script still has problems, first because you occupy the discontinued connector mysql and second because you are interpolating variables directly in the query. You should suso the extensions mysqli or PDO Mysql and do it more or less as follows:

I make the following assumptions:

The question table has a self-identical id. When you insert in this you omit that ID because MySQL already knows that by default it has to insert the following ID.
The response table has auto-incremental in id and you also omit it in the insert.
your CSV has a question and four answers and always has that structure.

Then:

<?php

// Abres la conexión
$mysqli = mysqli_connect('localhost', 'my_usuario', 'mi_contraseña', 'mi_base');

$filename = $_FILES['sel_file']['tmp_name'];
$handle = fopen($filename, "r");

$data = fgetcsv($handle, 1000, ",");
// Preparas la inserción de la pregunta omitiendo el campo id:
$sql1 = 'INSERT into preguntas(pregunta, pregunta_asturiano, juego, dificultad, url_imagen, url_video) values (?,?,?,?,?,?)';


$stmt = $mysqli->prepare($sql1);
$stmt->bind_param('ssssss', $data[0], $data[1], $data[2], $data[3], $data[4], $data[5]);

$stmt->execute();
$id_pregunta = $mysqli->insert_id;
$stmt->close();
// ya tienes el $id_pregunta


// Preparas la inserción de la pregunta omitiendo el campo id_respuesta, pero sí estableciendo el id_pregunta.
$sql2 = 'INSERT into respuestas(id_pregunta, respuesta, respuesta_asturiano, correcta) values(?,?,?,?,?)';

$stmt2 = $mysqli->prepare($sql2);
$stmt2->bind_param(dssss, $id_pregunta, $data[0], $data[1], $data[2], $data[3]);

while (($data = fgetcsv($handle, 1000, ',')) !== false) {
    $stmt2->execute(); // ya se bindeó la inserción a los componentes de $data, no necesitas repetir el 'prepare'
}
$stmt2->close();