Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Chapter 7: Improving the Insert and Update Functions

In the last chapter, I showed you how to break the code up into easily reusable functions. This has several advantages:

the code where the function is called is easier to read
you can re-use the same function from anywhere

In this chapter, I’ll take this a step further and show you how to make a function that could be used for any database table, and then show you how object-oriented programming can simplify this task even further.

Improving the Update Function

updateJoke($pdo, 1, 'Why did the programmer quit his job? He didn't get arrays', 1);

To run this function, all of the arguments for the function must be provided:

joke ID
joke text
author ID

What if you just wanted to update the joke text, rather than the joke text and author ID? Or just update the joke’s author? With the updateJoke function above, all the information needs to be provided each time.

A better way of doing this is to take the field values as an array, with the keys representing the field names and the array contents representing the data to be stored in the database. For example:

updateJoke($pdo, [
    'id' => 1,  
    'joketext' => 'Why did the programmer quit his job? 
    He didn't get arrays']
);

Another example:

updateJoke($pdo, [
    'id' => 1,  
    'authorId' => 4
]);

This is a lot nicer, as only the information being updated (and the primary key) need to be sent to the function. It also has the advantage of being easier to read: you can read this code and see exactly what each field is being set to. With the earlier version, you have to know which parameter represents which field. If you applied that to a table with 10 or 20 fields, you’d have to get the order of the arguments exactly right and remember which field was at each position.

To make the function take an array, it needs to be updated. Currently it looks like this, and takes an argument for each field:

function updateJoke($pdo, $jokeId, $joketext, $authorId) {
    $parameters = [':joketext' => $joketext,
    ':authorId' => $authorId, ':id' => $jokeId];

    query($pdo, 'UPDATE `joke` 
    SET `authorId` = :authorId, `joketext` = :joketext 
    WHERE `id` = :id', $parameters);
}

Changing the function so that it can take an array as a second argument and run the query is less than straightforward, as the query above is expecting parameters for :authorid, :joketext and :primaryKey. What will happen if they’re not all provided? If you try it, you’ll get an error.

To work around this error, we need to dynamically generate the query so it only contains the relevant fields (only the ones we actually want to update).

The foreach loop (which we saw in Chapter 2) can loop over an array. Take the following code:

$array = [
    'id' => 1,
    'joketext' => 'Why was the empty array stuck outside?
     It didn't have any keys'
];

foreach ($array as $key => $value) {
    echo $key . ' = ' . $value . ',';
}

This will print the following:

id = 1, joketext = Why was the empty array stuck outside? It didn't have any keys

It’s possible to use a foreach loop to produce the UPDATE query:

$array = [
    'id' => 1,
    'joketext' => '!false - it's funny because it's true'
];

$query = ' UPDATE `joke` SET ';

foreach ($array as $key => $value) {
    $query .= '`' . $key . '` = :' . $key . ','
}

$query .= ' WHERE `id` = :primaryKey';

echo $query;

The `.=` Operator

Note the use of the .= operator above. It adds to the end of the existing string, rather than overwriting it!

The code above will print this query:

UPDATE `joke` SET `id` = :id, `joketext` = :joketext, 
                     WHERE `id` = :primaryKey

The query is generated dynamically based on the contents of $array, and only the field names from the array will appear in the query.

You’ll notice that the id field is included in the statement. This isn’t needed, but having it here will simplify things later on.

The following code can be used to generate the query with just the authorId:

$array = [
    'id' => 1,
    'authorid' => 4
];

$query = 'UPDATE `joke` SET ';

foreach ($array as $key => $value) {
    $query .= '`' . $key . '` = :' . $key . ','
}

$query .= ' WHERE `id` = :primaryKey';

echo $query;

The code above will print the following query:

UPDATE `joke` SET `id` = :id, `authorId` = :authorId, 
                     WHERE `id` = :id

This lets us generate almost the exact query that’s needed to update any field, or set of fields, in the joke table.

I say almost, because if you sent this query to the database you’d get an error. Unfortunately, there’s a subtle problem with this generated query. You may have noticed that each time the loop runs, it adds a comma to each part of the SET clause, generating this:

SET `id` = :id, `authorId` = :authorId,

In the following section, I’ll show what to do about this.

Stripping the Trailing Comma

The problem with this part of the query is that there’s an extra comma at the end of the clause between authorId and WHERE:

UPDATE `joke` SET `id` = :id, `authorId` = :authorId, 
                     WHERE `id` = :id

To be a valid query, it should actually be this:

UPDATE `joke` SET `id` = :id, `authorId` = :authorId 
                     WHERE `id` = :id

This is a subtle, single character difference. However, it’s the difference between a valid query and an invalid one! It would be possible to tweak the foreach loop to omit the comma on the last iteration, but it’s simpler just to remove the comma after the string has been generated.

The rtrim function can be used to remove (or trim) specific characters from the end of a string. In our case, we want to remove the comma from the $query string before appending the WHERE clause:

$array = [
    'id' => 1,
    'authorid' => 4
];

$query = 'UPDATE `joke` SET ';

foreach ($array as $key => $value) {
    $query .= '`' . $key . '` = :' . $key . ','
}

$query = rtrim($query, ',');

$query .= ' WHERE `id` = :primaryKey';

echo $query;

The line $query = rtrim($query, ','); will remove the trailing comma from the end of the $query string, giving us a valid SQL UPDATE query. This will display the entire query without the extra comma:

UPDATE `joke` SET `id` = :id, `joketext` = :joketext 
                     WHERE `id` = :primaryKey

By placing this code into the function, the improved version of updateJoke can now be used:

function updateJoke($pdo, $fields) {

    $query = ' UPDATE `joke` SET ';

    foreach ($array as $key => $value) {
        $query .= '`' . $key . '` = :' . $key . ','
    }

    $query = rtrim($query, ',');

    $query .= ' WHERE `id` = :primaryKey';

    // Set the :primaryKey variable
    $fields['primaryKey'] = $fields['id'];

    query($pdo, $query, $fields);
}

You’ll notice I set the primaryKey key manually with this line:

    // Set the :primaryKey variable
    $fields['primaryKey'] = $fields['id'];

This is so that the WHERE clause in the query is provided with the relevant ID to update. :id can’t be used, because it has already been used in the query, and each parameter needs a unique name.

With this version of the updateJoke function, it’s now possible to run it as we designed earlier:

updateJoke($pdo, [
    'id' => 1,  
    'joketext' => '!false - it's funny because it's true']
);

Or we could do this:

updateJoke($pdo, [
    'id' => 1,  
    'authorId' => 4]
);

Writing Functions

When you write a function, it’s usually easier to write some examples of how you think it should be called before writing the code inside the function itself. This gives you a target to work towards, and some code you can run to see whether it’s working correctly or not.

Improving the Insert Function

Using this knowledge, we can do the same thing for the insertJoke function. The INSERT query can use a syntax different from UPDATE’s, and it works like this:

$query = 'INSERT INTO `joke` (`joketext`, `jokedate`,
 `authorId`)
    VALUES (:joketext, CURDATE(), :authorId)';

There are two parts to this—the field names and the values. Firstly, let’s handle the column names. As we did with the updateJoke function, we can use a loop and rtrim to create the list of fields for the first line of the query:

function insertJoke($pdo, $fields) {
    $query = 'INSERT INTO `joke` ('

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '`,';
    }

    $query = rtrim($query, ',');

    $query .= ') VALUES (';
}

