Cheat Sheet

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

From PHP, MySQL, & JavaScript All-in-One For Dummies

By Richard Blum

Working with PHP, MySQL, and JavaScript to create dynamic web applications can be difficult, but if you know a few programming tricks, you can make that job a lot easier. This Cheat Sheet shows you how to extract data from different databases in your PHP programs, filter out unwanted or potentially dangerous data from web forms, quickly find data stored in your MySQL database, and trigger timed events in your JavaScript programs.

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.

The PHP Filter Functions
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.

PHP Validation Filters
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.

PHP Sanitation Filters
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.

The MySQL Regular Expression Characters
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.