In an earlier post, I showed how to link a QR Code to a map by using PHP _GET. This allowed me to pass variables to a script and have the script run in realtime to update the page. I am going to do the same thing in this post, but the data I pass will be used in a SQL statement to retrieve data from the database.
I think this would be a great tool for a facility to use to inventory their equipment. Lets say I have a swamp cooler on the roof of my building — I live in New Mexico. I am on the roof and I need to know something about this particular unit. I can scan a QR Code on the side of the unit, it will then pull up a website that displays everything about this unit: The manufacturer, when it was installed, when it was last serviced, what was done to this unit during the last service, when it is beyond its useful life. On the same page, I could have a form that allows me to update the data. I can enter todays date as the new last serviced date and put a description of what I did – changed the pad and oiled motor. The next time the code was scanned, this data would be available.
Since I have a room database from Revit already setup, I am going to show how to connect a QR Code to this database to retrieve the room properties. To modify this to work on any other object, just change the database connection.
I wrote a PHP script called get.php. This script selects all the data from a rooms table in a MySQL database where the room number is from the QR Code: SELECT * FROM rooms WHERE number = $_GET[roomchosen].
_GET[roomchosen] is the PHP call to pull the data from the URL that was sent via the QR Code. Here is he full code:
Now I just need a QR Code that has the URL to the script and sends the room number: http://room.educationalfacilityplanning.com/get.php?roomchosen=3. This URL will select room 3. I have 6 rooms in this database, so feel free to change the URL to any number 1-6. If you don’t know where to make a QR Code just google “QR Code Generator” or go to http://www.qrstuff.com/.
Here is my QR Code for room 3.
To link to any database, you can pass a part or a full SQL statement through a URL using PHP _GET. Assign your swamp cooler and id=12345. Create a QR Code with the url http://YourDomain.com/swampCoolerGet.php?id=12345. In your script have a statement: SELECT * FROM swampcoolers WHERE id = $_GET[id].
Don’t forget to add a form for updating the data to make this perfect for facility management.
Architecture and Planning 
What if you need the QR code to login with username and password to access the database, say an https site?
If you don’t mind people being able to see the username and password you can just send it. But I’m guessing you mind and that’s why you’re asking how to send it. I think $_POST hides the data it sends but haven’t thought about how to send it without someone typing it in a form. Off the top of my head…maybe direct QR Code to a page with a form in a hidden html div tag and values set. A visible button to click to login.
Thank you. This is the trick we face. These are interesting suggestions. We looked at having the user log-in based on their device IP (UDID or similar), since most access our site from mobile device, but that apparently can be faked as well. As I explore these options, if you come up with other suggestions, I appreciate you posting them.
Would the PHP _GET script still work if they had to login first to the site? Say a format like such: https://somewebsite.com/swampCoolerGet.php?id=12345.. Somewebsite.com would prompt user to enter username & password then the scripts would run?
You could have the QR code link to a login form at the URL with the id passed, capture the id in a field and when they login the username, password, and id are sent to the next handler and the results of the query show up. It just puts a step between QR and results. How secure do you need everything to be? If you could use one password for the QR login, the user wouldn’t need to know it since the QR would log you in(set permissions for that user in the database to read only) then you could send username, password and id in the QR code but make the password something long and crazy and then use the script to select a real password from the long crazy text. So a password: aghytbeubdju would be sent public but your code would take that value and parse it so the password sent to the database is: ayebj – a subset of the long one and the user won’t know.
Oi meu nome é Lucas, sou um estudante de um curso técnico no Brasil ( em São Paulo ). Estou com algumas dificuldades para fazer com que o QRCode entre automaticamente em um cadastro (NetBeans/SQL) e gostaria muito que pode-se me ajudar a concluir essa tarefa, porque ela me ajudaria a terminar algo maior, meu trabalho de conclusão de curso. Caso queira me ajudar entre em contato comigo por email e te contarei sobre o meu projeto e como você poderia me ajudar.
Agradeço desde já, Lucas Antunes.
Luke
Posso tentar o meu melhor para ajudar. email mim em paulcrickard@yahoo.com
Hi thanks for posting this, it is very helpful. I have a question. How would you scan a QR code that contains a person’s membership ID, opens a database and marks that person as being present for the month? The same card would need to be scanned each month and the database updated with the current month.
1)So each person has a card with a QR code to a URL with their ID:
http://YourWebsite.com/get.php?userID=359965.
Each QR Code needs a unique URL-just the part after the ? will be different.
2) To connect and update the database you can do what my blog post shows but use an update query:
UPDATE table name
SET column = value
WHERE column=value
so: UPDATE attendance
SET present = true
WHERE id = $_get[userID]
3) if you need to know the month. You could use something like date(‘m’) to get the month and then pass that to the database. That just needs to be in the pages PHP, it has nothing to do with the QR code. Create a variable, use getdate() or date() then use the variable to call a table or column of the same name. Extract the month to variable $themonth. Then update a table named after months of the year by UPDATE $themonth….
Hope this makes sense. Good luck. Feel free to ask any more questions. I will help if I can. Thanks for checking out my blog.
Reblogged this on Human factors in FM and commented:
We’ve been experimenting with QR codes in the recent past for obtaining feedback within facilities management contracts. There are exciting – and cheap – methods for automatically linking assets to online databases and BIM systems using simple QR codes or NFC buttons. For those interested in finding out more, pay a visit to Paul Crickard’s blog where you can find a number of articles on linking the physical to the electronic. Also pay a visit to our earlier blog [http://chrisdpayne.wordpress.com/2012/05/28/quick-and-easy-customer-feedback/] for some suggestions on receiving meaningful feedback through the use of QR codes.
i want to connect RAPLA to QR CODE, i mean RAPLA consists of info about devices which need to be tracked through its unique id coded in the form of qr code, can u help???
hello, i just want to ask is this applicable in inventory system to count the scanned qr code to the inventory ?
No, but it should be easy to add an incrementor field in the DB or write out to a log file.
hi,,, i have a QR code
A – contains just ID number
B- ID no and information about the person,
Can both be used as a tool for scanning to update the database on system.
Sure they can
hi can i ask something, i have an project in my mid that involve qr code, my project is about users can scan qr code to translate article to other language.
so i think, i will place an qr code beside the article so user can scan it,then the translation from the article will display on their device. The qr code will lead to article that i already translate, that been saved in database
so my question is can i save an article in database and if can, what is the database?
hope you can help me to solve this problem and sorry for the bad English,