The most common programming mistakes 3

This part is mainly focused on working with data from a database. Especially on what is done unnecessarily and what can be done better with lesser server load. The examples require at least a basic knowledge of the object-oriented PDO driver and use a MySQL database.

Searching only for necessary data

There are two ways to search for data in SQL - to list individual columns or to write an asterisk (it'll search for all columns from a table). Using an asterisk is very simple and doesn't require much writing, but in most cases it also looks for columns that you don't need at all and don't use.

A simple example

We have a table with users that contains columns ID, username, first name, last name, date of birth and date of registration. We want to search for all users and display their first name, last name and registration date.

Using an asterisk, we'd write

$pdo->query("
  SELECT * FROM `user`
");

However, this query will look for all 6 columns, instead of the 3 we want. This means that more data will be transferred unnecessarily. For very small databases, this would have virtually no effect on performance, but imagine a database where 10,000 people are registered and a page with this query would be opened by, for example, 2,000 users at the same time. This would mean that 3 * 10k * 2k = 60M more blocks of data (3 extra columns, 10,000 rows and 2000 users on the page) would be transferred unnecessarily.

We wouldn't practically search for all 10,000 rows, but limit them in various ways with the LIMIT clause (e.g. in paging). However, for a small example of how much unnecessary data would be transferred, that is enough.

The solution is to list in a query only those columns that we really need.

$pdo->query("
  SELECT `nick`, `first_name`, `last_name`
  FROM `user`
");

Backticks

Sometimes it can happen that a table or column name will conflict with a database keyword. For example, if we had a column named text or from, such a query would be wrong:

$pdo->query("
  SELECT text, from
  FROM user
");

So we need to tell the database not to take our column names as keywords. Backticks are used for this.

$pdo->query("
  SELECT `text`, `from`
  FROM `user`
");

Personally, I recommend to always write backticks, even if names don't conflict. At the very least, you avoid the risk of adding a new keyword in a new version of the database that could conflict.

Manipulating rows

Databases contain a bunch of functions that we can use to obtain the exact row format. For example, if we want to sum a value of some column in all rows, we can do it as follows:

$sum = 0;
$query = $pdo->query("
  SELECT `amount`
  FROM `table`
");
$rows = $query->fetchAll(PDO::FETCH_OBJ);

foreach ($rows as $row) {
  $sum += $row->amount;
}

echo $sum;  // displaying the total sum

In this case, however, we write more code unnecessarily and at the same time get the column value from the database and only then sum it. Databases usually contain a function that calculates it for us and returns a result. In MySQL, there is a SUM() function for this.

$query = $pdo->query("
  SELECT SUM(`amount`)
  FROM `table`
");
$sum = $query->fetchColumn();

echo $sum;  // displaying the total sum

A very similar case is when getting the number of rows. We can do this in PHP, where we'll individually sum in a loop. If you're still using the old mysql_* driver, you could write something like this:

$query = mysql_query("
  SELECT `amount`
  FROM `table`
");
$rowsCount = mysql_num_rows($query);

echo $rowsCount;  // displaying the total sum

Again, we get the data unnecessarily and only then count rows. However, databases contain a COUNT() function that counts rows directly.

$query = $pdo->query("
  SELECT COUNT(*)
  FROM `table`
");
$rowsCount = $query->fetchColumn();

echo $rowsCount;  // displaying the total sum

We can also pass a name of a specific column to the COUNT() function. In this case, it calculates rows where a value of a specific column isn't NULL.

Databases contain many such functions. It's better to use these functions in a database query and not just in PHP.

Getting ID of the last row inserted

In certain cases, after inserting a new row into a table, we want to get its ID. We'll have a table with two columns - ID and username. This can be done in an unnecessarily complicated way:

// we'll omit ID from the column list because it's set as AUTO_INCREMENT
// and the database inserts a value into it automatically
$pdo->query("
  INSERT INTO `user` (`nick`)
  VALUES ('username')
");

$query = $pdo->query("
  SELECT MAX(`id`)
  FROM `user`
");
$lastId = $query->fetchColumn();

This code first inserts a new row into the table and then uses the MAX() function to select the highest number.

$query = $pdo->query("
  SELECT `id`
  FROM `user`
  ORDER BY `id` DESC
  LIMIT 1
");

$lastId = $query->fetchColumn();

Otherwise written, but it does the same. It simply selects the ID column's value, sorts from greatest, and selects only the first one (using LIMIT).

However, these queries are completely unnecessary. To get the last ID inserted, PDO has the lastInsertId() method.

$pdo->query("
  INSERT INTO `user` (`nick`)
  VALUES ('username')
");
$lastId = $pdo->lastInsertId();

Browsing rows

Unlike the old mysql_* driver, for example, PDO can return rows as we require. If we want only one value, we don't have to get it from some array or, conversely, we don't have to go line by line to get all rows, but PDO returns an entire list.

The FetchAll() method

As the name implies, the method returns all rows found. It stores them in a two-dimensional array in the syntax:

array (
  0 => array(
     // first line
  ),

  1 => array(
     // second line etc.
  )
)

This allows the array to go through a loop (usually used foreach) and work with individual rows.

$query = $pdo->query("
  SELECT `data`
  FROM `table`
");
$data = $query->fetchAll();

foreach ($data as $row) {
  // $row contains columns from the given row
}

If no data is found, a result is an empty array.

The Fetch() method

The fetch() method returns a one-dimensional array according to the given row. It's used when we're looking for one specific row, such as user information.

$id = (int) $_GET["id"];

$query = $pdo->query("
  SELECT `first_name`, `last_name`
  FROM `user`
  WHERE `id` = {$id}
  LIMIT 1
");
$data = $query->fetch(PDO::FETCH_OBJ);

echo $data->first_name;  // printing the user's first name

If no matching row is found, the method returns FALSE. It's very easy to find out if a row has been found or not. This can be used, for example, for user login.

$query = $pdo->query("
  SELECT `id`
  FROM `user`
  WHERE `nick` = 'some username' AND `password` = 'some password'
  LIMIT 1
");
$data = $query->fetch(PDO::FETCH_OBJ);
if ($data !== FALSE) {
  $_SESSION["userId"] = $data->id;
  // atd.

} else {
  echo "We're sorry, but a user with such a name or password doesn't exist.";
}

The FetchColumn() method

This method returns a value of one specific column in one row. It's often used, for example, when counting rows.

$query = $pdo->query("
  SELECT COUNT(*)
  FROM `user`
");
$usersCount = $query->fetchColumn();  // the variable contains the number of all users

If no value is found, the method returns FALSE.

Output data format

This part is closely related to the previous one. Not only we can determine the individual grouping of data, but also in what format it'll be returned. For example, we can store data only in an associative array or in an object.

$id = (int) $_GET["id"];

$query = $pdo->query("
  SELECT `first_name`, `last_name`, `email`
  FROM `user`
  WHERE `id` = {$id}
  LIMIT 1
");

// most commonly used
$data = $query->fetch(PDO::FETCH_BOTH);   // returns both indexed and associative arrays (by default)
$data = $query->fetch(PDO::FETCH_NUM);    // returns only indexed arrays
$data = $query->fetch(PDO::FETCH_ASSOC);  // returns only associative arrays
$data = $query->fetch(PDO::FETCH_OBJ);    // returns only an object of the stdClass class

I have listed only the most used styles, there are more (see php.net).

Unique values

If we want to have only unique values in a table, there is no need to query in advance whether the value is already there. Such code is often seen:

// we'll assume that the $name variable doesn't contain any invalid characters

$query = $pdo->query("
  SELECT 1 FROM `user`
  WHERE `name` = '{$name}'
  LIMIT 1
");
$exists = $query->fetchColumn();

if (!$exists) {
  // registering the new user
}

If any row exists, 1 is returned, otherwise PDO returns FALSE.

But we can use a unique key directly in a table. All we have to do is add a "unique key" to the given column and it can't happen that we insert the same row. If we try, the database will return an error. PDO represents it according to the settings. The most common setting is to throw an exception.

try {
  $pdo->query("
     INSERT INTO `user` (`nick`)
     VALUES ('some username')
  ");
} catch (PDOException $e) {
  echo "A user with such a username doesn't exist.";
}

So the third part is over. Thanks for reading, and if there is interest, I'll be happy to write more tips that could help both beginners and certainly slightly advanced developers.