This will generate the first part of the query, and $query will store the following:

INSERT INTO `joke` (`authorId`, `joketext`) VALUES (

… when called with this array:

[
    'authorId' => 4,
    'joketext' => '!false - it's funny because it's true'
]

The next part of the query should be the placeholders for the values:

VALUES (:authorId, :joketext)

These are the keys prefixed with a colon (:). Once again, we can use foreach to loop through the column names and add the placeholders before sending the query to the database:

function insertJoke($pdo, $fields) {
    $query = 'INSERT INTO `joke` ('

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '`,';
    }

    $query = rtrim($query, ',');

    $query .= ') VALUES (';

    foreach ($fields as $key => $value) {
        $query .= ':' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ')';

    query($pdo, $query);
}

The insertJoke function can now be used to insert data into any of the fields:

insertJoke($pdo, [
    'authorId' => 4,
    'joketext' => '!false - it's funny because it's true'
    ]
);

Of course, it will error if the wrong column names are used, but it’s clearer to anyone seeing this code which data is being placed into each of the columns. As we’ll see later, it also makes it easier to use the function with forms.

For now, amend your website to use the two new functions by replacing the existing ones in DatabaseFunctions.php:

function insertJoke($pdo, $fields) {
    $query = 'INSERT INTO `joke` ('

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '`,';
    }

    $query = rtrim($query, ',');

    $query .= ') VALUES (';

    foreach ($fields as $key => $value) {
        $query .= ':' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ')';

    query($pdo, $query);
}

function updateJoke($pdo, $fields) {

    $query = ' UPDATE `joke` SET ';

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '` = :' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ' WHERE `id` = :primaryKey';

    // Set the :primaryKey variable
    $fields['primaryKey'] = $fields['id'];

    query($pdo, $query, $fields);
}

Amend editjoke.php to use the new updateJoke function:

updateJoke($pdo, [
    'id' => $_POST['jokeid'],
    'joketext' => $_POST['joketext'],
    'authorId' => 1
]);

You might try amending addjoke.php to use the new function:

insertJoke($pdo, ['authorId' => 1, 'jokeText' => $_POST['joketext']]);

This example can be found in Structure2-ArrayFunctions-Error.

If you try this, you’ll get an error:

Database error: SQLSTATE[HY000]: General error: 1364 Field 'jokedate' doesn't have a default value in /home/vagrant/Code/Project/includes/DatabaseFunctions.php:5

The reason for the error is that we haven’t supplied a value for the jokedate column.

Handling Dates

The cause of the above error is that we’ve lost the ability to supply a date when inserting a joke.

Previously, we used the CURDATE() function. Using the new insertJoke function, you may attempt to set the joke like so:

insertJoke($pdo, ['authorId' => 1,
    'jokeText' => $_POST['joketext'],
    'jokedate' => 'CURDATE()'
]);

Although the logic here is sound (we want to insert the current date into the jokedate column), this approach won’t work as intended. Just as whatever text is stored in the $_POST['joketext'] variable will be inserted into the joketext column, the above code would try to write the value CURDATE() into the jokedate column, rather than executing the function and retrieving the date.

To solve this, we can read the current date using PHP—rather than from MySQL—using the PHP DateTime class.

The PHP DateTime class can be used to represent dates in PHP and format them in any way. For example:

$date = new DateTime();

echo $date->format('d/m/Y H:i:s');

By default, a new instance of a DateTime, without a provided argument, will represent today’s date. So if you were reading this on the 8th September 2019 at around 7.00 p.m., this would print something like:

08/09/2019 19:12:34

You can give PHP a string to convert to a date and then format the date in any way you like. For example:

$date = new DateTime('5th March 2019');

echo $date->format('d/m/Y');

The string d/m/Y here represents day/month/year, which would print 05/03/2019. The PHP DateTime class is very powerful and incredibly useful for handling dates in PHP. A full list of the available methods of formatting a data is available in the PHP manual

However, the only format we need to use is the format MySQL understands.

Dates and times in MySQL are always stored using the format YYYY-MM-DD HH:MM:SS. For example, MySQL would store the date of the 13th of July 2019 as 2019-07-13.

The DateTime class can be used to represent any date. Today’s date could be induced in this format, like so:

// Don't give it a date so it uses the current date/time
$date = new DateTime();

echo $date->format('Y-m-d H:i:s');

The above will print something like this:

2019-09-08 19:16:34

This is the format that’s needed when you insert into a DATETIME field in MySQL.

The insert function can already handle this. We can just pass the current date as one of the array keys to the function:

$date = new DateTime();

insertJoke($pdo, [
    'authorId' => 4,
    'joketext' => 'Why did the chicken cross the road? To get to the other side',
    'jokedate' => $date->format('Y-m-d H:i:s')
    ]
);

This will work fine. However, every time we want to use the function, we need to remember the date format used by MySQL. This can be a bit of a pain, as it requires repeating the code for formatting the date (and remembering the string Y-m-d H:i:s) each time a date needs to be inserted.

Instead, a better approach would be to have the function format the date for us, saving some work each time it’s called:

function insertJoke($pdo, $fields) {
    $query = 'INSERT INTO `joke` (';

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '`,';
    }

    $query = rtrim($query, ',');

    $query .= ') VALUES (';

    foreach ($fields as $key => $value) {
        $query .= ':' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ')';

    foreach ($fields as $key => $value) {
        if ($value instanceof DateTime) {
            $fields[$key] = $value->format('Y-m-d');
        }
    }

    query($pdo, $query, $fields);
}

Using the above version of the function, a date object can be passed into the function without formatting it:

insertJoke($pdo, [
    'authorId' => 4,
    'joketext' => '!false - it's funny because it's true',
    'jokedate' =>  new DateTime()
    ]
);

The function will look for any date objects it has been given and automatically format them to the format needed by MySQL. When the function is called, you don’t need to remember the exact format needed by MySQL; it’s done for you inside the function. The function will automatically convert any DateTime object it comes across into a string that MySQL can understand:

// Loop through the array of fields
foreach ($fields as $key => $value) {
    // If any of the values are a DateTime object
    if ($value instanceof DateTime) {
        // Then replace the value in the array with the date 
        // in the format Y-m-d H:i:s
        $fields[$key] = $value->format('Y-m-d H:i:s');
    }
}

One operator you haven’t come across yet is the instanceof operator. This is a comparison operator like == or !=. However, instead of checking to see if the two values are the same, it checks to see whether the variable on the left ($value) is the same kind of object as the one on the right (DateTime). One important distinction is that the value is not being checked, only the type. It’s like saying “Is it a car?” rather than “Is it an E-Type Jaguar?”

Wherever a DateTime object is found in the array, it’s replaced with the equivalent date string in the format MySQL needs. This allows us to completely forget about the format MySQL actually needs and just supply DateTime objects to the insertJoke function.

Let’s also add the automatic date formatting to the updateJoke function:

function updateJoke($pdo, $fields) {

    $query = ' UPDATE joke SET ';

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '` = :' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ' WHERE id = :primarykey';

    foreach ($fields as $key => $value) {
        if ($value instanceof DateTime) {
            $fields[$key] = $value->format('Y-m-d');
        }
    }

    // Set the :primaryKey variable
    $fields['primaryKey'] = $fields['id'];

    query($pdo, $query, $fields);
}

This will take the same approach we used in insertJoke and apply it to the update function. Any DateTime object that is passed to the function in the $fields array will be converted to a string that MySQL understands.

As we have copied/pasted code, it’s good practice to move the duplicated code into its own function to save repetition and follow the DRY principle:

function processDates($fields) {
    foreach ($fields as $key => $value) {
        if ($value instanceof DateTime) {
            $fields[$key] = $value->format('Y-m-d');
        }
    }

    return $fields;
}

Let’s put the function in DatabaseFunctions.php and change the updateJoke and insertJoke functions to make use of it:

function insertJoke($pdo, $fields) {
    $query = 'INSERT INTO `joke` (';

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '`,';
    }

    $query = rtrim($query, ',');

    $query .= ') VALUES (';

    foreach ($fields as $key => $value) {
        $query .= ':' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ')';

    $fields = processDates($fields);

    query($pdo, $query, $fields);
}

function updateJoke($pdo, $fields) {

    $query = ' UPDATE `joke` SET ';

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '` = :' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ' WHERE `id` = :primaryKey';

    // Set the :primaryKey variable
    $fields['primaryKey'] = $fields['id'];

    $fields = processDates($fields);

    query($pdo, $query, $fields);
}

Finally, amend addjoke.php to provide the DateTime() object for the value of the jokedate column:

insertJoke($pdo, ['authorId' => 1,
    'jokeText' => $_POST['joketext'],
    'jokedate' => new DateTime()
]);

This example can be found in tructure2-ArrayFunctions-Dates.

Displaying the Joke Date

While we’re dealing with dates, let’s display the date on which the joke was posted alongside the author’s name in the template. The page will now display something like “By Tom Butler on 2019-08-04”.

Firstly, we’ll need to amend the allJokes function to retrieve the date from the database:

function allJokes($pdo) {
    $jokes =  query($pdo, 'SELECT `joke`.`id`, `joketext`,
     `jokedate`, `name`, `email`
        FROM `joke` INNER JOIN `author`
        ON `authorid` = `author`.`id`');

    return $jokes->fetchAll();
}

Now we can reference the date column in the template file jokes.html.php:

<p><?=$totalJokes?> jokes have been submitted to the Internet Joke Database.</p>

<?php foreach ($jokes as $joke): ?>
<blockquote>
    <p>
    <?=htmlspecialchars($joke['joketext'], 
    ENT_QUOTES, 'UTF-8')?>

    (by <a href="mailto:<?=htmlspecialchars(
    $joke['email'],
    ENT_QUOTES,
        'UTF-8'
); ?>">
    <?=htmlspecialchars(
            $joke['name'],
            ENT_QUOTES,
        'UTF-8'
        ); ?></a> on <?=$joke['jokedate']; ?>)
    <a href="editjoke.php?id=<?=$joke['id']?>">
    Edit</a>

    <form action="deletejoke.php" method="post">
        <input type="hidden" name="id" 
        value="<?=$joke['id']?>">
        <input type="submit" value="Delete">
    </form>

    </p>
</blockquote>
<?php endforeach; ?>

If you run the code above, you’ll see the date printed on the page. Unfortunately, it will be printed in the format that MySQL uses—that is, 2019-08-04, rather than a nicer format that people who are viewing the website would prefer.

It’s possible to use the DateTime class to do this for us by amending the template file to create an instance of the DateTime class and formatting the date in a nicer way:

(by <a href="mailto:<?=htmlspecialchars(
    $joke['email'],
    ENT_QUOTES,
        'UTF-8'
); ?>">
    <?=htmlspecialchars(
            $joke['name'],
            ENT_QUOTES,
        'UTF-8'
        ); ?></a> on
<?php
$date = new DateTime($joke['jokedate']);

echo $date->format('jS F Y');
?>)
    <a href="editjoke.php?id=<?=$joke['id']?>">
    Edit</a>

