How to use prepared statements to get results and use it in a while loop?

Question

I want to use prepared statements instead of my current code:

    if(isset($_POST['submit']) && $_POST['checkGame'] != 'Any')
    {
        $game = $_POST['checkGame'];
        $sql="SELECT ipaddress, port FROM servers WHERE game=('$game')";
            $result=mysqli_query($con,$sql);
            while ($row=mysqli_fetch_array($result)) {
            array_push($serverConnectionArray, ["address" =>$row['ipaddress'], "port" =>$row['port']]);
    }
    }

I've tried:

    if(isset($_POST['submit']) && $_POST['checkGame'] != 'Any')
    {
        $game = $_POST['checkGame'];
        $stmt = $mysqli->prepare("SELECT ipaddress, port FROM servers WHERE game=?");
        $stmt->bind_param("s", $game);
        $stmt->execute();
        $result = $stmt->get_result();
        $stmt->fetch();
            while ($row=mysqli_fetch_array($result)) {
            array_push($serverConnectionArray, ["address" =>$row['ipaddress'], "port" =>$row['port']]);
        $stmt->close();
    }
    }

And more. However, I get this error:

Notice: Undefined variable: mysqli in ...list.php on line 26

Fatal error: Uncaught Error: Call to a member function prepare() on null in ...list.php:26 Stack trace: #0 {main} thrown in ...list.php on line 26

Thanks!

Answer 1

First of all, you are mixing API styles; the MySQLi extension can be used in two ways: either with the object oriented (OOP) approach ($mysqli->) or the procedural approach (the mysqli_* functions). You cant mix both styles. ¹

It appears as though you want to use the OOP style. In that case, you first need to create the actual mysqli object (also called an instance of the mysqli class), with something like: ²

$mysqli = new mysqli('127.0.0.1', 'username', 'password', 'dbname');

See the basic examples in the documentation for more information on how to proceed after that.

---

^{1) See @Fred-ii-'s comment below: it turns out the interfaces can actually be mixed. I was not aware of this.
2) The question is ambiguous about whether OP simply addressed the wrong variable name, or whether OP completely forgot to instantiate a MySQLi connection in the first place; both would emit the same notice.}