Error in mysqli_stmt :: fetch_assoc ()

Navigation
2

I am trying to develop a function that will help me simplify my database queries. I am implementing it with prepared queries and a bit of dynamism at the moment of knowing that I am executing. Actually I have never used the prepared queries nor have I used mysqli for objects (I have always done it by procedures, for example mysqli_query() ) The code I already documented then I do not think there is so much problem in explaining what it does. I have a problem that says the following

  

Fatal error: Uncaught Error: Call to undefined method   mysqli_stmt :: fetch_assoc () in   /fakepath/QueryEngine.php:80

Again, I have never used this procedure, you may find the error obvious. 

<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
class QueryEngine {

  protected $conn;
  public function __construct(){
    $server     = 'localjost';
    $user       = 'iuser';
    $pass       = 'pasuord';
    $db         = 'deirabeis';
    $this->conn = new mysqli( $server, $user, $pass, $db );
  }

  public function executeQuery( $query, $params, $type, $meta = false ){
    //Preparamos dos arreglos que servirán para retornar y almacenar parametros
    $result    = array();
    $data      = array();
    //Separamos la consulta y los tipos de datos que se enviaran
    $order_arr = explode( ' ', $query );
    $type_arr  = explode( ',', $type  );
    //La orden será la primera detección (select, update, insert)
    $order     = strtolower( $order_arr[0] );
    //Validamos si hay errores en la conexión y creamos un arreglo asociativo
    //Si está la meta activa se añade información extra
    if( $this->conn->connect_errno ){
      $result['data']    = null;
      $result['message'] = "Falló la conexión a la base de datos";
      if( $meta ){
          $result['meta']['query']  = $query;
          $result['meta']['params'] = implode( ',', $params );
          $result['meta']['error']  = $this->conn->connect_errno . ' ' . '('.$this->conn->connect_error.')';
      }

    }

    else{
      //Añadimos la codificación de los datos
      $this->conn->set_charset( 'utf-8' );
      //Validamos que se haya preparado bien la consulta y creamos el arreglo
      if( !$stmt = $this->conn->prepare( $query ) ){
        $result['data']    = null;
        $result['message'] = "Consulta mal formada";
        if( $meta ){
            $result['meta']['query']  = $query;
            $result['meta']['params'] = implode( ',', $params );
            $result['meta']['error']  =  $this->conn->errno . " " . $this->conn->error;
        }
      }

      else{
        //Ciclamos los parámetros, los limpiamos y los agregamos al arreglo $data
        foreach( $params as $param ){
          array_push( $data, $this->conn->real_escape_string($param) );
        }
        //Ligamos los parámetros y su respectivo tipo de dato al statement
        for ( $i = 0; $i < count( $data ); $i++ ) {
            $stmt->bind_param( $type_arr[ $i ], $data[ $i ] );
        }
        //Validamos la ejecución de la consulta
        if( !$stmt->execute() ){
          $result['data']    = null;
          $result['message'] = "Consulta mal formada";
          if( $meta ){
              $result['meta']['query']  = $query;
              $result['meta']['params'] = implode( ',', $params );
              $result['meta']['error']  =  $this->conn->errno . " " . $this->conn->error;
          }
        }

        else{
          //Según cual sea la sentencia se ejecutarán diferentes acciones
          switch ( $order ) {
            case 'select':
              //Almacenamos la información
              $stmt->store_result();
              $x = 0;
              //Barremos los resultados
              while ( $row = $stmt->fetch_assoc() ) {
                  $data[ $x ] = $row;
                $x++;
              }

              $result['data']    = $data;
              $result['message'] = "Consulta exitosa";
              if( $meta ){
                  $result['meta']['query']  = $query;
                  $result['meta']['params'] = implode( ',', $params );
                  $result['meta']['error']  = 'No error';
              }
              //Liberamos la memoria
              $stmt->free_result();
            break;

            case 'insert':
            break;

            case 'update':
            break;


            default:
              $result['data']    = null;
              $result['message'] = "No es posible ejecutar esta acción";
              if( $meta ){
                  $result['meta']['query']  = $query;
                  $result['meta']['params'] = implode( ',', $params );
                  $result['meta']['error']  = 'Sentencia ' . $oder . ' no reconocida';
              }
            break;
          }
        }
        //Cerramos la conexión
        $stmt->close();
      }
    }
    //Retornamos el resultado
    return $result;
  }
}
?>

I am invoking the method in the following way: 

