PHP mysqli class to connect with MySQL database

PHP mysqli class is one of the multiple ways to connect with MySQL database using PHP. In this post we will learn about PHP mysqli class, it’s useful methods and properties.

Following logical steps will show you how to use mysqli class to access MySQL database in PHP:

1. Connecting with MySQL database using PHP
2. Creating a Table in MySQL Database using PHP mysqli
3. Inserting data in MySQL database using mysqli class
4. Update data using mysqli class in PHP
5. Fetch Associated array from MySQL table
6. Fetch table row as object using mysqli class

Connecting with MySQL database using PHP mysqli

To establish connection, we need to create an object of PHP mysqli class. Once the object is created, we will check it’s connect_errno property. If connection is successful, connect_errno property will hold 0 otherwise it will hold the MySQL error code as described here.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog');
/*connecting with mysql*/
if(!$mysqli->connect_errno){
echo "Connection Successful";
} else {
	echo $mysqli->connect_error;
}
/*connecting end*/

Notice the order of the parameters of mysqli constructor, it is host,username,password and database name respectively.

Creating a Table in MySQL Database using PHP mysqli

To create a new table in database, we use query() method of mysqli class. This method returns FALSE if query fails. In queries, where a result set is expected, query() method will return result set and for other successful queries query() method returns TRUE.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog');
/*creating a table starts*/
if($mysqli->connect_errno){
echo $mysqli->connect_error;
} else {
	$sql = 'CREATE TABLE students(
		id INT NOT NULL AUTO_INCREMENT ,
		name varchar(30) NOT NULL,
		study_program varchar(20) NOT NULL,
		department varchar(20),
		date_added TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY (id)
		)';
	$result = $mysqli->query($sql);
	if(!$result){
		echo "Query Failed. ".$mysqli->error;
	} else {
		echo "Query Successfull";
	}
$mysqli->close();//close the connection



}*/
/*creating a table ends*/

CREATE TABLE is SQL command therefore, discussing it is not in the scope of this article. We store SQL query in a string, later this string is passed as first and only required parameter for mysqli query() method.

Inserting data in MySQL database using mysqli class

Inserting records in MySQL database using mysqli class is similar to the previous section as the process is same but we will use a different SQL Command INSERT INTO for this purpose.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog');
if($mysqli->connect_errno){
echo $mysqli->connect_error;
} else {
	$sql = 'INSERT INTO students (name,study_program,department) VALUES("John","BS","Computer Science")';
	$result = $mysqli->query($sql);
	if(!$result){
		echo "Query Failed. ".$mysqli->error;
	} else {
		echo "Successfully Inserted. ".$mysqli->affected_rows." Records";
	}
}

The mysqli class affected_rows property is useful in insert and update operations as it hold the number of rows, which query has affected .i.e. in this case, it will hold 1 as this query is inserting one row in table.

For inserting multiple records at once, we just need to change the SQL command INSERT INTO as follows:

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog');
if($mysqli->connect_errno){
echo $mysqli->connect_errno;
} else {
	$sql = 'INSERT INTO students 
	(name,study_program,department) VALUES
	("John","BS","Computer Science"),
	("Kane","MS","Computer Science"),
	("Bob","PhD.","Physics"),
	("Stewart","MBA","Computer Science")
	';
	$result = $mysqli->query($sql);
	if(!$result){
		echo "Query Failed. ".$mysqli->error;
	} else {
		echo "Successfully Inserted. ".$mysqli->affected_rows." Records";
	}
}

Update data using mysqli class in PHP

Updating the record in MySQL table using mysqli class is pretty straight forward.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog');
if($mysqli->connect_errno){
echo $mysqli->connect_errno;
} else {
	$sql = 'UPDATE students SET name="Kane" WHERE id=1';
	$result = $mysqli->query($sql);
	if(!$result){
		echo "Query Failed. ".$mysqli->error;
	} else {
		echo "Successfully Updated. ".$mysqli->affected_rows." Records";
	}
}

Fetch associated array from MySQL table using mysqli

Reading data from MySQL table using PHP mysqli extension involves retrieving a result set from table and then iterate through it by fetching record as associative array one by one.

$mysqli = new mysqli('127.0.0.1','root','','weblearningblog');
if($mysqli->connect_errno){
echo $mysqli->connect_errno;
} else {
	$sql = 'SELECT * FROM students';
	$result = $mysqli->query($sql);
	if(!$result){
		echo "Query Failed. ".$mysqli->error;
	} else {
		if($result->num_rows > 0){
		$html = "";
		$html .= "";//header row	
		while($current_row = $result->fetch_assoc()){
			$html .= '';
			$html .= '';
			$html .= '';
			$html .= '';
			$html .= '';
			$html .= '';
			}

			$html .= "
ID
Name
Study Program
Department
'.$current_row['id'].'
'.$current_row['name'].'
'.$current_row['study_program'].'
'.$current_row['department'].'
";	
			$result->free();//free the resultset memory
			echo $html;
		} else {
			echo "No Record Exists";
		}
	}
}

As shown in code snippet above, the query() method is now returning a result set instead of TRUE or FALSE as in insert and update operations discussed in previous sections. Here we are getting the students records from MySQL table and creating a HTML table to view it in a browser.

num_rows property of result set object is having the number of rows or records, this result set is consisting of, therefore it is a good idea to evaluate num_rows property before using the result set to read data.

While reading data from database table, it is very important to learn how fetch_assoc() method of result set object works. Whenever we call fetch_assoc() method on result set object, it gives us one row and move internal data pointer to next row, which means if we call fetch_assoc() again, it will return next row and so on. Ultimately if we use fetch_assoc() in a while loop, it will execute on each iteration and returns a new row. If there is no record left fetch_assoc() will return NULL which will cause while loop to exit.

As fetch_assoc() returns associative array, The key of the array is column name of the table and value of the array is the column value for current row. Building up of HTML table is straight forward and don’t need any explanation.

Fetch table row as object using mysqli class

To fetch record as object instead of associative array, we just have to replace fetch_assoc() method with fetch_object().

while($current_row = $result->fetch_object()){
			$html .= '';
			$html .= ''.$current_row->id.'';
			$html .= ''.$current_row->name.'';
			$html .= ''.$current_row->study_program.'';
			$html .= ''.$current_row->department.'';
			$html .= '';
			}

Conclusion

In this tutorial, I tried to focus on practical and code oriented approach to teach this topic. Please share your valuable feedback by commenting.

Share on Facebook

Tweet

Follow us