PHP Script to Sync Between Two Different Database Servers

This page gives an example of source code in PHP and MySQL which can sync individual data fields between two different MySQL database servers.

I use it to update from my LOCALHOST MySQL development server to the live one, which this website Codewiz.au loads from. Since I'm the only person who needs to update the website, there's no need for a fancy HTML-based front-end admin section, like would be done in most cases when developing a site like this for a client. So I can work directly with the MySQL server (in MySQL Workbench for example) on my local machine, and then run this script to update/sync the changes to the live version.

Some of the details have been changed from the actual code on this website (e.g. the login details for the remote MySQL server).

The PHP code colouring for the display on this page was done using highlight.js. The colour theme used here is a slightly modified version of the theme called 'a11y-dark.min.css', which is one of the files that comes with the highlight.js package when you download it from their website. The modified version adds rounded corners to the dark grey background. I called it 'a11y-dark_rounded.min.css', you can see it on this site if you click here. The only change I've made (so far) to the original is the border radius of 10px added to the end of the very first line of .css code.

How the Code Example Works

I'll update this in more detail later...

Basically, there's an include file which adds a wrapper class for MySQL connections. The relevant parts of this are shown further down on this web page. There are some other functions used here, I've added most (perhaps all, I'll have to look more closely to be sure) of them to this page, shown after the main script source code below.

The script (in one of the include files) detects that it's running on the LOCALHOST, otherwise it terminates (so it can't be run online from anywhere on the internet).

The script as shown here is divided into two main parts. The first part looks for records in the posts table on the local (development) MySQL server, and if there are any which are missing on the remote/live MySQL server, it will add them. The second part then compares the records on each server to see if there are any changes which need to be updated, and, if so, it updates them.

It's quite convenient in that all I have to do is press F5 (or click on the refresh icon in the web browser) to refresh this web-page based script, and all the database updating is done in one step.

I was looking online for more built-in ways of doing this (rather than record-by-record like this script does), and there did seem to be at least one way, though it looked so complex to set up (and seemed to be designed for a more complex type of application that I have here) that it seemed easier just to write a script like this to do it.

PHP and MySQL Source Code to Sync Two Databases

<?php

$siteCode = 'sp'; // This isn't really needed by this page, other than to satisfy some
                  // generic functions which the include files want, but which aren't
				  // used in the script itself - to stop warnings being generated because
				  // of the non-set variable.
				  
				  // The same database table (not just a copied table with the same structure,
				  // but the same actual table) is used to store the posts for several different
				  // websites, and the particular site which is being loaded currently,
				  // (in this case 'sp' for 'Spysafe'),
				  // is set here at the start of the page. 
				  
				  // Unlike (I think) every other use of this database so far, the script on this
				  // page will update/sync the posts for *all* of the websites used by the DB posts 
				  // table in the one run.

require("includes/include_filename.php");

$total_affected_rows = 0;
$total_inserted_rows = 0;
$total_updated_rows = 0;
$rows_to_potentially_change = 0;

$charset_change_string = $sql->set_charset('utf8mb4');

function colour_highlight($start_or_end, $on_or_off = 0)
{
	//$optional_colour_highlight['on'] = '<span style="color:#00ff00;">';
	//$optional_colour_highlight['off'] = '</span>';
	
	if ($start_or_end == 'start') // Is "start" of the optional colour-highlighted text
	{
		if ($on_or_off) $result = '<span style="color:#00ff00;">';
		else 			$result = '<span>';	
	}	
	else // Is "end"
	{
		$result = '</span>';		
	}

	return $result;
}

if (!IS_LOCALHOST)
{
	//require("404.php");
	exit;
}

// At this point we are definitely running on the LOCALHOST and we have a database connection to it.

// The main include file (as already loaded above) senses that the script is running on the LOCALHOST
// and therefore makes a database connection to the local hosted DB, since it's expecting a web page
// that's being loaded from the local Apache server. 

