Koneksi Scan Barcode Dengan Php Mysql
Pemesanan DVD Source Code PHP & Mysql. PAKET DVD PHP dan MYSQL. Program Aplikasi Barcode Scanner; Software Toko Bahan Bangunan. Crystal Report Listing Datagrid Visual Basic 6 Listview to Textbox VB.Net Login Koneksi VB6 dan Mysql Login VB.Net agar Koneksi dengan Mysql Membuat Datagridview Koneksi Mysql. 500+ Koneksi. Using PHP Phalcon, MYSQL, REST, SOLR and MongoDB. Template, create ticket barcode, printing ticket, scanning barcode, on field IT.
In a previous, I demonstrated how to use “Data Pull” to read sensor data over a computer network using an and some sensors ( for example). In this article we will do the opposite: Data Push. This means that we will make the Arduino send data to our server, which stored the results in a database. For this we will use an Apache, MySQL and PHP setup, which can be a full-size web-server or an easy to install “WAMPServer” setup on your desktop or laptop computer. Some basic experience with PHP and web-servers will be helpful when working your way through this article.
Arduino and ENC28J60 Ethernet Shield This article is based on using an Arduino ENC28J60 Ethernet shield, as discussed in our ““, which we will use with one or more DS18B20 digital temperature sensors (you can consider using other sensors as well of course). The goal is to READ the data of these sensors from our Arduino over a network connection. We use the exact same hardware setup as in out. So you don’t need to change anything if you followed that article first. The “ UIPEthernet” Library can be or from Tweaking 4All, but as usual we recommend you check out the Github page first so you have the most recent version.
DOWNLOAD - OneWire Filename: OneWire.zip Version: 2.2 Size: 14.7 KiB Date: March 19, 2014 Using the demo with the standard Arduino Ethernet Shield For the examples we use “ UIPEthernet” which is a fully compatible drop-in library for the standard “Ethernet” library that comes with the Arduino IDE. So you should be able to use it with the standard, W5100 based, Arduino Ethernet controller and standard Ethernet library as well. Just replace the include line ( #include ) with these two lines.
#include #include Wiring your Ethernet Shield The wiring of the Ethernet shield is pretty straight forward, for more details read the “ ” article. Any Ethernet Module uses the GND and +3.3V or +5V pin, after all: we do need power. For my eBay module I used the +5V(it has a voltage regulator onboard to handle that). Below a table, based on a, and, with the additionally needed pins ENC28J60 Pins Pin name UIPEthernet My eBay Module SS 10 10 MOSI (SI) 11 11 MISO (SO) 12 12 SCK 13 13. Retrieving Data remotely In this example, I’ll take the and I’d like to be able to log the temperature data in a database, for which we will use an Apache, MySQL and PHP setup – so called AMP setups (Linux: LAMP, Windows: WAMP, MacOS X: MAMP).
If you have a capable NAS, for example a, then please read our “” article on how to install Apache, MySQL and PHP. A real, full-size, web-server will work as well if you have access to one of course Installation of such an AMP setup on your desktop or laptop PC is relatively simple (especially for MacOS X and Windows):. Windows – WAMP (for example ). Salary slip format in excel with formula. MacOS X – MAMP (for example ).
Linux – LAMP Pulling or Pushing Data We have two options to get our Arduino data, and the best option depends on your purpose: – Pull: An application on our computer (or server) that is going to ask the Arduino for the data. – Push: The Arduino will connect to a server application to push the data to.
In this article we will focus on PUSHING DATA to our computer/server. The alternative, is much easier to implement, since we do not need a “server”. In this article however we will use the more complex Data Push approach. In essence we are going to have the Arduino fill out a web-page form and submit the data to our server.
For this we will use MySQL and PHP. Pushing Data Pushing data can be a little trickier so I will present some possible approaches to do just this. First we would need a “receiving” party, a server like application that is ready and capable of receiving data. The receiving party would need to listen at a certain IP address and IP port. Such an application can be for example MySQL (free database server that can also run on your desktop with setups like ). Since the Arduino is small and loading full-size MySQL (or other) libraries is not an option.
There is however an that can directly access MySQL over Ethernet. The library however is not small, and the memory space limitations of your Arduino could become a problem when realizing that (in our example) OneWire is needed for the sensors, and UIPEthernet is needed for the network as well. In my case this didn’t work as I was already running low on memory with my example sketch, for those interested, feel free to explore this option. It looks very promising! You can also look at this article I found on. Pushing Data through PHP As already hinted before. We could utilize is using HTML forms and PHP, and why not?
Each AMP setup comes with the required elements. Some basic web-server, PHP, HTML and MySQL understanding and experience is going to be very helpful for the next steps! In HTML we can use to create a form which can be submitted through POST or GET. Now POST makes things a little more secure I suppose, but also much more complicated, so we will stick with the GET option. A problem with a form, is that we are a little limited when thinking about an x (unknown) number of temperature sensors. With some PHP trickery we could read more than one sensor at once of course, but we’d like to start this article “simple” under the assumption that we will send just one set of sensor data at a time. The PHP trickery can be found in the final Sketch.
The basic format for such a “submit” of data would be something like this. Data is send as variable pairs (variable1=value1) separated with an ampersand (&) and this is the trick we will (ab)use for submitting data. You will however need, on your PHP server (this can be WAMPServer), a PHP file to receive and process this information and of course a table in MySQL to hold the data. In the example line, this would be the “adddata.php” file.
This file will post the received data into an MySQL table, which of course needs to exits, so let’s start with that. This “adddata.php” file is in this example stored in the “root” of your “WWW” directory. Under for example “WAMPServer”, this directory is called “www”. MySQL Table: Let’s assume we want to store the following values (you can add and modify at a later time):. Unique ID (int),. Event date and time (timestamp) so we know when this was submitted,. Sensor serial number (varchar) so we know which of the sensors reported the temperature,.
Temperature in Celsius (varchar) to keep it simple. The SQL statement to create this “ temperature” table (you can do this in phpMyAdmin for example). CREATE TABLE `test`.
`temperature` ( `id` INT NOT NULL AUTOINCREMENT PRIMARY KEY COMMENT 'unique ID', `event` TIMESTAMP NOT NULL DEFAULT CURRENTTIMESTAMP COMMENT 'Event Date and Time', `sensor` VARCHAR ( 30 ) NOT NULL COMMENT 'Unique ID of the sensor', `celsius` VARCHAR ( 10 ) NOT NULL COMMENT 'Measured Temperature in Celsius', INDEX ( `event`, `sensor` ) ) ENGINE = InnoDB; Whenever we do an INSERT into this table, we only need to provide the sensor serial number and the temperature in Celsius. Values for the ID and date/time will be automatically populated by MySQL. An INSERT example. INSERT INTO test.temperature (sensor,celsius ) VALUES ( '00006a', '21.65' ); The database I’m using here is called “test”, which can be found in any freshly installed MySQL – yours might be called differently of course. The same goes for the table name. Just make sure that you’re using both consistently. Now that we have a table for our data, on to the PHP files.
We will create 3 PHP files, one to submit data and one to retrieve data so we can see what’s going on. Both will be connected to the database, so we will make a third file, that the other 2 files will share, to connect to MySQL. PHP: MySQL Connection This file has only one purpose: setup a connection from PHP to MySQL which will be used for both submitting and retrieving data. Save it as “ dbconnect.php” in the www directory you’d like to use. PHP: Show current table content I’ll keep this file very simple, it’s just to illustrate that it works, although I could not help myself adding some CSS to make a simple table still look nice.
Save this file as “ reviewdata.php” in your www directory, and if you’d like you can already test it by entering the address of your web-server followed by “reviewdata.php” (the table will be empty of course, unless you did a few INSERTs to add some dummy data, like the example displayed earlier). For example: (replace “yourserver” with the name or IP address of our server, or with “localhost” if the AMP setup is running on the same computer).
You can test submitting data as well. Enter the address of your server in your web-browser, followed by “adddata.php”, followed by some values, for example: After pressing ENTER, the adddata.php file will be opened, data will be read and submitted to the database, and the reviewdata.php file will appear with a table with your new data in it. The result will look something like this.
Arduino to MySQL with PHP – Example Table Keep in mind that this example is far from secure, you should add your own security if needed! Now that we’ve dealt with the “server” side of things, on to the Arduino side. Testing the Arduino Ethernet Code So we have assembled our Arduino-Ethernet-Sensor combo and created the needed PHP documents and MySQL table. Let’s first look at a simple Sketch that will test our setup and give us a better understanding of how this works – we will not yet read the sensor data, but you can leave the sensors connected. In the example sketch below, we will connect to our “web-server” and submit some dummy data every 5 seconds.
You can open/refresh the “retrievedata.php” file in your browser to follow the progress, the same way we did before. The output of “retrievedata.php” should look something like this, refresh the page every 5 seconds to monitor progress (see “Date and Time” column to observe the every 5 seconds update). Monitoring the MySQL table being filled by your Arduino Walking through the code Network basics: Line 1: Include the needed Ethernet Library (UIPEthernet). Line 6: Define the MAC address of your Ethernet shield, which must be unique in your network (take the given value, I’m pretty sure it will be unique). Line 9: Define the variable “client” for holding our connection. Line 10: The name or IP address or hostname of the web-server which we use for dumping our data. So the server where we find adddata.php.
This should just be the server name, so no extra info like path and filename! We will handle that part later. Do NOT use “localhost” – it won’t work.
Keep in mind that the IP address or hostname (for example “www.google.com”) is to be entered without the “prefix!!! Line 11: For this test we want to dump our data every 5 seconds (5000). Setup: We’re setting up the serial port so we can see debug info in the Arduino IDE (menu “ Tools“ “ Serial Monitor” and make sure it’s set to “ 9600 baud“). Next we’re initializing our Ethernet Shield with something we might not have done before: We’re using DHCP!
See how easy this works in UIPEthernet? Just provide the Mac Address and everything will be taken care of. The reason why we can use DHCP in this example is simple: we really don’t care for the IP Address, we will be pushing the data to the server, so nobody needs to know the IP Address, as long as it’s a good IP address in your network. DHCP will handle this just fine and automatically. After that we will dump a welcome message and some network info to the serial port so we can see in the Arduino IDE that we did get connected. Loop Line 33 will try to open a connection and if it succeeds lines 36 to 47 will do a HTTP request.
This is where a little explanation might be welcome. In line 36, we will start a GET request. The basic format of the URL to the adddata.php file in this example is: Keep in mind that this is just an example, adapt the URL to your setup. Keep in mind that if you’re using WAMPServer (or other local AMP variant) and you used to type “in your browser, that “localhost” will not work for the Arduino. Localhost is the dummy hostname for your computer, when accessed from your computer!
For the Arduino “localhost” would mean itself, the Arduino, and not the needed web-server. The first part of the URL http: //192.168.1.100 is something we already defined ( line 10) and used ( line 33), so we do not need that part anymore. The second part is the requested “adddata.php” file and it’s path ( /testserver /arduinotemperatures /adddata.php ) followed by a question mark. The question mark is needed to indicate the start of the GET variables we discussed earlier.
Lines 37 to 41 will now add the variables “serial” and “temperature” separated by an ampersand. So these lines build serial=00006X & temperature= 12.3 and add it to the output.
Lines 42 to 48 complete the HTTP GET request and finally closes the request. Maximum number of variables we can pass The URL of our “adddata.php” PHP script has limitations.
Please note that PHP setups will have a default limit of 512 characters for GET parameters. Although bad practice, most browsers (including IE) supports URLs up to around 2000 characters, while Apache has a default of 8000. This brings us to a max of about 8 sensors for which data can be passed this way – but your milage may vary, depending on your PHP setup. Updating the Arduino Sketch for (Multiple) Sensors Readings Now that we have prepared our PHP code to handle multiple sensors, time to add the actual sensor reading code and making it work for multiple sensors. Below and example of code that worked just fine for me, you’ll recognize the example code we used just now and I’ve added the sensor readings to it.
If you’ve played with the temperature sensors before then the “ TemperaturesToGetVariables” will look familiar. It basically wraps the code used in our “ ” article, I just removed some of the unused tasks like printing Fahrenheit etc. I also changed the interval to 10 seconds (10,000) and in a real world scenario 60 seconds or even more might not be a bad idea unless you’re interested in filling your database in no-time The code below is an updated version with some modifications as suggested by Norbert Truchsess (developer of UIPEthernet – thank you Norbert!!), which makes sure that other Ethernet traffic is handles as well (Ethernet.maintain). I did also make the debug serial output optional – simply define DEBUG (#define DEBUG) to enable it, or remove that line to disable it. Mostly because in a production environment we’d never really use serial output anyway. With the help of Frank, I’ve also added some code to make sure that during long term use, failing network connections get reset. The final code and PHP files can be downloaded or just use copy and paste.
“retrievedata.php” output with multiple sensors Please note that, when observing the times in the “Date and Time” column, the delay between two readings is the sum of the “interval” value (10,000 = 10 seconds) and the sum of all the time it took to read the sensors and send the data to the server. In our example, with 2 sensors: 10 second delay + 2x (750ms) ≈ 12 seconds. Keep this in mind if you’d like to have a perfect 10 seconds interval. You might even need a more advanced timing mechanism if you prefer exact timing – the Arduino does not have a realtime clock so either you have to register a starting time and/or add additional hardware to get a real clock (see also: ). Oh, and how did you work out the interval with millis instead of delay in the end?
I tried editing the sketch by changing it to the way in these comments, but it would not work (error: pointer to a function used in arithmetic). Maybe you can update the code?
I think millis is indeed better, because with delay, the whole system is freezed. You can notice that because the Arduino won’t respond to pings.
Also delay cannot be used directly for longer delays (minutes instead of msecs). Value that the ‘int interval’ can hold is something like 32k, which translates to 30 seconds. I searched a while for this, and found a workaround, by using: delay(5UL.
60UL. 1000UL). That is translated to 5Min. The ‘UL’ stands for ‘usigned long’. But as i said, this is a workaround, because working with millis would be better.
Hi Frank, Well, I’ve modified the code (and uploaded the new version as well) which includes Norbert’s suggestions (Thanks again Norbert! So far it seems to be working very well. However; I haven’t had it running very long. So far it works very well for the past 4 hours. I have set the interval to 300,000 (5 minutes) in line 13 the code that I use. I left the code in the example (line 13) at 10000 (10 seconds) to stay consistent with the explanation.
Give it a try and please let me know if you run into any issues. Maybe traffic on my network is higher? I’m doing some heavy duty downloads at night? I also use my laptop as a power source (changed that with the current test to a separate power supply) – My laptop does not sleep when not used, just the screen blanks, so I don’t expect that to be an issue.
I used it mostly to log serial dumps. As for the Watchdog implementation: I’ve read some complaints about an alternative method, but had not seen this one yet. Not sure how much memory it would take to include this AVR function. I’ll give it a try in my next test (if needed). Resetting the Arduino is for me a method that might work, but kind-a feels like a work around for a problem that could be solved in a cleaner way – wouldn’t want to generate extra network traffic each time it restarts to get an IP address from the DHCP for example.
Not to mention other applications where we’d like the Arduino to stay on all the time and resetting in between is not an option. Let’s see how UIPEthernet 1.5.5 performs before resorting to this. For some reason after a while the Arduino can no longer setup a connection.
I wonder if this is server or client based? I’ll keep looking - Connected Sensor: 1 Serial: 28 da ca 27 05 00 00 49 Temperature: 23.69 C - connection failed!?!? - connection failed!?!? - connection failed!?!? - connection failed!?!?
I’ve noticed that “Arduino freezes” is maybe not the proper wording to describe this issue. The Arduino keeps chugging along, but seems to be unable to setup a connection. `client.connect(server, 80)` seems to fail after a while.
I’m trying to see if I can find any server log files which might suggest connection issues (refuse) or not I’ll also enable logging on my router, just to see if I can notice anything there. I agree: not a permanent solution. I still have to do some work with the PowerOff and PowerOn statements, maybe that works. Not entirely to my satisfaction I might add. You could most certainly try another library – I think the main issue is that the EtherShield vs the ENC28J60.
The Ethershield (according from what I understand from Norberts comments) is more “intelligent” and more expensive. The lacking intelligence is something a library needs to compensate for, and I can only imagine the increased complexity, hans. So are we both Dutch? And there is a Dutch version of this page (upper right corner, you’ll find a link to the same page in Dutch) and here we are speaking English? That’s actually funny However; I do suppose the larger audience can be found with the English speaking visitors. (you’ll see that has a much higher rating that ) Anyhow, thanks for the info. I didn’t know that about the HR-ketels, one learns something new every day.
Here people use hot-air to heat the house, no radiators not sure which is more efficient, but I do miss radiators I have been thinking about some sensors throughout the house as well, and some to monitor the temperature of my fridge and freezer. Groetjes uit Amerika (ik woon hier nu al zo’n 10 jaar)! Hello, I am testing the codes php and mysql database and all correct. But I would like to create and update a single record by adddata.php, what would have to change in adddata.php to update a single line and not create me new? New Online: “INSERT INTO test.temperature (sensor, celsius ) VALUES (‘ ” $ GET “serial “.
“ ‘, ‘ ” $ GET “Temperature.”. “ ‘)”; Update same line? “UPDATE INTO test.temperature (sensor, celsius ) VALUES (‘ ” $ GET “serial “. “ ‘, ‘ ” $ GET “Temperature.”.
“ ‘)”; It does not work. Thank you very much and best regards. Frank is right I just noticed that my reply was nuked because of the SQL statement I had put in there. You will also need to remove the “ID” field from the table, otherwise MySQL cannot update the record – it would need to know the unique ID, which you would not know.
1) Remove the field ID from the table 2) Rewrite your query This will result in only one record in the table for each sensor! Query should look something like: UPDATE test.temperature SET celsius=‘” $ GET “Temperature.”. “‘ WHERE sensor=‘” $ GET “serial”. Hi Hans, I am using parts of this sketch for posting stuff from Arduino to MySQL (i am now busy editing this sketch: so it puts the number it displays on the screen in my MySQL database). However i noticed something in your sketch (the one belonging to this article). On line #58 you check for a connection (with ‘!client’), and on line #68 again, this time with ‘client’. Shouldn’t one check be enough?
I think some optimalisation can be done here;)!client = TRUE = no connection!client = FALSE = there is a connection client = TRUE = there is a connection client = FALSE = there is no connection. I think maybe the init.enc28j60 can be put in the ‘if’ case when there is no connection, and so one of the checks can be removed perhaps? Also, about the stability of the sketches (where we discussed about earlier), i read that someone had a problem with a different sketch, which eventually turned out to be the buffer of the ENC28J60 being over filled. He said something like: “The solution is that after you do a request, wait some time (i use 100ms) so the response of the server is in the buffer. And after that do a ‘client.flush’ so the buffer is being emptied again.” Could be worth a try, because you don’t have anything like that in your code right now I think i will buy a W5100 board from eBay, they can be found for $7 and i think is easier to use, and more stable. I found something else today (unexpected, i came by a webpage and saw this mentioned). Someone (i can’t find the page anymore where i read it).
The ENC28j60 uses quite some power (it also gets quite warm). In my case i power it with 3.3V, because my ENC28j60 module has no converter onboard. The person mentioned that the onboard 3.3V is supplied by the FTDI chip, which can only supply a limited amount of mA. The case he was describing was that the current drawn by the ENC28j60 was too high for the FTDI chip to supply, and therefore the sketch would sometimes freeze because of the ethernet module stalling because a lack of power.
He adviced to power the ENC28j60 with 3.3V generated by an external DC-DC converter that creates 3.3V out of 5V or 12V. You were powering it by 5V i thought? So it is not the issue in your case i think. But it’s worth mentioning! Sources: (mentions 50mA @ 3.3V) (ENC28j60 datasheet, mentions 160mA as typical current, 180mA max). Nice:) Mine is coming from China, so it will take some time.
For now the ENC28j60 seems comfortable with this sketch. The logger is now working for 10days straight (without failure) since i plugged it in. In the meantime i have been working on some nice graphing. You have created a great sketch, without your time and effort i wouldn’t be able to create something like this myself.
In favour of this, as a sign of appreciation i have decided to share my code for the graphs. You can find it here: It is based on the shiny Highcharts graphing API. Change the MySQL username/password, the sensor ID’s, and save it to a.php file. You also need the jquery library Folder structure should look like this: / adddata.php (the file from your sketch) graph.php (my code from the link above) /js/highcharts.js (folder with jquery stuff, download the package from ) I hope you can use the code. I find it very useful because of the zooming functionality, and the ability to hide/show sensors from the graph. Hans, sketch is running for a while now.
No problems so far. However it is not a fair comparison, as i had to power off the Arduino a few times (turned everything off because i went on vacation, and i hate idle power consumption) and also because my power company came to install a so called ‘Smart Meter’. I have written a blog about reading the data from this meter in my blog: with a little bit of help from the forum users i was able to readout the meter with my self made Arduino sketch.
Quite proud of myself haha. Setup uses a W5100, works fine. Much more stable than those ENC28J60 thingies. But back to this temperature measuring sketch. What is in your eyes the most simple way to get a temperature in the database that is the result of sensor1 MINUS sensor2? I would like to know this subtracted value to monitor the efficiency of my centralheating-kettle.
Membuat Barcode Dengan Php
(sensor1 = outgoing temperature, sensor 2 = water returning to kettle, higher difference = better). I am now showing it on a PHP page by just subtracting two variables that contain the newest values for sensor1 & 2, but plotting some historical data would be nice. Maybe you have some good idea of doing this;) Frank. Excellent work on the “Smart Meter” (and in Dutch too!
Out here in one of the rural areas of the US, “smart meters” are not used yet but I’ll keep you’re work in mind in case we get them here too. Idle power consumption when on vacation is something that irritates me too, so I can relate! The simplest way to get the difference in the database, hmm.
If both values would have been submitted at the same time in one single record then that would be easy to do with a trigger on the table. But that’s not what we’re looking. Option 1: I suppose a global variable, something like “PrevTemp”, where you store the temperature after reading it, and when writing the next value store the difference in a separate table? I guess the difficulty would be to determine which sensor is IN and which one is OUT.
Option 2: I assume that at some point you know which serial number goes with what, and that the submit time of both sensors are very close (ie. Only a few seconds apart in the time field). In that case you might be able to use an approach where everything runs on the database (the subtraction). A trigger on your received temperatures table (afterinsert) could: – see if there is a record of the other sensor, with a similar time stamp (say with a maximum of 2 minutes difference) – if so: calculate the difference and store it in a different table Working with triggers at first can be a little daunting, and the tool you use for your MySQL database needs proper support for that (I know phpMyAdmin isn’t the greatest when it comes to triggers). Of course: you PHP code for the graph or table display could calculate this on the fly as well. Hi, It is the most comprehensive and detailed tutorial i have ever seen I am getting some problems.
In my code when I use client.print( “GET /testserver/arduinotemperatures/adddata.php?”); I am not able to send data to my site, but when i use client.print( ” “); I am able to post data to my site. Second issue is, in my database I am getting value like this 12.3HTTP/1.1 http 1.1 is getting appended in my data field.
If i delete http/1.1 command, Host: is appended to my data. If I dont use following lines, again i am not able to post data. I dont know php so my friend is helping me out. I think we are doing something wrong. Client.println( ” HTTP/1.1″); client.print( “Host: ” ); client.println(server) client.println( “Connection: close” ); Thank you Suyog. Thanks you for your instruction on ENC28J60, I have the problem that still can not found reason.
Please advise, The test was run on localhost, using Apserv, it works. But when I am testing with real internet site, the database has not put inside MySQL even the connection to server is connected. And testing with communication with server is fine. I also tested the GET php script, it works fine by below manual entry and DATA has been in the database. I have change chmod of php and its directories script to 755 already.
Great examples and library! I m using arduino nano connected to the ethernet module, as a slave unit connected to a master Arduino Mega which sends the parameters for the GET request via I2C, using wire.h. As soon as i receive the parameters, i send them with the GET request and echo serial.print for debug.
It shows ok in debug, but in my webserver I dont receive the first GET request. So no action in the mysql database. After receiving a second parameter from Mega Master arduino, the first GET request is gotten in my apache webserver, and although serial print shows ok the second parameter is not received, only the previous, the first. So, it seems that data is being kept in a sort of buffer? Surely i am making a mistake, thanks for the help!
The ENC Ethernet shield has proven to be not exactly the greatest, and what seems like congestion has been seen before. Make sure you get the latest library. Another thing I have noticed is conflicting gear connected to the Arduino (an example before: an LCD screen and SD-Card reader). I don’t know you exact setup, but these are the 2 things I’d look at first.
If that doesn’t resolve things, I’ve found a “wrong” way of doing it by resetting the ethernet shield. Norbert has updated his excellent library to make that unnecessary though. Worth to toy with hans.
Hi, no problem. Let’s make it better together. I’am not really sure if in the adddata.php file, there should be a “close connection” code as well. Mysqliclose($con); // NEW!!! Mysqli code Maybe not really necessary, because i read somewhere, that if a script closes, the connection is closed as well. But if we want it nice and according the rules, this line of code should be placed in the adddata.php as well.
Next stop for me is, – Graphing, Frank shared a peace of code for Highcharts ( Jul 26, 2014 – 5:30 AM), but can’t get it to work yet – Security, don’t know where to start here hahaha. First some basic access security. If someone has some good advice where to start, let me know. Merry Christmas! Hi Robert, for all correctness, a proper Close would be better indeed. I have to admit though, that in such short scripts, I always rely on the end-of scrip = closes connection.
I have not done anything with the graph yet, but it’s definitely worth looking into (Frank did some great leg work there already indeed). As for security, well, you could work with a few options:. Add a “&secretcode=zyx” part (not secure when others listen into the netwrok traffic).
Use PUT instead of GET (more complex). Only allow particular IP addresses (read IP through PHP) Mac Address is unfortunately harder. Hi Hans, Thanks for the suggestions. Prison break descargar torrent.
Much appreciated! First i’ll try the &secrectcode option, which must be in the PHP file? Is it a simple IF statement? I will add the close connection code to the adddata.php file as well. For the graphing part, for now it’s still a bit difficult.
PHP file has to collect data, put it to an array, then the graphing “engine” and settings, have to produce something. And in the future i would like to get the data through AJAX, so the page does not need to be refreshed all the time for realtime graphing. I have set the bar at a high level for me. Hi Hans, happy new year! No need to be sorry, thanks for the replys!
I’ll try the secretcode option first, i know it’s not that secure, but you have to start somewhere. That code is going to be send from an Android app via 3G to my server.
So not much listening from others i suppose. This is my bar: I’am making a logging device, Arduino Mega, to be put in a racing motorcycle sidecar. This device logs makes lap times with the onboard GPS, Gyro for some forces, RTC-clock, to the onboard SD-card. But in addition it sends the data to my webserver every second. When viewing this data on a webpage on the smartphone along the track, it needs to be realtime updated. So when i implement a graph, I want this graph to update itself without refreshing to whole page.
This graph needs to update/add new data points to the existing graph. This realtime updating thing bites me, and is at the moment the most difficult part. I’ve seen a lot of tutorials but they don’t really work, or are to complicated for me to adjust to my needs/DB. Or they have random generated data in the script, but i use a mySQL DB. Found a very similar or the same code Frank uses,.
But it refreshes the whole page every 5min. I’ve used Flot and take a look at it again. So the quest continues hahaha.
Hi Robert, wow that’s indeed a high bar – but never give up A few things that come to mind: – If you like a realtime update, isn’t the track you’re racing to big for WiFi to remain connected? – AJAX in essence (when using some jQuery stuff) can load a “webpage” in the background and have your JavaScript refresh the chart. – I’m not familiar with Highcharts, but I would assume that it takes JavaScript based data. Through PHP you could generate the JavaScript data array, so using MySQL would not be a problem (I do this for my statistics page as well).
I just looked at their demo, and yes it uses a JavaScript array. Well it’s a big challenge overall. But getting there.
I’am very pleased with what i have accomplished so far. Main reason for Highcharts was that it is smartphone/touchscreen friendly. Especially when you want to be able to zoom in charts on a smartphone. That’s were Flot is not that usable. On pc’s it’s no problem. But for the time being i think i’ll use Flot and your column style on 2 separate pages on my webserver. Than i have something working.
After that, next biggy, is that i want to see where the vehicle is on a track/street. Maybe using Google maps. Haven’t seen much examples of this nice option on the internet though.
Dear Sir; I modify your pushing data program to push 4 analog data to WAMP php text file. It works great thanks. How ever, When I setup My Arduino/ENJ28J60 as web server to control some lights and relay.
And every 10 sec to pushing data to WAMP. By just copy your pushing program as function saendData; It did go through that function with no error. But just did not store to that php file. Do you have any idea? Thank very much in advance if you can help.
Best Regards and Merry X’mas and New Year. Excellent posting Hans.
I’m working my way through what you have taught. I have a 12 sensor network and I’m looking to expand it to 24. Also, I’m looking to add other information collected and managed by the Arduino. I’m concerned because you mention the character limit and for what I’m planning, I’m certain I will go over. Are there any ideas as to how to get around this potential problem? Possibly building different loops that would build and post to maybe different tables?
I’m thinking temperature bank 1, temperature bank 2, Tachometer data, gps data and other things. (All this is for my boat). Any thoughts would be appreciated Michael. Hi Michael, Thank you for the compliment! I can image a few ways to upload large amounts of sensor data, for example: If you’re using an Arduino MEGA, you could consider trying, which requires more memory (Uno will run out of memory very fast). Another method would be using POST instead of GET. This does take a little more effort to program though, but you’d get rid of the link length limitation.
Here you can use multiple fields or for example data in a JSON format. Some example code can be found at, and. HI Thanks for all the good work and hints here. I’m working on a sensordata applikation who will send data to a mysql server.
Now when trying to verify the code its to large!? I have a Leonardi board with 32kB. I don’t see that you all have any problem with that?? Do you have the bigger boards? Sketch uses 29 494 bytes (102%) of program storage space. Maximum is 28 672 bytes.
Global variables use 1 376 bytes (53%) of dynamic memory, leaving 1 184 bytes for local variables. Maximum is 2 560 bytes. I’m planing to have many different sensors in my applikation but at the moment its your code without even changing the ip address. Just want it to work the first time before I start do any changes. I dont understand why less than 30 KB would do this? 32KB in me and 2KB reserved for the loader and still it’s full already at 29 494 bytes?? Do you have any idea about that?
I tried to se if there is a way to clean the memory but no. And I restart the IDE every time. Sketch uses 29 494 bytes (102%) of program storage space.
Maximum is 28 672 bytes. I think I found what the potential culprit be in: “ Less flash memory – the bootloader uses 4 kB of the 32 kB flash (the Uno used 0.5 kB)“ So you’re “loosing” an additional 3.5 kB. Maybe this is needed to support the build-in USB support in the ATmega32u4 microprocessor? To save memory, try removing all serial related statements, or by removing the quotes in front of line #4 (// #define DEBUG) so that DEBUG is defined and all serial stuff is disabled. The compiler should then ignore anything serial related. If you still want to see something, then just remove the Serial.Print lines that you don’t deem needed, and reduce the text for the Serial.Print statements. This is what the compiler say’s Sketch uses 34 080 bytes (6%) of program storage space.
Maximum is 524 288 bytes. Hello, I could make this example work, however I still have some concerns: The temperature table, while hanging from the “test” data base works ok. It is possible to add records either by running the php script (adddata) from the browser or through the client connections performed by Arduino. However, if the table belongs to another (let´s say a new) data base, records can still be inserted as php in-line from the php-myadmin, but not from the browser anymore (of course after making the corresponding tweak to the dbconnect and adddata scripts.
What am I missing?a privilege to write other than the test database? Any clue will be very much appreciated. Regards Mauricio Mauricio.
Hi Hans, his French is not too good also:-) logical google won’t understand it, I hope his english is better. (Tayech Najeh) I like to thank you for the article, it saved a lot off time to sort everything out. I’m monitoring already several months the temperature in- and outside and also the temperature of the heating-side of the c.v. To monitor the behaving of the heater, it’s working perfectly on a Arduino Nano with a ethernetboard pluggen on to it and the data on a qnap server. Also I’ve build with other Nano’s a database to monitor swimming-pools which is working also fine but because not all are on the same network I have to push the data over internet.
So I’ve tested with the dyndns-address with and without; in front (I don’t have a fixed IP-address) and I’ve also opened the SQL port but nothing works. When I’m on the same network I have to set the SQL-address at localhost:PORT The IP-ADDRESS:PORT won’t work eather. I do hope someone can give me a clue. Many thanks, Robert. I use myself and have had very good experiences with it, even with exotic port.
First I would try working just with HTTP, I’m not sure how wel the Arduino would handle HTTPS. I assume you have this scenario: – Several Arduino’s at home for the C.V. At home) – MySQL / Webserver in the same location (ie. At home) – One or more Arduino’s outside for the pools (have to connect to “home” through the Internet) There are 2 ways to connect to MySQL, but I assume you’re using the method used in this article (first option).
The second option is very unlikely to be used, but I’m mentioning it anyway to be complete. If you’re sending data to a PHP page (as in this article): You might have some port forwarding issues. The port needed for regular HTTP would be port 80 and you would need to forward that to your webserver (at home) in your modem/router (at home). Now I would not use port 80, rather pick something else like port 8080 or 81 (which you have to use in the URL used in the Arduino’s at the pool). Just to avoid confusion with regular HTTP traffic. Most modems/routers allow you to map an incoming port (8080 or 81 for example) to a port used internally (port 80). So in your modem you’d see something like this: Port forwarding: incoming port 81 TCP - local port 80 TCP (if you can only choose from a list of Apps: HTTP Server or Webserver).
No special things have to be set with your MySQL, since the webserver (PHP) is talking to it as a “localhost”. (modify line 68 of the sketch accordingly, so if you use port 8080, then line 68 would be if (client.connect(server, 8080)) ) If you’re sending data to MySQL directly (VERY unlikely): Assuming your MySQL server is at home, and your Arduino is connected to the Internet, then you’ll have to do Port Forwarding on your Internet modem/router for Port 3306 (assuming you’re using the default port for MySQL and you allow MySQL remote access), So assume that remote access has not been set in your MySQL database – more details in.
Short version: GRANT ALL PRIVILEGES ON mydb. TO 'user'@'yourremotehost' IDENTIFIED BY 'newpassword'; “ mydb.” refers to all tables in the “mydb” named database.
“‘ user’@’yourremotehost’” refers to the user that has access to the table and yourremotehost. The latter would be an IP address for example. You could also use ‘%’ if all remote connections are allowed. So “ user@’%’” should work no matter what the IP address of the Internet connection of your Arduino is. ‘newpassword’ should be the password you’d like to use.
Thanks for your extended explication Hans, for me not all necessaire, I know my way with port-forwarding. A second forward to the same internal port won’t work but your idea to separate the traffic could be practical so I’ve created a second virtual host on another port i.e. A second internet-host with a different portnumber and from a different map on the server. And a miracle happened, this one worked, now I can use the arduino’s on every network on the world and get the data on any SQL-server on the world.
Second advantage is that I can separate the file who’s putting the data on the SQL-server from the rest. Thanks again, two are knowing more than one. Hi Minu, When looking at then you have 2 options in line 6: #define STATIC 0 // set to 1 to disable DHCP (adjust myip/gwip values below) 1) is using DHCP, your modem/router should have this enabled, otherwise this will not work. STATIC should be 0. 2) using a fixed IP address, and in that case you should define a suitable IP address etc., STATIC should be 1. Another problem I have seen is when users connect their Ethernet straight to their computer – this I cannot recommend, as it would only work with a crossed Ethernet cable and a fixed IP address.
Tricky at times even for the best of us. So the Ethernet should be connected to your modem/router. Does your serial output show an IP address that is in the range of your network? For example, your computer has 192.168.1.x and the serial output show 192.168.1.y (typically the first 3 numbers should match). Seems your WAMP setup might be set to use another port than 80?
Often 8080 and 8100 are being used, can you try those? Also, if these do not work, look in the config files of your WAMP setup. If you’re using WAMPserver, go to httpd.conf file, for example at: C: wamp bin apache apache2.2.21 conf httpd.conf (number might be different – depending on the Apache version with your WAMPserver) go to line where is says “ Listen 80” (or another number) and change it to the number you’d like to use (if needed). Ideally you’d use “80” but yours could be set to a different number. Either take note of it or change it.
Next go to the line where it says “ ServerName localhost: 80” (Servername might be different) accordingly. Next restart WAMP. To avoid a total hernia, I take little brakes in between, like now see what I can do for you First thing I’d do, to debug, is: change this part: client.print( 'GET /adddata.php?' ); client.print('humidity='); client.print( DHT.humidity ); client.print('&&'); client.print('temperature='); client.print( DHT.temperature ); client.println( ' HTTP/1.1'); so it dumps each line to serial as well – so we can see the URL being build properly. So for each line: client.print( 'GET /adddata.php?'
); Serial.print( 'GET /adddata.php?' ); client.print('humidity='); Serial.print('humidity='); client.print( DHT.humidity ); Serial.print( DHT.humidity ). Just to make sure this all looks good. It should look similar to your manual URL. Seems to work alright hmm weird. The same link manual works and the Arduino actually connects to the server as well.
I think I found the problem. Can you try: char server = 'vintagegreens.byethost7.com'; instead of: char server = 'p.s. You might want to add a secret code to the URL so people will not start toying with your database, ie. Add “&secretcode=” and in PHP catch if “secretcode” exists and matches (use $GET“secretcode”) and only then submit data to your database.
Actually, “$MyHostname = “localhost”;” is correct in most cases. It’s pretty common dat MySQL and Apache are running on the same machine, therefor “localhost” is often correct. Now, doing the link manually in browser works just fine, so that would confirm that there is nothing wrong with the PHP code, so the problem is to be found elsewhere. Just to make sure, the following link would actually add a record to your MySQL database, right?
Great!!as for the humidity problem, would you mind showing the sql statement you used to create the database? The PHP files are ok as far as i can tell.you should use something like: CREATE TABLE `test`.`temperature` ( `id` INT NOT NULL AUTOINCREMENT PRIMARY KEY COMMENT ‘unique ID’, `event` TIMESTAMP NOT NULL DEFAULT CURRENTTIMESTAMP COMMENT ‘Event Date and Time’, `Humidity` VARCHAR( 30 ) NOT NULL COMMENT ‘humidity in%’, `Temperature` VARCHAR( 10 ) NOT NULL COMMENT ‘Measured Temperature in Celsius’, INDEX ( `event` ) ) ENGINE = InnoDB; make sure you get the spelling right in the PHP files joel. Hi Mia, since submitting manually works, I’d assume that your PHP code is OK. As for your sketch: I see that you’re including mysql.h (not sure if that causes a conflict). Second thing I notice is that your code is a little all over the place. For example lines: client.println( 'Host: localhost' ); client.println( 'Content-Type: application/x-www-form-urlencoded' ); Ehm, I have not used it in this way: client.println( ' HTTP/1.1'); client.println( 'Host: 192.168.1.100' ); Localhost would be your Arduino in this line and we’d need the host we connect to, not localhost.
I am trying to set up a library for books in my work place. I have the MySQL database set up, with the data and everything. Now what i am trying to do is have the ability for someone to take their phone, scan a QR code for a certain book, and it would check out the book. I have a field in the database that states if the book was checked out, and another stating check out date. How would i access my database externally through the internet and QR codes, and have the QR code change data in specific fields of my database, thanks. I used phpMyAdmin to set up the database, and my webhost is iPage.com.
2 Answers
As I already noted in a comment you question is simplar to the question Tranfer QR Code Into a MySql Database. But in your question are more points unclear: So far I did some research some guy from the support of iPage.com told me that php is supported by your webhoster in all packages.
So I will give your some hints how you can implement that with php:
For the database access use prepared statements and PDO. Here is a good tutorial about PDO.
For clean and simple urls for some kind of REST API use mod_rewrite with .htaccess:
Basically you have to check if mod rewrite is enabled. In your case you can skip this, but in general you have to check if you have a AllowOverride All statment in your directory directive.
Just put this code in your .htaccess file:
So you can access the qr code in yourphpfile.php with this simple variable access:
For the scanning part you can use the ZXing Barcode Scanner app:
If the user has the ZXing installed the brower will open the app automatically. If not the user will get a simple site which gives him or her a hint how to install the app.
You just have to link to the page the resulting value of the qr code will be submitted to the url http://example.com/seachbook/QR-code-content.
See also the full explanation on the ZXing documentation.
In general that certainly is possible. Actually every QRCode / barcode stores information. But usually the size of that information is pretty small. Typically only references like URLs or numbers (IDs) are stored and the 'real' data is retrieved from a database using those references. But strictly speaking that is a form of storing information in a QRCode / barcode.
Looking closely we see that the data is stored in the code itself, not inside a database if we are talking about that small amount of data. Certainly that data can be stored inside a database when the code is read, all you need is a trivial piece of software. But that makes little sense in most cases, since the data already is stored. If you want to use a database to combine data from different sources, so if you only want to use codes as a means of transport, then reading the codes is just a replacement for typing the information in with a keyboard. So no magic here, nothing code specific you have to consider.
here is the php script to generate QR directly in db