PHP
Running queries with query()
Learn to run a SQL query directly with query().
Running SQL with query()
query() runs a SQL statement and returns a PDOStatement object. You read result rows from that object.
Example: $stmt = $pdo->query("SELECT name FROM users"). On the returned $stmt you call fetch() or fetchAll().
Use query() only for fixed SQL that contains no external values. When user data is involved, prepare() is used; that comes in the next group.
query() vs exec()
query() and exec() do different things. query() is for SELECT-like statements that return rows; it gives the result as a PDOStatement.
exec() is for INSERT, UPDATE, DELETE and other statements that do not return rows; it returns the affected row count as an integer.
So when you read rows, use query(); when you make changes, use exec().
Exercise Print the first name with query()
Create a users table in in-memory SQLite and insert three users. Run SELECT name FROM users ORDER BY id with query(); fetch the first row with fetch(PDO::FETCH_ASSOC) and print the name field.
Show example solution
:FETCH_ASSOC);. Print with echo $row['name'] . "\n";.
Example solution:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$pdo->exec("INSERT INTO users (name) VALUES ('Sam')");
$pdo->exec("INSERT INTO users (name) VALUES ('Dana')");
$stmt = $pdo->query("SELECT name FROM users ORDER BY id");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name'] . "\n";
Test yourself
-
When should query() be used?
- To embed user values into SQL
- To compile a query and run it later
- To run fixed SQL with no external values
- To silently swallow errors
Show answer
To run fixed SQL with no external values — Correct. query() suits fixed SQL; when external values are needed, prepare() is used.
-
Pick the right method for SELECT
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$stmt = $pdo->____("SELECT name FROM users");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name'] . "\n";
?>
Show answer
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$stmt = $pdo->query("SELECT name FROM users");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name'] . "\n";
?>
-
Match the query and exec concepts
- query()
- SELECT that returns rows
- exec()
- INSERT, UPDATE, DELETE
- PDOStatement
- Returned by query()
- affected rows
- Returned by exec()
Next lesson: Single row with fetch()
