This is going to be a working collection of notes I have on PHP. They will include code snippets that I frequently include in my projects, and other things of that nature.
These 2 code snippets are how I usually connect to my database in php. Most of the time, I create a file called functions.php
where I place the dbConnect()
function. Then, on all my other files I use an include('functions.php');
statement at the top.
function dbConnect() {
include('db-info.php');
try {
// connect to database
$pdo = new PDO("mysql:host=$host;dbname=$dbName",$user,$password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $pdo;
} catch(PDOexception $e) {
return 0;
}
}
$user = "username";
$password = "password";
$dbName = "dbname";
$host = "localhost";
Prepared statements are a way of executing sql statements in a safe and efficient manner. They help protect against sql injection attacks, by sanitizing and filtering the data that is to be used in the statements. Using prepared statements over inserting raw query parameters into a SQL query is widely considered to be the proper approach when interacting with databases.
Below are the steps I take when writing a prepared statement. This is not the only way to write correct prepared statements, and there are ways that I can improve them. However, these can help get anyone started. The steps I take are as followed:
$pdo = dbConnect();
$sql = $pdo->prepare('SELECT id, dept, number FROM Classes where term=:term ORDER BY dept, number');
$sql = $pdo->prepare('UPDATE ListItems SET completed=:completed WHERE id=:id');
$sql = $pdo->prepare('INSERT INTO Lists (title) VALUES (:name)');
$sql = $pdo->prepare('DELETE FROM ListItems WHERE id=:id');
Notice the semicolin before the variable name!
You need to choose one of these next 2 options. The first option is for when you have a standard variable. The second option is when you want to use a global variable like $_POST
or $_GET
.
$id = filter_var($id, FILTER_SANITIZE_NUMBER_INT);
$listID = filter_input(INPUT_GET, 'listID', FILTER_SANITIZE_NUMBER_INT); // get
$name = filter_input(INPUT_POST, 'update-todo-list-title', FILTER_SANITIZE_STRING); // post
For both functions, the last parameter is dependent on the type of variable (int, string, double, etc...) being passed in. The table below shows the corresponding relationships. Source.
string | FILTER_SANITIZE_STRING |
---|---|
int | FILTER_SANITIZE_NUMBER_INT |
double/float | FILTER_SANITIZE_NUMBER_FLOAT |
URL | FILTER_SANITIZE_URL |
FILTER_SANITIZE_EMAIL | |
magic quotes | FILTER_SANITIZE_MAGIC_QUOTES |
$sql->bindParam(':id', $id, PDO::PARAM_INT);
For the third parameter in bindParam(), depending on the variable type there are different options to choose from. Below is a table showing the different variable types and their respective predefined constant. Additional constants can be found here.
string | PDO::PARAM_STR |
---|---|
int | PDO::PARAM_INT |
double* | PDO::PARAM_STR |
email, date, other | PDO::PARAM_STR |
There are no predefined constants for a type double so it is recommended to use the string constant.
The final step is to execute the SQL statement and close the pdo object connections. It is good practice to explicitly close the pdo connections by setting the objects to null. If you don't do this, php will automatically close the connections when the script ends.
// execute sql statement
$sql->execute();
// close the pdo connections
$pdo = null;
$sql = null;
Below is an example of a php function that executes a simple prepared update statement to a MySQL database table using the steps I have listed above.
// sets a todo list item as complete
function updateTodoListItemComplete($id, $completed) {
$pdo = dbConnect();
$sql = $pdo->prepare('UPDATE ListItems SET completed=:completed WHERE id=:id');
// filter variables
$id = filter_var($id, FILTER_SANITIZE_NUMBER_INT);
$completed = filter_var($completed, FILTER_SANITIZE_STRING);
// bind the parameters
$sql->bindParam(':id', $id, PDO::PARAM_INT);
$sql->bindParam(':completed', $completed, PDO::PARAM_STR);
// execute sql statement
$sql->execute();
// close the pdo connections
$pdo = null;
$sql = null;
}
If my file is used to handle ajax requests, then the file either returns raw html or data encoded in a json format. Here is I typically return data in a json format.
$studentID = $_GET['studentID']; // get the student id from GET
$student = getStudentInfo($studentID)->fetch(PDO::FETCH_ASSOC); // call a function to return student data by id
$response = json_encode($student); // encode the student data in a json format
echo $response; // return the json data
© 2024 by Ryan Rickgauer