The DateTime class can take an argument of a date and, luckily for us, it understands the format MySQL uses—allowing us to quickly format the data in any way we like.

The line echo $date->format('jS F Y'); formats the date in a much nicer way, and will display something like 4th August 2019.

This example can be found in Structure2-ArrayFunctions-Dates2.

Making Your Own Tools

Blacksmiths are unique in that they make their own tools. Carpenters don’t make their saws and hammers, tailors don’t make their scissors and needles, and plumbers don’t make their wrenches, but blacksmiths can make their hammers, tongs, anvils, and chisels — Daniel C. Dennet, Intuition Pumps and Other Tools for Thinking

This is an interesting observation—and I’ll explain why I’ve included in in a moment-but it implies a small paradox. If a blacksmith is needed to make blacksmith’s tools, where did the first blacksmith’s tools come from? As it turns out, there’s actually a simple explanation: the first tools were incredibly crude—just some rocks used to bash out some metal rods. But the blacksmith can fuse two of those rods together to make a basic hammer, and then use that hammer to make an even better hammer, gradually making newer and better equipment.

By improving the tools to be more precise and easier to use, the blacksmith can make products faster, create higher-quality items, produce a wider variety of products, make other specialist tools, and let lesser-skilled workers such as apprentices produce products beyond their skill level.

