CODEWITHSUNDEEP
mysqlprepared-statement
Steven
Steven

Reputation: 119

mathematical operator as a variable in mysql query

I am wondering if it is possible to use a mathematical operator for a variable in a prepared mysql statement? I have written what I think it would look like below (albeit it doesn't work, I get "Call to a member function bind_param() on a non-object").

$result = $mysqli->prepare("SELECT t.column FROM table t WHERE t.value ( ? ) ( ? );");

$result->bind_param('ss', $operator, $value);
$result->execute();

I am using this along with an if statement that changes the operator value based on if a radio button is checked on the greater or less than value. Like below.

if (isset($_POST["abovebelow"]) && $_POST["abovebelow"] == "Above"){

    $operator = ">";

}

elseif (isset($_POST["abovebelow"]) && $_POST["abovebelow"] == "Below"){

    $operator = "<";

}

elseif (!isset($_POST["abovebelow"])){

    $operator = "=";

}

Upvotes: 0

Views: 967

Answers (3)

Barmar
Barmar

Reputation: 781751

No, it's not possible. Placeholders in a prepared statement are only allowed in places where expressions are allowed. So you can't use a placeholder for a table or column name, and you can't use it for syntactical elements like operators.

If you need to substitute those things dynamically, you need to use string operations in PHP, not prepared statement placeholders.

Upvotes: 0

Darwin von Corax
Darwin von Corax

Reputation: 5246

The short answer is, "No, you can't do that."

The long answer is, "No, you can't do that." Among other reasons is that the complier must be able to parse the complete query, which means it must know the operators at parse time.

Emil Borconi suggests a good work-around, that you could insert the operator into the text of the query, and then prepare it.

Upvotes: 1

Emil Borconi
Emil Borconi

Reputation: 3467

You have only 2 variables in your query string but you bind 3 values, so that isn't right in the first place.Then you shouldn't add the operator like that, better do like this:

if (isset($_POST["abovebelow"]) && $_POST["abovebelow"] == "Above"){

    $operator = ">";

}

elseif (isset($_POST["abovebelow"]) && $_POST["abovebelow"] == "Below"){

    $operator = "<";

}

elseif (!isset($_POST["abovebelow"])){

    $operator = "=";

}

$result = $mysqli->prepare("SELECT t.column FROM table t WHERE t.value".$operator." ? ;");

$result->bind_param($value);
$result->execute();

Upvotes: 1

Related Questions