CODEWITHSUNDEEP
phpmysqlsql
Dani Valverde
Dani Valverde

Reputation: 409

How can I retrieve data from MySQL table and insert it into QR code using PHP?

I have this PHP code that creates a QR code with the given data: 

<?php

   include(JPATH_LIBRARIES . '/phpqrcode/qrlib.php');

   $tempDir = JPATH_SITE . '/images/';   
   $codeContents = 'This Goes From File';
   $fileName     = 'qr_'.md5($codeContents).'.png';

   $pngAbsoluteFilePath = $tempDir.$fileName;
   $urlRelativeFilePath = JUri::root() .'images/' . $fileName;

   if (!file_exists($pngAbsoluteFilePath)) {
      QRcode::png($codeContents, $pngAbsoluteFilePath);
   }
   else {
      echo "Not working!";
   }

   echo '<img src="'.$urlRelativeFilePath.'" />';

?>

What we need is to connect to a MySQL table (#__rsforms_submissions), retrieve some fields (for example, Name and Mail, Phone) and insert them in the QR code instead of the data provided by the example. To do so I would retrieve the logged in user's user

$user = JFactory::getUser();
$username = JUserHelper::getUsername($user->id);

Then, the MySQL query would look something like

SELECT Name,Mail, Phone FROM #__rsforms_submissions WHERE Username = $username

However, what's next? How do I insert these values into the PHP code? Thanks!

Dani

Upvotes: 0

Views: 1848

Answers (1)

machineaddict
machineaddict

Reputation: 3236

I'm assuming you are using Joomla. I don't know Joomla, but as I can figure it out from selecting and retrieving SQL data:

<?php

/* get username */
$user = JFactory::getUser();
$username = JUserHelper::getUsername($user->id);

/* get data from database */
// Get a db connection.
$db = JFactory::getDbo();

// Create a new query object.
$query = $db->getQuery(true);

// Select all records from the user profile table where key begins with "custom.".
// Order it by the ordering field.
$query->select($db->quoteName(array('Name', 'Mail', 'Phone')));
$query->from($db->quoteName('#__rsforms_submissions'));
$query->where($db->quoteName('Username') . ' LIKE '. $db->quote('\'' . $username . '\''));
$query->order('ordering ASC');

// Reset the query using our newly populated query object.
$db->setQuery($query, 0, 1); // 0 = start, 1 = number of records
$row = $db->loadRow();

/* create the qrcode */
include(JPATH_LIBRARIES . '/phpqrcode/qrlib.php');

$tempDir = JPATH_SITE . '/images/';
$codeContents = $row->Name . ', ' . $row->Mail . ', ' . $row->Phone . ', '; // this is what goes into qrcode
$fileName     = 'qr_'.md5($codeContents).'.png';

$pngAbsoluteFilePath = $tempDir.$fileName;

if (!file_exists($pngAbsoluteFilePath)) {
  QRcode::png($codeContents, $pngAbsoluteFilePath);

  $urlRelativeFilePath = JUri::root() .'images/' . $fileName;
  echo '<img src="'.$urlRelativeFilePath.'" />';
}
else {
  echo "Not working!";
}

Note: It's not tested! However, it should give you an idea about how to do it.

Upvotes: 1

Related Questions