Using the PHP database object library functions
When you know your application will run in a MySQL environment, it makes sense to use the php_mysqli library functions to interact with the database. However, there may come a time when you want to write an application that can work using other databases as well. PHP supports quite a few database libraries, each of which interacts with a specific database server. The downside, though, is that you’d need to create a different version of your application for each database server.
Alternatively, you can use the PHP Database Object (PDO) library functions. PDO allows you to use the same code to interact with any type of underlying database server. You just need to specify the database server used for the connection, and PDO does the rest!
To open a PDO connection to a database, you instantiate a PDO object:
$con = new PDO(
"mysql:host=localhost;dbname=mydata;charset=utf8',
'username', 'password');
The databases your application can connect to depends on which PDO database drivers are loaded in the PHP server. To determine which drivers are available, use the getAvailableDrivers()
static method:
$list = PDO::getAvailableDrivers();
foreach ($list as $db) {
echo "$db<br>\n";
}
After you connect to the database, to submit a simple query, use the query()
method:
$query = "SELECT bidderid, lastname, firstname, address
FROM bidders";
foreach($con->query($query) as $row) {
$bidderid = $row['bidderid'];
$lastname = $row['lastname'];
$firstname = $row['firstname'];
$address = $row['address'];
echo "$bidderid - $lastname, $firstname<br>$adress<br><br>\n";
}
Unlike the php_mysqli library, the PDO library query()
method returns the actual data records from the result set. You don’t need to fetch the results. Each time you call the query()
method, it returns the next data record from the result set.
You can also use prepared statements with the PDO library to help filter input data:
$sql = "INSERT INTO bidders (bidderid, lastname,
firstname, address) VALUES (?, ?, ?, ?)";
$stmt = $con->prepare($sql);
$stmt->bindParam(1, 100, PDO::PARAM_INT);
$stmt->bindParam(2, 'Blum', PDO::PARAM_STR);
$stmt->bindParam(3, 'Rich', PDO::PARAM_STR);
$stmt->bindParam(4, "123 Main St.; Chicago, IL 60633",
PDO::PARAM_STR);
$stmt->execute();
Using this method, you can submit multiple data records by binding each data set to the prepared statement and executing them individually.
With the PDO library, you can now write a single application that will work with any underlying database server your customers need to use!
Filtering data in PHP
Validating input data is crucial to any PHP application. You don’t want an attacker trying to attack your system by submitting improper form data. Fortunately, the PHP developers have provided some help with that process.
PHP provides several filter functions that allow you to easily check for valid data or sanitize the data if any unwanted data is present. The following table lists the different functions available in the filter family.
Function | Description |
filter_has_var() |
Checks if a variable of the specified type exists |
filter_id() |
Returns the filter ID of the specified filter |
filter_input() |
Retrieves a value passed by GET, POST, sessions, or cookies and filters it |
filter_input_array() |
Retrieves multiple values passed to the PHP program and filters them |
filter_list() |
Returns a list of supported filters |
filter_var() |
Filters a variable |
filter_var_array() |
Filters a list of variables |
These functions allow you to specify a variable to check and the type of check to perform. There are two main groups of filter:
- Validation: Checks if the specified data is present
- Sanitation: Checks if the specified data is present and removes it
The following table shows the different validation filters available.
Filter | Description |
FILTER_VALIDATE_BOOLEAN |
Checks for a valid Boolean value |
FILTER_VALIDATE_EMAIL |
Checks for a valid email address |
FILTER_VALIDATE_FLOAT |
Checks for a valid float value |
FILTER_VALIDATE_INT |
Checks for a valid integer value |
FILTER_VALIDATE_IP |
Checks for a valid IP address value |
FILTER_VALIDATE_REGEXP |
Checks for a valid regular expression value |
FILTER_VALIDATE_URL |
Checks for a valid URL string |
The validation checks return a TRUE
value if the data contains the data type being checked, or a FALSE
value if not.
The following table shows the different sanitation filters available.
Filter | Description |
FILTER_SANITIZE_EMAIL |
Removes illegal characters from an email address |
FILTER_SANITIZE_ENCODED |
Encodes special characters in the string |
FILTER_SANITIZE_MAGIC_QUOTES |
Apply the addslashes() function |
FILTER_SANITIZE_NUMBER_FLOAT |
Remove all characters, except digits, +, –, and E |
FILTER_SANITIZE_NUMBER_INT |
Removes all characters except digits and + or – |
FILTER_SANITIZE_SPECIAL_CHARS |
Removes any special characters in the string |
FILTER_SANITIZE_FULL_SPECIAL_CHARS |
Same as htmlspecialchars() |
FILTER_SANITIZE_STRING |
Removes HTML tags and special characters from a string |
FILTER_SANITIZE_STRIPPED |
Same as FILTER_SANITIZE_STRING |
FILTER_SANITIZE_URL |
Removes all illegal characters from a URL string |
You can combine both the sanitizing and validating features in your code to ensure the data you receive from an HTML form is valid:
$address = $_POST['email'];
$address = filter_var($address, FILTER_SANITIZE_EMAIL);
if (!filter_var($address, FILTER_VALIDATE_EMAIL)) {
echo "<h2>Sorry, you have entered an incorrect address</h2";
} else {
echo "<h2>Thank you for submitting your data</h2>";
}
Using the PHP filter functions will help you safely process any type of input data received in your application HTML forms.
Using regular expressions in MySQL
Searching for data in a MySQL SELECT
statement means incorporating a WHERE
clause. It’s easy to search for a single item:
SELECT prodid, product WHERE prodid = 100;
What gets tricky is if you need to search for an item based on search pattern. MySQL uses the LIKE
clause to help out with that:
SELECT prodid, product WHERE product LIKE 'apple%';
The percent sign works as a wildcard character, matching zero or more characters in the string. So this will return apples
, as well as apple juice
.
The wildcard character in the LIKE operator is handy, but it’s somewhat limited. A more advanced way of searching for data is using a regular expression pattern match. Regular expressions use their own language to define a template used to match data patterns.
MySQL supports regular expressions using the REGEXP
operator. You specify the matching pattern similar to how you do it with the LIKE
operator:
SELECT prodid, product WHERE product REGEXP 'apple';
In the regular expression, by default any text you enter is matched anywhere in the data field. So, this query will return apple
, apple juice
, or candy apple
.
You can specify exactly where in the string the text pattern should appear by using anchor characters. The caret character (^
) indicates the start of the string. So, the following query matches only apple
and apple juice
:
SELECT prodid, product WHERE product REGEXP '^apple';
The dollar sign indicates the end of the string and would return apple
or candy apple
:
SELECT prodid, product WHERE product REGEXP 'apple$';
The MySQL regular expression languages uses lots of special characters to define the matching template. The following table shows the more popular ones.
Character | Description |
^string |
Matches the text at the beginning of the string |
string$ |
Matches the text at the end of the string |
. |
Matches any single character (including special characters) |
a* |
Matches the sequence of zero or more of the specified character |
a+ |
Matches the sequence of one or more of the specified character |
a? |
Matches zero or one occurrence of the specified character |
abc|def |
Matches either one of the specified strings |
[abc] |
Matches any one of the specified characters |
With the LIKE
and REGEXP
operators in the WHERE
clause, you can customize your SELECT
statement to look for just about any type of data that you need for your application!
Working with timers in JavaScript
Often you’ll run into a situation where you need to trigger an event dynamically in a web page, without the website visitor doing anything. JavaScript provides two simple functions that allow you to schedule an event to trigger at a preselected time.
The setTimeout()
function allows you to schedule a specified function to trigger at a specific time from the current time:
setTimeout(function, time);
The function
parameter specifies the name of the function to trigger, while the time
parameter specifies the amount of time (in milliseconds) for the browser to wait until triggering the function. An example would be the following, which triggers the myfunction()
function after waiting five seconds:
setTimeout(myfunction, 5000);
You may also run into situations where you need a specific function to trigger repeatedly at a specific time interval, such as if your application needs to refresh data from the application database on the server. Instead of having to set multiple setTimeout()
functions, you can use the setInterval()
function:
setInterval(function, time);
With the setInterval()
function, JavaScript repeats the event trigger for the specified number of milliseconds, and repeats the function for each interval.
If you need to disable the timer functions before they trigger, you use the clearTimeout()
and clearInterval()
functions. You’ll need to include the value returned by the individual functions when they’re set as the parameter:
$timer = setInterval(myfunction, 5000);
clearInterval($timer);
With the use of the timer functions in PHP, you can trigger automatic updates to a web page at a regular interval, checking for updated data. This comes in handy when working with Ajax applications.