PHP, MySQL & JavaScript All-in-One For Dummies Cheat Sheet

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.

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.

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.

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.

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.