Tuesday, September 18, 2012

Controlling multiple Arduinos from a webserver with the details stored in a mySQL database using ENC28J60 network adaptor


See previous post for original details and code for the Arduino.

Following the last tinkering session I finally got a chance to look at the webserver side.
On my XAMPP install I enabled mySQL
Created a Database - arduinolist
Created 2 tables - alist (list of the Arduinos) and apin (pins used on each Arduino)

Used mySqlAdmin to enter some data to test it

Code to create the Arduino List Table

CREATE TABLE IF NOT EXISTS `alist` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Arduino` varchar(17) NOT NULL,
  `Active` int(11) NOT NULL,
  `aname` varchar(200) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Code to insert the entry for the Arduino


INSERT INTO `alist` (`ID`, `Arduino`, `Active`, `aname`) VALUES
(1, '192.168.1.205', 1, '1st Arduino'),
(2, '192.158.1.205', 1, '2nd Arduino');

Code to create the table for the pins used per Arduino

CREATE TABLE IF NOT EXISTS `apin` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `pinName` varchar(200) NOT NULL,
  `alistID` int(11) NOT NULL,
  `pin` int(11) NOT NULL,
  `active` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;


Code to enter the details for the 2 pins I am using

INSERT INTO `apin` (`ID`, `pinName`, `alistID`, `pin`, `active`) VALUES
(1, 'Pin 2', 1, 2, 1),
(2, 'Pin 4', 1, 4, 1)

(3, 'Pin 4 on Arduino 2', 2, 4, 1);

From above this structure means you can add as many Arduinos as you spare IP addresses
It also means you can add as many pins as there are available on your Arduino

Once I added all the data I now had he data in place for the Arduino and the pins I have LEDs on.

Then it was a matter of doing the PHP code.  This is when I discovered my PHP is very rusty. So, it took a while to get this sorted.

In the end I got there and now the PHP code on the server will list all the Arduinos and then the pins on each Arduino.  As I only have 1 Arduino/ENC28J60 I  added a 2nd entry for the same Arduino with a different name and added one of the pins again.and gave it a different name.

Since the code references the Arduino by it's ID record in the database rather than the IP address this was a successful test of whether the code would support multiple Arduinos with multiple pins on each.


If you are interested then below is the revised PHP code for the real webserver.
This is real nasty ugly code so there will be a lot of places where it can be improved which is my next task.

<!--
Winlkeink
September 2012
For feedback, comments and questions go to winkleink.blogspot.com

This code works with the Ethercard_LED_ONOFF_PHPCall code for Arduino to control Pins on Arduinos
using a web server as the interface.
The web page is served from a database where you list your Arduinos and the pins used.
With the options and when you click the button it does an fopen in the background
to the Arduino with the relevant command.
By doing this the IP address of the Arduino is not displayed on the webpage and also the
commands to control the Arduino is not shown publicly.
When compared to using the Arduino as the web server itself and using a GET REQUEST.

By doing it this way you have more control over the Arduino and the web interface.
This version is VERY rough with horrible colours.

NOTE:
As always my code is rough and is designed to get things done rather than beign perfect or pretty.
Use as you wish and let me know your thoughts.

-->

<!-- Start of the HTML -->
<html>
<head>
<title>Click to Turn on or OFF the LED in the background</title>
</head>
<body bgcolor="#FF9933">
<?php

// START CONNECT TO DATABASE

$database = "ArduinoList";
$username = "root";
$password = "";

IF ($LinkID = mysql_connect('localhost', $username, $password))
{
//print ('mySQL Connected<BR>');
}
else
{
//print ("mySQL NOT Connected<BR>");
//echo mysql_error();
//echo "<P>";
}

//Select the Database
IF (mysql_select_db($database))
{
#print ('Selected Database<BR>');
}
else
{
# print ("Failed to Select Database<BR>");
# echo mysql_error();
echo "<P>";
}

// END CONNECT TO DATABASE

//
// Start check for button presses
//

// Get the list of Arduinos
$querya = "SELECT ID, Arduino, aname FROM alist WHERE Active=1 ORDER BY ID DESC";
        IF ($resulta = mysql_query($querya))
        {
        # The Query was successful
        $column_counta = mysql_num_rows($resulta);
       
        // echo "column_counta is $column_counta";
            IF ($column_counta != 0 )
            {
                for ($column_numa = 0;
                $column_numa < $column_counta;
                $column_numa++)
                    {
                    $result_arra = mysql_fetch_array($resulta);
                    $AID  = $result_arra['ID'];
                    $Arduino = $result_arra['Arduino'];
?>
<?php
// Check if this Arduino is the one where a button was pressed.  If it is use it
if (isset($_POST["AID"]) == $AID)
{
?>       
<?php
//
// If Arduino is the correct one then check the pins to see which one was selected
//

                    // Get a list of all the pins being used on this Arduino
                    $queryb = "SELECT ID, alistID, pin FROM apin WHERE alistID=$AID AND Active=1 ORDER BY pin DESC";
                        IF ($resultb = mysql_query($queryb))
                        {
                        # The Query was successful
                        $column_countb = mysql_num_rows($resultb);
                            IF ($column_countb != 0 )
                            {
                                for ($column_numb = 0;
                                $column_numb < $column_countb;
                                $column_numb++)
                                    {
                                    $result_arrb = mysql_fetch_array($resultb);
                                    $pinID  = $result_arrb['ID'];
                                    $pin  = $result_arrb['pin'];
?>

<?php
// Check if a PIN has been actioned
if (isset($_POST["LED".$pin]) == "ON")
{
    if ($_POST["LED".$pin] == "ON")
    {
    // Set ON by calling the Arduino using fopen
    $h = @fopen("http://192.168.1.205/?LED".$pin."=ON", "rb");
    }
    else if ($_POST["LED".$pin] == "OFF")
    {
    // Set OFF by calling the Arduino using fopen
    $h = @fopen("http://192.168.1.205/?LED".$pin."=OFF", "rb");
    }
}
?>

<?php



                                }
                            }
                        }
?>

<?php

// END Check if an Arduino is Selected
}
?>


<?php

                   
                }
            }
        }
?>

<?php
?>

<!-- LED2 FORM -->
<!-- <table> -->
<?php

// Get the list of Arduinos
$querya = "SELECT ID, Arduino, aname FROM alist WHERE Active=1 ORDER BY ID DESC";
        IF ($resulta = mysql_query($querya))
        {
        # The Query was successful
        $column_counta = mysql_num_rows($resulta);
       
        // echo "column_counta is $column_counta";
            IF ($column_counta != 0 )
            {
                for ($column_numa = 0;
                $column_numa < $column_counta;
                $column_numa++)
                    {
                    $result_arra = mysql_fetch_array($resulta);
                    $AID  = $result_arra['ID'];
                    $Arduino = $result_arra['Arduino'];
                    $aname = $result_arra['aname'];
?>

<table>
<tr><td colspan="2"><b>
<?php echo "$aname";?>
 </b></td></tr>
       
<?php       
                    $queryb = "SELECT ID, pinName, alistID, pin FROM apin WHERE alistID=$AID AND Active=1 ORDER BY ID DESC";
                        IF ($resultb = mysql_query($queryb))
                        {
                        # The Query was successful
                        $column_countb = mysql_num_rows($resultb);
                            IF ($column_countb != 0 )
                            {
                                for ($column_numb = 0;
                                $column_numb < $column_countb;
                                $column_numb++)
                                    {
                                    $result_arrb = mysql_fetch_array($resultb);
                                    $pinID  = $result_arrb['ID'];
                                    $pinName  = $result_arrb['pinName'];
                                    $pin  = $result_arrb['pin'];
?>
<tr><td colspan="2"><font size="4" color="yellow">Turn on and off <?php echo $pinName;?><!!!!/font></H4></td></tr>
<tr><td>   
<form action="led2_sql.php" method="post">
<input type="hidden" name="AID" value="<?php echo$AID;?>">
<input type="hidden" name="LED<?php echo $pin;?>" value="ON">
<input type="submit" name="submit" value="ON!!">
</form>
</td><td>
<form action="led2_sql.php" method="post">
<input type="hidden" name="AID" value="<?php echo$AID;?>">
<input type="hidden" name="LED<?php echo $pin;?>" value="OFF">
<input type="submit" name="submit" value="OFF!!">
</form>
</td></tr>

<?php



                                }
                            }
                        }
?>
</table>
<?php

                   
                }
            }
        }
?>
</body>
</html>