When to use PDO exec() vs execute() vs query() [duplicate]

Question

Below are snippets of W3's examples using these methods (with links). I get anxiety wondering if the material I'm spending so much time studying is the most current and commonly used conventions.

There is this Stack Overflow post. However this post didn't answer the when to use the above methods. Also it advocates to ONLY use prepare()/execute() for reason given. If this is true, it seems to imply the examples in W3 out of date. As a student trying to learn, I find W3 to be user friendly. I like the organization of info into segments without an overwhelming amount of detail (so for that reason I prefer not to be referred to php.net or something like that at the moment). Also I'm told W3Schools is kept up-to-date (which again to me means conventionally used not necessarily bleeding edge) and I would be interested to hear from experienced programmers if they agree.

Are the methods used in the examples considered "best practices" as I'd hope or is the SO post above right and why?

W3school examples:

MySQL Create Table uses exec():

// use exec() because no results are returned
$conn->exec($sql);
echo "Table MyGuests created successfully";

MySQL Update Data uses prepared statement and execute():

// Prepare statement
$stmt = $conn->prepare($sql);

// execute the query
$stmt->execute();

// echo a message to say the UPDATE succeeded
echo $stmt->rowCount() . " records UPDATED successfully";

MySQL Select Data uses query() as such:

$sql = "SELECT id, firstname, lastname FROM MyGuests";
$result = $conn->query($sql);

Post Script Context: This is my first SO Post, I hope it will be helpful. I'm learning MySQL along with HTML/CSS/Javascript/PHP using W3Schools which is the designated resource for an online Internet Programming College Course I'm taking. It is the instructors first time teaching the course and he is admittedly rusty. I like to visit his office to ask questions in person when I can because I find it easier to communicate. However it often results in him googling it right then and there which makes me wonder if all the answers he's giving is just the first thing HE finds Googling vs an answer that is considered a best practice (what I hope for). After many years, I am back in college pursuing a second degree in Computer Science. This time around though, I must say it seems more like being made aware of things and referred to go online to figure out the "how" than my courses of the past.

Answer 1

I made a flow chart to try to help you determine which you should be using for any given situation:

The reason we must use PDOStatement::prepare() and bound variables for user data is to prevent SQL injection attacks. You should never put user input directly into a query. In fact, this is just personal preference, but even if I'm hard-coding a value into a query, I will typically use PDOStatement::prepare() anyway. Maybe today I'm hard-coding it. Tomorrow I pull it from the database, and after that I allow users to edit that field in the database. But will I remember to go back and update the query to use prepared statements? Probably not. Binding the variables to the query will also prevent accidental syntax errors in the query as well.

The reason to use PDOStatement::prepare() when reusing the query with different variables is because when you execute this, it sends the prepared statement to the database server, but not the values. When you execute() the PDOStatement you then send the values to the database server. So for subsequent queries, all you need to send are the values and not the whole query.

However, if you decide to use PDO::query() instead of PDO::exec(), or if you use PDOStatement::prepare() instead of either of the other two, it is not a security issue, but more of an efficiency issue.

P.S. Sorry for the mouse.