// The local hosted database connection was made by similar code to below, except that the 
// new sql_connection object was called 'sql' rather than the new one below which is called
// 'sql_web' to refer to the live web DB server connection.

// Make another DB connection to the web server now:

		$DB_HOST = "your.remote.db.server.com";			/* database server hostname */
		$DB_USER = "remote_db_username";				/* database user */
		$DB_PASS = 'remote_db_pw';						/* database password */
		$DB_NAME = "remote_db_schema_name";				/* database name */
		$DB_PREFIX = "";   								/* database table prefix */

		//Initialise generic database connection
		$sql_web = new sql_connection($DB_HOST, $DB_USER, $DB_PASS);
		$sql_web->select_db($DB_NAME);
		
		// For the rest of this PHP/web page, 
		//     $sql->some_db_function()     is the LOCAL database, and
		//     $sql_web->some_db_function() is the ONLINE LIVE database
		
		// These are unset for security, since some errors generate a dump 
		// of all the set variables, so this will stop the database login
		// details accidentally being revealed in such a way. The variables
		// are no longer needed now, since the DB has already been logged into
		// above for this page run.
		
		unset($DB_HOST);
		unset($DB_USER);
		unset($DB_PASS);
		unset($DB_NAME);
		
?>

<!DOCTYPE HTML>
<html lang = "en">

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Updating Posts Table from LocalHost to Server</title>
</head>

<body style="background-color:#000000";>

<div style="font-family:'Courier New', Courier, monospace; background-color:#000000; color:#00bb00">

<?php
      
// Query for last updated local row
echo '<p>Looking for posts to insert or update.......</p>';

	$query = "
		SELECT 		id, site, url, date_any_fields_modified
		FROM   		posts
		# WHERE 		site = '".$siteCode."'	
		ORDER BY 	date_any_fields_modified DESC
		LIMIT 		1
		;";

$result = $sql->query($query);
$lrow = $sql->fetchrow($result);

echo 'The latest updated LOCAL DB SERVER posts table row is &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' 
 . $lrow['id'] . ', ' . $lrow['site'] . ', "' . $lrow['url'] . '": Updated  on ' 
 . $lrow['date_any_fields_modified'] . '<br />';
$last_local_update = strtotime($lrow['date_any_fields_modified']);

$result = $sql_web->query($query);
$wrow = $sql_web->fetchrow($result);

echo 'The latest updated HOSTED WEB DB SERVER posts table row is ' . $wrow['id'] . ', ' 
 . $wrow['site'] . ', "' . $wrow['url'] . '": Updated on ' . $wrow['date_any_fields_modified'];
$last_web_server_update = strtotime($wrow['date_any_fields_modified']);

//if ($last_local_update > $last_web_server_update)  
/////////////////// THERE IS STUFF TO UPDATE!!! ///////////////////
// ^ This was no longer a valid test after I realised that it was possible 
// for a row older then the latest update to be deleted,
// though it would not happen often in real-life use of the DB, it was left 
// as a possibility so the comparisons of the tables
// done by this page would be more thorough and cover more possibilities 
// of differences in the two databases.

