Create DataBase

Hi everyone, i’m sorry for my english, i’m french student.

I have project, i must create a database to store few sentances.

The sentances must be picked randomly when a sensor is on, i would like to know how can i do ?

I use MySQL, i never use that before and no one (Teachers) can help me.

Thank you all.

Welcome on the forum,

What platform do you use to run the database?

Have you thought about how to connect that platform to the Arduino?

What sensor do you use?

There are still many questions that need to be answered before we understand enough detail to help you,

Going on the information in the Original Post this sounds like a project for a Raspberry Pi or a laptop rather than an Arduino.

Arduinos don't have the memory or the computing power to manage an SQL database.

If you are new to SQL databases you will probably find SQLite much easier to use than MYSql

...R

Hi robtillaart, thank you for the welcome.

I connect my PC (used like database) and an arduino Uno with Ethernet (https://www.gotronic.fr/art-carte-arduino-uno-ethernet-a000068-18745.htm) to plug both together,

My sensor is movement detector (Détecteur de mouvement Grove 101020020 Seeed Studio - Capteurs de distance et mvt | GO TRONIC)

I'm open to reply all question you have.

What software have you go on the PC apart from mySql which is to be used for this Arduino application ? Have you also got a web Server (such as Apache) and PHP (for server side scripting) ?
If you have a package like that, it is sometimes known as a LAMP / WAMP or XAMPP server and you communicate with it via HTTP GET or POST statements to store/retrieve data from the database.

Hi 6v6gt, i don't know... i'm lost do you have any videos or tutorials ? Or can you advice me ?

BastienGJ:
i don't know... i'm lost do you have any videos or tutorials ? Or can you advice me ?

If you can't tell us what you are trying to achieve how can we give advice.

There have been lots of suggestions and questions already and you have not responded to them - even if only to ask for more explanation of things you don't understand.

...R

I have project, i must create a database to store few sentances.

Try the Arduino DataBase or Extended DataBase code.

It looks like you have an Arduino Uno, an ethernet card and a passive infrared (PIR) sensor and a PC with at least a mySQL installation. You want to store some data (I guess from the sensor, maybe supplemented with a timestamp?) in the data base.

What do you want to do with the stored data? view it on a web page ? retrieve it back using the Arduino ? or what ?
You mentioned random sentences, but that is not very clear what you mean.

Setting up your own server on a PC such that it can be talked to by an Arduino is not a trivial task. If you have some support, I'd suggest installing a WAMP server on it and using that. That includes mySQL. It is quite versatile and there are some online turorials. Search for: "Arduino WAMP".

If it is a school project, then also get some guidance from your teacher.

Here's an example (PHP + Arduino code) for inserting data (+timestamp) into the database from an Arduino.

It was originally written for the ESP8266 (WiFi instead of Ethernet) but the Arduino networking API is largely the same.

Setting up a LAMP server on Ubuntu, for example, is extremely easy. I believe Digital Ocean has a great tutorial.
PhpMyAdmin provides a GUI to easily create and manipulate tables and databases.

Read PDO Tutorial for MySQL Developers to learn how to talk to the SQL server using PHP.

Pieter

Hi jremington, thank you for the link.

6v6gt, my project is "Aided Mail Box" to help disabled people to take their letters, i must create a letter box and when the postman put something in it, a sensor is actived and it must send automatic message on our cellphone, i want use database to store sentances (ex : "You have receive letters", "The postman put something in your box"). The programm must take a random sentances and send it, but i don't know how i can do.

I use : -Arduino Uno + Ethernet A000068
-Shield GSM-GPRS 2 arduino GPS-GPMS (for send message)

It's school projet, but no one can help me...

Hi PieterP, thank you for the link.

OK. So you do not need an external data base to hold text such as "Mailbox empty", "Mailbox not empty" etc. You embed such strings in the sketch which you write to run on the Arduino.
You'll need the PC only to load sketches onto the Arduino.

BastienGJ:
it must send automatic message on our cellphone, i want use database to store sentances (ex : “You have receive letters”, “The postman put something in your box”). The programm must take a random sentances and send it, but i don’t know how i can do.

Don’t use a cannon to kill a mosquito.

That’s not what databases are used for. Just save the sentences on the Arduino itself. In RAM if you can afford it, PROGMEM (flash) otherwise. Create an array of pointers to your strings, and select a random index.
In my eyes, you don’t even need the Ethernet shield.

Exactly. The big problems will be:
Finding a sensor to detect the presence of mail in the mail box
Powering the whole thing with a reasonable battery life.

I have developed my own system for monitoring my remote mailbox.
It uses a TSOP infra red detector and 4 IR diodes mounted on a mat. The led light is reflected from the top of the mailbox back to the TSOP. Each led is lit in turn and the TSOP tested. This beam is blocked if there is a letter covering one of the devices.
It uses a barebones ATMEGA328P chip (like in the Uno) and an nrf24l01 transceiver for the remote unit.
It transmits the results back every 32 seconds to the base unit.
Battery life of the remote unit is > 9 months.

Indeed, no need for a database. Having to set up a WAMP or LAMP stack is a bit much for a simple school project anyway, and total overkill for an automated mailbox.

For the mail detection: break beam sensor, vertical, in the middle of the mailbox. That should be able to catch just about all mail. Depends a bit on the geometry of the mailbox of course.

If battery powered, just check for mail every 10-30 minutes, switch off the sensor and put the Arduino in deep sleep when not active. Maybe even use an RTC to limit checking to the times to when mail is expected, and stop checking when mail has been detected and the message sent out until the mailbox has been opened (microswitch or magnetic switch in the door could activate the Arduino again).

1 Like

Thank you PieterP, i’ll see that.

6v6gt, don’t worry, i’m with 5 students in this project, each have their part, my part is just : detect the letter, take random sentance and send it on my phone.

Hi wvmarle, i know it’s a big project but it’s for my diploma. Thank you for your time.

Then you can just tell your fellow students to set up MySQL, and give you a user name with SELECT privileges. You can then simply connect with the MySQL connector library, and start pulling down sentences.

Still makes me feel like a database is total overkill for this, and also wondering why the "random" part of the sentences. It may be boring but it's more user friendly to receive a standard "you've got mail" message on your phone.

wvmarle:
Then you can just tell your fellow students to set up MySQL

No. Don't. You're just making it much harder than it should be. Using a database is not the right approach.

wvmarle:
Still makes me feel like a database is total overkill for this, and also wondering why the "random" part of the sentences. It may be boring but it's more user friendly to receive a standard "you've got mail" message on your phone.

+1

1 Like

It's a school project, using a database may be part of the project description.
My usual approach to answering these questions is in the lines of "I think it's a bad idea but if you insist this is how you could go about it".

Of course, the OP is free to do what he wants.

But if he wants to rent a storage unit (database) 100km from his home, just to store a single address book (sentences), and he wants to drive all the way to the storage unit every time, just to look up an address, while he could just as well keep the address book in a drawer at home, what option should we recommend?

If it's part of the project description, well, there's no way around it, but given that the teachers don't seem to know any SQL, I doubt that's the case.

There are enough botches, hacks and poorly designed systems in this world already, I don't think it's a good idea to encourage people to use bad solutions to their problems.
People are stubborn: if you hand them two possible solutions: the right solution, and the solution for the bad approach they had in mind, they will most likely pick the latter, even if it means more work and won't produce the results they might have hoped for.
Sometimes you have to give them a little push to get them on the right track when they're stuck on a certain approach :wink:

1 Like