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!

`$mysqli` isn't defined... – Tom Udding Dec 03 '17 at 19:45 — Tom Udding, Dec 03 '17 at 19:45

Decent Dabbler · Accepted Answer · 2017-12-04T03:27:20.023

2

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.}

edited Dec 04 '17 at 03:27

answered Dec 03 '17 at 19:55

Decent Dabbler

22,532
8
74
106

Thanks! Do you mean this like this "$conn = mysqli_connect($servername, $username, $password, $dbname);"? Nvm saw your edit. – Leyer Dec 03 '17 at 20:00
@Leyer Yes, but then make sure you use the correct variable name (`$conn->` instead of `$mysqli->`) in the rest of your code, when you want to interact with the connection. – Decent Dabbler Dec 03 '17 at 20:03
1

*"You cant mix both styles."* - That's false. It's not recommented but they do work together. I quote from the manual http://php.net/manual/en/mysqli.quickstart.dual-interface.php under "Mixing styles": *"It is possible to switch between styles at any time. Mixing both styles is not recommended for code clarity and coding style reasons."* – Funk Forty Niner Dec 04 '17 at 02:34
What you posted doesn't outline what the real problem was and the question was closed in that respect. Oh sure it fixed their code, but that was obvious; they used the wrong variable which is exactly what they posted as the error that they got back: `Notice: Undefined variable: mysqli in ...list.php on line 26` - In using the correct variable, the rest of the code then proceeded in a successful manner. – Funk Forty Niner Dec 04 '17 at 02:37
@Fred-ii- I was convinced (I even thought I had read it in the manual at some point) that the API styles wouldn't mix. I must have confused this with some other OOP/procedural PHP API. Thank you for pointing this out. And to address your second comment: since OP's code snippet didn't show any instantiation of a connection, I interpreted the question as though OP forgot to do this, in stead of OP simply addressing the wrong variable name, which would both lead to the same notice. – Decent Dabbler Dec 04 '17 at 03:17
@DecentDabbler it can be a bit difficult to find those facts in the manuals at times and there are even some that are undocumented. I find those out here on Stack also, so I'm sharing the wealth as it were, *cheers* and you're welcome. – Funk Forty Niner Dec 04 '17 at 11:00

score -3 · Answer 2 · answered Dec 03 '17 at 19:50

-3

it's look like you didn't defined the object properly.

$connection = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

answered Dec 03 '17 at 19:50

Shlomi Fenster

67
6

OP is not using the PDO extension, but the MySQLi extension. – Decent Dabbler Dec 03 '17 at 19:54
I had the variable misspelled. Thanks! – Leyer Dec 03 '17 at 19:59

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

2 Answers2