Query with PHP PDO and SQLServer

Question

Query with PHP PDO and SQLServer

Navigation

#1 by (1 votes)

0

I am trying to make a query with PHP to a database in SQLServer with ajax and MVC but when I execute the query it throws me the following error

sqlsrv_fetch_array() expects parameter 1 to be resource, boolean given in <b>C:\xampp\htdocs\Personal\modelos\usuarios.modelo.php</b> on line <b>49</b><br />

This is the way I sent the input via JS to the ajax file

var validarUsuarioRepetido = false;
var rutaOculta = $("#rutaOculta").val();

$("#password").change(function(){
  var usuarioPassword = $('#password').val();
  var datos = new FormData();
  datos.append("validarUsuario", usuarioPassword);

  $.ajax({
    url: rutaOculta+"ajax/usuarios.ajax.php",
    method: "POST",
    data: datos,
    cache: false,
    contentType: false,
    processData: false,
    success:function(respuesta){
      $("#user").val(respuesta);
      console.log(respuesta);
    }
  })
})

later the ajax receives it in the following way and sends it to the controller

<?php

require_once "../controladores/usuarios.controlador.php";
require_once "../modelos/usuarios.modelo.php";

class AjaxUsuarios{
  /**
   * Validar Usuario
  **/
  public $validarUsuario;

  public function ajaxValidarUsuario(){
      $datos = $this->validarUsuario;
      $respuesta = ControladorUsuarios::ctrMostrarUsuarioP($datos);
      echo ($respuesta);
  }

}

/**
 * Validar Usuario
 **/
if (isset($_POST["validarUsuario"])){
    $valUsuario = new AjaxUsuarios();
    $valUsuario -> validarUsuario = $_POST["validarUsuario"];
    $valUsuario -> ajaxValidarUsuario();
}

When the controller receives it, he sends it to the model to generate the query and return it

static public function mdlMostrarUsuarioP($tabla, $datos){
    $serverName = "SERVIDOR"; //serverName\instanceName
    $connectionInfo = array( "Database"=>"BASEDEDATOS", "UID"=>"USUARIO", "PWD"=>"PSSWORD");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);

      $sql = "SELECT * FROM $tabla WHERE CB_ID_NUM=?";
      $stmt1 = sqlsrv_prepare($conn, $sql, array($datos));
      $result = sqlsrv_execute($stmt1);
      return $stmt3 = sqlsrv_fetch_array($result);
    }

and the moment it enters the sqlsrv_fetch_array is where I get the error mentioned above, I hope someone can help me, this is the first time I try to generate the connection and a query with SQLServer

php sql-server mvc

asked by cesg.dav 02.12.2018 в 07:59

source

1 answer

Open the same file from several (or all) URLs Reload the page when submitting in a form that consumes an API

score 1 · Answer 1

I propose this code that controls all eventualities. When there are errors, you will create a error key within the $data array.

In the destination you can test the data by doing print_r($data) . To determine the errors, you can search for the error key within the resulting array.

public static function mdlMostrarUsuarioP($tabla, $datos){
    $serverName = "SERVIDOR"; //serverName\instanceName
    $connectionInfo = array( "Database"=>"BASEDEDATOS", "UID"=>"USUARIO", "PWD"=>"PSSWORD");
    $msgError=FALSE;
    if ( $conn = sqlsrv_connect( $serverName, $connectionInfo) ){
        $sql = "SELECT * FROM $tabla WHERE CB_ID_NUM=?";
        if ( $stmt = sqlsrv_prepare($conn, $sql, array($datos)) ) {
            if ( $result = sqlsrv_execute($stmt) ) {
                $data = sqlsrv_fetch_array($stmt);
            } else {
                $msgError="Error ejecutando la consulta: ";
            }
        } else {
            $msgError="Error preparando la consulta: ";
        }
    } else {
        $msgError="Conexión no disponible: ";
    }
    if ($msgError){
        $errors = sqlsrv_errors();
        $data["error"]=$msgError.$error[ 'message'];
    }
    return $data;
}