How to store the values of a phpMyAdmin table in a variable/array/array and use it in Arduino code?

Asked

Viewed 266 times

-1

My school work consists of a basic security system using card frequencies, in a similar way to buses: passes card in the reader, if the frequency is X releases the ratchet, if it is non-X the ratchet remains closed.

In my case, I approach a card from the reader and the key relay if I set your UID (card frequency) in the code:

if (conteudo.substring(1) == "40 C8 12 D9") //UID
{
Serial.println("Acesso liberado.");
...
}

else
{
Serial.println("Acesso negado.");
...
}

The problem is that the project should, in theory, be implemented on a large scale, IE, I can not store manually much more than 100 Uids (frequency of the card), Arduino does not have memory for this -- and would be a lot of work on several occasions.

What I did was create a form filled site, a local server with Apache, a database with Mariadb and phpMyAdmin.

My challenge now is to take a column of this database (which would be the registered Uids) and play in a variable to use in the Arduino code to look something like:

if (conteudo.substring(1) == "$variavel_UID") //Todas as UIDs da tabela
{
Serial.println("Acesso liberado.");
...
}
  else
{
Serial.println("Acesso negado.");
...
}

That way, I would never need to mess with the Arduino code, only update the table that already entered automatically as a permissible value.

Things I’ve tried before:

<?php
$query = "SELECT tabela from `usuarios`";
$result = mysql_query($query);
$fetch = mysql_fetch_row($result);
$
?>

.

<?php
$sql="SELECT tabela FROM `usuarios`";

$records=mysqli_query($dbconnect,$sql);
$json_array=array();

while($row=mysqli_fetch_assoc($records))
{
$json_array[]=$row;
}
echo json_encode($json_array);
?>

.

<?php
$colunas = array();
$selectColunas = mysqli_query(
$conexao,
"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'banco_de_dados' AND TABLE_NAME = 'tabela';"
); 
while($coluna = mysqli_fetch_assoc($selectColunas)){
array_push($colunas, $coluna['COLUMN_NAME']) ;
}

print_r($colunas)
?>

And many others... I’ve been trying for a long time, but I’m not experienced in it and I don’t pay material on.

At first, I want to at least display the table results in an HTML/PHP page. Then I decide on the translation to Arduino code, but any advice on how to proceed with the project.

EDIT

I was able to show the Uids on a php page with the code:

<?php
    session_start();
  include_once("conexao.php")
?>
<html>
<head>
<title>JSON</title>
</head>
<body>
<?php
  $dbselect = mysqli_select_db($connect, "Arduino");

    $sql="SELECT frequencia FROM usuarios";

    $records=mysqli_query($connect,$sql);
    $json_array=array();

    while($row=mysqli_fetch_assoc($records))
    {
        $json_array[]=$row;
    }

    echo json_encode($json_array);
?>
</body>
</html>

If someone knows how to improve and play for Arduino!

database mysqli phpmyadmin arduino

2 answers

0

#!/usr/bin/python
import mysql.connector #Biblioteca para conexao com o banco de dados
import serial #Biblioteca para conexao com o monitor serial do arduino

#-------------------CONEXAO COM BANCO-------------------#
conexao_banco_de_dados = mysql.connector.connect(host="localhost", user="root", passwd="1202", db="Arduino") #Conexao propriamente dita

validar = conexao_banco_de_dados.cursor() #Criar o cursor, para executar o CRUD

validar.execute("SELECT frequencia FROM usuarios") #Variavel que armazena os dados da tabela 'frequencia' do banco de dados

dados_banco_de_dados = validar.fetchall() #Armazenar todos os dados selecionados

for dados_frequencia in dados_banco_de_dados:
    print ('\n{}'.format(dados_frequencia))
#-------------------------------------------------------#

#-------------------CONEXAO SERIAL----------------------#
#conexao_monitor_serial_arduino = serial.Serial('/dev/ttyUSB0', 9600, timeout=1.7) #Conexao propriamente dita

#uid = conexao_monitor_serial_arduino.readline() #Ler e armazenar a primeira linha do serial arduino na variavel 'uid'
uid = 'UID1' #Armazenado o valor serial, ou seja, da uid do cartao detectado pelo RFID
#-------------------------------------------------------#

