Problems with fetch () fetch_array ()

Question

Problems with fetch () fetch_array ()

Navigation

#1 by (2 votes)

1

I'm having problems with fetch() fetch_array() I'm working with sentences prepared since yesterday I'm new to it and I have this code that practically doing fetch() is not throwing me anything I do not know what I'm doing wrong, if I do normal without prepared sentences if you throw me result that is $query=$conexion->query() etc ...

I hope you can help me and here my code:

if (isset($_GET['id'])) {

    $profile=$_GET['id'];//recojer id 

     $infouser="SELECT * FROM registro WHERE id=?";

        $stmt = $conexion->prepare($infouser);//preparar sentencia

          $stmt->bind_param("i",$profile);

           $stmt->execute();

         $stmt->store_result();

         if ($stmt->num_rows > 0) {


          $userInfo = $stmt->fetch();//si aqui pongo fetch_array() me lanza error

            //aqui solo me imprime nombre, el resultado esperado seria nombre:Fulano 
          echo "nombre ".$userInfo["nombre"];
          $stmt->close();

I already googled a bit but still I do not understand what's happening, hopefully they can help me, since I've tried a thousand ways and I can not get any thanks. Thanks in advance!

php mysqli query

asked by andy gibbs 25.08.2018 в 22:13

source

1 answer

Show data Binding WPF C # java properties / xml as constant

score 2 · Accepted Answer

In mysqli getting results when using prepared queries is a bit more complicated than with PDO. This is due in part to the fact that a direct data acquisition depends on a driver external to mysqli called mysqlnd . When you have that driver you can use the get_result method to get the results in the form of an associative array or another.

As I do not know if you have that driver, you can opt for another method that always works without depending on other drivers.

Consists of the following:

Name in SELECT explicitly the columns you need. This, for the rest, is a best practice instead of the famous SELECT * that we usually see everywhere.
Use bind_result to assign the value of the column to a variable.
Read in while results row by row. This is an important point. For reasons of memory, database queries do not return all rows (unless explicitly stated), but return as a pointer to traverse the data row by row. We will do this with the while , being able to print the data directly or store them in a variable only in the case that we need them to pass them to another part of the code .

The code would look like this:

if (isset($_GET['id'])) {

    $profile=$_GET['id'];//recojer id 
    $infouser="SELECT nombre FROM registro WHERE id=?";
    $stmt = $conexion->prepare($infouser);//preparar sentencia
    $stmt->bind_param("i",$profile);
    $stmt->execute();
    $stmt->store_result();

    if ($stmt->num_rows > 0) {
        $stmt->bind_result($nombre);
        while ($stmt->fetch()) 
        {
            echo "nombre: ".$nombre.PHP_EOL;
        }
    }
    $stmt->close();
}

If you want to save the data in an array to use it in another part of the code, you can then do the following:

Declare the array before while that reads each row.
Go filling the array within while . You can enter the same name as the column.

For example:

    $arrResult=array();

    if ($stmt->num_rows > 0) {
        $stmt->bind_result($nombre);
        while ($stmt->fetch()) 
        {
            $arrResult[]=array ("nombre"=>$nombre);
        }
    }

    /*Prueba de datos*/
    print_r($arrResult);

You will have an associative array made by hand in $arrResult .

Note that this process is easier if you have the driver mysqlnd .

Also, the get_result function can be emulated, by writing your own function. There is a question here that deals with that topic . The accepted response provided a function that is a surrogate of get_result , useful to incorporate it into a possible utilitarian class or use it directly if we are interested.

I hope you find it useful.