It does take time to create a tool, but once the tool is created, the blacksmith can use it to make thousands of products. Over the long term, the time spent making the tool quickly pays off.

You’re probably wondering what any of this has to do with PHP programming. I’m going to amend Dennet’s quote slightly and say that blacksmiths are almost unique in being able to make their own tools—because programmers also possess this ability.

Every opportunity I listed for blacksmiths to create their own tools exists for programmers as well. In fact, everything you use on your computer is a tool written by another programmer. Even the PHP programming language you’re using is a tool originally written by a developer called Rasmus Lerdorf.

Programming languages don’t just suddenly exist. Computers don’t even understand PHP at all! They only understand binary code.

Like blacksmiths, programmers started with very crude tools: punch cards that had a hole or no hole to represent a one or a zero. These were then manually fed into the computer to program it. Writing and understanding the code took an incredible amount of skill. As computers developed, so did the way we program them.

Instead of representing everything as ones and zeros with punch cards, programmers invented tools that take human-readable and easier-to-understand code and convert it (or “compile” it, if you want to get technical) into the binary code that the computer understands. It’s easy to forget that programming languages exist for humans, not for computers.

When you sit down in front of your computer with your favorite code editor, you can be forgiven for thinking you’re using the most refined hammer that can be made and that you have all the tools available to pick from.

A programming language is just a tool, and you can use it to make your own tools. Every time you write a function, you’re creating a new tool. You can either make tools that have many uses, which you can use over and over again, or tools with limited use that can only be used for one very specific job.

In your kitchen you have many tools. You probably need a knife to prepare nearly every meal, but how often do you use a garlic press?

A tool is more useful if it can be used for a variety of different tasks. In the kitchen, the more recipes the tool can help make, the more useful it is.

When writing a function in PHP (or any programming language), strive to write functions that are more like the knife than the garlic press. Write functions that can be used over and over again on any website, rather than functions that only apply to a very specific case on a single website.

So far in this chapter, we’ve taken functions that need an exact number of arguments in a very specific order and rewritten them as functions that allow arguments to be specified in (almost) any order, with values optionally omitted entirely if they don’t need updating.

The problem with the function now is that it’s more like the garlic press than the knife. Just as the garlic press is only useful for recipes that have garlic, the updateJoke function is only useful when we want to update a record in the joke table. We can’t take this function and use it on another website, because the next website you build probably won’t even have a table called joke.

The next step in refining our tools is to make our functions more like the knife, able to work with any database table. After all, a knife can be used to chop garlic anyway.

Generic Functions

Before we make any large-scale changes, let’s expand the website and add a function for retrieving all the authors from the database in the same manner we used for the allJokes function:

function allAuthors($pdo) {
    $authors =  query($pdo, 'SELECT * FROM `author`');

    return $authors->fetchAll();
}

Let’s also add functions for inserting and deleting authors from the author table:

function deleteAuthor($pdo, $id) {
    $parameters = [':id' => $id];

    query($pdo, 'DELETE FROM `author` 
    WHERE `id` = :id', $parameters);
}

function insertAuthor($pdo, $fields) {
    $query = 'INSERT INTO `author` (';

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '`,';
    }

    $query = rtrim($query, ',');

    $query .= ') VALUES (';

    foreach ($fields as $key => $value) {
        $query .= ':' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ')';

    $fields = processDates($fields);

    query($pdo, $query, $fields);
}

These deleteAuthor and insertAuthor functions are almost identical to the corresponding joke functions, deleteJoke and insertJoke. It would be better to create generic functions that could be used with any database table. That way, we can write the function once and use it for any database table. If we continued down the route of having five different functions for each database table, we’d very quickly end up with a lot of very similar code.

The differences between the functions are just the names of the tables. By replacing the table name with a variable, the function can be used to retrieve all the records from any database table. That way, it’s no longer necessary to write a different function for each table:

function findAll($pdo, $table) {
    $result = query($pdo, 'SELECT * FROM `' . $table . '`');

    return $result->fetchAll();
}

Once the function has been written, this new tool can be used to retrieve all the records from any database table:

// Select all the jokes from the database
$allJokes = findAll($pdo, 'joke');

// Select all the authors from the database
$allAuthors = findAll($pdo, 'author');

The same thing can be done with delete:

function delete($pdo, $table, $id) {
    $parameters = [':id' => $id];

    query($pdo, 'DELETE FROM `' . $table . '` 
    WHERE `id` = :id', $parameters);
}

This allows deleting a record from any table based on its ID:

// Delete author with the ID of 2
delete($pdo, 'author', 2);

// Delete joke with the id of 5
delete($pdo, 'joke', 5);

This function works, but it’s still a little inflexible: it assumes that the primary key field in the table is called id. This function can only work with tables that have a field named id for their primary key, which isn’t always the case. A table that stored information about books, for example, might have isbn as the primary key. In order for our function to work with any database table structure, the primary key can also be replaced with a variable:

function delete($pdo, $table, $primaryKey, $id ) {
    $parameters = [':id' => $id];

    query($pdo, 'DELETE FROM `' . $table . '` 
    WHERE `' . $primaryKey . '` = :id', $parameters);
}

Whenever the delete function is called, it’s now supplied with four arguments:

the $pdo database connection
the name of the table to delete a record from
the ID of the record to delete
the field that acts as the primary key

And it can be called like this:

// Delete author with the ID of 2
delete($pdo, 'author', 'id', 2);

// Delete joke with the id of 5
delete($pdo, 'joke', 'id', 5);

// Delete the book with the ISBN 978-3-16-148410-0
delete($pdo, 'book', '978-3-16-148410-0', 'isbn');

Along with the delete and select functions, let’s do the same thing with the update and insert functions by replacing the table name with a function argument:

function insert($pdo, $table, $fields) {
    $query = 'INSERT INTO `' . $table . '` (';

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '`,';
    }

    $query = rtrim($query, ',');

    $query .= ') VALUES (';

    foreach ($fields as $key => $value) {
        $query .= ':' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ')';

    $fields = processDates($fields);

    query($pdo, $query, $fields);
}

function update($pdo, $table, $primaryKey, $fields) {

    $query = ' UPDATE `' . $table .'` SET ';

    foreach ($fields as $key => $value) {
        $query .= '`' . $key . '` = :' . $key . ',';
    }

    $query = rtrim($query, ',');

    $query .= ' WHERE `' . $primaryKey . '` = :primaryKey';

    // Set the :primaryKey variable
    $fields['primaryKey'] = $fields['id'];

    $fields = processDates($fields);

    query($pdo, $query, $fields);
}

Notice that in the update function, I’ve also created a variable called primaryKey. That’s because we can’t assume that the primary key (used in the WHEREid= :primaryKey part of the query) will always be id.

In the last chapter we created a function called getJoke, which found a specific joke by its ID. That function, too, can be made to work with any database table. This findById function can be used to find a single record from any table using the primary key:

function findById($pdo, $table, $primaryKey, $value) {
    $query = 'SELECT * FROM `' . $table . '` 
    WHERE `' . $primaryKey . '` = :value';

    $parameters = [
        'value' => $value
    ];

    $query = query($pdo, $query, $parameters);

    return $query->fetch();
}

We now have a set of functions that can be used to interact quickly and easily with any database table and use PHP’s DateTime class for dates:

// Add a new record to the database
$record = [
    'joketext' => '!false - it's funny because it's true',
    'authorId' => 2,
    'jokedate' => new DateTime()
];

insert($pdo, 'joke', $record);

// Delete from the author table where `id` is `2`
delete($pdo, 'author', 'id', 2);

$jokes = findAll($pdo, $joke);

Finally, for the sake of completeness, let’s also make the same change to the totalJokes function, allowing us to get the number of records in any table:

function total($pdo, $table) {
    $query = query($pdo, 'SELECT COUNT(*) 
    FROM `' . $table . '`');
    $row = $query->fetch();
    return $row[0];
}

Using These Functions

Now that we’ve got the functions, let’s put them into our controllers. Firstly, we’ll change the the addjoke.php controller to use the new generic insert function. It currently looks like this:

<?php
if (isset($_POST['joketext'])) {
    try {
        include __DIR__ . '/../includes/DatabaseConnection.php';
        include __DIR__ . '/../includes/DatabaseFunctions.php';

        insertJoke($pdo, ['authorId' => 1, 
        'jokeText' => $_POST['joketext'], 
        'jokedate' => new DateTime()]);

        header('location: jokes.php');

        // …

To replace the insertJoke function with the generic insert function, we just need to change the name and supply the name of the table:

<?php
if (isset($_POST['joketext'])) {
    try {
    include __DIR__ . '/../includes/DatabaseConnection.php';
    include __DIR__ . '/../includes/DatabaseFunctions.php';

    insert($pdo, 'joke', ['authorId' => 1, 'jokeText' => $_POST['joketext'], 'jokedate' => new DateTime()]);

    header('location: jokes.php');

    // …

Now amend editjoke.php and deletejoke.php files in the same way. Firstly editjoke.php:

<?php
include __DIR__ . '/../includes/DatabaseConnection.php';
include __DIR__ . '/../includes/DatabaseFunctions.php';

try {
    if (isset($_POST['joketext'])) {
        update($pdo, 'joke', 'id', ['id' => $_POST['jokeid'],
            'joketext' => $_POST['joketext'],
            'authorId' => 1]);

        header('location: jokes.php');
    } else {
        $joke = findById($pdo, 'joke', 'id', $_GET['id']);

        $title = 'Edit joke';

        ob_start();

        include  __DIR__ . '/../templates/editjoke.html.php';

        $output = ob_get_clean();
    }
}
// …

And here’s the updated deletejoke.php:

<?php
try {
    include __DIR__ . '/../includes/DatabaseConnection.php';
    include __DIR__ . '/../includes/DatabaseFunctions.php';

    delete($pdo, 'joke', 'id', $_POST['id']);

    // …

The next part is the list of jokes. Currently, it uses the allJokes function, which also retrieves information about the author of each joke. There’s no simple way to write a generic function that retrieves information from two tables. It would have to know which fields were used to join the tables and which tables were being joined. It would be possible to do this with a function with lots of arguments, but the function would be difficult to use and overly complex.

Instead, we can use the generic findAll and findById functions to achieve this:

$result = findAll($pdo, 'joke');

$jokes = [];
foreach ($result as $joke) {

    $author = findById($pdo, 'author', 'id', $joke['authorId']);

    $jokes[] = [
        'id' => $joke['id'],
        'joketext' => $joke['joketext'],
        'name' => $author['name'],
        'email' => $author['email']
    ];
}

The complete jokes.php looks like this:

    <?php

    try {
        include __DIR__ . '/../includes/DatabaseConnection.php';
        include __DIR__ . '/../includes/DatabaseFunctions.php';

        $result = findAll($pdo, 'joke');

        $jokes = [];
        foreach ($result as $joke) {
            $author = findById($pdo, 'author', 'id',
                $joke['authorId']);

            $jokes[] = [
                'id' => $joke['id'],
                'joketext' => $joke['joketext'],
                'jokedate' => $joke['jokedate'],
                'name' => $author['name'],
                'email' => $author['email']
            ];
        }

        $title = 'Joke list';

        $totalJokes = total($pdo, 'joke');

        ob_start();

        include  __DIR__ . '/../templates/jokes.html.php';

        $output = ob_get_clean();
    } catch (PDOException $e) {
        $title = 'An error has occurred';

        $output = 'Database error: ' . $e->getMessage() . ' 
        in ' . $e->getFile() . ':' . $e->getLine();
    }

    include  __DIR__ . '/../templates/layout.html.php';

This example can be found in Structure2-GenericFunctions.

This works by fetching the list of jokes (without the author information), then looping over each joke and finding the corresponding author by their id, then writing the complete joke with the information from both tables into the $jokes array. This is essentially what an INNER JOIN does in MySQL.

Each joke is now made up of values from the $author variable, which is a record from the author table and values from the joke table.

You may have realized that this method is going to be slower, because more queries are sent to the database. This is a common issue with these kinds of generic functions, and it’s called the N+1 problemFor more information on the N+1 problem, refer to the Microsoft article “Select N+1 Problem – How to Decrease Your ORM Performance”: http://blogs.microsoft.co.il/gilf/2010/08/18/select-n1-problem-how-to-decrease-your-orm-performance/ There are several methods for reducing this performance issueTo see how the N+1 problem can be avoided entirely, refer to https://www.sitepoint.com/silver-bullet-n1-problem/, but for smaller sites, where we’re dealing with hundreds or thousands of records rather than millions, it’s unlikely to cause any real problems. The difference will likely be in the region of milliseconds.

Repeated Code Is the Enemy

Whenever you’re writing software, you need to be vigilant about repeated code. Any time you find yourself with two or more copies of identical or very similar code, it’s always worth taking a step back and looking to see if there’s a way of merging the code into one reusable block.

By creating the generic functions insert, update, delete, findAll and findById, it’s now very quick and easy for us to create a website that deals with any kind of database operation. The functions can be used to interact very simply with any database table we might ever need.

But there’s still room for improvement. The files addjoke.php and editjoke.php do very similar jobs: they display a form, and when the form is submitted, they send the submitted data off to the database.

Similarly, the template files addjoke.html.php and editjoke.html.php are almost identical. There’s very little difference between them. The only real difference is that the edit page displays a pre-filled form where the add page presents an empty form.

The problem with repeated code is that, if something has to change, you have to make the same change in multiple locations. What if we wanted to add a category to a joke, so that when a joke was added or edited the user could choose Knock knock jokes, Programming Jokes, Puns, One liners, etc?

We could achieve this by adding a <select> box to the add joke page, but we’d also need to make the same change to the edit joke page. Each time we made a change to addjoke.php, we’d need to make the corresponding change in editjoke.php.

If you ever find yourself in a situation like this, where you have to make similar changes in multiple files, it’s a good sign that you should combine both sets of code into one. Of course, the new code needs to handle both cases.

There are a couple of differences between addjoke.php and editjoke.php:

addjoke.php issues an INSERT query, while editjoke.php issues an UPDATE query.
editjoke.php’s template file has a hidden input that stores the ID of the joke being edited.

But everything else is almost the same.

Let’s merge both pieces of code together, so that editjoke.php can handle both editing an existing joke and adding a new one. The script will be able to tell whether we’re adding or editing based on whether or not an ID is supplied in the URI.

Visiting editjoke.php?id=12 will load the joke with the ID 12 from the database and allow us to edit it. When the form is submitted, it will issue the relevant UPDATE query, while just visiting editjoke.php—without an ID specified—will display an empty form and, when submitted, perform an INSERT query.

Creating a Page for Adding and Editing

Let’s handle the form first—which either loads the joke into the fields or displays a blank form. Currently, editjoke.php assumes there’s the id GETvariable set, and loads the joke accordingly before loading the template file:

else {
    $joke = findById($pdo, 'joke', 'id', $_GET['id']);

    $title = 'Edit joke';

    ob_start();

    include  __DIR__ . '/../templates/editjoke.html.php';

    $output = ob_get_clean();
}

This can be replaced with an if statement to only load the joke from the database if an id has actually been provided:

else {
    if (isset($_GET['id'])) {
        $joke = findById($pdo, 'joke', 'id', $_GET['id']);
    }

    $title = 'Edit joke';

    ob_start();

    include  __DIR__ . '/../templates/editjoke.html.php';

    $output = ob_get_clean();
}

If you try the above code and visit editjoke.php without supplying an ID as a GET variable, it won’t quite work as intended. You’ll actually see some strange errors appearing inside the <textarea>. That’s because when editjoke.html.php is loaded, it references a variable called $joke which, because of the new if statement, is only created when the ID is supplied.

One solution would be to load the addjoke.html.php file if there’s no ID set and editjoke.html.php if there is. Unfortunately, this doesn’t solve the initial problem: to add a new field to the form using this approach, we’d still need to edit two files.

Instead, let’s amend editjoke.html.php so it only tries to print the existing data into the textarea and hidden input if the joke variable is set:

<form action="" method="post">
    <input type="hidden" name="jokeid" 
        value="<?php if (isset($joke)): ?>
            <?=$joke['id']?>
        <?php endif; ?>
    ?>">
    <label for="joketext">Type your joke here:
    </label>
    <textarea id="joketext" name="joketext" rows="3"
        cols="40"><?php if (isset($joke)): ?>
        <?=$joke['joketext']?>
    <?php endif; ?></textarea>
    <input type="submit" name="submit" value="Save">
</form>

Now the joketext and id will only be written to the page if the $joke variable has been set, and it’s only set when we’re editing an existing record.

Before continuing, let’s tidy up the code a little. Previously, to print the ID of the joke into the hidden input, it required a simpler piece of code:

<input type="hidden" name="jokeid" 
    value="<?=$joke[id]?>">

Now that we’re using the if statement, we need the full <?php opening and several lines of code.

A nice feature introduced in PHP 7 is the Null coalescing operator. It’s a very confusing name, but is actually just a shorthand for this:

if (isset($something)) {
    echo $something;
}
else {
    echo 'variable not set';
}

This can be expressed using the null coalescing operator (??) like so:

echo $something ?? 'variable not set';

On the left-hand side of the ?? operator is the variable being checked, and on the right is the output that’s used if it’s not set. In the case above, if the $something variable is set, it will print the contents of the variable. If the variable isn’t set, it will print variable not set, and even better, it also works with arrays.

Let’s make use of it in our templates, rather than having to write out the entire if statement for each field:

<form action="" method="post">
    <input type="hidden" name="jokeid" 
        value="<?=$joke['id'] ?? ''?>">
    <label for="joketext">Type your joke here:
    </label>
    <textarea id="joketext" name="joketext" rows="3"
        cols="40"><?=$joke['joketext'] ?? ''?></textarea>
    <input type="submit" name="submit" value="Save">
</form>

In this instance, the right-hand part is an empty string. Either the text of the loaded joke will be displayed in the text box, or if the $joke variable is not set, it will display an empty string '' in the box.

To complete this page, we need to change what happens when the form is submitted. Either an update or insert query will need to be run.

The simplest way would be to look to see if the ID field has been supplied:

if (isset($_POST['id']) && $_POST['id'] != '') {
    update(...);
}
else ;
    insert(...)
}

Although this would work, once again there’s an opportunity to make this more generic. This logic for if the ID is set, update, otherwise insert is going to be the same for any form.

If we had a form for authors, the same logic would be useful: if there’s no ID, perform an insert; if there is, perform an update. Indeed, this would apply anywhere we have a form that will be used for add or edit. The code above would need to be repeated for any form we implemented in this way.

Another problem with this approach is that it wouldn’t work in cases where the primary key was a normal field on the form, such as ISBNs for books. Regardless of whether an UPDATE or INSERT query is required, the ISBN would be provided.

Instead, we can try to insert a record, and if it’s unsuccessful, update instead using a try … catch statement:

try {
    insert(…);

}
catch(PDOException $e) {
    update(…);
}

Now an INSERT will be sent to the database, but it may cause an error—“Duplicate key”—when a record with the supplied ID is already set. If an error does occur, an UPDATE query is issued instead to update the existing record.

To stop us needing to repeat this logic for every form, we could make another function, called save, which performs an insert or an update using the try … catch above:

function save($pdo, $table, $primaryKey, $record) {
    try {
        if ($record[$primaryKey] == '') {
            $record[$primaryKey] = null;
        }
        insert($pdo, $table, $record);
    }
    catch (PDOException $e) {
        update($pdo, $table, $primaryKey, $record);
    }
}

This will work for any record in any table. If there’s an error when trying to insert, it will issue the corresponding update query instead.

The save function here needs all the arguments required by both the insert and update functions in order to call them. There’s no need to repeat any of the logic from those functions; they can just be called from within the relatively short save function.

The line if ($record[$primaryKey] == '') { is used here so that the INSERT function never tries to insert an empty string into the ID column. Most of the time, the primary key will be an INT column that only accepts numbers. By replacing the empty string with NULL, it will trigger MySQL’s auto_increment feature and generate a new ID.

Now editjoke.php can be modified to use the new save function:

try {
    if (isset($_POST['joketext'])) {

        save($pdo, 'joke', 'id', ['id' => $_POST['jokeid'],
            'joketext' => $_POST['joketext'],
            'jokedate' => new DateTime(),
            'authorId' => 1]);

        header('location: jokes.php');  
    }

Finally, you can delete the controller addjoke.php and the template addjoke.html.php, as they’re no longer needed. Both add and edit are now handled by editjoke.php. Change the link in the menu in layout.html.php to go to editjoke.php instead of addjoke.php.

This example can be found in Structure2-GenericFunction-Save.

Further Polishing

The new save function can be used to add records to the database. It will automatically insert or update depending on whether the id field is set to an existing ID. For the add form, where the ID field isn’t set, there’s still an id key in the $_POST array, because the hidden input still exits on the form.

This allows us to treat add and edit identically, saving us a lot of work. Without this generic save function, we’d need different HTML forms and different controller logic for processing the submissions.

Using this approach, there’s less code to write initially, and the lack of HTML or PHP code repetition is an extra bonus. To add a new field, all we need to do is add the field to the database, amend the template and supply the new value to the save function.

Adding new fields is a very simple process. Because the save function handles both INSERT and UPDATE queries, and the insert and update functions both dynamically generate the queries, adding a field to the query is now as easy as adding an entry to an array!

This updated code is incredibly easy to manage compared to the code we started with at the beginning of the last chapter. However—and you’re probably anticipating me saying this by now—it’s always worth asking whether it can be simplified further. Once again, the answer is yes.

There’s a little repetition here:

[
    'id' => $_POST['jokeid'],
    'authorId' => 1,
    'jokedate' => new DateTime(),
    'joketext' => $_POST['joketext']
];

Each field in the $_POST array is mapped to a key in the $joke array with the same name.

Take a look at the line 'joketext' => $_POST['joketext'],. All we’re really doing here is creating a key in the $joke array with the name joketext and the value from the $_POST array’s joketext element.

Really, we’re just copying data from the $_POST array exactly into the $joke array. If the form had more fields, we’d need to copy those too.

It would be possible to do something similar using this code:

    $joke = $_POST;
    $joke['authorId'] = 1;
    $joke['jokedate'] = new DateTime();

    save($pdo, 'joke', 'id', $joke);

This will automatically include all the fields from the form in the $joke array without needing to manually copy them.

Now, if we added a new field to the form, there are only two steps: add the column in the database and add the form. As long as the column name in the database is the same as the field name on the form, it’s possible to add new fields to the form without ever opening up the controller!

As neat as that sounds, if you do try the code above, you’ll find you get an error. That’s because the $_POST array also contains the submit button. When the INSERT query is generated, it would actually generate this query:

INSERT INTO `joke` (`joketext`, `jokedate`, `authorid`, `submit`)

This is obviously a problem: there’s no submit column in the database table joke. A quick and dirty way of fixing this is to remove the submit button from the array using unset:

$joke = $_POST;

// Remove the submit element from the array
unset($joke['submit']);

$joke['authorId'] = 1;
$joke['jokedate'] = new DateTime();

save($pdo, 'joke', 'id', $joke);

Although this works, the problem with this approach is that you’d have to remove any form elements you don’t want inserted into the database. For example, if you have a checkbox on the form that sends an email when checked, you’d also remove this checkbox from the $_POST array prior to calling the save function.

As usual, there’s a better way. When using HTML forms, you can actually send an array as post data by changing the field names. Change editjoke.html.php to this:

<form action="" method="post">
    <input type="hidden" name="joke[id]" 
        value="<?=$joke['id'] ?? ''?>">
    <label for="joketext">Type your joke here:
    </label>
    <textarea id="joketext" name="joke[joketext]" rows="3
        " cols="40"><?=$joke['joketext'] ?? ''?></textarea>
    <input type="submit" name="submit" value="Save">
</form>

Each of the fields that represents some data for the joke has been changed slightly. The name attribute of each form field has been updated to represent an array: jokeid is now joke[id] and joketext is now joke[joketext].

This tells PHP to treat these fields like an array when the form is submitted.

If you submit the form, the $_POST array will store two values: submit and joke. $_POST['joke'] is itself an array from which you can read the id value using $_POST['joke']['id'].

For our purposes, we can read all of the information for the joke using $joke = $_POST['joke'] and it won’t contain the submit button entry, or any other array keys/values that we don’t want to send to the save function.

$joke = $_POST['joke'];
$joke['authorId'] = 1;
$joke['jokedate'] = new DateTime();

The $joke array will contain all the values from $_POST['joke'] and any we want to add that don’t come from the form—in this case, authorId and jokedate. Following this approach, however, it’s important that the field names on the form directly match up to column names in the database, so I’ve used joke[id] instead of joke[jokeid]. The latter would try to write to a column in the database called jokeid, which doesn’t exist.

Finally, you’ll need to update the if statement that detects whether the form has been submitted to look for the new joke key, rather than joketext which no longer exits:

    if (isset($_POST['joke'])) {

The complete controller code now looks like this:

Structure2-GenericFunctions-SaveArray

<?php
include __DIR__ . '/../includes/DatabaseConnection.php';
include __DIR__ . '/../includes/DatabaseFunctions.php';

try {
    if (isset($_POST['joke'])) {
        $joke = $_POST['joke'];
        $joke['jokedate'] = new DateTime();
        $joke['authorId'] = 1;

        save($pdo, 'joke', 'id', $joke);

        header('location: jokes.php');
    } else {
        if (isset($_GET['id'])) {
            $joke = findById($pdo, 'joke', 'id', $_GET['id']);
        }

        $title = 'Edit joke';

        ob_start();

        include  __DIR__ . '/../templates/editjoke.html.php';

        $output = ob_get_clean();
    }
} catch (PDOException $e) {
    $title = 'An error has occurred';

    $output = 'Database error: ' . $e->getMessage() . ' in ' 
    . $e->getFile() . ':' . $e->getLine();
}

include  __DIR__ . '/../templates/layout.html.php';

If we wanted add a field to the joke table and alter the form now, it would only require two changes: adding the field to the database and then editing the HTML form. A single update to editjoke.html.php will let us add a form field that works for both the edit and add pages.

Moving Forward

In this chapter, I’ve showed you how to reduce repeated code and write functions for use with any database table. We’ve moved from some very specific functions to functions that can be used in several different situations.

You now have a set of tools you can use to extend this website or even write a completely different one. None of the functions are tied to concepts like jokes or authors, which means we could use them on a website that dealt with entirely different concepts—for example, books, products, blogs, or anything you can think of. Now that you’ve written the tools, the hard part is done, and in your next project you can save a lot of time by reusing the tools you’ve created.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.