if(true) // Originally this was more selective (see above comment), but then I realised 
		 // that it would be better for this code below (i.e. most of this PHP script)
		 // to run all the time. This was my lazy change so it would do that,
		 // and which also allows it to be easily changed further, later on, if/as required...
{
	echo '<p>ONLINE POSTS TABLE MIGHT NEED MODIFICATION. Checking...</p>';
	
	// Get list of field names to update in a CSV row as a string. 
	// ((This is mainly for experimental / development / testing / debugging))
	// Actually the INSERT section of this page uses it to save doing a * query
	
	$query = "SELECT group_concat(COLUMN_NAME) AS column_names
			  FROM INFORMATION_SCHEMA.COLUMNS
			  WHERE TABLE_SCHEMA = 'master_au' 
			  AND TABLE_NAME = 'posts'
			  AND COLUMN_NAME <> 'id'
			  ;";
	
	$result = $sql->query($query);	
	$lrow = $sql->fetchrow($result);
	$local_posts_table_column_names_string = $lrow['column_names'];
	//print_r ($lrow);
	//echo'<br>'.$local_posts_table_column_names_string.'<br><br>';
		
		// Get list of field names to update as an array, without the id, 
		// ((or site, or url since these are keys -- these are included now too))
		
		$query = "SELECT COLUMN_NAME
				  FROM INFORMATION_SCHEMA.COLUMNS
				  WHERE TABLE_SCHEMA = 'master_au' 
					AND TABLE_NAME = 'posts'
					AND COLUMN_NAME <> 'id'
				#	AND COLUMN_NAME <> 'site'
				#	AND COLUMN_NAME <> 'url'		
				;";
	// echo 'These are the column names in an array: <br>';	// THIS IS A GOOD ARRAY TO USE!! 
	$result = $sql->query($query);
	$num_column_names = $sql->numrows();
	while( $lrow = $sql->fetchrow($result) )
	{
		$local_posts_table_column_names_array[] = $lrow['COLUMN_NAME']; // Inside while loop
	}
		
	//print_r ($local_posts_table_column_names_array);
	//echo'<br><br>';	
	// Now (from the above code) we have a good array of column names 
	// in the posts table on the local DB server	
		
		$query = "
		SELECT 		id, site, url, date_any_fields_modified
		FROM   		posts
		# WHERE 		site = '".$siteCode."'	
		ORDER BY 	date_any_fields_modified ASC
		#LIMIT 		1
		;";
	
	// Query local DB
	$result = $sql->query($query);	
	while( $lrow = $sql->fetchrow($result) )
	{
		$local_server_array[ $lrow['id']] = $lrow; // Inside while loop
	}
	
	/*
	echo '<p><br><br><br>List of all local DB server rows in update order, oldest to newest</p>';
	foreach ($local_server_array as $local_row) 
	{
	  echo "$local_row[0] <br>";
	}
	*/
	
	// Query web server remote DB
	$result = $sql_web->query($query);	
	while( $wrow = $sql_web->fetchrow($result) )
	{
		$web_server_array[ $wrow['id']] = $wrow; // Inside while loop
		$web_server_array_ids_only[] = $wrow['id'];
	}
	
	// print_r($web_server_array_ids_only) ; echo '<br>';
	
	/*
	echo '<p><br><br><br>List of all web / remote DB server rows in update order, oldest to newest</p>';
	foreach ($web_server_array as $web_row) 
	{
	  echo "$web_row[0] <br>";
	}
	*/	
	
	/////////////////////////////////////////////////////////////////////////////////////////////////////
	// Look for any rows that exist on the local DB 
	// but not on the online web server DB (i.e. newly made rows/posts)
	
	$rows_to_potentially_insert = 0;
	echo '<p>Looking for NEW LOCAL ROWS that need inserting, oldest `date_any_fields_modified` to newest:</p>';
	foreach ($local_server_array as $local_row) 
	{
		if (!in_array($local_row['id'],  $web_server_array_ids_only)) 
		{
			$rows_to_potentially_insert++;
			$rows_to_potentially_change++;
			echo "ONE IS MISSING ON THE REMOTE/ONLINE/LIVE WEB DB!!! ";
			echo 'The local row that\'s missing on the live web server is <span style="color:#00ff00;">ID ' 
				  . $local_row['id'] . ', '. $local_row['url'] . '.html</span><br>';
			
			// Now we need to insert this missing/new row into the remote/online/live web DB
			
			// Get all the non-NULL values of this missing from from the local DB
			
			$query = "
				SELECT 		".$local_posts_table_column_names_string."
				FROM   		posts
				WHERE 		id = ".$local_row['id']."
				 
				# AND WHERE 		site = '".$siteCode."'	
				# ORDER BY 	date_any_fields_modified ASC
				#LIMIT 		1
				;";
	
			$result = $sql->query($query);
			$lrow = $sql->fetchrow($result);
			//print_r($lrow); echo '<br>';
	
			$data_array_to_insert['id'] = $local_row['id'];
			
			foreach ($lrow as $key => $value)
			{
				if (!is_numeric($key) and !is_null($value))
				{
					// echo 'Field name: ' .$key.', Field value: ' .$value.'<br>';					
					
					// if ($key == 'content') 
					// $data_array_to_insert[$key] = $sql_web->make_escape_string($value);					
					// else $data_array_to_insert[$key] = $value;
					// Above 2 lines are commented out and replaced with the line below
					// so ALL the newly inserted fields 
					// are now escaped and not only the content field. 
					// This will allow things like single quotes in the Title, etc.
					$data_array_to_insert[$key] = $sql_web->make_escape_string($value);	
				}
			}
			
			//print_r($data_array_to_insert); echo '<br>';
	
			// This function is in includes/t_functions.php not far from the top of the file
			// Adds a data record into the db. 
			// 
			// The data is in an associative array
			// where the array keys are the fieldnames and the values are the values to insert
			// in the new database record. No key field is genereated.
			//
			//        $sql_web->add_simple_data_record($tablename, $data);
			
			$sql_web->add_simple_data_record('posts', $data_array_to_insert);
			$affected_rows = $sql_web->affected_rows();
			
			echo '<br>Number of rows successfully inserted by this query was: ' . $affected_rows . '<br>';
			$total_affected_rows += $affected_rows;	
			$total_inserted_rows += $affected_rows;	
				
			// This is the array of column names, as generated earlier on this page, no id field in it:
			//                  $local_posts_table_column_names_array[]
		}
	}	
	
	// Re-query the live online DB for updates now, with the newly inserted row(s) in it too.
	if($total_inserted_rows)  
	{
		unset($web_server_array);
	
		$query = "
			SELECT 		id, site, url, date_any_fields_modified
			FROM   		posts
			# WHERE 		site = '".$siteCode."'	
			ORDER BY 	date_any_fields_modified ASC
			#LIMIT 		1
			;";
			
		// Query web server remote DB
		$result = $sql_web->query($query);	
		while( $wrow = $sql_web->fetchrow($result) )
		{
			$web_server_array[ $wrow['id']] = $wrow; // Inside while loop
		}
		
		$is_highlighted = 1;	
	}	
	else 
	{	
		echo 'NO NEW LOCAL ROWS were found that need inserting.<br>';
		$is_highlighted = 0;
	}
	  
	echo '<br>'.colour_highlight('start', $is_highlighted)
	 .'TOTAL NUMBER OF ROWS THAT NEEDED INSERTING  by this web page run WAS: '
	 .$rows_to_potentially_insert . ''.colour_highlight('end').'<br>';
	 
	echo '    '.colour_highlight('start', $is_highlighted)
	 .'TOTAL NUMBER OF ROWS SUCCESSFULLY INSERTED  by this web page run WAS: ' 
	 . $total_inserted_rows . ''.colour_highlight('end').'<br>';
	  
	  /*$web_row = $web_server_array[$local_row['id']];
	  if ($web_row['date_any_fields_modified'] != $local_row['date_any_fields_modified'])
	  {
		  //echo "$web_row[0] <br>";
		  $rows_to_potentially_update++;
		  echo 'GOT ONE TO UPDATE!!<br>';
		  echo 'LOCAL&nbsp;&nbsp;' . $local_row['id'] . ', ' . $local_row['site'] 
		  . ', ' . $local_row['url'] . ', ' . $local_row['date_any_fields_modified'] . '<br>';
		  
		  echo 'REMOTE&nbsp;' . $web_row['id']   . ', ' . $web_row['site']   
		  . ', ' . $web_row['url']   . ', ' . $web_row['date_any_fields_modified']   . '<br><br>';
		  
		  $array_of_posts_row_ids_to_update[] = $local_row['id'];
	  }
	}
	*/
	// print_r($web_server_array);
	
	/////////////////////////////////////////////////////////////////////////////////////////////////////
	// Look for anything that needs updating (same `id` fields, different `date_any_fields_modified` fields)
	
	$rows_to_potentially_update = 0;	
	echo '<p>Looking for DIFFERING ROWS (i.e. different `date_any_fields_modified`), 
		  oldest `date_any_fields_modified` to newest:</p>';
	foreach ($local_server_array as $local_row) 
	{
	  $web_row = $web_server_array[$local_row['id']];  
	  // This line will fail with a Notice: Undefined offset [newly inserted row id] if there are 													   
	  // still any new rows left by the time the page gets to here.
	  if ($web_row['date_any_fields_modified'] != $local_row['date_any_fields_modified'])
	  {
		  //echo "$web_row[0] <br>";
		  $rows_to_potentially_update++;
		  $rows_to_potentially_change++;
		  echo 'GOT ONE TO UPDATE!!<br>';
		  
		  echo 'LOCAL&nbsp;&nbsp;<span style="color:#00ff00;">' . $local_row['id'] . ', ' 
		  		. $local_row['site'] . ', ' . $local_row['url'] . '</span>, ' 
				. $local_row['date_any_fields_modified'] . '<br>';
				
		  echo 'REMOTE&nbsp;<span style="color:#00ff00;">' . $web_row['id']   . ', '
		        . $web_row['site']   . ', ' . $web_row['url']   . '</span>, ' 
				. $web_row['date_any_fields_modified']   . '<br><br>';
		  
		  $array_of_posts_row_ids_to_update[] = $local_row['id'];
	  }
	}
	
	if ($rows_to_potentially_update)
	{
		echo'$array_of_posts_row_ids_to_update = '; print_r ($array_of_posts_row_ids_to_update); echo'<br>';
			
		$ids_to_update_string = make_string_for_AS_query_of_numeric_data($array_of_posts_row_ids_to_update);
		
			  $query =" 
				SELECT 		*
				FROM 		posts
				WHERE		id IN (".$ids_to_update_string.")
				;";
		
		echo '<br>Sending this query to local DB:<br>' . $query . '<br>';
				
		$r_result = $sql->query($query);
		
		$num_rows = $sql->numrows();
		echo '<br>Number of rows from above query is: ' . $num_rows
		   . '. That is, <strong>there are '.$num_rows.' rows to update</strong>.<br><br>';
		while( $lrow = $sql->fetchrow($r_result) ) 
		// This is looping over all the local records that need to be send to the server for updates
		{
			//$local_server_array[ $lrow['id']] = $lrow; // Inside while loop
			echo '<strong>PROCESSING ROW: ';
			echo 'LOCAL&nbsp;&nbsp;' . $lrow['id'] . ', ' . $lrow['site'] . ', ' 
			 . $lrow['url'] . ', ' . $lrow['date_any_fields_modified'] . '</strong><br><br>';
			
			// First we need to query the remote DB for the row which we are working on
			$query =" 
					SELECT 		*
					FROM 		posts
					WHERE		id = ".$lrow['id']."
					;";
					
			$wresult = $sql_web->query($query);	
			$wrow =   $sql_web->fetchrow($wresult);
			  
			$update_query = '
			UPDATE `master_au`.`posts`
			SET ';
			
			// This 'foreach' is looping over each column/field name in the posts table
			// to see which ones need updating
			foreach ($local_posts_table_column_names_array as $column_name) 
			{
				if ($lrow[$column_name] != $wrow[$column_name])
				{
					if ($column_name != 'content')
					
					echo 'THIS COLUMN IS DIFFERENT! Local: '.$column_name.' ---> ' . $lrow[$column_name] 
					   . '<br>  IS NOT THE SAME AS&nbsp;&nbsp;&nbsp;Web Server: '
					   .$column_name.' ---> ' . $wrow[$column_name] . '<br><br>';
					   
					else echo 'THIS COLUMN IS DIFFERENT! Local: ' .$column_name
					   .' ---> **CONTENT**<br>  IS NOT THE SAME AS&nbsp;&nbsp;&nbsp;Web Server: '
					   .$column_name.' ---> **CONTENT**<br><br>';
					
					$update_query .= " ". $column_name . " = '".$sql_web->make_escape_string($lrow[$column_name])."',";
					//if ($t < $num_column_names) echo ', ';
				}
			}
			
			$update_query = rtrim($update_query, ", \t\n"); // Remove any trailing comma and whitespace
			
			$update_query .= "  WHERE id = ".$lrow['id'].";";
			  
			// echo '<pre>' . $update_query . '</pre><br>';	
			
			// REMEMBER TO RUN THE ABOVE QUERY ON THE **REMOTE** SERVER sql_web-> AND NOT THE LOCAL ONE!!
		
			$wresult = $sql_web->query($update_query);
			$affected_rows = $sql_web->affected_rows();
			echo '<br>Number of rows successfully updated by this query was: ' . $affected_rows . '<br>';
			$total_affected_rows += $affected_rows;	
			$total_updated_rows += $affected_rows;	
		}
		$is_highlighted = 1;
	}	
	else 
	{	
		echo 'NO DIFFERING ROWS were found that need updating.<br>';
		$is_highlighted = 0;
	}
	
	echo '<br>'.colour_highlight('start', $is_highlighted)
	 .'TOTAL NUMBER OF ROWS THAT NEEDED UPDATING&nbsp;&nbsp;by this web page run WAS: '
	 .$rows_to_potentially_update .''.colour_highlight('end').'<br>';
	 
	echo '    '.colour_highlight('start', $is_highlighted)
	 .'TOTAL NUMBER OF ROWS SUCCESSFULLY UPDATED&nbsp;&nbsp;by this web page run WAS: ' 
	 . $total_updated_rows . ''.colour_highlight('end').'<br>';
	
	if($rows_to_potentially_change) $is_highlighted = 1;
	else $is_highlighted = 0;
	
	echo '<br><br>'.colour_highlight('start', $is_highlighted)
	 .'TOTAL NUMBER OF ROWS THAT NEEDED CHANGING&nbsp; by this web page run WAS: ' 
	 . $rows_to_potentially_change . ''.colour_highlight('end').'<br>';
	 
	echo ''.colour_highlight('start', $is_highlighted)
	 .'TOTAL NUMBER OF ROWS SUCCESSFULLY AFFECTED by this web page run WAS: '
	 . $total_affected_rows . ''.colour_highlight('end').'<br>';
	
	if(!$rows_to_potentially_change) 
		echo '<BR><span style="color:#00ff00;">THERE WAS NOTHING DETECTED THAT NEEDED UPDATING.</span><br>';

	if($rows_to_potentially_change == $total_affected_rows) echo '<br>RUN WAS SUCCESSFUL.';
	else echo '<BR><span style="color:#00ff00;">THERE WAS A PROBLEM WITH THIS RUN.</span>';
}
   
