joomla getListQuery limit 20 problem

 JModelList::getListQuery - How to expand limit.

I'm using the following code in my Component Model to retrieve my database array

protected function getListQuery()
        {
                // Create a new query object.          
                $db = JFactory::getDBO();
                $query = $db->getQuery(true);
                // Select some fields
                $query->select('product_id,product_sku,product_s_desc,product_thumb_image');
                // From the virtuemart product table
                $query->from('#__vm_product');
                $query->where('product_in_stock >0','product_publish =1');
                $query->limit('0,40');
                return $query;
        }

the problem with this is that it's only return me 20 results at a time.  And I need this to be unlimited.   The solution for this I found in this post JModelList::getListQuery  limits to 20 only
What you need to do is add this code above the function getListQuery
protected function populateState()
{
        $this->setState('list.limit', 0);
}

and it'll work like magic :)

Building an Administration based Component for Joomla 2.5

Using the Hello World as a basis for a component, found at Joomla 2.5 Hello World Component Example [http://docs.joomla.org/Developing_a_Model-View-Controller_Component/2.5/Adding_a_view_to_the_site_part]

There's a couple of things missing on this page and that's the examples for

admin/sql/updates/index.html and admin/sql/updates/mysql/index.html are missing. 

You can find the example code for that at http://tutsforu.com/using-database-in-components.html

which is to put

  DROP TABLE IF EXISTS `#__productmapper`;
    CREATE TABLE `#__productmapper` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `greeting` varchar(25) NOT NULL,
    PRIMARY KEY (`id`)
    );
    
    INSERT INTO `#__productmapper` (`greeting`) VALUES
    ('DB Hello World!'),
    ('DB Good bye World!');

in both files. 

The other thing that's missing is the zip files for the project.   Here's mine for the very basic starting point of the component.  Mine has been relabelled 'productmapper' 

Download Basic Joomla 2.5 component here

Keeping a record of Deductions for retrieval in Staff Invoicing

Joomla 2.5 Payroll System

Here are some notes on how deductions are retrieved and displayed in Staff Invoicing .

 open components/com_projectfork/models/bacs.php

line 187

$sumvalues = $sumvalues - $tot_accountancy_fees;


for loans code is under line 194


#####

line 235 we do an insert into  #__alterations_bacs_amount

I think we can enter here and retrieve by user_id .  but really could do with a date check as well to cross ref against.

PHP excel $objPHPExcel limit to 2 decimal places

this is what  worked for me

$objPHPExcel->getActiveSheet()->getStyle('H'.($current_row + 28))->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

php excel ->getNumberFormat()->setFormatCode

what does PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00 mean









I've seen other solutions but the above worked perfectly for me.

how to check for empty Left Join values where 3 tables are joined in Mysql

If you've found this post then you'll probably done quite a specific search for a problem.  I'll explain what I've solved here and you can see if it's relevant to what your trying to achieve.


I have 3 tables

Shifts
Projects
and User

What I need to do is run a check and see if I have any shifts that hasn't got user details attached to it.  And then return the details of this with some information from the Project table also

Here's the solution

SELECT tt.id, tt.project_id, tt.cdate, tt.start_time, tt.end_time, cp.user_id, cp.firstname, cp.lastname, pj.title, pj.location
FROM vfvi7_pf_time_tracking AS tt
LEFT JOIN vfvi7_comprofiler AS cp ON tt.user_id = cp.user_id
LEFT JOIN vfvi7_pf_projects AS pj ON tt.project_id = pj.id
WHERE cp.user_id IS NULL

the tricks here are this

* I've used LEFT JOIN twice

in previous mysql queries I've used the syntax

LEFT JOIN (
vfvi7_comprofiler AS cp, vfvi7_pf_projects AS pj
) ON ( tt.user_id = cp.user_id
AND tt.project_id = pj.id )

but this didn't work.

*  ALSO using IS NULL

'WHERE cp.user_id IS NULL'  - it's as simple as that; this will let me know the shifts that haven't got a user assigned.

Here's some phrases I used to search for this problem

how to check mysql for a column with no left join

mysql left join produces no results

mysql find rows that have no left join

javascript to check O (zero) is in decimal format

I've been having a little problem with one of my input boxes in a form .  It seems that my code doesn't like '0' being entered.  Zero does not equate to '0.00' in PHP and this must be throwing an error somewhere. 

However other number are fine; like '1' does not need to be entered as '1.00' . 

php 0 to 0.00 conversion problems

php 0 does not equal 0.00

joomla component development how to force decimal input

php force decimal input in form

The quickest way to solve this problem for me was to write a bit of javascript takes that input box - and if the entered value is '0' - then change it to '0.00'  .  Here's the code.

var x=document.forms["myForm2"]["hourly_rate"].value;
if (x==0)
  {
  document.forms["myForm2"]["hourly_rate"].value='0.00';
  return;
  }


with the form being something like

< form name="myForm2" method="post">
Hourly Rate* :
< /form>

PHPExcel setting up the pages in a Workbook to print easily

Tomorrow morning I need to visit the client and help set things up so that they can easily print the pages from a Workbook .  Here are some notes taken from PHPExcel Cheat Sheet

How to add pagebreak view with PHPExcel
$objPHPExcel>setBreak('A4', PHPExcel_Worksheet::BREAK_ROW);

how to use $objPHPExcel>setBreak to make the page A4 sized

how to set orientation in PHPExcel
$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation


Setting printing breaks on a row or column

To set a print break, use the following code, which sets a row break on row 10.

$objPHPExcel->getActiveSheet()->setBreak( 'A10' , PHPExcel_Worksheet::BREAK_ROW );

The following line of code sets a print break on column D:

$objPHPExcel->getActiveSheet()->setBreak( 'D10' , PHPExcel_Worksheet::BREAK_COLUMN );


Setting a worksheet’s page orientation and size

Setting a worksheet’s page orientation and size can be done using the following lines of code:

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);

$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

Note that there are additional page settings available. Please refer to the API documentation for all possible options.

After visiting the office it seemed that the main thing that needed doing was to scale to fit the page.

The code I used here is .

     $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);
                            $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
                            $objPHPExcel->getActiveSheet()->getPageSetup()->setFitToPage(true);

the one thing you need to watch out for here is the the object for Active Sheet has been created.  So therefore put the above code under this code.

 $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

and all will be good.

Allow access to 2 IP addresses only

What I'm looking to do here is to make my website that has a Payroll System on it only accessible to myself and the clients office.
.htaccess will only allow certain ip . So this it the method I'm using.  Create or edit a .htaccess file.  ADD

order deny,allow
deny from all
allow from 888.888.888.888
allow from 888.888.888.881





Find out my IP Address here


Thank you to IP to Access for the .htaccess information

Why does my Joomla articles look wrong.

Here's the Question posed.

Everytime I upload an article on the website but it's the wrong format, is it possible for me to write the article in any format and then insert the HTML text in the HTML section?

This is the one he said was wrong:

But if I could use the above code in the correct format could I then use that on any article I create….?

The HTML the page is controlled by a style sheet.  Which then applies the formatting to the whole site and therefore keeps continuity that we can easily change .  

for example the code you just sent to me

to achieve this on the css file I would use the code.

div {
color: #333333;
font-family: Tahoma, Helvetica, Arial, sans-serif;
font-size: 100%;
margin-top: 8px;
margin-right: 8px;
margin-bottom: 8px;
margin-left: 8px;
background-image: initial;
background-attachment: initial;
background-origin: initial;
background-clip: initial;
background-color: #ffffff;
line-height: 1.3em;

}

This way all you then need in the article for this to work is

My text here

And all the formatting is done for you. The easy way to do this is to use Wordpad or Notepad.   Using Word or other editors copies over loads of HTML garbage that we don't need.   I'm not sure what the best editor to use is on the mac, if you find out please let me know http://www.youtube.com/watch?v=a0-nXk2MzVU&feature=plcp  .  The other thing is not only will this HTML make the text look rubbish but can break the page.

Here's a quick video I made showing a demonstration of this

Joomla 2.5 how to add a button with action to my module in the MVC format

Here's the answer to a fairly simple task - which is how to add a form and button inside a module.  Keeping within our nice MVC format.

Here's some of the searches I've done to research the solution for this.

joomla should a mysql UPDATE query be in MODEL of Controller

