MySQLi / MySQL Improved

MySQLi provides an updated version of the MySQL functions, designed to take advantage of newer PHP features.

 

The following focuses upon procedural techniques. Object Oriented database concepts are covered in Object Oriented PHP.

 

The MySQLi functions are more or less the same as the MySQL functions, with some subtle differences.

 

The main MySQLi functions are:

  • mysqli_connect(server, user, pass, database) //note new 4th parameter defining the database
  • mysqli_select_db($handle, database) //first parameter $handle required!
  • mysqli_connect_error() //just for connection errors
  • mysqli_error($handle) //for query errors
  • mysqli_query($handle, $query) //first parameter $handle required!
  • mysqli_fetch_array($resultSet) //pass through while loop
  • mysqli_num_rows($resultSet)
  • mysqli_free_result($resultSet)
  • mysqli_close($handle)

This example follows on from the previous MySQL example, but uses the MySQLi functions:

 

<?php
	//call in database connection variables from dbconfig.php file
	require("/var/www/dbconfig.php");

	//connect to the database
	$connection = mysqli_connect( $server, $user, $passwd, $database ) ;

	/* function to check connected and get name of current selected database */
	function checkViewDB($connection) {

		//1st check connected to the database
		if( !mysqli_connect_errno ) {

			die('Error connecting to the database: ' . mysqli_connect_error() );
		}
		//then get the name of the currently selected database
		if ($result = mysqli_query($connection, "SELECT DATABASE()")) {
			$row = mysqli_fetch_row($result);
			echo "Connected to the " . $row[0] . " database.<br>";
			mysqli_free_result($result); //release cursor memory, only really required for big queries
		}
	}

	checkViewDB($connection) ; //check and view current db
	mysqli_select_db($connection, "Academy"); //change db
	checkViewDB($connection) ; //check and view current db

	//function to run and view the query
	function runView( $connection, $query ) {

		//run and assign the query
		$resultSet = mysqli_query( $connection, $query );

		if( ! $resultSet ){

			die('Error getting result set: ' . mysqli_error() );
		}

		//view the result set
		while( $record = mysqli_fetch_array( $resultSet ) ) {

			echo "CustID: {$record[CustID]}, " .
				 'First: ' . $record[First] . ', ' .
				 "Surname: {$record[Surname]}, " .
				 'Age: ' . $record[Age]. '<br>';
		}
	}

	echo "<br>Here's the initial data:<br>";

	//construct a query to view the data
	runView($connection, 'SELECT * FROM customers');

	echo "<br>Insert data<br>";

	//construct and run query to INSERT data INTO the customers table:	
	runView($connection, "INSERT INTO customers (First, Surname, Age) VALUES ('Vanessa', 'Loopy', '44')");

	//construct a query to view the data
	runView( $connection, 'SELECT * FROM customers' );

	echo "<br>Update data<br>";

	//construct and run query to UPDATE data INTO the customers table:	
	runView($connection, "UPDATE customers SET Surname = 'Strange' Where Surname = 'Loopy' ");

	//construct a query to view the data
	runView($connection, 'SELECT * FROM customers');

	echo "<br>Delete data<br>";

	//construct and run a query to INSERT data INTO the customers table:	
	runView($connection, "DELETE FROM customers WHERE Surname = 'Strange' ");

	//construct a query to view the data
	runView($connection, 'SELECT * FROM customers') 

	//close the database handle connection
	mysqli_close($connection);
?>

Leave a Reply