Should I define my SQL statement statement handle using separate variables in PHP?

Question

Are all 3 options the same (Y/N) or is one better (A/B/C)?

Option A - (1) defining the SQL string in the variable $sql and (2) define the statement handle using the "method" prepare on the variable $sql to compose the statement and (3) activating composition of the statement in step 2 using the execute function.

    $sql = "SELECT * FROM table1";
    $sth = $dbh->prepare($sql);
    $sth->execute();

Option B - Similar to Option C because the ->query method is used directly on the db object and similar to Option A in that the sql statement is kept separate.

    $sql = "SELECT * FROM table1";
    $sth = $dbh->query($sql);

Option C - The statement handle is the sql query itself (no reference to any another variables and just using one method.

    $sth = $dbh->query("SELECT * FROM table1");

Questions:

Do both options yield the same result?

If they yield the same result, is one approach recommended (i.e. best practice)?

Did I get the vocabulary like "variable", "method", and "function" correct?

Does Option A still query the db despite not explicitly using the ->query() method?

What are the pros/cons of including the sql stmnt in a seperate variable vs in the PDO Statement?

Answer 1

I would say both methods are the same about using a string variable or direct text string.

Assuming you using PDO there is difference between prepare statement and invoke a query statement.

In case A, the prepare statement will make the database create a plan so that statement can be reexecuted without reparsing the query string.

In case B the query is executed at once.

In your given example case B would run a little bit faster. But if your statement uses arguments case A would benefit you with additional security due to placeholders been replaced by the driver.

Answer 2

Do both options yield the same result?

Yes.

If they yield the same result, is one approach recommended (i.e. best practice)?

Both are fine. In your particular example Option B gets the same job done with less code.

However, if you need to use parameters and/or constraints in your query (e.g. ...WHERE id = :id) you need to opt for option A to bind params using the $dbh->bindParam(':id', $id, PDO::PARAM_INT) method after your prepare your statement, e.g:

$dbh->prepare('UPDATE table SET column = :value WHERE id = :id');
$dbh->bindParam(':value', $someNewValue, PDO::PARAM_STR);
$dbh->bindParam(':id', $targetId, PDO::PARAM_INT);
$dbh->execute();

Doing it this way will also protect you from nasty things like SQL injections.

Storing the query in a separate variable is only needed if you plan to re-use that query at a later time in your code. Otherwise you might as well just type it within your prepare/query method directly.

Did I get the vocabulary like "variable", "method", and "function" correct?

Looks about right! Except: "the execute function" is a method. Functions and methods are basically the same things except when they belong to an object they are referred to as methods. In your example execute belongs to the $sth object, so it's called a method.

Does Option A still query the db despite not explicitly using the ->query() method?

Yes. The execute method executes the query that was prepared in $dbh->prepare(...). If you want to use parameters you can call ->bindParam() between your prepare and execute methods. If you don't need parameters, invoking ->query() directly is really the more convenient way to do it.