include 'QueryEngine.php';
$queryBuilder = new QueryEngine();

$query  = "SELECT * FROM USERS";
$params = array();
$values = '';

$result = $queryBuilder->executeQuery( $query, $params, $values, 1 );

print_r( $result );
    
asked by Alberto Siurob 14.10.2018 в 07:14
source

1 answer

3

Alberto the error is not so stupid as you might believe, and is the result of one of the biggest difficulties (in my opinion) the API mysqli : the difficulty of obtaining an associative arrangement of results when we use prepared queries .

I will explain it with the documentation.

If you look at the PHP Manual, you'll see that fetch_assoc is a method applicable to the object: mysqli_result Why does it say Call to undefined method ? For a very simple reason: Prepared queries do not return objects mysqli_result ! Here, while ( $row = $stmt->fetch_assoc() ) { you apply the method fetch_assoc() to an object $stmt that does not have that method (see here the methods available to the object mysqli_stmt ). If you were using query , which does return objects mysqli_result you would not have that problem. But query does not suit you, because you need to give security to your code and for this it is mandatory to use prepared queries.

What then returns a prepared query?

So that we understand each other, with a prepared query it does not happen as with query which returns by itself a result when executed directly. The prepared query must:

  • prepare it prepare
  • execute% execute
  • obtain a set of results after execution with a suitable method that is not precisely fetch_assoc . The PHP Manual says this when explaining the execute() method :
  

Note:

     

When using mysqli_stmt_execute() , the function mysqli_stmt_fetch() must be used to obtain the data before performing   any additional questions.

That is, to get the data, you have to use the fetch method. Let's review that method in the PHP Manual:

The first thing we see is that the method is defined as follows:

mysqli_stmt::fetch That is, it is a method applicable to mysqli_stmt , which is the object with which we work in prepared queries. Then, we read its definition, which (could) complicate our existence. Let's see:

  

Gets the results of a statement prepared in the variables   linked

The uncomfortable issue in the definition is the end: the linked variables . That is, in a SELECT with 15, 20, 40 columns, you should link the same amount of variables to get the results , using the bind_result method. For example: 

$stmt->bind_result($col1, $col2, $col_n);
$arrDatos=array();
/* obtener los valores */
while ($stmt->fetch()) {
    $arrDatos[]=array("col1"=>$col1, "col2"=>$col2, "col_n"=>$col_n);
}

This would be a form (somewhat cumbersome, even with few columns, imagine if there are many!) to obtain an associative arrangement of results when we use prepared queries.

Is there no alternative?

Yes. There are two (or three) alternatives.

1st Alternative: you can use get_result

get_result if applicable to objects mysqli_stmt . But this method depends on whether or not you have installed the driver mysqlnd . This driver is not installed on all systems and you could then be producing a code that would work in some scenarios and not in others.

If you want to implement it, it would be like this: 

   $stmt->execute();
   $result = $stmt->get_result();
   $arrDatos=array();
   while ($row = $result->fetch_assoc()) {
        $arrDatos[]=$row;
   }

Super simple, without having to write the column names by hand!

2nd Alternative: you can use your own function that emulates get_result

The problem that you are facing I perceived it a while ago, so I raised this question to find a solution to cases like these: How to obtain an associative arrangement using queries prepared with mysqli?

Our friend @ D.Bulten responded with a feature that does the work : 

function my_get_result( $Statement ) {
    $RESULT = array();
    $Statement->store_result();
    for ( $i = 0; $i < $Statement->num_rows; $i++ ) {
        $Metadata = $Statement->result_metadata();
        $PARAMS = array();
        while ( $Field = $Metadata->fetch_field() ) {
            $PARAMS[] = &$RESULT[ $i ][ $Field->name ];
        }
        call_user_func_array( array( $Statement, 'bind_result' ), $PARAMS );
        $Statement->fetch();
    }
    return $RESULT;
}

The use would be very simple. It would be to pass the object $stmt to the function, it organizes an associative arrangement and returns this arrangement: 

   $stmt->execute();
   $arrDatos=my_get_result($stmt);

This function could be incorporated in your program to a clase utilitarian, which will keep your code independent of drivers that may or may not be installed in certain environments.

If you want to see the application of the function in a complete context of prepared queries and data management , you can see this answer .

3rd Alternative

You can use PDO , which will not give you so many laps to get results when you use prepared queries.

    
answered by 14.10.2018 / 20:08
source
How do Trailing Commas work in JavaScript? modal bootstrap close with enter