//else echo '<BR><BR><span style="color:#00ff00;">THERE WAS NOTHING DETECTED THAT NEEDED UPDATING.</span>';			 

?>

</div>
</body>
</html>

Included PHP Functions Used Above

Here are some of the functions as used above. The sql_connection class has more functions in it than are shown here — I've added ones that would help with understanding how the rest of the code on this page works.

It's useful having a "wrapper" class that handles most of the interaction with the database. Among other benefits, it means that when a certain way to do something common changes in a new version (either of PHP or of MySQL), usually, only the one thing (the class definition) needs changing, and all the rest of the existing code in the site will still work the same.

Some of the code as shown below goes back almost 20 years, and has been updated periodically to reflect changes in PHP and MySQL.

<?php 

////////////////////////////////////////////////////////////////////
// This is the class to inferface with the database
////////////////////////////////////////////////////////////////////
class sql_connection
{
	var $result;
	var $db;

	// Constructor  (because it's a function with the same name as the class NO! it's not anymore,
	// This was the PHP4 style and is deprecated in PHP 7 , the fix is to change the name of the 
	// constructor to __construct      )
	function __construct($dbhost, $dbuser, $dbpass)
	{
		// Open connection to database. the @ means suppress errors		
		if (!isset($this->db)) @ $this->db = mysqli_connect($dbhost, $dbuser, $dbpass);
		if (!$this->db)
		{	echo "Error: Could not connect to database. Please try again later.";
			exit;
		}
		//$this->query("SET time_zone = '" . TIME_ZN_RAW . "';"); // TIME_ZN_RAW set to +10 in application.php
		// if (IS_LOCALHOST == 0) $this->query("SET time_zone = '" . TIME_ZN . "';"); 
			// TIME_ZN set to Australia/Sydney in application.php. This should vary from +10 
			// and +11 in daylight savings time
		// DOESN'T WORK in both localhost and dreamhost
		// It still seems to work (even on Dreamhost) without setting this.
	}
	
