Error inserting data into MySQL using PHP and AJAX. (NO jQuery)

0

PHP class where I connect to the Database

<?php 

    class Buscador
    {
        var $host = 'localhost', $user = 'root', $pass = 'chachin', $db = 'employee', $conexion = 'Se conecto',
            $conexion_i = "No se Conecto correctamente", $db_c = 'Se encontro a la DB', $db_i = 'No se encontro la BD';


        function Conectar()
        {
            if ([email protected]_connect($this->host, $this->user, $this->pass)) {
                print $this->conexion_i;
            } else {
                if([email protected]_select_db($this -> db)) {
                    print $this->db_i;
                }
            }
        }
    }
    ?>

Ajax file where I have the function to insert the BD

function Buscador(){
    var xmlhttp=false;
    try {
        xmlhttp = new ActiveXObject("Msxml2.XMLHTTP");
    } catch (e) {
        try {
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        } catch (E) {
            xmlhttp = false;
        }
    }if (!xmlhttp && typeof XMLHttpRequest!='undefined') {
        xmlhttp = new XMLHttpRequest();
    }
    return xmlhttp;
}

function enviarDatosEmpleado(){

    //recogemos los valores de los inputs
    aidi=document.nuevo_empleado.id.value;
    nom=document.nuevo_empleado.nombre.value;
    ape=document.nuevo_empleado.apellido.value;
    proy=document.nuevo_empleado.proyecto.value;
    fe=document.nuevo_empleado.fecha.value;

    //instanciamos el objetoAjax
    ajax=Buscador();

    //uso del medotod POST
    //archivo que realizará la operacion
    //registro.php
    ajax.open("POST", "registro.php",true);
    //cuando el objeto XMLHttpRequest cambia de estado, la función se inicia
    ajax.onreadystatechange=function() {
        if (ajax.readyState==4) {
         c.innerHTML = ajax.responseText;
        }
    }
    //enviando los valores a registro.php para que inserte los datos
    ajax.send("id"+aidi+"&nombre="+nom+"&apellido="+ape+"&proyecto="+proy+"&fecha="+fe)
}

finally the data form

<?php
include("config.php");
$c = new Buscador;
$c -> Conectar();    
session_start();



if (isset($_SESSION['user'])) {

    $html = <<<html
    <form name="nuevo_empleado" action="" onsubmit="enviarDatosEmpleado(); return false">
            <h2>Nuevo empleado</h2>
                <table>
                <tr>
                    <td>ID</td><td><label><input name="id" type="text" /></label></td>
                </tr>
                <tr>
                    <td>Nombres</td><td><label><input name="nombre" type="text" /></label></td>
                </tr>
                <tr>
                    <td>Apellido</td><td><label><input type="text" name="apellido"></label></td>
                </tr>
                <tr>
                    <td>Proyecto</td><td><label><input name="proyecto" type="text" /></label></td>
                </tr>
                <tr>
                    <td>Fecha</td><td><label><input name="fecha" type="text" /></label></td>
                </tr>
                <tr>
                    <td>&nbsp;</td><td><label><input type="submit" name="Submit" value="Grabar" /></label></td>
                </tr>
                </table>
        </form>


html;
    echo $html;
}else{
    echo'<a style="font-family:arial;color:red;font-size:18px" href="login.html">Inicie sesion para ver esta pagina</a>';
}
?>

File registro.php which is in charge of making the insertion query to the database

<?php

include("config.php");
// para no mantener la sesion de MySQL abierta
// se utilize solamente en los archivos
$c = new Buscador;
$c -> Conectar();

//variables POST
$aidi=$_POST['id'];
$nom=$_POST['nombre'];
$ape=$_POST['apellido'];
$proy=$_POST['proyecto'];
$fe=$_POST['fecha'];

//registra los datos del empleados
$sql="INSERT INTO employee_details (id, nombre, apellido, proyecto, fecha) VALUES ('$aidi','$nom', '$ape', '$proy', '$fe')";
mysql_query($sql,$con) or die('Error. '.mysql_error());
?>

I hope you can help me

    
asked by user1396620 07.07.2016 в 04:29
source

2 answers

1

