Is this INSERT syntactically correct? (PHP script trying to import into MySQL with a concat() function and duplicated values)

Question

I'm trying to import data from a csv file into a blank database. I'm using a php script for that.

I'm not concerned about the security of the query at the moment, because I'm the only one using the script. I just created a small testing script for myself.

The csv file has duplicated items.

It won't insert anything, and the error log won't tell me anything.

This is my query:

        if ($data[16] != NULL) {
            $cursosViejos = ', '.$data[16];
        } else {
            $cursosViejos = '';
        }
        $x = $data[3];
        $importar = $conectar->prepare('
                INSERT INTO usuarios (
                    deprecated_userID,
                    deprecated_userName,
                    userEmail,
                    deprecated_userCursos,
                    deprecated_userRoles,
                    userApellido,
                    userDNI,
                    userDomicilioCalleyNumero,
                    userDomicilioLocalidad,
                    userEstadoCivil,
                    userEstudios,
                    userProfesion,
                    userTelefono,
                    deprecated_cuestionarioFinal,
                    userAnoticiado,
                    deprecated_entregaDoc
                )
                VALUES (
                ?,
                ?,
                ?,
                concat(?,?), 
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?,
                ?
                )
                ON DUPLICATE KEY UPDATE
                ');
            $importar->bindParam(1, $data[0]);
            $importar->bindParam(2, $data[1]);
            $importar->bindParam(3, $data[2]);
            $importar->bindParam(4, $x);
            $importar->bindParam(5, $cursosViejos);
            $importar->bindParam(6, $data[4]);
            $importar->bindParam(7, $data[5]);
            $importar->bindParam(8, $data[6]);
            $importar->bindParam(9, $data[7]);
            $importar->bindParam(10, $data[8]);
            $importar->bindParam(11, $data[9]);
            $importar->bindParam(12, $data[10]);
            $importar->bindParam(13, $data[11]);
            $importar->bindParam(14, $data[12]);
            $importar->bindParam(15, $data[13]);
            $importar->bindParam(16, $data[15]);
            $importar->bindParam(17, $data[17]);

        $ok = $importar->execute();

This is the table that will receive the data:

CREATE TABLE usuarios(
    userID int unsigned not null auto_increment primary key,
    userEmail char(50) null,
    userApellido char(50) null,
    userNombres char(20) null,
    userDNI char(15) null,
    userPass char(65) null,
    userFechaGeneracion char(25) null,
    userLastLogin char(25) NULL,
    userTelefono char(200) null,
    userCelular char(200) null,
    userDomicilioCalleyNumero char(100) null,
    userDomicilioLocalidad char(80) null,
    userDomicilioProvincia char(80) null,
    userProfesion char(180) null,
    userEstadoCivil char(80) null,
    userEstudios char(80) null,
    nacionalidad char(80) null,
    userAnoticiado char(80) null,
    docFoto char(100) null,
    docDNI char(100) null,
    docCertif char(100) null,
    docVerificada int(1) not null DEFAULT '0',
    comentariosAdmin text null,
    deprecated_userName char(50) null UNIQUE,
    deprecated_userID int(6) null,
    deprecated_userStatus int(6) null,
    deprecated_userNode int(6) null,
    deprecated_userRoles char(150) null,
    deprecated_userCursos varchar(7000) null,
    deprecated_entregaDoc char(150) null,
    deprecated_cuestionarioFinal char(150) null,
    userDatosPublicos text null,
    userTelsPublicos varchar(200) null DEFAULT NULL,
    UNIQUE(userEmail)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Is there any syntax error that I'm not seeing? Because other imports that I've done with the same script did worked.

Here's some sample data from the csv file:

"27","casia","mrcasia@yahoo.com.ar","","alumno_presencial","CASIA, María Rosa","","","","","","","","","","","","" "28","alefrei","dralejandra@gmail.com","Curso Interdisciplinario On Line de Administración de Consorcios","alumno_online, online1","","","","","","","","","","","","","" "57","Arq. Emilio Gomez","eaos@yahoo.com","","autor","Arq. Emilio Fernando Gómez","","","","","","","","","","","","" "27","casia","mrcasia@yahoo.com.ar","","alumno_presencial","CASIA, María Rosa","","","","","","","","","","","","" "27","casia","mrcasia@yahoo.com.ar","","alumno_presencial","CASIA, María Rosa","","","","","","","","","","","",""

UPDATE:

I've put the code in a try/catch and I have this error shown:

SQLSTATE[42000]: Syntax error or access violation: 1064

yes, I'm just trying to do it as a PHP script with an INSERT as a proof of concept and to learn how to do it. — Rosamunda, Feb 27 '17 at 17:21
Add error reporting to the top of your file(s) right after your opening ` — Jay Blanchard, Feb 27 '17 at 17:26
Thanks for the reminder, I've added that but it still won't show anything. The error log is saying nothing as well. — Rosamunda, Feb 27 '17 at 17:27
What is the value of $ok if you var_dump it? Does it work if you trim it down to the first field? — Matt, Feb 27 '17 at 21:47

score 1 · Answer 1 · answered Feb 27 '17 at 17:49

1

THe CONCAT statement you use to build your deprecated_userCursos column is not using any quotes for the strings. As you've parameterized the rest of the query, why not parameterize that? e.g. concat(?,?), and then

 $importar->bindParam(4, $x);
 $importar->bindParam(5, $cursosViejos);
 //adjust the other param indices which follow...

answered Feb 27 '17 at 17:49

Paul Dixon

295,876
54
310
348

Thanks for your answer, and you are right in the sense that I should have done that in the first place. But I've changed the code, and the error still appears, with a blank error log. – Rosamunda Feb 27 '17 at 19:01

score 0 · Answer 2 · edited May 23 '17 at 11:46

0

I've found the answer. Just in case it help other, I'll post it here.

The problem is with the syntax, in the last ON UPDATE part.

It's incomplete:

ON DUPLICATE KEY UPDATE

When it should have exactly how to do the update. The statement is incomplete, because the UPDATE part is not there. I should tell mySQL how to make the update.

Here's more information on the subject.

edited May 23 '17 at 11:46

Community

1
1

answered Feb 28 '17 at 02:52

Rosamunda

14,620
10
40
70

Is this INSERT syntactically correct? (PHP script trying to import into MySQL with a concat() function and duplicated values)

UPDATE:

2 Answers2