Executing simple non-SELECT queries

We have seen how to use the query method to submit select queries. You can also use it for non-select queries but there is another method called exact that's probably more useful for insert, update and delete queries. Let's take a look at both query and exact and see how they differ. This is query_insert.php, which you can find in the chapter 2, 02_07 folder of the exercise files. On lines four and five is an insert statement that adds a new entry to the names table.

So, let's submit that using query. And we'll restore the return value as result. So, our database connector, db. Then the query method, and we pass it the SQL. And to find out what result contains we'll use var_dump and pass it result. So, if we save that and then load the page into a browser. The result that we get is objects PDOStatement public queryString and then the value of the insert statement that we've just used.

This isn't really very useful because this works only when query succeeds. If the query fails, it returns false. But if you do want to inspect the contents of a non-select statement that is submitted using query, then you can use the queryString property. Let's just see how that's done. So, instead of dump, we can use echo and then it's result and the arrow operator queryString. And if we save that, go back to the browser and refresh the page.

It'll insert the same value again but that doesn't really matter, this is a test database. So if we refresh it, there's is that insert statement, that succeeded using the query method. But when you're using a non-select statement, a statement that is going to make changes to your database, it's usually more useful to get details of what's actually happened. And that's where the exact method comes in. So let's go back to the editing program and change to exec_insert.php.

That's in the same exercise folder as 02_07. We've got exactly the same insert statement here. This time, instead of using query, we're using the exec method. So that's the only difference. And we're using var_dump to see what that contains. So let's just load that into a browser. And what we've got here is int 1. Basically, what it's doing is it's telling us that one row has been affected in our database.

That's much more useful. So, let's go back to the editing program and we'll change result to affected, and instead of dump what we'll do is we will echo affected row inserted. And it's also useful at times to find out if it's being given and id, this only works with some databases, and it relies on the database using auto increment ids, the way in which you can get that is using the last insert id method.

So, if we do that With ID and then concatenate on the end of that. It's the database object that contains the last insert ID method. So let's just get that lastInsertId. And then if we save that and go back to the browser. Refresh, we've now got one row inserted with ID 14. Although this can be rather useful, you need to be careful with the lastInsertId method.

It doesn't work in all databases and if the method isn't supported, it generates an error. And we'll be looking at error handling in the next video. And if you're using PostgreSQL, you need to pass the name of a sequence object as an argument to lastInsertId. And EXEC works exactly the same way with UPDATE and DELETE. We've got a lot of Williams in our database now so let's delete them. Go back to the editing program and exec_delete.php.

Delete from names where name equals William, so we'll create a variable called affected and then our database object and the exec method. We parse it at sql. And we can then echo affected records deleted. So we just save that. And I see I've got a little red error message up here. Yes, what we need is the concatenation operator in there.

Everything's turned green. Save that and then load it in the browser. Four records have been deleted. Wonderful. With two methods to choose from, the question arises as to which to use. The PDO query method returns different values depending on the type of query. With the select query, the returned value is the result set, but with insert update and delete queries, you simply get back a string containing the query that's just been executed. The PDO exec method always returns the number of rows affected.

So, exec is the better choice for INSERT, UPDATE and DELETE, but exec should should never be used for SELECT queries because it doesn't return a result set.