Custom SQL

Dear All,
Can we use custom SQL that generated on the fly?
I try to use view, generated error when save : The connected table does NOT have a primary key, please add a primary key to your table or some functions will not work correctly

SELECT DATE_FORMAT(date_8,'%d-%m-%Y') as date_t,
txt_region,
sum(IF(`shift`=1,`lalin`,0)) AS lalin_s_1,
sum(IF(`shift`=2,`lalin`,0)) AS lalin_s_2,
sum(IF(`shift`=3,`lalin`,0)) AS lalin_s_3,
sum(IF(`shift`=1,`tunai`,0)) AS tunai_s_1,
sum(IF(`shift`=2,`tunai`,0)) AS tunai_s_2,
sum(IF(`shift`=3,`tunai`,0)) AS tunai_s_3,
sum(IF(`shift`=1,`ppc`,0)) AS ppc_s_1,
sum(IF(`shift`=2,`ppc`,0)) AS ppc_s_2,
sum(IF(`shift`=3,`ppc`,0)) AS ppc_s_3,
sum(`lalin`) AS total_lalin,
sum(`tunai`) AS total_tunai,
sum(`ppc`) AS total_ppc
FROM `ctc320_hpt`
where cf_user_id = "???????"    <----- dynamic
group by date(date_8),txt_region

my table

CREATE TABLE IF NOT EXISTS `ctc320_hpt` (
  `cf_id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(255) NOT NULL,
  `recordtime` text NOT NULL,
  `ipaddress` text NOT NULL,
  `cf_user_id` text NOT NULL,
  `date_8` date NOT NULL,
  `txt_region` varchar(255) NOT NULL,
  `Lalin` varchar(255) NOT NULL,
  `tunai` varchar(255) NOT NULL,
  `PPC` varchar(255) NOT NULL,
  `shift` varchar(255) NOT NULL,
  `note` varchar(255) NOT NULL,
  PRIMARY KEY (`cf_id`)
)

Hi dion,

Where are you trying to use this in CC?

Bob

I hope i can use it on body.
but for now, i put my messy script in footer. I left body blank.

<?php
$loggeduser   =& JFactory::getUser();
$db           =& JFactory::getDBO();
$akses        = "User";

$query = "SELECT cb_hptaccess FROM jos_comprofiler WHERE `user_id` = '".$loggeduser->id."'";


$db->setQuery($query);
$row = $db->loadObject();

if($row->cb_hptaccess==$akses){
		$query="SELECT DATE_FORMAT(date_8,'%d-%m-%Y') as tanggal,
		txt_region,
		sum(IF(`shift`=1,`lalin`,0)) AS lalin_s_1,
		sum(IF(`shift`=2,`lalin`,0)) AS lalin_s_2,
		sum(IF(`shift`=3,`lalin`,0)) AS lalin_s_3,
		sum(IF(`shift`=1,`tunai`,0)) AS tunai_s_1,
		sum(IF(`shift`=2,`tunai`,0)) AS tunai_s_2,
		sum(IF(`shift`=3,`tunai`,0)) AS tunai_s_3,
		sum(IF(`shift`=1,`ppc`,0)) AS ppc_s_1,
		sum(IF(`shift`=2,`ppc`,0)) AS ppc_s_2,
		sum(IF(`shift`=3,`ppc`,0)) AS ppc_s_3,
		sum(`lalin`) AS total_lalin,
		sum(`tunai`) AS total_tunai,
		sum(`ppc`) AS total_ppc
		FROM `ctc320_hpt`
		where `cf_user_id`=$loggeduser->id
		group by date(date_8),txt_region";
}else {
		$query="SELECT DATE_FORMAT(date_8,'%d-%m-%Y') as tanggal,
		txt_region,
		sum(IF(`shift`=1,`lalin`,0)) AS lalin_s_1,
		sum(IF(`shift`=2,`lalin`,0)) AS lalin_s_2,
		sum(IF(`shift`=3,`lalin`,0)) AS lalin_s_3,
		sum(IF(`shift`=1,`tunai`,0)) AS tunai_s_1,
		sum(IF(`shift`=2,`tunai`,0)) AS tunai_s_2,
		sum(IF(`shift`=3,`tunai`,0)) AS tunai_s_3,
		sum(IF(`shift`=1,`ppc`,0)) AS ppc_s_1,
		sum(IF(`shift`=2,`ppc`,0)) AS ppc_s_2,
		sum(IF(`shift`=3,`ppc`,0)) AS ppc_s_3,
		sum(`lalin`) AS total_lalin,
		sum(`tunai`) AS total_tunai,
		sum(`ppc`) AS total_ppc
		FROM `ctc320_hpt`
		group by date(date_8),txt_region";
}
?>


    <table style="text-align: left; width: 100%;" border="1" cellpadding="1" cellspacing="2">
      <tbody>
        <tr>
         <td style="width: 7%;text-align: center;">Tanggal</td>
         <td style="width: 7%;text-align: center;">Region</td>
         <td style="width: 7%;text-align: center;">Shift I</td>
         <td style="width: 7%;text-align: center;">Shift II</td>
         <td style="width: 7%;text-align: center;">Shift III</td>
         <td style="width: 7%;text-align: center;">Shift I</td>
         <td style="width: 7%;text-align: center;">Shift II</td>
         <td style="width: 7%;text-align: center;">Shift III</td>
         <td style="width: 7%;text-align: center;">Shift I</td>
         <td style="width: 7%;text-align: center;">Shift II</td>
         <td style="width: 7%;text-align: center;">Shift III</td>
         <td style="width: 7%;text-align: center;">Lalin</td>
         <td style="width: 7%;text-align: center;">Tunai</td>
         <td style="width: 7%;text-align: center;">PPC</td>
         
        </tr>
<?  
$loggeduser   =& JFactory::getUser();
$db =& JFactory::getDBO();

 $db->setQuery($query);
 $t_rows = $db->loadRowList();
 foreach ( $t_rows as $t_row ) {
//echo $t_row[0]."<br/>";
?>
        <tr>
<td style="width: 7%;text-align: center;"><?echo $t_row[0]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[1]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[2]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[3]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[4]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[5]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[6]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[7]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[8]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[9]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[10]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[11]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[12]?></td>
<td style="width: 7%;text-align: center;"><?echo $t_row[13]?></td>
         
        </tr>

<?
   }

?>
</tbody>
    </table>

Hi dion,

this error:

The connected table does NOT have a primary key, please add a primary key to your table or some functions will not work correctly

will show once you select a table and try to save the connection, it checks for primary key in the table and your code will not affect it, it simply cant find a primary key so open the table in phpmyadmin and check to make sure that the table has one!

Cheers
Max

but as far as i know, we cannot create primary key for 'view'. CMIIW

Hi dion,

we can not or we may not ? anyway, if you are going to view records only then i think it will work fine.

and if you are going to view only and add much SQL code then its better to use Chronoforms instead!

Regards
Max

Hi Max,

we can not or we may not ?

i'm sorry, cannot speak english very well. I don't understand what you mean.. please forgive me..

and if you are going to view only and add much SQL code then its better to use Chronoforms instead!

Is it true? (using Chronoforms). Why ?


Regards,
Dion

Hi Dion,

No problems!

because you are viewing data only and you are comfortable with the code you have, so instead of going into troubles while learning how Connectivity works exactly, all you will need is to paste your code in a new form and save!

Cheers
Max

thank you max 😀
this extensions really great. !!