	function make_escape_string($string)
	{
		return mysqli_real_escape_string ($this->db , $string);	
	}
	
	function select_db ($dbase) 
	{
		mysqli_select_db($this->db, $dbase);	
	}

	function query ($query)
	{
		unset ($this->result);
		$this->result = mysqli_query($this->db, $query) or trigger_error ("Invalid query: " 
											. mysqli_error($this->db) 
											. ', File: ' . $_SERVER['PHP_SELF']
											. ', Query: ' . $query
											, E_USER_ERROR); 
		return $this->result;
	}
	
	function fetchrow ($query_id = 0)
	{
		if(!$query_id)
		{
			$query_id = $this->result;
		}
		if($query_id)
		{
			//$this->row[(integer)$query_id] = @mysqli_fetch_array($query_id);
			//return $this->row[(integer)$query_id];
			$this->row = @mysqli_fetch_array($query_id);
			return $this->row;
			
			
		}
		else
		{
			return false;
		}
	}
	
	//  This function row_data_seek resets the row pointer for fetchrow(). If called
	//  without an argumentor with an argument of 0 it resets to the first row 
	//  (as if fetchrow had neverbeen called.
	function row_data_seek ($row_number = 0)
	{
			//$this->row[(integer)$query_id] = @mysqli_fetch_array($query_id);
			//return $this->row[(integer)$query_id];
			if (@mysqli_data_seek($this->result, $row_number)) return true;
			else return false;
	}
	