#---------------------MAIN------------------------------#
validacao = 1 if (uid in dados_frequencia) else 0 #Validacao, se existe os dados ou nao na tabela do banco de dados

print ('\n{}\n'.format(validacao)) #Printar a resposta de validacao
#-------------------------------------------------------#

#----------------FIM CONEXAO SERIAL---------------------#
#conexao_monitor_serial_arduino.write(validacao) #Printar para o arduino a resposta de validacao

#conexao_monitor_serial_arduino.close() #Conexao com o monitor serial encerrada

The only problem is in the output (the two prints):

(u'UID0',)

(u'UID1',)

(u'UID2',)

(u'UID3',)

0

It is indeed with my Mysql data, but it was not supposed to be leaving 0 but 1. Maybe the "u'" is getting in the way or something. I don’t know how to fix it, I’ve tried hard. Any idea?

  • Is that when you said data_banco_de_data = validate.fetchmany(1)) you just said that, not that uid = UID0 you have tried to change the (validate = 1 if (uid in dados_frequencia) Else 0) instead of in, to not in to see what happens? it would also be interesting to print the frequency_data to know how it is formatted, if it is an array and etc, and it would also be interesting to see if in is actually detecting if an element is in an "array" for example: create a random array and check if a data that you know is in the array really is, with IN

  • Friend, simply change your select so it already fetches the UID you want to validate, like this validar.execute("SELECT frequencia FROM usuarios WHERE frequencia = 'UID1' "), so when executing the fetchall() command you only need to arrive if you returned something, if you returned it is because there is then released ta, if the fetchall returns empty then it does not exist, it does not release

  • If you want to go this way then change that line of your code validacao = 1 if (uid in dados_frequencia) else 0 for him to check with a for, as in the comment is not identado this site to put the code you should use. This is due to the fact that fetchall returns the data.

  • Apparently I solved the problem. I deleted the for for dados_frequencia in dados_banco_de_dados: print (dates_frequencia) and subistitui the dados_banco_de_data = validate.fetchall() for data_frequency = [i[0] for i in list(validate)]. As far as I can Tell, all right. Thank you both so much for guiding me through the process, it really made a difference to someone who’s starting now!

-1

Good night buddy.

My challenge now is to take a column of this database (which would be the registered Uids) and play in a variable to use in the Arduino code to look something like:

From what I understand you want to take the Uids that are on the bench and play for the right Uino?
But the main reason you keep them in the bank is not because the memory of the Adian is small, so think to me, if you keep them in the bank and then you want to throw them into a variable in the Adian this variable will burst his memory.

Storing the Uids in the database is the right thing, but to validate the UID when the user passes through the sensor you can only take the UID from it (as you have already shown there in the code content.substring(1)) and send from the Rduino to the PC only this information, this sending of the UID from the Arduino to the PC can be done by serial, bluetooth or network, being that the serial you only need the usb cable stuck in the arduindo and the bluetooth and network you will need Shields to add these functions.

Well, then after you send the read UID from Rduino to the PC, you do the check if this UID exists registered in the database, if you have it you return an OK (or 1 to make it easier) to the Rduino and so it will know that it can key the relay, If you have not registered returns a 0 and the arduindo does not release the ratchet. This validation and response on the pc can be done in several ways, everything will depend on which way you choose to do communication, serial, bluetooth, wifi.

The answer was long but I tried to be as detailed as possible to be clear. I hope I’ve helped.

  • Yes, true! I hadn’t thought about it, but I expressed myself through the excerpt of the code. This is a problem, can I validate a UID in the middle of 100 without all of them being in the variable? Can you give me some guide to research how to do this?

  • It has to validate yes, you send UID from Arduin to PC, it can be by serial that will be easier. On the PC you create a program that will be "listening" to the serial port, as soon as the program receives the UID it checks if it exists in the database, If it exists it answers 1 to the Arduin or if it does not exist answers 0. If the Arduin receives 1 it knows that the UID has been validated and is registered in the bank, then it releases the ratchet, if it receives 0 then it does not release. Start by searching on how to communicate Rduino by serial port, have many videos on youtube. anything let me know!

  • Oops, I’m back. I took a little time to relax, but I already have my hand in the cookie jar. Look what I got (I’ll answer the question):

Browser other questions tagged database mysqli phpmyadmin arduino

You are not signed in. Login or sign up in order to post.