Personal "cheatsheet" for MySQLi prepared statements in PHP

php_mysqli_ps_cs.md

PHP-MySQLi Prepared Statement Cheatsheet

Dug up some reference code from my old PHP projects. Hopefully it's useful.

---

Querying database (SELECT)

// Array to store query results.
$my_results = array();

// SQL statement to execute.
$sql = "SELECT id, title, description, category from events";

// Assuming such function to get the mysqli connection object exists.
$conn = get_conn();

// Prepare the statement.
if ($query = $conn->prepare($sql)) {
	// Execute the query.
	$query->execute();
	
	// Bind the results from the query to some predefined PHP variables.
	$query->bind_result($id, $title, $description, $category);

	// Fetch all results and store them into an associative array (Representing one "row").
	while ($query->fetch()) {
		$row = array(
			"id" => (int)($id),
			"title" => $title,
			"description" => $description,
			"category" => $category,
		);

		// Store the associative array into another array for use later.
		// This array contains all the "rows" fetched from the query.
		array_push($my_results, $row);
	}

	// Close the query.
	$query->close();
}

// Close the connection.
$conn->close();


// Make use of our results fetched from the database now.
if (count($my_results) !== 0) {
	foreach ($my_results as $row) {
		echo "ID: " . $row["id"];
		echo "Title: " . $row["title"];
		echo "Description" . $row["description"];
		echo "Category" . $row["category"];
	}
}

Querying database with parameters (SELECT)

// Array to store query results.
$my_results = array();

// User input.
$category = "cycling";

// SQL statement to execute.
// Question marks ("?") are placed in the statement to represent parameters.
// Do not concatenate the user input directly into the SQL statement. NEVER TRUST USER INPUT.
$sql = "SELECT id, title, description from events WHERE category = ?";

// Assuming such function to get the mysqli connection object exists.
$conn = get_conn();

// Prepare the statement.
if ($query = $conn->prepare($sql)) {
	// Bind the user input to the prepared statement.
	// The first parameter of the `bind_param` function is the input parameter type.
	// "s" in this case stands for string.
	$query->bind_param("s", $category);
	
	// Execute the query.
	$query->execute();
	
	// Bind the results from the query to some predefined PHP variables.
	$query->bind_result($id, $title, $description);

	// Fetch all results and store them into an associative array (Representing one "row").
	while ($query->fetch()) {
		$row = array(
			"id" => (int)($id),
			"title" => $title,
			"description" => $description,
		);

		// Store the associative array into another array for use later.
		// This array contains all the "rows" fetched from the query.
		array_push($my_results, $row);
	}

	// Close the query.
	$query->close();
}

// Close the connection.
$conn->close();


// Make use of our results fetched from the database now.
if (count($my_results) !== 0) {
	foreach ($my_results as $row) {
		echo "ID: " . $row["id"];
		echo "Title: " . $row["title"];
		echo "Description" . $row["description"];
	}
}

Inserting, updating or deleting records in database (INSERT, UPDATE, DELETE)

// User inputs.
$id = 5
$title = "YOLO Run";
$description = "Its back again this year, bigger and better!";
$category = "running";

// SQL statement to execute.
// Question marks ("?") are placed in the statement to represent parameters.
// Do not concatenate the user input directly into the SQL statement. NEVER TRUST USER INPUT.
$sql = "INSERT INTO events (id, title, description, category) VALUES (?, ?, ?, ?)";

// Example SQL statement for UPDATE query.
$sql_update = "UPDATE events SET title = ?, category = ? WHERE id = ?"

// Example SQL statement for DELETE query.
$sql_delete = "DELETE from events WHERE id = ?"


// Assuming such function to get the mysqli connection object exists.
$conn = get_conn();

// Prepare the statement.
if ($query = $conn->prepare($sql)) {
	// Bind the user input to the prepared statement.
	// The first parameter of the `bind_param` function is the input parameter type.
	// "s" in this case stands for string, while "i" stands for integer.
	$query->bind_param("isss", $id, $title, $description, $category);
	
	// Execute the query.
	if (!$query->execute()) {
		// Something went wrong.
		echo "Failed to insert record into database: " . $query->error;
	}

	// Close the query.
	$query->close();
}

// Close the connection.
$conn->close();