	// Fetches a value from a single value query, e.g. a MAX() or a COUNT()
	function fetchvalue ($query_id = 0)
	{
		if(!$query_id)
		{
			$query_id = $this->result;
		}
		if($query_id)
		{
			$value_array = @mysqli_fetch_array($query_id);
			return $value_array[0];
		}
		else
		{
			return false;
		}
	}
	
	function numrows($query_id = 0)
	{
		if(!$query_id)
		{
			$query_id = $this->result;
		}
		if($query_id)
		{
			$result = mysqli_num_rows($query_id);
			return $result;
		}
		else
		{
			return false;
		}
	}
	
	// New function added 7 April 2017 to get how many rows are actually updated in an UPDATE query
	function affected_rows()
	{
		return mysqli_affected_rows($this->db);
	}
	
	// New function added 25 Sept 2018 to change the character set eg to UTF-8
	function set_charset($string)
	{
		
		if (!mysqli_set_charset($this->db , $string)) 
		{
   			$return_string = 'Error loading character set ' .$string . mysqli_error($this->db);
		} 
		else 
		{
    		$return_string = 'Current character set: ' . mysqli_character_set_name($this->db);
		}
		
		return $return_string;
	}
		
	// Adds a data record into the db. The data is in an associative array
	// where the array keys are the fieldnames and the values are the values to insert
	// in the new database record. No key field is genereated.
	function delete_simple_data_record($tablename, $whereclause)
	{
		// Generate SQL statement for database insert 		
		$sql_statement = "DELETE FROM " . $tablename . " WHERE " . $whereclause;			
		$result = $this->query($sql_statement);
		return $result;		
	}
	
