I have spent several days searching the web for an example but only find examples using the serial port.
In short I want to build an UNO w/Eth Shield system that collects voltages from my analog inputs at regular intervals, then serve them and store them in a database on a client on the internet.
I'm struggling with the application that runs with mySQL to retrieve and write the data into the db. I have the database and tables configured. I have the hardware built/coded too, and will be receiving the Ethernet Shield this week.
I need help understanding how to run a script in mySQL to call the server(UNO) and get the data, time stamp it, and load it into the database.
I've installed the XAMPP package for mySQL on my Windows box.
Is there a simple way to initiate calls to my Arduino over the Ethernet and listen/respond?
Once I get comfortable with this, can I use python (preferred) or Pearl scripts to automate the process?
Will this work on a windows box or should I set up a Linux system?
An example would be appreciated if you know of one.
Ken,
Thanks so much for the quick reply... If you could not tell from my question, I am trying to get up to speed on the database side of things.... Where does this code reside? Is it PHP code? Something I need to study up on... To be more explicit in my question, does the PHP code run on/call from the client to the server(Uno), then the Uno serves the data and the PHP stuffs it in the database?
Thanks.
-Marc
mbiundo:
Ken,
Thanks so much for the quick reply... If you could not tell from my question, I am trying to get up to speed on the database side of things.... Where does this code reside? Is it PHP code? Something I need to study up on... To be more explicit in my question, does the PHP code run on/call from the client to the server(Uno), then the Uno serves the data and the PHP stuffs it in the database?
Thanks.
-Marc
Yep. When you create a page for your page for your server, you can build it in exactly the same way as you would with ANY HTML document BUT save it with a .php extention. (it's the .php that tells the server that it should be "preprocessed");
Within your web page, any serverside scripting you want to run, you simply place between an opening <?php** and then close with **?>
All variables within php start with a $ symbol. There are loads of really useful variables available that are created for you by the server. $_GET is an array of every tupple in the GET query string. So if you open the page myphppage.php?shoesize=7&colour=blue You can then access those values from within your php.
$_GET['shoesize'] will hold the value 7
$_GET['colour''] will hold the string "blue"
but before you attempt to assign those values to anything it's best to check they were actually passed in the first place. For this you'll use the isset function. like so
if (isset($_GET['shoesize']))
$shosize=$_GET['shoesize'];
else
$shosize="somethingElse";
Have you setup the database and user in mySql yet?
Your response is fantastic. However, I'm going to really show my ignorance here... I want to start from a very basic/simple approach. I.E. The Uno sits on my network and It is collecting voltages(analog signals via the A/D converter.....) from some hardware I designed. A Windows box is remote on the web. Do I need to use PHP as you explain... I'm struggling with understanding how to simply call the Uno from a "script" on the windows networked box that will then insert the data(with a time stamp) into mySQL db that also resides on this remote windows box. I have the database set up, with the tables I need. I will study the user features now.
I can see you are very well versed in this subject matter, sorry in advance If I am not articulating well....
Well I'd do it the other way around. It's very difficult to get a web server to run procedures without provocation. Web servers respond to requests.
Look at the address in your address bar at this very moment. You'll see that the URL contains a question mark at the end of the page name. For the server this marks the end of the page name Everything after the question mark is called the queryString. It follows the form of fieldname=fieldvalue. So for our current page on this forum you'll see topic=283117.0
This is what the PHP code can use. In the case of this forum page it can look up the topic mentioned and then present it to us on our page.
So what I propose is on YOUR server you could have a page called (for arguments sake) update.php Your arduino can request the page update.php?somereading=106&someotherreading=29 (naturally it will replace the 106 and 29 with the actual values it has found.)
As the server retrieves the page update.php, the code within it will be executed. This code will then take the values that your arduino has just passed and store them in the database. (along with the current time)
The arduino will then recieve the content of the page, (and may simply discard it if it doesn't contain anything worth it's while).
A short while later, your arduino can make another reading of any values of interest and once again open the page update.php but this time with the new values it has found. Your server will then take these new values and once again store them on the database.
In the meantime, the server could also have another page (ideally something like index.php). When YOU as a genuin visitor, visit this page, the php code within it can retrieve the data from the database and present it to you in any form you like.
Much clearer! I have to study it to make sure I get it...Let me experiment with it....I will learn more, then I can implement it....Excellent insight and examples!
Thanks!
-Marc
The beauty is that you can debug your php code completely separately from the arduino code, simply by using a browser to call the same page with some example query string, to emulate the anticipated call that would normally be made by the arduino.
Have you setup the database to receive your data yet?
Yes, I have set up the database. I have never used mysql... But have used MSAccess. Here is what my table looks like if you are interested...I want to get this part working well. Next step. It is my understanding there is a very usefull application called RRDTool, or Round Robin Tool. In short, the RRDTool is optimized for storing and graphing data of the type I am collecting... I figured I would get it working with mySQL first then see about exporting or going strait into RRDTool... I want to take small steps since I am new to working with large data sets like this...
My database is called mcsoh. The sql I ran to create the table is listed below.
When it comes to creating the user accounts. Every user consists of two parts. The name AND where they are accessing the database from.
Although '%' is supposedly logging in from anywhere, it implicitly excludes access from the localhost. This can be a cause of much frustration.
So you actually want to create two users with identical names but for access from % (anywhere) AND the other for access from 'localhost'
So following your example I notice that the database name you have chosen is mcsoh. With this in mind this is what you want to setup those two users
CREATE USER 'someName'@'localhost' IDENTIFIED BY 'somePassword'
CREATE USER 'someName'@'%' IDENTIFIED BY 'somePassword'
GRANT SELECT, INSERT, UPDATE, DELETE ON `mcsoh`.* TO 'someName'@'localhost'
GRANT SELECT, INSERT, UPDATE, DELETE ON `mcsoh`.* TO 'someName'@'%'
Naturally you can remove the option for DELETE if you don't need your scripts to be able to delete records but I'd leave it in for now.
Edit: Just noticed a couple of stray $ symbols in there. Now removed.