Writing to mysql from a pic... possible?

For questions and comments on programming in general. And for any items that don't fit into the forums below.

Moderators: Benj, Mods

Post Reply
jndlogic
Posts: 6
Joined: Tue Feb 26, 2013 6:47 pm
Has thanked: 2 times
Been thanked: 1 time
Contact:

Writing to mysql from a pic... possible?

Post by jndlogic »

Hi all,

I have several projects that require to add a record to a mysql server when an object is detected. All is working fine at the moment. I have the following setup.
Object sensor connected to pic (with flowcodeprogram). If object detected, serial data is sent to a computer. I have written a script on the pc, that detects incoming serial data. If data is detected, it adds a record to the database over the internet (mysql community server)... Client accesses a website to check data (php)

Q:
It would be VERY AWSOME to be able to skip the computer and let the pic write the data to the sql database. Even if its an simple insertrecord with only one letter or so.
Has anyone done this, or has a tip for me?

Thanks in advance... Davy

User avatar
Enamul
Posts: 1772
Joined: Mon Mar 05, 2012 11:34 pm
Location: Nottingham, UK
Has thanked: 271 times
Been thanked: 814 times
Contact:

Re: Writing to mysql from a pic... possible?

Post by Enamul »

Hi,
I haven't done exactly what you want but something similar you want which removes the requirement of using pc. I've collected quite a lot of data from different sensors and then made a packet with defined format. Then I've sent those data at a defined time interval through gprs to a remote PHP server where an java application is running. TCP port is established between site gprs and server machine and java code is listening that particular port. Java code is receiving the data and sending acknowledgement and also writing data in database. If you have any query about the system you can email me.
Enamul
University of Nottingham
enamul4mm@gmail.com

hyperion007
Posts: 528
Joined: Sat Dec 01, 2012 1:23 pm
Location: Sweden
Has thanked: 49 times
Been thanked: 101 times
Contact:

Re: Writing to mysql from a pic... possible?

Post by hyperion007 »

Hi Enamul,

I'm looking for a way to do exactly what you describe, send data from a PIC via GPRS to a database on a PC and displaying the data on a website.

Any ideas, pointers etc would be much appreciated. What hardware can I use for the GPRS function? I have little to no experience with programming on the PC side.

Thanks!

Rudi
Posts: 666
Joined: Mon Feb 10, 2014 4:59 am
Has thanked: 493 times
Been thanked: 187 times

Re: Writing to mysql from a pic... possible?

Post by Rudi »

Hi

i make with Pic18F2550 and a wifi shield connect to Internet and put data to my private homepage...

You can connect a Wifi rs232 shield like this to the uart.
simple connect
Wifi Rx to Tx Uart ( Soft or HW )
Wifi Tx to Rx Uart Soft or HW )
make Sure GND and VCC is on Wifi Shiled same like Pic ( 5 v Gnd )

then send data to the uart from the Wifi Shield by RS232 from Pic

the wifi shiels self you can configure ip, port, network ect by
web, at commands or simple win prog
if you want more info you can ask me,
i for sale this moduls under 30 EUR over microcontroller.net

http://www.mikrocontroller.net/topic/327155#new


have a look:

http://www.mikrocontroller.net/attachme ... r_WIFI.JPG
http://www.mikrocontroller.net/attachme ... G_1343.JPG
http://www.mikrocontroller.net/attachme ... __MIDI.JPG
http://www.mikrocontroller.net/attachme ... _-_USB.JPG

The Wifi Shield are from Shuncom
The Shield are with a PIC32 for managed the WIFI and the UARTS.
Simple Transparent.

a second - only a wifi shield
http://www.robot-italy.com/en/gs1011-ga ... board.html

but you must take all by your self, the TCP IP Stack and pins from your PIC...


For communication in MySQL you can simple make a Terminal Prog or SQL Commands that will push the PIC over UART to this WIfi Shield and the Wifi goes online with your Request... ;-)

If you need help in translate let me know.


In one Project,
I make a LS MIWI with this Wifi Shield, ( google LS MIWI )
I simply send MIDI from PIC over Wifi to DSMIDI ;-) or DMX Controll.


Best wishes from Germany

Rudi
;-)

Rudi
Posts: 666
Joined: Mon Feb 10, 2014 4:59 am
Has thanked: 493 times
Been thanked: 187 times

Re: Writing to mysql from a pic... possible?

Post by Rudi »

Code: Select all


 I have written a script on the pc, that detects incoming serial data. If data is detected, it adds a record to the database over the internet (mysql community server)... Client accesses a website to check data (php)

hi.. this "command" simple send over the UART from the PIC..


PIC...UART - TX ----> connect Wifi Shield RX .....wifi goes online with your "MySql Request."... and make the job what your pc makes...
if you want read from "internet´" to your Pic, so receive over Rx from Uart the Tx (sendDatas) from the WIfi Shield.....
You do not manag IP Stack and so .. at the Pic...
you can simple managed this at the wifi shield...
tzhe wifi shield has an PIC32MX440 on Board for the MRF24WB0MB

so only connect

PIC Rx / Tx to Wifi Shield Tx / Rx , make a VCC, GND Connect to the Wifi Shield and you can go on ;-)
If you send the command from Pic.. the command will send transparent from your Uart over the Echo Uart from Picew over the Wifi to your MySQL Sever...
Put only the IP from your MySQL Server in WebControll for the Wifi Shield..
i have attached details from the Wifi Shield...

Have phun
Best wishes
Rudi
;-)

hyperion007
Posts: 528
Joined: Sat Dec 01, 2012 1:23 pm
Location: Sweden
Has thanked: 49 times
Been thanked: 101 times
Contact:

Re: Writing to mysql from a pic... possible?

Post by hyperion007 »

Hi Rudi,

Thanks. The thing I don't know how to do is set up the MySQL side on my webhost and what commands/requests to send (to what address?) I will do it using GPRS and a SIM900 module but will also try it using WIFI for future use.

Best regards,
Daniel S

User avatar
Benj
Matrix Staff
Posts: 15312
Joined: Mon Oct 16, 2006 10:48 am
Location: Matrix TS Ltd
Has thanked: 4803 times
Been thanked: 4314 times
Contact:

Re: Writing to mysql from a pic... possible?

Post by Benj »

Hello,

The easiest way to learn this stuff is to do it from your home PC and then apply it to your remote server at a later date if you need world wide web access.

1) Download and install EasyPHP - Creates an Apache web server on your PC with PHP and mySQL support built in.
2) The address is the IP address of your computer, via wifi this will be easy as your already in the internal network, via GPRS or external network will require that you configure your router to port forward to your Apache server and in this case you use the IP address of your router. There are services like noip which allow you to use a fixed URL to connect to a dynamic IP address as most home internet connections will be dynamic.
3) I would do all of the actual mySQL interaction using PHP and use your device to simply request the PHP web pages. This lets the PHP code do all the work leaving your embedded device free to get on with what it needs to do. PHP and mySQL are pretty simple once you get the hang of them.

This tutorial looks quite relevant: http://www.freewebmasterhelp.com/tutorials/phpmysql/1

Rudi
Posts: 666
Joined: Mon Feb 10, 2014 4:59 am
Has thanked: 493 times
Been thanked: 187 times

Re: Writing to mysql from a pic... possible?

Post by Rudi »

hyperion007 wrote:Hi Rudi,

Thanks. The thing I don't know how to do is set up the MySQL side on my webhost and what commands/requests to send (to what address?) I will do it using GPRS and a SIM900 module but will also try it using WIFI for future use.

Best regards,
Daniel S
Hi Daniel,

Benj has given Top Tips to you.

I have a second Tip for you, if you not will install all at your PC..

you can make a USB stick with XAMPP Portable -
this is included :
Apache, MySQL, PHP, Perl, OpenSSL, phpMyAdmin, Webalizer, NetWare, FileZilla FTP-Server, Mercury Mail Transport System and SQLite

Simple unpack this to a USb Stick or to a PC Folder if you want at your harddisk.
So you can try the commands in PHP .. MySQL and many others.

If you Read Benj's Tip for the First Step in PHP so it is easy to make a "Webpage" for the Mikrocontroller and your doing.

example this same packet i have at my usb stick and do all fine :

http://www.computerbild.de/download/XAM ... .html?dl=1

you can download from Apache Friends Homepage Too.

Search for "xampp-portable"

You can create a test Database at your PC Step by Step
Then you can make a Table for your Data
Then you can Add Values to the Table
Then you can make a Webpage that connect the Database at your MySQL Server at your PC and run a SQL QUERRY like "Select * from "YOURTABLE"..
and List this at the Webpage.
Then you can make a WebSide with a Form to Add / Append / Refresh Data to the Table.

So you have the Knowing for go on with the MySQL Server at your Provider..
Still test all at your PC and then make the same at the Provider.

You must read a little in Basis for MYSQL and PHP... but if you know how this works you will easy make more as you think...
the step by step from Benj's Tip is perfect for you..

Best wishes
Rudi
;-)

hyperion007
Posts: 528
Joined: Sat Dec 01, 2012 1:23 pm
Location: Sweden
Has thanked: 49 times
Been thanked: 101 times
Contact:

Re: Writing to mysql from a pic... possible?

Post by hyperion007 »

I read the article Ben linked to and I have now configured my hosted webserver and am able to populate fields in the correct database table using POST from a form on a web page.

But I can't seem to get this to work from my SIM900 GSM/GPRS module. I will try using Flowcode 5 tonight as I think my recent update to 6.0.7 caused all kinds of problems. I can't get things running that I had running just fine on 6.0.4 (like the GSM component, and the GPS component that both uses UART, not responding to anything now)

Post Reply