Using MySQLConnector with Ethenet Shield 2

Projects General Guidance
27

brandonm222:
But why? I get it I'll be wasting memory but in all reality I don't require that much becuase there will only be 1 or maybe 2 at most connected to it.

I think the point is that you will constantly pinging (requesting) the data...

its not so much the data your passing (although def. is a factor to be considered).. its that your constantly 'calling' asking if someone/something there.... hanging up and calling right again asking if someone/something is there again..

*rinse & repeat

Its like your own self inflicted DDoS attack! lol

not very efficient. :slight_smile:

what you want is a MQTT server/broker of some kind/flavor (There are many on-line and free ones.. or you can host your own using a Raspberry Pi and something like Mosquitto MQTT server installed)

something that has PUSH notification (and not a polling/REQUEST) type of protocol

then you make some sort of central 'admin page' that updates the MQTT server/broker and all devices that are subscribed to that event/value (ie: they are subscribed to the living room light status event/value) will get a PUSH notification if the status of that value changes.

If you do want to learn how to make an Arduino send some data to a PHP script that is logged to a database, I have some example of code to:

set up an example database with a few test fields (columns)
2 php scripts (although with little effort could be combined into one I suppose) that handle saving data received from an Arduino to a database and also retrieve requested data/values from a database (MySQL)
and some example sketches that you can use for sending/saving data to a DB and requesting data from a DB, and you can test/run this all from a (free) local install of WAMP Server.

this is more or less a generic starting point... you will need to edit some things, like:

IP address for your network/ip range
right now there is now parsing of the returned data/response.. just outputs it all to serial monitor, so if you are expecting a 1 to turn on the led/pin..etc.. you need to add that portion to handle the data response as you see fit.

Package contains:
1 x db set-up script
2 x php files for saving send data and retrieving requested data (values)
2 x arduino sketches for sending/saving data and requesting data

All files:
http://dmstudios.net/misc/Arduino_Save_Retrieve_Database/Arduino_Save_Retrieve_Database.zip

MySQL table setup:
http://dmstudios.net/misc/Arduino_Save_Retrieve_Database/database_setup.txt

Insert Data PHP: (rename to .php instead of .txt)
http://dmstudios.net/misc/Arduino_Save_Retrieve_Database/insert_data2.txt

Retrieve Data PHP: (rename to .php instead of .txt)
http://dmstudios.net/misc/Arduino_Save_Retrieve_Database/get_data2.txt

Send/Log Data Sketch:
http://dmstudios.net/misc/Arduino_Save_Retrieve_Database/WAMP_server_logging_v1.ino

Request/Retrieve Data Sketch:
http://dmstudios.net/misc/Arduino_Save_Retrieve_Database/WAMP_server_requesting_v1.ino

The general concept is:

Saving/Logging Data:

  • Arduino requests specific .php script (see sketch and matching .php file)

  • Each 'adruino' has a device name (ie: LR_temp1 = living room temp 1 sensor for example) (which is sent over via query string parameter)

  • Each 'submission' (logging) of data checks to see if 'device name' is in the database.. if not, it makes a new entry with this device's IP address, timestamp, device name, and (sensor) value it needs to save/log. It the device name -is- already in the database, then it just updates that record with the new value (and ip and updated timestamp)

  • logging/saving doesnt have to be done through an arduino..

Requesting/Retrieving Data:

  • Arduino requests specific .php script (see sketch and matching .php file)
  • Arduino sends along its device name and list of query parameters WITHOUT values as its list of requested data.
  • PHP script checks database for device name.. if found it will loop through all query params (which should match the database columns you want data from) and get the matching data for the device name.

so if you just want to see what is in column/field value1... then you just send over the device name and value1.. if you also want the timestamp it was updated.. then you can add time_stamp to query params..

All files could be cleaned up a bit from commented out code.. and notes..

As always if there is a better way to accomplish this, I'm open for discussion. Not saying anything I do is the best way, just that is works for me. :slight_smile: