PHP - Mysqli Prepared Statement - Join Tables

<?php
class Database {
 
    private $host;
    private $user;
    private $pass;
    private $name;
    private $link;
    private $error;
    private $errno;
    private $query;
 
    function __construct($host, $user, $pass, $name = "", $conn = 1) {
        $this -> host = $host;
        $this -> user = $user;
        $this -> pass = $pass;
        if (!empty($name)) $this -> name = $name;      
        if ($conn == 1) $this -> connect();
    }
 
    function __destruct() {
        @mysql_close($this->link);
    }
 
    public function connect() {
        if ($this -> link = mysql_connect($this -> host, $this -> user, $this -> pass, TRUE)) {
            if (!empty($this -> name)) {
                if (!mysql_select_db($this -> name, $this->link)) $this -> exception("Could not connect to the database!");
            }
        } else {
            $this -> exception("Could not create database connection!");
        }
    }
 
    public function close() {
        @mysql_close($this->link);
    }
 
    public function query($sql) {
        if ($this->query = @mysql_query($sql, $this->link)) {
            return $this->query;
        } else {
            $this->exception("Could not query database!");
            return false;
        }
    }
 
    public function num_rows($qid) {
        if (empty($qid)) {         
            $this->exception("Could not get number of rows because no query id was supplied!");
            return false;
        } else {
            return mysql_num_rows($qid);
        }
    }
 
    public function fetch_array($qid) {
        if (empty($qid)) {
            $this->exception("Could not fetch array because no query id was supplied!");
            return false;
        } else {
            $data = mysql_fetch_array($qid);
        }
        return $data;
    }
 
    public function fetch_array_assoc($qid) {
        if (empty($qid)) {
            $this->exception("Could not fetch array assoc because no query id was supplied!");
            return false;
        } else {
            $data = mysql_fetch_array($qid, MYSQL_ASSOC);
        }
        return $data;
    }
 
public function fetch_object($qid) {
        if (empty($qid)) {
            $this->exception("Could not fetch object assoc because no query id was supplied!");
            return false;
        } else {
            $data = mysql_fetch_object($qid);
        }
        return $data;
    }
 
    public function fetch_all_array($sql, $assoc = true) {
        $data = array();
        if ($qid = $this->query($sql)) {
            if ($assoc) {
                while ($row = $this->fetch_array_assoc($qid)) {
                    $data[] = $row;
                }
            } else {
                while ($row = $this->fetch_array($qid)) {
                    $data[] = $row;
                }
            }
        } else {
            return false;
        }
        return $data;
    }
 
    public function last_id() {
        if ($id = mysql_insert_id()) {
            return $id;
        } else {
            return false;
        }
    }
 
    private function exception($message) {
        if ($this->link) {
            $this->error = mysql_error($this->link);
            $this->errno = mysql_errno($this->link);
        } else {
            $this->error = mysql_error();
            $this->errno = mysql_errno();
        }
        if (PHP_SAPI !== 'cli') {
        ?>
 
            <div class="alert-bad">
                <div>
                    Database Error
                </div>
                <div>
                    Message: <?php echo $message; ?>
                </div>
                <?php if (strlen($this->error) > 0): ?>
                    <div>
                        <?php echo $this->error; ?>
                    </div>
                <?php endif; ?>
                <div>
                    Script: <?php echo @$_SERVER['REQUEST_URI']; ?>
                </div>
                <?php if (strlen(@$_SERVER['HTTP_REFERER']) > 0): ?>
                    <div>
                        <?php echo @$_SERVER['HTTP_REFERER']; ?>
                    </div>
                <?php endif; ?>
            </div>
        <?php
        } else {
                    echo "MYSQL ERROR: " . ((isset($this->error) && !empty($this->error)) ? $this->error:'') . "\n";
        };
    }
 
}
?>

<?php

//COOKIE CHECKER
if (isset($_COOKIE["person"])){

if (filter_var($_COOKIE["person"], FILTER_VALIDATE_INT)){
$user_id = $_COOKIE["person"];

//DATABASE CONNECTION VARIABLES
$myserver ="localhost";
$myname = "username";
$mypassword = "password";
$mydb ="dbname";

/*SQL CONNECTION*/
// Create connection
$conn = new mysqli($myserver, $myname, $mypassword, $mydb);

// Check connection
if ($conn->connect_error) {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>
<H1>Decline of the Han - Three Kingdoms</H1>';
die("Connection failed: " . $conn->connect_error);
echo '</BODY>
</HTML>';
}
else {

//COUNT USER
$cquery = "SELECT COUNT(*) AS usercheck FROM Players WHERE ID = ?";
$cid = $conn->prepare($cquery);
$cid->bind_param('i', $user_id);
$cid->execute();
$cid->bind_result($usercheck);
$cid->fetch();

if ($usercheck ==1){

if (isset($_POST["profile"])){

if(!filter_var($_POST["profile"], FILTER_SANITIZE_STRING)){
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">Unable to filter bio <a href="biography.php">return</a></P>';
echo '</BODY>
</HTML>';
}
else {

$profile = $_POST["profile"];

$sql = "UPDATE Player_Data SET Bio =? WHERE ID=?";
$q = $conn->prepare($sql);
$q->bind_param("si", $profile, $user_id);
$q->execute();

echo '<P>Biography altered <a href="biography.php">return</a></P>';

//close connection
$conn->close();

}

}

}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">'.$usercheck.' '.$user_id.'</P>';
echo '<P class="error">No such user found!</P>';
//close connection
$conn->close();
//foot
echo '</BODY>
</HTML>';
}

//end connection check
}

}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">ERROR invalid cookie!</P>';
echo '</BODY>
</HTML>';
}

}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">No cookie detected!<br><a href="login.php">login</a></P>';
echo '</BODY>
</HTML>';
}

?>

<?php

//COOKIE CHECKER
if (isset($_COOKIE["person"])){

if (filter_var($_COOKIE["person"], FILTER_VALIDATE_INT)){
$user_id = $_COOKIE["person"];

//DATABASE CONNECTION VARIABLES
$myserver ="localhost";
$myname = "username";
$mypassword = "password";
$mydb ="dbname";

/*SQL CONNECTION*/
// Create connection
$conn = new mysqli($myserver, $myname, $mypassword, $mydb);

// Check connection
if ($conn->connect_error) {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>
<H1>Decline of the Han - Three Kingdoms</H1>';
die("Connection failed: " . $conn->connect_error);
echo '</BODY>
</HTML>';
}
else {

//COUNT USER
$cquery = "SELECT COUNT(*) AS usercheck FROM Players WHERE ID = ?";
$cid = $conn->prepare($cquery);
$cid->bind_param('i', $user_id);
$cid->execute();
$cid->bind_result($usercheck);
$cid->fetch();

if ($usercheck ==1){

if (isset($_POST["profile"])){

if(!filter_var($_POST["profile"], FILTER_SANITIZE_STRING)){
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">Unable to filter bio <a href="biography.php">return</a></P>';
echo '</BODY>
</HTML>';
}
else {

$profile = $_POST["profile"];

$sql = "UPDATE Player_Data SET Bio =? WHERE ID=?";
$q = $conn->prepare($sql);
$q->bind_param("si", $profile, $user_id);
$q->execute();

echo '<P>Biography altered <a href="biography.php">return</a></P>';

//close connection
$conn->close();

}

}

}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">'.$usercheck.' '.$user_id.'</P>';
echo '<P class="error">No such user found!</P>';
//close connection
$conn->close();
//foot
echo '</BODY>
</HTML>';
}

//end connection check
}

}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">ERROR invalid cookie!</P>';
echo '</BODY>
</HTML>';
}

}
else {
echo '<!DOCTYPE HTML>
<HTML>
<HEAD>
<TITLE>test</TITLE>
</HEAD>
<BODY>';
echo '<P class="error">No cookie detected!<br><a href="login.php">login</a></P>';
echo '</BODY>
</HTML>';
}

?>

Hello there!

I've got this code:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
error_reporting(E_ALL);

if (isset($_POST["id"]) && !empty($_POST["id"])) {

    require_once "login.php";

    $stmt = $conn->prepare("DELETE FROM teamlid WHERE lidnummer = ?");
    $stmt->bind_param("i", $param_id);
    $param_id = $_POST["id"];
    $stmt->execute();

        if ($stmt) {
            header("location: teamsTabel.php");
            exit();
        } else {
            echo "Oeps, er is iets verkeerds gegaan. Probeer het later nog een keer.";
        }

    mysqli_close($conn);
}

?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>Gegevens inzien</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
    <style type="text/css">
        .wrapper {
            width: 500px;
            margin: 0 auto;
        }
    </style>
</head>

<body>
    <div class="wrapper">
        <div class="container-fluid">
            <div class="row">
                <div class="col-md-12">
                    <div class="page-header">
                        <h1>Gegevens verwijderen</h1>
                    </div>
                    <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
                        <div class="alert alert-danger fade in">
                            <input type="hidden" name="id" value="<?php echo trim($_POST["id"]); ?>" />
                            <p>Weet je zeker dat je deze gegevens wilt verwijderen?</p><br>
                            <p>
                                <input type="submit" name="submitDeleteBtn" value="Ja" class="btn btn-danger">
                                <a href="teamsTabel.php" class="btn btn-default">Nee</a>
                            </p>
                        </div>
                    </form>
                </div>
            </div>
        </div>
    </div>
</body>

</html>

Another PHP script I tried didn't work either:

<?php

if (isset($_POST["id"]) && !empty($_POST["id"])) {

    require_once "login.php";

    // DELETE query om op basis van lid.lidnummer en postcode.postcode de tabellen te legen. 
    $sql = "DELETE lid, postcode FROM lid INNER JOIN postcode WHERE lid.postcode = postcode.postcode AND lid.lidnummer = ?";

    if ($stmt = mysqli_prepare($conn, $sql)) {
        mysqli_stmt_bind_param($stmt, "s", $param_id);

        $param_id = trim($_POST["id"]);

        if (mysqli_stmt_execute($stmt)) {
            header("location: read.php");
            exit();
        } else {
            echo "Oeps, er is iets verkeerds gegaan. Probeer het later nog een keer.";
        }
    }

    mysqli_stmt_close($stmt);

    mysqli_close($conn);
} else {
    if (empty(trim($_GET["id"]))) {
        header("location: error.php");
        exit();
    }
}
?>

But they're both not doing what I want: deleting the rows in the "teamlid" table where the id matches the column "lidnummer".

I'm not getting any errors. When using the delete statement directly in MySQL using a hardcoded "lidnummer" it works. But it's not using the lidnummer here to delete the corresponding columns in the table.

I can't get my head around it. Do you guys have any ideas? Thanks!

I have an array that has comma sep values.

I am wanting to run a select statement that includes a NOT IN function.

SELECT user_firstname, user_lastname, user_id
                            FROM ssm_user
                            WHERE user_role_id = ? and where user_id NOT IN(?)

Is this possible, oir does the ? just represent a literal string.

If so do i need to convert the array to a string before running the above?

After deciding to venture into the realm of prepared statements, I have this line in my script

if ($stmt = $conn->prepare('SELECT username FROM users WHERE username = ?')) {

Everything was working fine. 

I reviewed my code to adjust it to my old habits, and realized that I had hardcoded the TABLE NAME rather than using a variable.

I updated my code to

$table = "users";

if ($stmt = $conn->prepare('SELECT username FROM $table WHERE username = ?')) {

and results from my SELECT statement vanished.

Is the use of a variable for a table's name outdated? Even possible??

$stmt = $mysqli->prepare("SELECT d.id,d.val FROM datalog pd LEFT JOIN datafeeds d ON d.id = pd.did WHERE pd.pid = ? AND pd.aa = ? AND pd.bb = ?");
$stmt->bind_param('sii', $id,$aa=1,$bb=1);
$stmt->execute();
$stmt->bind_result($id,$val);
mysqli_stmt_store_result($stmt);
if(mysqli_stmt_num_rows($stmt) != 0)
{
while ($stmt->fetch())
{
$array[] = array('numA'=>$id, 'numB'=>$val);
}
}
return $array;

$array = array(1,2,4,7,9);

d.type IN ($array)