PHP MySQL Prepared Statements, data binding Example/Sample (With “CURRENT_TIMESTAMP” etc.)

(If you are not sure what is “->” in php or what is ‘ssi’ in bind_param Scroll to “Some basics” section first, then start from top of the page)

Database connection configuration

(Before starting, we need to create database connection string)

$servername = "localhost";
$username = "database_username";
$password = "database_password";
$dbname = "database_name";
// Create connection
$db_connect = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($db_connect->connect_error) {
    die("Connection failed: " . $conn->connect_error);
//echo "Connected successfully";

Example 1 – Insert with prepared statement, data binding

//Prepare statement
$stmt = $db_connect->prepare("INSERT INTO Table_Name VALUES (?, ?, CURRENT_TIMESTAMP, ?)");
//Bind data
$stmt->bind_param('ssi', $First_Name, $Last_Name, $Age);
//Populate Data
$First_Name = 'Fred';
$Last_Name = 'Smith';
$Age = 50;
if(!$stmt->execute()) {
	die('Error: ' . mysqli_error($db_connect));

Example 2 – Select with prepared statement, data binding

$stmt = $db_connect->prepare("SELECT Product FROM Table_Name WHERE ID = ?");
$ID = 'ABC123';
$result = $stmt->get_result();
if($result->num_rows > 0) {
	while($row = $result->fetch_assoc()) {
		echo $row['Product'];


if($result->num_rows > 0) {
	while($row = $result->fetch_assoc()) {
		echo $row['Product'];

Can be replaced by:

if($result->num_rows > 0) {
	while($row = mysqli_fetch_array($result)) {
		echo $row['Product'];

What is fetch_assoc()? [5]

What is mysqli_fetch_array()? [6]

Example 3 – Update with prepared statement, data binding

$stmt = $db_connect->prepare("UPDATE Table_Name SET First_Name=?, Last_Name=?, Time=CURRENT_TIMESTAMP, Num_Books=? WHERE ID=?");
$stmt->bind_param('ssis', $First_Name, $Last_Name, $Num_Books, $ID);
$First_Name = 'Fred';
$Last_Name = 'Smith';
$Num_Books = 10;
$ID = 'ABC123';
if(!$stmt->execute()) {
	die('Error: ' . mysqli_error($db_connect));

Some basics

Object operator “->” [2,3,4]

e.g. “$db_connect->close();”

// Create a new instance of MyObject into $obj
$obj = new MyObject();
// Set a property in the $obj object called thisProperty
$obj->thisProperty = 'Fred';
// Call a method of the $obj object named getProperty
-> is like
. in Javascript
. in JAVA
. in RUBY
. in CloudFusion
. in Python

Type specification chars “i,d,s,b”

($stmt->bind_param(‘ssi’, $First_Name, $Last_Name, $Age);)

(To learn what is ‘ssi’ in Bind data part, see this “Type specification chars” table)

Type specification chars [1]

icorresponding variable has type integer
dcorresponding variable has type double
scorresponding variable has type string
bcorresponding variable is a blob and will be sent in packets


[1] php official document: mysqli_stmt::bind_param
[2] w3schools: PHP OOP – Classes and Objects
[4] php official document: Objects
[5] php official document: mysqli_result::fetch_assoc
[6] w3schools: PHP mysqli fetch_array() Function

Open source Online Document Management System for Developers – ShowDoc

ShowDoc is a tool greatly applicable for an IT team to share documents online. It can promote communication efficiency among members of the team.

What can it be used for?

  • API Document ( Demo

With the development of mobile Internet, BaaS (Backend as a Service) becomes more and more popular. The Server end provides API, and the APP end or Webpage frontend can invoke data conveniently. Using ShowDoc can compile exquisite API documents in a very fast and convenient way.

  • Data Dictionary ( Demo

A good Data Dictionary can easily exhibit database structure to other people, such as definition of each field and the like.

  • Explanation Document ( Demo

You can absolutely use ShowDoc to compile the explanation documents for some tools, as well as to compile some technical specifications explanation documents for the team to look up.

What functions does it have?

  • Sharing and Exporting

Responsive webpage design can share the project documents to computer or mobile devices for reading. It can also export the project into word document for browsing offline.

  • Permission Management
  • Public Project and Private ProjectProjects on ShowDoc are divided into two categories including Public Project and Private Project. Public Project can be visited by any user no matter he/she logs in or not, while inputting password for verification is needed for visiting the Private Project. The password is set by project creator.
  • Project Transfer

The project creator can transfer the project to other users of the website freely.

  • Project Members

You can easily add or delete project members in the project of ShowDoc. Members of the project can edit the project, but they can not transfer or delete the project (only creator of the project has the permission).

  • Edit Function
    • Markdown Edit
    ShowDoc adopts Markdown Editor, and it is excellent both in editing and reading experience. If you know nothing about Markdown, please search “Learning and Introduction of Markdown” on the search engine.
    • Template Insert
    On the editing page of ShowDoc, a click on the button which is on the top of the Editor can easily insert API interface template and data dictionary template. After inserting the template, altering data is the only thing that need to do and it reduces a lot of work in editing.
    • History Version
    ShowDoc provides a function of History Version on the page, and you can easily restore the page to the former version.


Configure Squid proxy with php redirector

Squid proxy can be used with different redirectors and rewriters, in this guide we will be using a redirector written with php.

This guide is using Ubuntu 18.04 LTS, your configuration file maybe located differently if using different version or Linux distribution etc.

Note: Before starting following this guide, you have to make sure your squid proxy is configured properly and running without any issue.

Squid configuration

1 Open squid configuration file from




For following steps, make sure you are using right path/configuration file (either /squid/ or /squid3/), I will be using /squid/

2 Add following line

url_rewrite_program /usr/bin/php /etc/squid/redirect.php

This indicates that we will be using an redirector/ url write program written in php so that we will need php binary to run the script thus “/usr/bin/php” the redorector file path is “/etc/squid/redirect.php” which means will be putting the “redirect.php” file under “/etc/squid”

PHP url rewrite program/redirector

1 We create a “redirect.php” file under “/etc/squid/”

2 We write necessary script

//Modified based on
//Using PHP for Redirects
$temp = array();
// Extend stream timeout to 24 hours
stream_set_timeout(STDIN, 86400);
while ( $input = fgets(STDIN) ) {
  // Split the output (space delimited) from squid into an array.
  $temp = explode(' ', $input);
  // Set the URL from squid to a temporary holder.
  $output = $temp[0] . "\n";
	if(preg_match("/(.*[0-9])\/ABCD\/.*/i", $input)){
		//Replace any url matching and change to to
		$output = "308:".str_replace(parse_url($input,PHP_URL_HOST),'',strtok($input, ' '))."\n"; //Outout intended modified url (308 Permanent Redirect)
		//file_put_contents("/etc/squid/a.txt", $input,FILE_APPEND); //For debugging we can enable this line, output will be found at /etc/squid/a.txt
		//$output = "302:".""."\n"; //(302 Found, Moved Temporarily)
		//We can either output modified url based on ip address, partial url matching, domain matching and modify partialy as well or we can redirect to another url completely
		//Following by more examples
	if(preg_match("/(.*[0-9])\/forum\/.*\/sign\=.*\.jpg/i", $input)){
		$output = "308:".str_replace(parse_url($input,PHP_URL_HOST),'',strtok($input, ' '))."\n";
		// /forum/*/sign
	if(preg_match("/(.*[0-9])\/forum\/pic\/item\/.*\.jpg/i", $input)){
		$output = "308:".str_replace(parse_url($input,PHP_URL_HOST),'',strtok($input, ' '))."\n";
		// /forum/pic/item
	//Temporarily block URLs based on partial url match with regular expression
	if(preg_match("/ad\.m\.domain\.com(\/.*|$)/i", $input)){
		$output = "308:".""."\n";
	if(preg_match("/.*admaster\.com.*/i", $input)){
		$output = "308:".""."\n";
	if(preg_match("/.*\/AdvertiseInterface\//i", $input)){
		$output = "308:".""."\n";
	if(preg_match("/.*\/advertise\//i", $input)){
		$output = "308:".""."\n";
  echo $output;

3 We add execute permission

chmod +x /etc/squid/redirect.php

4 We restart squid

~# systemctl restart squid

5 Now we should be able to see the redirector in action

Extended reading


Online regular expression testers

Which can help you craft desired regular expression for use within redirector