daily pastebin goal
3%
SHARE
TWEET

Untitled

a guest Oct 11th, 2018 61 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE `jos_rsform_submission_values` (
  2.   `SubmissionValueId` int(11) NOT NULL,
  3.   `FormId` int(11) NOT NULL,
  4.   `SubmissionId` int(11) NOT NULL DEFAULT '0',
  5.   `FieldName` text NOT NULL,
  6.   `FieldValue` text NOT NULL
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  8.  
  9. INSERT INTO `jos_rsform_submission_values` (`SubmissionValueId`, `FormId`, `SubmissionId`, `FieldName`, `FieldValue`) VALUES
  10. (129862, 28, 548, 'creationdate', '27-08-2018 10:30 AM'),
  11. (129863, 28, 548, 'CustomerAccount', 'TEST'),
  12. (129864, 28, 548, 'otheraccount', ''),
  13. (129865, 28, 548, 'salesorderno', 'SO-00006296'),
  14. (129866, 28, 548, 'DocumentUpload', '-'),
  15. (129867, 28, 548, 'numberofbayan', '1'),
  16. (129868, 28, 548, 'BayanNo', '189426'),
  17. (129869, 28, 548, 'portofloading', 'Seaport'),
  18. (129870, 28, 548, 'containers', '3'),
  19. (129871, 28, 548, 'Container1', 'TTNU8517512'),
  20. (129872, 28, 548, 'CargoDescription1', 'Chocolate'),
  21. (129873, 28, 548, 'containertype1', 'Reefer'),
  22. (129874, 28, 548, 'purchaseorder1', ''),
  23. (129875, 28, 548, 'Location1', 'Jeddah'),
  24. (129876, 28, 548, 'Status1', 'Return Empty'),
  25. (129877, 28, 548, 'requesteddate1', '18-08-2018 07:00 AM'),
  26. (129878, 28, 548, 'Container2', 'TCLU1256192'),
  27. (129879, 28, 548, 'CargoDescription2', 'Chocolate'),
  28. (129880, 28, 548, 'containertype2', 'Reefer'),
  29. (129881, 28, 548, 'purchaseorder2', ''),
  30. (129882, 28, 548, 'Location2', 'Jeddah'),
  31. (129883, 28, 548, 'Status2', 'Return Empty'),
  32. (129884, 28, 548, 'requesteddate2', '18-08-2018 07:00 AM'),
  33. (129885, 28, 548, 'Container3', 'KKFU6780793'),
  34. (129886, 28, 548, 'CargoDescription3', 'Chocolate'),
  35. (129887, 28, 548, 'containertype3', 'Reefer'),
  36. (129888, 28, 548, 'purchaseorder3', ''),
  37. (129889, 28, 548, 'Location3', 'Jeddah'),
  38. (129890, 28, 548, 'Status3', 'Pending'),
  39. (129891, 28, 548, 'requesteddate3', '18-08-2018 07:00 AM'),
  40. (129892, 28, 548, 'Container4', ''),
  41. (129893, 28, 548, 'CargoDescription4', ''),
  42. (129894, 28, 548, 'containertype4', ' '),
  43. (129895, 28, 548, 'purchaseorder4', ''),
  44. (129896, 28, 548, 'Location4', '-'),
  45. (129897, 28, 548, 'Status4', ' '),
  46. (129898, 28, 548, 'requesteddate4', ''),
  47. (129899, 28, 548, 'Container5', ''),
  48. (129900, 28, 548, 'CargoDescription5', ''),
  49. (129901, 28, 548, 'containertype5', ' '),
  50. (129902, 28, 548, 'purchaseorder5', ''),
  51. (129903, 28, 548, 'Location5', '-'),
  52. (129904, 28, 548, 'Status5', ' '),
  53. (129905, 28, 548, 'requesteddate5', '')
  54.  
  55. ALTER TABLE `jos_rsform_submission_values`
  56.   ADD PRIMARY KEY (`SubmissionValueId`),
  57.   ADD KEY `FormId` (`FormId`),
  58.   ADD KEY `SubmissionId` (`SubmissionId`),
  59.   ADD KEY `SubmissionId_2` (`SubmissionId`),
  60.   ADD KEY `SubmissionId_3` (`SubmissionId`);
  61.  
  62. ALTER TABLE `jos_rsform_submission_values`
  63.   MODIFY `SubmissionValueId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=133092;
  64.    
  65. ## SubmissionId    FieldName    FieldValue
  66. ## -----------------------------------------
  67. ##     548         Status1      Return Empty
  68. ##     548         Status2      Return Empty
  69. ##     548         Status3      Pending
  70. ##     548         Status4      
  71. ##     548         Status5
  72.    
  73. ## SubmissionId    FieldName    FieldValue
  74. ## ----------------------------------------
  75. ##     548         Container3   KKFU6780793
  76.    
  77. <?php
  78. $db = JFactory::getDbo();
  79. $query = $db->getQuery(true);
  80. $query->select($db->quoteName(array('FieldName')));
  81. $query->from($db->quoteName('#__rsform_submission_values'));
  82. $query->where($db->quoteName('FieldName') . ' IN ("Status1","Status2","Status3","Status4" ,"Status5")');
  83. $query->where($db->quoteName('FieldValue') . ' = "Pending"');
  84.  
  85. $db->setQuery($query);
  86. $result = $db->loadColumn();
  87.  
  88. foreach($result as $value) {
  89.         foreach($value as $key => $data) {
  90.          $cut = substr($data, 6);
  91.          $cut = "Container".$cut;
  92.      }
  93. }
  94. ?>
  95.    
  96. <?php
  97. $query = $db->getQuery(true);
  98. $query->select($db->quoteName(array('FieldValue')));
  99. $query->from($db->quoteName('#__rsform_submission_values'));
  100. $query->where($db->quoteName('FieldName') . ' IN ' . '(' . implode(',', $cut) . ')');
  101.  
  102. $db->setQuery($query);
  103. $results = $db->loadObjectList();
  104.  
  105. foreach($results as $value) {
  106.         foreach($value as $key => $data) {
  107.         echo $data."<br />";
  108.      }
  109. }
  110. ?>
  111.    
  112. try {
  113.     $db = JFactory::getDbo();
  114.     $query = $db->getQuery(true)
  115.                 ->select("a.FieldName, a.FieldValue")
  116.                 ->from("#__rsform_submission_values a")
  117.                 ->innerJoin("#__rsform_submission_values b ON a.SubmissionId = b.SubmissionId AND a.FieldName = REPLACE(b.FieldName, 'Status', 'Container')")
  118.                 ->where(["b.FieldValue = 'Pending'", "b.FieldName LIKE 'Status%'"]);
  119.     $db->setQuery($query);
  120.     echo $query->dump();    // never show to public
  121.     if (!$result = $db->loadAssocList()) {
  122.         echo "No rows found";
  123.     } else {
  124.         echo "<pre>";
  125.             var_export($result);
  126.         echo "</pre>";
  127.     }
  128. } catch (Exception $e) {
  129.     JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');    // never show to public
  130. }
  131.    
  132. SELECT a.FieldName, a.FieldValue
  133. FROM jos_rsform_submission_values a
  134. INNER JOIN jos_rsform_submission_values b ON a.SubmissionId = b.SubmissionId AND a.FieldName = REPLACE(b.FieldName, 'Status', 'Container')
  135. WHERE b.FieldValue = 'Pending' AND b.FieldName LIKE 'Status%'
  136.  
  137. array (
  138.   0 =>
  139.   array (
  140.     'FieldName' => 'Container3',
  141.     'FieldValue' => 'KKFU6780793',
  142.   ),
  143. )
  144.    
  145. foreach($result as $value) {
  146.      $cut[] = "Container" . substr($value, 6);
  147. }
  148.    
  149. "... IN ('" . implode("','", $cut) . "')"
  150.    
  151. SELECT a.SubmissionId, a.FieldName AS ContainerName, a.FieldValue AS ContainerValue
  152. FROM jos_rsform_submission_values a
  153. INNER JOIN (
  154.     SELECT SubmissionId, SUBSTRING(FieldName, 7) AS StatusNum
  155.     FROM jos_rsform_submission_values
  156.     WHERE FieldValue = 'Pending'
  157.       AND FieldName LIKE 'Status%'
  158. ) b ON a.SubmissionId = b.SubmissionId AND a.FieldName = CONCAT('Container', b.StatusNum)
  159.    
  160. Warning: Invalid argument supplied for foreach()
  161.    
  162. foreach($result as $key => $value) {
  163.     $result[$key] = $db->quote('Container' . substr($value, 6));
  164. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top