Home / Questions / 457272
Accepted
John Doe
Asked: 2022-06-04 11:53:49 +0800 CST 2022-06-04 11:53:49 +0800 CST

How to validate that key does not exist in the database before uploading XML

  • 772

I would like to carry out a validation where, before uploading the XML file, it is verified in a database table if the field claveis different from what is obtained from the XML reading, if it is different from what the XML reading contains, allow it to upload otherwise display a message indicating that the clave.

Initially I claveam already obtaining it from reading an XML file as follows:

if($res['xml']) {
      
        if( $_FILES['XmlToUpload']['error'] !== UPLOAD_ERR_OK ){
          // error_log($errores[$_FILES['XmlToUpload']['error']]);
          die( $errores[$_FILES['XmlToUpload']['error']] );
      }
        
      $xml = new SimpleXMlElement( $_FILES['XmlToUpload']['tmp_name'], 0, true ); 
      $clave = (string)$xml->children('cfdi',true)->Complemento->children('tfd',true)->attributes()['CLAVE'];

include './db/conectar.php';

  if( $conn === false ){
    die(print_r(sqlsrv_errors(), true));
  }

  $sql = "{call ValidarXML(?,?,?)}";

  $params = array($rfc, $total, $factura,);
  $result = sqlsrv_query( $conn, $sql, $params );
    

      if( $row = sqlsrv_fetch_array($result) ){
        $archivoXML = $_FILES['XmlToUpload']['name'];
        $rutaXML = "/XML/";

        $filepathXML = $rutaXML.$archivoXML;

        if(move_uploaded_file( $_FILES['XmlToUpload']['tmp_name'], $rutaXML . $archivoXML)){
          echo 'Documentos cargados exitosamente';

  }
  else {
          echo 'Error al mover a directorio destino';
        }           
    } else {
        echo 'Por favor verifique el contenido de los documentos';       
    }        
  }

The query with which I intend to validate in the database is the following:

SELECT CLAVE FROM PURCHASEORDER WHERE CLAVE = ?;

I hope someone can give me some guidance on this validation.

Update 1:

I have created the following stored procedure based on the comments to check if it exists or not:

ALTER PROCEDURE [dbo].[SPValidarClave]
    -- Add the parameters for the stored procedure here
    @CLAVE  VARCHAR(100)
AS
BEGIN
    SET NOCOUNT ON;
    /**/
    Declare @Existe int
    if not exists(Select 1 From PURCHASEORDER WHERE CLAVE=@CLAVE)
    begin
        
        set @Existe =1
    end
    else
    begin   
        set @Existe =0
        
    end

    Select @Existe
    /**/
  
END

With this stored procedure done, I only require validation from my PHP code.

php

1 Answers

  1. Best Answer

    You can create a validation function that receives the key as a parameter, checks if there are records with that key and returns trueor falsedepending on the case.

    All of this can be done with a simple query, without the need to use stored procedures.

    For example:

    <?php
    function isClave($mClave) {
      /*
          Asumimos que la conexión no está en contexto
          y por eso conectamos aquí. 
          Si es un contexto más amplio considera crear una Clase o algo así ...
          Asumimos que este archivo conecta a la BD
          donde quieres verificar la existencia de la clave
      */
      include './db/conectar.php';

      if($conn){
          $sql="SELECT COUNT(*) FROM PURCHASEORDER WHERE CLAVE=?";
          if ($stmt = sqlsrv_prepare($conn, $sql, array(&$mClave))) {
              if ( sqlsrv_execute($stmt) && sqlsrv_has_rows($stmt) ) {
                /*
                  La función sólo retornará true cuando encuentre filas
                */
                return true;
              }
          }
      }
      return false;
    }
?>

    Now to call it:

    //...
$clave = (string)$xml->children('cfdi',true)->Complemento->children('tfd',true)->attributes()['CLAVE'];

if ( isClave($clave) ) {
    //La clave existe, no hay que subir el archivo
} else {
    //La clave no existe, se sube el archivo
}

    Post-Data : If you want to do the same thing from an SP, you only have to change $sqlto a call to the SP.

    To adapt it to the SP that you have indicated in your question, it would be more or less like this.

    I have put comments in the code explaining what's new. If you don't understand something, ask in comments.

    <?php
    function isClave($mClave) {
      /*
          Asumimos que la conexión no está en contexto
          y por eso conectamos aquí. 
          Si es un contexto más amplio considera crear una Clase o algo así ...
          Asumimos que este archivo conecta a la BD
          donde quieres verificar la existencia de la clave
      */
      include './db/conectar.php';

      if($conn){
          /*
            Se pasan dos parámetros porque la SP 
            tiene un parámetro de entrada @CLAVE 
            y uno de salida: el valor 1 o 0 
            Asumo que tu SP no tiene errores y que funciona bien 
          */
          $sql = "{CALL SPValidarClave (?, ?)}";
          #Definimos variable para parámetro de salida
          $mOut=-1;
          $mParams = array(
                            array(&$mClave, SQLSRV_PARAM_IN),
                            array(&$mOut, SQLSRV_PARAM_OUT)
                          );  
          if ($stmt = sqlsrv_prepare($conn, $sql, $mParams)) {
              if ( sqlsrv_execute($stmt) ) {
                /*
                  La función sólo retornará true cuando 
                  el parámetro de salida de la SP sea igual a 1
                  La salida de la SP estará siempre en la variable $mOut
                */
                return ($mOut == 1);
              }
          }
      }
      return false;
    }
?>
    • 8