No result when using prepared statements

153 views Asked by At

I want to create a simple select with prepared statements but as result I just get NULL :( When I do it without prepared statements everything is working fine.

<?php
  class DbHandler{
    public function select($columns, $table_name, $alias, $where, $order){
      //echo(phpinfo());

      $db = new mysqli("localhost", "root", "", "superhelden");
      if(!$db){
        exit("Verbindungsfehler: ".mysqli_connect_error());
      }

      if(empty($columns)){
        $columns = "*";
        trigger_error("No columns chosen. Value set to *.", E_USER_WARNING);
      } else{
        $prepColumns = $columns;
      }

      if(empty($table_name)){
        trigger_error("Tablename must not be empty.", E_USER_ERROR);
      }

      if(empty($where)){
        trigger_error("WHERE is empty so no conditions are set. All entries will be selected.", E_USER_WARNING);
      }

      //I don't know why but I can't use a param for the tablename
      $query = "SELECT ? FROM $table_name";

      if(!empty($alias)){
        $query .= " AS ?";
      }

      if(!empty($where)){
        $query .= " WHERE ?";
      }

      //This is working..
      $query1 = "SELECT name FROM karten WHERE name='Fausthieb'";
      $res = $db->query($query1);
      while($row = $res->fetch_assoc()){
        echo($row["name"] . "<br>");
      }
      //.... :(

      if(empty($order)){
        //Show created query
        echo("$query  ||  ");
        if($prep = $db->prepare($query)){
          if(!empty($alias)){
            if(!empty($where)){
              $prep->bind_param("sss", $prepColumns, $alias, $where);
            } else{
              $prep->bind_param("ss", $prepColumns, $alias);
            }
          } else if(!empty($where)){
            //Show params of function
            echo("columns: $prepColumns  ||  ");
            echo("Where: $where  ||  ");
            $prep->bind_param("ss", $prepColumns, $where);
          } else {
            $prep->bind_param("s", $prepColumns);
          }
//            print_r($prep->result_metadata());
//            echo(var_dump($prep));
          $prep->execute();
          var_dump($prep->error);
          echo("  ||  ");
          $prep->bind_result($result);
          $prep->fetch();
          echo(gettype($result));
          $prep->close();
        }else{
            var_dump($db->error);
        }
      } else {
        $query .= " ORDER BY ?";
        if($prep = $db->prepare($query)){
          if(!empty($alias)){
            if(!empty($where)){
              $prep->bind_param("ssss", $prepColumns, $alias, $where, $order);
            } else{
              $prep->bind_param("sss", $prepColumns, $alias, $order);
            }
          } else if(!empty($where)){
              $prep->bind_param("sss", $prepColumns, $where, $order);
          } else {
              $prep->bind_param("ss", $prepColumns, $order);
          }

          $prep->execute();
          $prep->bind_result($result);
          $prep->fetch();
          echo($result);
          $prep->close();
        }else{
            var_dump($db->error);
        }
      }
    }
  }
?>

This code is calling my select function:

include("DbHandler_dominic.php");

$test = new DbHandler();

$test->select("name", "karten", "", "name='Fausthieb'", "");

?>

And then I got this one which works just fine.

<?php  
  include("dbconnect.php");
  $pepper = "KratzigeStirn?!";

  $username = $_POST["username"];
  $prep = $db->prepare("SELECT name FROM spieler WHERE name=?");
  $prep->bind_param("s", $username);
  $prep->execute();
  $prep->bind_result($user);
  $prep->fetch();
  $prep->close();

  $email = $_POST["email"];

  $prep = $db->prepare("SELECT email FROM spieler WHERE email=?");
  $prep->bind_param("s", $email);
  $prep->execute();
  $prep->bind_result($mail);
  $prep->fetch();
  $prep->close();

  if($user == "" && $mail == ""){
    $password = password_hash($_POST["password"].$pepper, PASSWORD_BCRYPT);

    $prep = $db->prepare("INSERT INTO spieler(name, passwort, email) VALUES(?, ?, ?)");
    $prep->bind_param("sss", $username, $password, $email);

    $prep->execute();

    $prep->close();
  } else if($user == $username){
    echo "Benutzer existiert schon..";
  } else if($mail == $email){
    echo "E-Mail bereits vergeben..";
  }

  $db->close();
?>

What's the difference? And what can I do in order for the SELECT to work :/

My DB Diagram and Table properties:

DB relationdiagram

Table properties

If I forget any needed information just tell me and I'll add if possible :)

1

There are 1 answers

2
No name On

This is the PHP class I am using RTmysqli and example php code at HERE

Usage:

$RTmysqli= new RTlib\RTphp\RTmysqli();
$RTmysqli->config(DB_HOST, DB_UNAME, DB_PWORD, DB_NAME);

$result = $RTmysqli->query("SELECT * FROM slider ORDER BY id");

if (!empty($result)) {
    foreach($result as $row) {
         print "<br>" . $row["id"] . "(" . $row["title"] . ")\n";
    }
}

Hope it helps. :)