	// Adds a data record into the db. The data is in an associative array
	// where the array keys are the fieldnames and the values are the values to insert
	// in the new database record. No key field is genereated.
	function add_simple_data_record($tablename, $data)
	{
		// Generate SQL statement for database insert 		
		$sql_part_1 = "INSERT INTO " . $tablename . " ( ";
		$sql_part_2 = " VALUES ( ";
		
		$done_one = 0;
		foreach ($data as $fieldname => $value)
		{
			if ($done_one) 
			{
				$sql_part_1 .= ", ";
				$sql_part_2 .= ", ";
			}
			else $done_one = 1;
					
			$sql_part_1 .= $fieldname;
			$sql_part_2 .= "'" . $value . "'";
		}
		
		$sql_part_1 .= " ) \r\n ";
		$sql_part_2 .= " ) ";		
		
		// Add new data record		
		
		$result = $this->query($sql_part_1 . $sql_part_2);
		return $result;		
	}
	
	// Calculates a new key id number as 1 + the existing biggest key id
	// (simulates the function of an autonumber database field)
	function newkeyid($tablename, $keyfieldname)
	{
		$result = $this->query("
				SELECT 		MAX(".$keyfieldname.")
				FROM		".$tablename."
				");				
		if ( $existing_max = $this->fetchvalue() ) $newkeyid = $existing_max + 1;
		else $newkeyid = 1;	
	
		return $newkeyid;
	}
	
	// Adds a new data record to a database table. The data is in an associative array
	// where the array keys are the fieldnames and the values are the values to insert
	// in the new database record
	//
	// $data does not include the key field since this is treated as an autonumber (the number
	// is created by this function
	function add_data_record($tablename, $keyfieldname, $data)
	{
		// Lock database table		
		$result = $this->query("
				LOCK TABLES ".$tablename." WRITE
				");
		
		// Calculate new keyfield ID		
		$newkeyid = $this->newkeyid($tablename, $keyfieldname);
		
		// Add key values to data		
		$data[$keyfieldname] = $newkeyid;
		
		// Add record to db
		$this->add_simple_data_record($tablename, $data);							
			
		// Unlock database table		
		$result = $this->query("
				UNLOCK TABLES
				");
		
		return $newkeyid;
	}
	
	// Updates a data record.
	// The data is in an associative array
	// where the array keys are the fieldnames and the values are the values to insert
	// in the new database record.
	// The $whereclause passed to this function should NOT include the WHERE keyword.
	function update_data_record($tablename, $data, $whereclause)
	{
		$sql = "UPDATE " . $tablename . " SET ";
		
		$i = 0;
		foreach ($data as $fieldname => $value)
		{
			if ($i) $sql .= " ,\r\n ";			
			$sql .= $fieldname . " = '" . $value . "'";
			$i++;
		}
		
		$sql .= " \r\n WHERE " . $whereclause;		
		$result = $this->query($sql);			
		return $result;	
	}	
	
// end of class sql_connection	
}

///////////////////////////////////////////////////////////////////////////////////////
// Global functions

function redirect ($location)
{
	// THIS FUNCTION ONLY WORKS PROPERLY IF NOTHING HAS BEEN WRITTEN TO THE HTTP OUTPUT.
	// THIS INCLUDES NO HTTP HEADERS OR ANYTHING. IT MUST COME BEFORE THE DOCTYPE TAG, ETC.
	$hstring = "Location: http://".$_SERVER['HTTP_HOST'].dirname($_SERVER['PHP_SELF']);
	if (substr($hstring, -1) != "/") $hstring .= "/";
	$hstring .= $location;
	header($hstring);		
	//echo $hstring;  
	exit();
}

?>

I'll further tidy up the code formatting, and explain more of the script (and the included functions) later...

Cover image by Shutterstock

Share This Page

If you liked this page, please share it with others! You can use the links to share on Facebook, Twitter, LinkedIn, Pinterest, and Email. Ther is also an RSS feed to get updates for the website.