BindParam not returning any values - PHP PDO

Question

I have a problem when i bind a variable to my sql query, i've tried without using params and it works fine, so it's not a connection issue.

This is my connection to my db, which is in another file:

<?php 
$dbhost="localhost";  // localhost
$dbport =""; 
$dbname="proyecto_final";

$usuario="root";
$contrasenia="38919650";



$strCnx = "mysql:dbname=$dbname;host=$dbhost";  

$db ="";

try {
    $db = new PDO($strCnx, $usuario, $contrasenia);
    
    $db->setAttribute(PDO::ATTR_CASE,PDO::CASE_LOWER); # para referenciar en minúscula el nombre de las columnas
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); # Relizar el FETCh ASSOC por defecto para ahorrar memoria
    
} catch (PDOException $e) {
    print "Error: " . $e->getMessage() . "<br/>";   # cambiar por un error personalizado 
    die();
}
?>

And here is the file where i include the connection, and then use PDO FETCH to get the values:

      <?php
      require_once 'database/conn.php';
      $ciudad = (isset($_POST["valor_ciudad"]) && !empty($_POST["valor_ciudad"]))? $_POST["valor_ciudad"] : "";
      $oficio = (isset($_POST["valor_oficio"]) && !empty($_POST["valor_oficio"]))? $_POST["valor_oficio"] : "";
      echo $ciudad; //works fine
      echo $oficio; //works fine
      $query = 
            "
              SELECT trabajador.nombre, trabajador.apellido, trabajador.telefono, oficios.categoria, oficios.experiencia, localidad.ciudad 
              FROM trabajador
                      INNER JOIN oficios ON oficios.FK_IDtrabajador = trabajador.ID_trabajador
                      INNER JOIN localidad ON localidad.ID_localidad = trabajador.FK_IDlocalidad
              WHERE oficios.categoria = :oficio
              AND localidad.ciudad = :ciudad      
            ";
      // prepare the query
      if(!empty($ciudad) && !empty($oficio)){
        try {
          //acá va la consulta
          $stmt = $db->prepare($query);
          $stmt->bindParam(':oficio', $oficio, PDO::PARAM_STR);
          $stmt->bindParam(':ciudad', $ciuddad, PDO::PARAM_STR); // PDO::PARAM_STR

          $stmt->execute();

          $num = $stmt->rowCount();
          echo $num; //returns 0, which means it doesnt get any value from the db
          if (!$stmt ) { 
            print_r($db->errorInfo());      #mensaje en desarrollo
            echo "Error al ejecutar consulta.";
          }else{
            while ($fila = $stmt->fetch(PDO::FETCH_ASSOC)) {
              print_r($fila);
              $datos[] = $fila;
            }
            print_r($datos); //i get the error in this line "Undefined variable"
          }
          } catch (PDOException $e) {
          print "Error: " . $e->getMessage();
          die();
          }
      }
   ?>

As i marked above, i get the error "Undefined variable" when i try to print $datos, because it doesn't have any values inside. I tried doing the query without params, and it works fine.

FYI, `isset && !empty` is completely superfluous. Use one or the other, it doesn’t make a big difference which you use in this case here. — deceze, Nov 19 '20 at 20:27
You should initialize `$datos` to an empty array before the loop. Or just use `$datos = $stmt->fetchAll(PDO::FETCH_ASSOC)` instead of looping. — Barmar, Nov 19 '20 at 20:30
You don't even need to use `bindParam()` at all. You can just pass an array of key/value pairs to `execute()`. — GrumpyCrouton, Nov 19 '20 at 20:32
But no matter how you pass it, you need to spell the variable right. — Barmar, Nov 19 '20 at 20:34
@Barmar this is what it returns when i echo $ciudad $oficio $num (which are the rows that i got from the fetch) and the $datos array (i already fixed it to push the $fila array inside of it): BahiaBlancaPlomeria0Array ( [records] => Array ( ) ) — le0nicolas, Nov 19 '20 at 20:37
@GrumpyCrouton i already tried doing that: // $parametros = [":oficio" => $oficio, ":ciudad" => $ciudad]; // $stmt -> execute($parametros); but im still getting nothing — le0nicolas, Nov 19 '20 at 20:38
That means there are no matches for the values in your table. — Barmar, Nov 19 '20 at 20:39
@Barmar but thats not possible, i do have values in those tables. Also when i run the query on phpmyadmin it does return like 2 rows at least — le0nicolas, Nov 19 '20 at 20:40
Check for special characters like spaces in the variables. Use `var_dump` instead of `echo`. — Barmar, Nov 19 '20 at 20:42
Configure the proper error reporting first, make sure there are no errors in the query executions and then make sure there is the data to mach the input parameters — Your Common Sense, Nov 19 '20 at 20:48

BindParam not returning any values - PHP PDO

0 Answers0