CODEWITHSUNDEEP
phpmysql
ForJ9
ForJ9

Reputation: 745

MySQL Prepared Statement failed

I've written a php-code which should print the output of the table.

function getPlanets($x, $y) {

    $stmt = $this->MySQLConnection->prepare("SELECT * FROM planets WHERE (x >= (? - 50) AND x <= (? + 50)) AND (y >= (? - 50) AND y <= (? - 50))");

    $stmt->bind_param("iiii", $x, $x, $y, $y);
    $stmt->execute();
    $stmt->store_result();

    if($this->MySQLConnection->errno) {
        printf("SQL-Error: %s\n" + $this->MySQLConnection->error);
    }

    if($stmt->num_rows == 0) {
        return json_encode(array("error" => "0"), JSON_PRETTY_PRINT);
    }

    $infoArray = array();

    while($obj = $stmt->get_result()->fetch_object()) {
        $infoArray["id"] = $obj->id;
        $infoArray["name"] = $obj->name;
        $infoArray["x"] = $obj->x;
        $infoArray["y"] = $obj->y;
        $infoArray["systems"] = $obj->systems;
    }
}

The problem is that num_rows always return "0". If I use normal MySQLi-Query with the same SQL-Command it works.

Upvotes: 0

Views: 35

Answers (1)

Marc B
Marc B

Reputation: 360672

Typo:

[..snip..](x >= (? - 50) AND x <= (? + 50)) AND (y >= (? - 50) AND y <= (? - 50))")
                                                                           ^---

The indicated spot should probably be +. As written, it basically resolves to where y = ?

You could simplify this a lot:

WHERE (? BETWEEN (x-50) AND (x+50)) AND (? BETWEEN (y-50) AND (y+50))
or
WHERE (abs(x - ?) <= 50) AND (abs(y - ?) <= 50)

Upvotes: 3

Related Questions