joomla development how to use a POST form in module

Retrieving Data for GET & Post - Joomla Development

my module has these files

modules/mod_PRSwarnings/tmpl/default.php
modules/mod_PRSwarnings/tmpl/index.php
modules/mod_PRSwarnings/mod_PRSwarnings.php
modules/mod_PRSwarnings/mod_PRSwarnings.xml
modules/mod_PRSwarnings/helper.php
modules/mod_PRSwarnings/index.php

 MODEL - this goes in  helper.php

Inside your CLASS bracket add

     public function setCancelWarnings($shift_id){
      $db = &JFactory::getDBO();

      $query  = "UPDATE #__pf_time_tracking ";
      $query .= "SET `dShftChked` = '1' ";
      $query .= "WHERE id =$shift_id ";
      
      $db->setQuery($query);
      $db->query();
      echo "
QUERY:$query
";
     
    }

CONTROLLER - this goes in modules/mod_PRSwarnings/mod_PRSwarnings.php

!! enter below the current code on your contoller page.

$edit_warnings = JRequest::getVar('edit_warnings');
// echo "EDIT - $edit_warnings
";

if($edit_warnings=='Cancel Warninig'){
           
            $shift_id = JRequest::getVar('shift_id', '', 'POST');
            $setCancelWarnings = ModPRSwarnings::setCancelWarnings($shift_id);
            }

VIEW  - this goes in tmpl/default.php

put this where you want the form to be displayed

 echo "

     
   

   
";


Although the code above is specific to my website I think it will be useful to others. As it shows a good demonstration of the logic of MVC in Joomla.  I welcome any discussion you may have on this.  Would you have done it differently.

Mysql - Can I use WHERE and HAVING in the same query

Yes is the quick answer .  It's just about the order that you call the 'HAVING' or 'WHERE' up. 

Have a look at this webpage  How to call up HAVING and WHERE in the same MYSQL query  

So with the following statement

select id, user_id, cdate, start_time, end_time, count(*)
  from vfvi7_pf_time_tracking
  group by user_id, cdate
  having count(*) >1


I cannot put my WHERE clause at the end but need to put it before the GROUP BY; like this


select id, user_id, cdate, start_time, end_time, count(*)
  from vfvi7_pf_time_tracking
  WHERE dShftChked !=1
  group by user_id, cdate
  having count(*) >1

Mysql - Grouping Row and Counting them

The problem:  I am printing out shifts done at certain venues.  And what I want to do is on some shifts rather than print out every shift; if the shift is done at the same time on the same date then these can be grouped together .  Also though I'll need to know how many shifts have been grouped together to refer to later in my code.

Question : can I group by 3 different columns.

Yes. and this gives me less row results - so if I can get a count of how many rows have been grouped then I'm on to a winner.

Question:  How Can i get a count of how many rows have been grouped together by GROUP BY in MySQL

By using count(*) as count in the select .  this gives me a column with how many times some rows have been grouped

Here's the original mysql call .


SELECT * FROM vfvi7_pf_time_tracking
WHERE vfvi7_pf_time_tracking.cdate >=1339974000
AND vfvi7_pf_time_tracking.cdate <=1340578800
AND vfvi7_pf_time_tracking.project_id =7

and the new mysql call is

SELECT *, count(*) as count
FROM vfvi7_pf_time_tracking
WHERE vfvi7_pf_time_tracking.cdate >=1339974000
AND vfvi7_pf_time_tracking.cdate <=1340578800
AND vfvi7_pf_time_tracking.project_id =7  GROUP BY vfvi7_pf_time_tracking.cdate, vfvi7_pf_time_tracking.start_time, vfvi7_pf_time_tracking.end_time

Joomla Frontpage Slideshow - Ordering Problem

Being able to get your slides in order isn't as straight forward as it seems for the Joomla component Frontpage Slideshow;  So yesterday I made a short video for a client explaining how to do this.

joomla development how to make a menu item for your component

 Here's a real quick one.  I'd made a component and couldn't find it in the menu selection in administrator. 

To make sure your component is added to the menu selections then follow the instructions here.
 
Joomla Development - Add a Component Menu Item

My mistake was really simple though and that was that I was already using the same title  !!