At some point it will cross your mind…

“I want to train my Neural network from data stored in my database…”

Fear not! because that’s what this post is about!

This post will include actual functional (cut and paste) code so rather than a long series of tutorials I will keep things simple and brief so you can see how easy it is.

You might recall reviewing the XOR function when I wrote about it over in Getting started with Neural Networks Part 2

In that post I trained a neural network to perform the XOR operation.

XOR is frequently used as a sort of “hello world” example and the training data required is short and simple so we will use XOR again here, however you could easily extend the code so that it meets your project needs.

As for the database, MySQL (or MariaDB) is wildly popular, especially for web based projects and it also offers ACID compliance so it stands to reason that any important data you want to train your Neural Network on will likely already be stored in MySQL and as such I will be using it in this tutorial but you could do this with MongoDB or some other storage resource if you prefer.

Step 1 – Setup the Database

So to get started, go create an empty database, you can call it whatever you like but in this example my database will simply be called ‘test‘. There is a function bellow called “CreateTable” that will put the XOR training data in the database for you or you can use this SQL query:



CREATE TABLE `TrainingSets` (
  `ID` int(11) NOT NULL,
  `Name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `TrainingData` text COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `TrainingSets` ADD PRIMARY KEY (`ID`);
 
INSERT INTO `TrainingSets` (`ID`, `Name`, `TrainingData`) VALUES(1, 'XOR', '-1 -1\n-1\n-1 1\n1\n1 -1\n1\n1 1\n-1');
ALTER TABLE `TrainingSets` MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;


 

Step 2 – The Functions

Here are the functions you will need. You will need to modify them to meet your needs or write your own versions as the code here is written with the XOR example in mind.



// This function will setup the $table table in your database and
// then insert the XOR Training Data
function CreateTable($host, $user, $pass, $database, $table, $field){
    
    $result = false;
    
    // Create connection
    $connection = new mysqli($host, $user, $pass, $database);
    // Check connection
    if ($connection->connect_error) {
        die('Connection failed: ' . $connection->connect_error);
    }
    // sql to create $table table
    $sql = "CREATE TABLE `$table` (
  `ID` int(11) NOT NULL,
  `Name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `$field` text COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    if ($connection->query($sql) === TRUE) {
        echo "Table $table created successfully.<br>";
        
        // sql to set the ID field as the PRIMARY KEY
        $sql = "ALTER TABLE `$table` ADD PRIMARY KEY (`ID`)";
        if ($connection->query($sql) === TRUE) {
            echo "PRIMARY KEY set to ID.<br>";
            
            // sql to insert XOR into DB
            $sql = "INSERT INTO `$table` (`ID`, `Name`, `$field`) VALUES(1, 'XOR', '-1 -1\n-1\n-1 1\n1\n1 -1\n1\n1 1\n-1')";
            if ($connection->query($sql) === TRUE) {
                echo "XOR inserted into DB.<br>";
                // sql to "auto increment" ID when new $table are added
                $sql = "ALTER TABLE `$table` MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2";
                if ($connection->query($sql) === TRUE) {
                    echo 'ID will AUTO_INCREMENT.<br>';
                    $result = TRUE;
                } else {
                    echo 'Error altering ID: ' . $connection->error . '<br>';
                }
            } else {
                echo 'Error inserting XOR into DB: ' . $connection->error . '<br>';
            }
        } else {
            echo 'Error setting PRIMARY KEY to ID: ' . $connection->error . '<br>';
        }
    } else {
        echo "Error creating $table table: " . $connection->error . '<br>';
    }
    $connection->close();
    
    return $result;
}

// This function calls/pulls the TrainingData from MySQL
function GetTrainingDataFromDB($host, $user, $password, $database, $table_name, $field, $id) {
    $connection=mysqli_connect($host, $user, $password, $database); // open db connection
    $result=mysqli_query($connection,"SELECT $field FROM $table_name WHERE ID=$id"); // query the db
    $data=mysqli_fetch_assoc($result); // pull training data
    mysqli_close($connection); // close connection
    return $data[$field]; // return training data
}

