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:
If I forget any needed information just tell me and I'll add if possible :)
This is the PHP class I am using RTmysqli and example php code at HERE
Usage:
Hope it helps. :)