Rfid mqtt mysql php - done

Configuration
1

Hya,

after seaching around, most solutions i’ve found for RFID card demands reading the car token and store it in the device.

I’m planning to build the following:

  • nodemcu device with rfid card reader registering the token in my mysql database

  • nodemcu reading the rfid tag and quering the mqtt broker for it (topic /ping token).

  • php-mqtt subscribes the topic and query the mysql database

  • mysql database returns 0, 1, 2, where 0=ok, 1=ok, but not now, 2=deny

  • php-mqtt publish the topic /pong 0,1,2

  • devices subscribing the topic, like door lock, etc, do what is supposed to do.

Any suggestions?

Thx in advance.

2

done:

<?php

require('phpMQTT.php');


$server = 'hp15pw';             // change if necessary
$port = 1883;                   // change if necessary
$username = 'mqttuser';            // set your username
$password = 'mqttpwd';                   // set your password
$client_id = 'RFID-MQTT-MYSQL'; // make sure this is unique for connecting to sever - you could use uniqid()

$mysql_host = 'localhost';
$mysql_port = '';
$mysql_user = 'dbuser';
$mysql_pass = 'dbpwd';
$mysql_mydb = 'rfidcards';

$mqtt = new Bluerhinos\phpMQTT($server, $port, $client_id);
if(!$mqtt->connect(true, NULL, $username, $password)) {
  echo "Failed to connect to Mqtt: ";
  exit(1);
}


$con = new mysqli($mysql_host, $mysql_user, $mysql_pass, $mysql_mydb );
if ($con -> connect_errno) {
   echo "Failed to connect to MySQL: " . $con -> connect_error;
   exit(2);
}
$con->set_charset('utf8mb4');   // always set the charset

//$mqtt->debug = true;

while (true) {                  // endless loop

       // public function subscribeAndWaitForMessage($topic, $qos)
       $msg = $mqtt->subscribeAndWaitForMessage('rfid/card/ping', 0);
       echo "$msg";

       // query con
       //$tagid = "39EAB06D";
       $query = "SELECT name, id  FROM rfidtags WHERE id = ?";
       $stmt = $con->prepare($query);
       $stmt->bind_param('s', $msg);
       $stmt->execute();
       $stmt->store_result();
       $stmt->bind_result($name, $id);
       if ($stmt->fetch()) {
          echo "$name $id\n";
          $pong="1";
       } else {
          echo "failed to fetch data\n";
          $pong= "2";
       }
       // function publish($topic, $content, $qos = 0, $retain = false)
       $mqtt->publish('rfid/card/pong', "$pong", 0, false);

}

$con->close();
$mqtt->close();

not polished yet, but working with the mosquitto broker and mysql, subscribing to a topic, searching the datavase and returning a value (payload).