// This function prepares the newline delimited data to be handed off to FANN
/*
Example of "newline delimited data" (like XOR in a Plain Text File) stored in MySQL:
-1 -1
-1
-1 1
1
1 1
-1
1 -1
1
*/
function PrepareDataFromDB($training_data) {
    $training_data = explode("\n", $training_data); // convert training data rows to array
    $num_data = count($training_data);
	   
    // Sift the data and split inputs and outputs
    for($i=0;$i<$num_data;$i++) {
      if($i % 2) { // $training_data[$i] is Output
       $training_data['outputs'][] = explode(' ', $training_data[$i]);
      }else{ // $training_data[$i] is Input
       $training_data['inputs'][] = explode(' ', $training_data[$i]);
      }
    }
    // remove the unsifted data
    foreach ($training_data as $key => $value) {
        if (is_numeric($key)) {
            unset($training_data[$key]);
        }
    }
    return $training_data; // returned the prepared associative array
}

// This function hands the prepared data over to FANN
function create_train_callback($num_data, $num_input, $num_output) {
    global $training_data;
    global $current_dataset;
  
    $dataset = array('input' => $training_data['inputs'][$current_dataset],
                    'output' => $training_data['outputs'][$current_dataset]);
    $current_dataset++;
	
    return $dataset;
}


Step 3 – The Code

Great, now here is the code that demonstrates how to read the data from your database and then train an FANN Neural Network.




// Change to your DB credentials
$host = '127.0.0.1';
$user = 'username';
$password = 'password';
$database = 'test';
$table = 'TrainingSets';
$field = 'TrainingData';

// Insert training data into the database
CreateTable($host, $user, $password, $database, $table, $field); 



// Initialize the program variables
$record_id = 1; // the 'ID' for the training data in MySQL
$current_dataset = 0;
$num_input = 2;
$num_output = 1;
$num_layers = 3;
$num_neurons = 3;
$desired_error = 0.001;
$max_epochs = 500000;
$epochs_between_reports = 1000;

// Get the Training Data from MySQL
$training_data = GetTrainingDataFromDB($host, $user, $password, $database, $table, $field, $record_id);

// Prepare the data
$training_data = PrepareDataFromDB($training_data); 


// How many sets are there?
$num_data = count($training_data['inputs']); 

// Hand the data over to FANN
$train_data = fann_create_train_from_callback($num_data, $num_input, $num_output, "create_train_callback");



// Test for $train_data
if ($train_data) {

    // Create $ann
    $ann = fann_create_standard($num_layers, $num_input, $num_neurons, $num_output);
	
	 

    // Test for $ann
    if ($ann) {
        fann_set_activation_function_hidden($ann, FANN_SIGMOID_SYMMETRIC);
        fann_set_activation_function_output($ann, FANN_SIGMOID_SYMMETRIC);
		
		$result = fann_train_on_data($ann, $train_data, $max_epochs, $epochs_between_reports, $desired_error);

		
		// Train XOR ANN with training data obtained from MySQL
        if (fann_train_on_data($ann, $train_data, $max_epochs, $epochs_between_reports, $desired_error))
		{
           echo 'XOR trained.<br>' . PHP_EOL;
           // Test $ann
           $input = array(-1, 1);
           $calc_out = fann_run($ann, $input);
           printf("XOR test (%f,%f) -> %f\n", $input[0], $input[1], $calc_out[0]);
          
           // destroy $ann
           fann_destroy($ann);
         }
    }
}


echo "<br>All Done!";

If you would like to obtain a copy of this code from GitHub you can find it here: TrainFANNNeuralNetworkFromMySQL.php on GitHub

Here is how it works,  we first make sure the training data is in the database by calling CreateTable(), we then define the variables to use with the ANN. You could compute these values at run time from the training data or store it elsewhere in the database, its up to you but I wanted to keep things simple to lessen the chance of confusion so I predefined them.

We call GetTrainingDataFromDB() to retrieve the raw training data from MySQL.

We call the PrepareDataFromDB() function to split the inputs and outputs into an associative array of ‘inputs’ and ‘outputs’ followed up with a call to fann_create_train_from_callback() which uses my callback function create_train_callback() to build the training resource.

At this point we can create a standard ANN resource using fann_create_standard().

Then as usual we specify the activation functions, but instead of using fann_train_on_file() as you may be familiar with, we use  fann_train_on_data() since our training data is a resource in memory because it loaded from MySQL.

We follow that up with a quick test using fann_run() before destroying the resource.

And with that, you now know how to train a neural network from a database!

Please support me on Patreon so I can continue to bring you more great tutorials like this!

As always I hope you found this both interesting and informative. Please Like, Comment & Share this post with your friends and followers on your social media platforms and don’t forget to click the follow button over on the top right of this page to get notified when I post something new.

If would like to suggest a topic or project for an upcoming post feel free to contact me.

Much Love,
~Joy

 

Advertisements