Ok, I managed to get it working locally by correcting two things in your JavaScript code (although that does not mean that there may be errors in the registry code.php and that you should correct the security problems with the SQL injection). The errors are:

  • The format of the parameters is incorrect . This is your code:

    //enviando los valores a registro.php para que inserte los datos
    ajax.send("id"+aidi+"&nombre="+nom+"&apellido="+ape+"&proyecto="+proy+"&fecha="+fe)
    

    If you notice, after the parameter id the = is missing. That is going to make the parameters not send well and the PHP fails you. The correct code would be like this:

    //enviando los valores a registro.php para que inserte los datos
    ajax.send("id="+aidi+"&nombre="+nom+"&apellido="+ape+"&proyecto="+proy+"&fecha="+fe)
    
  • You are not passing the parameters correctly . You have to specify in the header of the AJAX request that what you are sending is a form so that the parameters are sent correctly by POST. You can do this by adding the following line:

    ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    
  • Once these two errors have been corrected, the function enviarDatosEmpleado stays like this and works fine:

    function enviarDatosEmpleado(){
    
        //recogemos los valores de los inputs
        aidi=document.nuevo_empleado.id.value;
        nom=document.nuevo_empleado.nombre.value;
        ape=document.nuevo_empleado.apellido.value;
        proy=document.nuevo_empleado.proyecto.value;
        fe=document.nuevo_empleado.fecha.value;
    
        //instanciamos el objetoAjax
        ajax=Buscador();
    
        //uso del medotod POST
        //archivo que realizará la operacion
        //registro.php
        ajax.open("POST", "registro.php",true);
        //cuando el objeto XMLHttpRequest cambia de estado, la función se inicia
        ajax.onreadystatechange=function() {
            if (ajax.readyState==4) {
             console.log( ajax.responseText );
            }
        }
        ajax.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        //enviando los valores a registro.php para que inserte los datos
        ajax.send("id="+aidi+"&nombre="+nom+"&apellido="+ape+"&proyecto="+proy+"&fecha="+fe)
    }
    

    Edit after reviewing all the code

    In addition to the errors specified above that made the JavaScript code not work, I found another series of errors that made the code not work. Maybe one occurred because you shared a simplified version of your code.

    I have replicated your database and used the files you shared and, after making the following changes, the code works for me and inserts into the database without problems:

    • Include the JavaScript file in the PHP file . Right now guardarEmp.php only shows the form, but does not include the ajax.php file, which makes the EnviarDatosEmpleado function is not called correctly and fails.

      In saveEmp.php, add this before the form:

      <script src="./ajax.js"></script>
      
    • You have a typo when you assign the variable p . You have changed the way variables are read (before you had aidi , nom , ape , proy and fe , but now you have id , n , a , p , and f ). When creating p you made a typo because you put new_empleadi:

      p = document.nuevo_empleadi.proyecto.value;
      

      should be new_emplead or :

      p = document.nuevo_empleado.proyecto.value;
      
    • You have changed the names of the variables, but you still use the old names instead of the new ones. This is related to the above, now you read the variables in a different way and have different names, then this line:

      ajax.send("id="+aidi+"&nombre="+nom+"&apellido="+ape+"&proyecto="+proy+"&fecha="+fe);
      

      no longer makes sense because you do not have aidi , nom , etc. but id , n , etc. Change the names of the variables:

      ajax.send("id="+id+"&nombre="+n+"&apellido="+a+"&proyecto="+p+"&fecha="+f);
      

    With these three changes (plus the two explained above), the AJAX request is already done correctly, but now you get an error in PHP. So there is an error in registro.php :

      

    Warning: mysqli_query () expects at least 2 parameters, 1 given

    You have changed the mysql_* functions (which are no longer used) by mysqli_* , which is a good step (but not enough to avoid SQL injection, you have to use prepared queries) that has caused this error to appear.

    The mysqli_query method needs two parameters: the first one would be the connection and the second the SQL string that you want to execute. The problem you have now is that you do not save the connection anywhere, the first thing would be to modify config.php so that the connection is saved once it is established:

  • Add $conn = null to the list of variables in class Buscador
  • Assigns the connection to the variable $conn
  • At the end of config.php it would look something like this:

    <?php
    
    class Buscador
    {
        var $conn = null, $host = 'localhost', $user = 'root', $pass = '', $db = 'stackoverflow', $conexion = 'Se conecto',
            $conexion_i = "No se Conecto correctamente", $db_c = 'Se encontro a la DB', $db_i = 'No se encontro la BD';
    
    
        function Conectar(){
          $conex = mysqli_connect($this->host, $this->user, $this->pass);
          $this->conn = $conex;
            if([email protected]$conex){
                print $this->conexion_i;
            }else{
                if ([email protected]_select_db($conex,$this->db)) {
                    print $this->db_i;
                }
    
            }
        }
    }
    

    Then the only thing you would have to do is pass the connection to mysqli_query as the first parameter in registro.php :

    mysqli_query($c->conn, $sql) or die('ERROR. '.mysqli_error());
    

    Et voilà! Everything works without problems.

        
    answered by 07.07.2016 в 16:52
    0

    The problem you have with the consutla you are doing. By putting the variables in single quotes, you are giving the error. Try this

    $sql="INSERT INTO employee_details (id, nombre, apellido, proyecto, fecha) VALUES ('".$aidi."','".$nom."', '".$ape."', '".$proy."', '".$fe."')";
    
        
    answered by 07.07.2016 в 16:29