new project help w/ onSubmit and db structure

Hello again,

I am working on a series of forms for managing a personal budget. I have already created a few forms for creating categories and sub-categories and one form for imputing an amount for the sub-category under a certain year and a certain month. The category and sub-category forms have their own tables - but I have not yet created the budget table (for fear I might mess up if I don't think this through).

What I would like to be able to do is allow the user to radio select whether they want the Budgeted amount to apply to all the months in the year or just to the selected month.

here is the FormHTML I have so far

<?php
global $mainframe;

$database =& JFactory::getDBO();
$database->setQuery( '
SELECT 
	category_name,cat_id
FROM jos_chronoforms_excategories
WHERE 1
');

$database->query();
$records = $database->loadAssocList();

?>

<div class="form_item">
  <div class="form_element cf_heading">
    <h1 class="cf_text">Budget Goal by Month</h1>
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Select Year:</label>
    <select class="cf_inputbox validate-selection" id="select_1" size="1" title=""  name="select_year">
    <option value="">Choose Option</option>
      <option value="2010">2010</option>
<option value="2011">2011</option>
<option value="2012">2012</option>
<option value="2013">2013</option>
<option value="2014">2014</option>

    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Select Month:</label>
    <select class="cf_inputbox validate-selection" id="select_2" size="1" title=""  name="select_month">
    <option value="">Choose Option</option>
      <option value="January">January</option>
<option value="February">February</option>
<option value="March">March</option>
<option value="April">April</option>
<option value="May">May</option>
<option value="June">June</option>
<option value="July">July</option>
<option value="August">August</option>
<option value="September">September</option>
<option value="October">October</option>
<option value="November">November</option>
<option value="December">December</option>

    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_radiobutton">
    <label class="cf_label" style="width: 150px;">Apply To:</label>
    <div class="float_left">
      <input value="Selected Month" title="" class="radio validate-one-required" id="radio00" name="radio0" type="radio" />
      <label for="radio00" class="radio_label">Selected Month</label>
      <br />
      
<input value="All Months" title="" class="radio validate-one-required" id="radio01" name="radio0" type="radio" />
      <label for="radio01" class="radio_label">All Months</label>
      <br />
      

    </div>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Expendature Type:</label>
    <select class="cf_inputbox" id="select_category" size="1" title=""  name="select_category"  onchange="htmlData('http://localhost/andy/items.php', 'ch='+this.value)" >
    <option value="">Choose Option</option>
<?php
// List of Categories 
	foreach( $records as $row ) {
		echo '<option value="'.$row[cat_id].'">'.$row[category_name].'</option>';
	}
?>
    </select>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_dropdown">
    <label class="cf_label" style="width: 150px;">Item Type:</label>
	<div id="txtResult">
    <select class="cf_inputbox" id="select_subcat" size="1" title=""  name="select_subcat">
    <option value="">Choose Option</option>
    </select>
	</div>
    
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_textbox">
    <label class="cf_label" style="width: 150px;">Budget Amount:</label>
    <input class="cf_inputbox required validate-currency-dollar" maxlength="150" size="30" title="" id="text_7" name="budget_amount" type="text" />
  
  </div>
  <div class="cfclear"> </div>
</div>

<div class="form_item">
  <div class="form_element cf_button">
    <input value="Submit" name="button_8" type="submit" /><input type="reset" name="reset" value="Reset"/>
  </div>
  <div class="cfclear"> </div>
</div>

NOTE: I am using ajax to populate the sub-categories drop down.

Any Idea how I might accomplish this?

Let me know if I have not been clear,

Hi samoht,

Do you mean 'all the months' OR 'just one month'? If you do then I'd be inclined to use a radio button group with 13 entries. Then selecting any one button will unselect the rest.

If you want to have the option of 'May + July + October' then you could do the same with a checkbox array with 12 entries; and an extra box for 'all' with a script snippet to select all 12.

Either way you probably need some OnSubmit PHP to turn the result into useable data.

Bob

What about a multi-select?
I used to do those in Access - but have not tried one in HTML. That way I could just display the months in a listbox and let the user chose as many as they want (like the checkbox idea, but perhaps a little cleaner)

Either way It is the save that I need to work out. Let's say a user picks all 12 months. Would I need to loop through a save x times and execute the save whenever the selected month is in the array of months?

So I have put in a multi-select drop down for the months and I added this in the OnSubmit:

<?php 
$months = JRequest::getVar('select_month', '', 'post');
foreach($months as $month){
// do the save
}
?>

Currently that is not doing anything. However, just by making the select multi-select I get an array of months saved to one field in the table. I suppose this can work - but is it the best way to store the data?

Obviously I am going to need to do a bit of php for the display of the budget - and for whatever options I have I will need the correct queries - I am guessing the only difference in the query syntax would be instead of a '=' I would use the '%%' wildcards ??

Hi samoht,

Yes a multi-select would be fine. (I'd want to make the size big enough to display all the months without scrolling.)

I'd save the month array as some kind of searchable string. Im not exactly sure what - minimally 10110011 . . . would to it. More likely I'd set the month values in the drop down to 01, 02, . . . and save as 01|02|05| . . .

And yes, searching on LIKE '%05%' would work, or call the whole string back and explode it into an array. It really depends on how you are going to use the data.

Bob

OK,
So I am trying to start my budget report. I have setup two date pickers fro a start and end date for the report - but for some reason when I check the value of the month inside the var I keep getting "Dec" for the start month no matter what date I chose?

<?php
$this_var = JRequest::getVar('date_start', '', 'post');
echo "Start month is ".date("M",$this_var);
?>

If I just echo $this_var I get the correct date entered (e.g. 13/02/2010 etc)

What is wrong with what I have?

Hi samoht,

Check the PHP manual for the date() function -- I think it needs a timestamp. If so you'll need to convert the datestring first.

Bob

I tried mktime($this_var); but that didn't work?

I'll keep checking

Hi samoht,

It's not a US format date* so you may need to explode it into bits and re-assemble to get a reliable timestamp result.

Bob

* Is today 01/02/2010 or 02/01/2010 ???

*today would be
01/02/2010

So I decided to go with:

$sdate = JRequest::getVar('date_start', '', 'post');
$edate = JRequest::getVar('date_end', '', 'post');

list($sday, $smonth, $syear) = explode("/", $sdate);
list($eday, $emonth, $eyear) = explode("/", $edate);

then if I need the timestamp I can say:

$sd = mktime(0,0,0,$smonth,$sday,$syear);
$ed = mktime(0,0,0,$emonth,$eday,$eyear);

now I can get whatever I want from those to vars.

Bob,

two quick questions:
1. how do I get the id of the save I just made so I can insert that into another table? (I seem to remember something like $MyPlugins =& CFPlugins::getInstance($MyForm->formrow->id);
global $cf_just_registered; ??)

2. how would I concat the results from two select boxes for a save in 1 database field where one fo the select boxes is a multiselect?? **(( I should mention that I also want to save a record for each value in the multiselect so I'll have to do some sort of loop through the array saving each time ))**


Thanks,

HI samoht,

You can see the 'saved data' code near the end of the Autogenerated Code box $MyForm->tablerow["table_name"] so you will probably want something like:

$form_date = $MyForm->tablerow["table_name"];
$cf_id =  $form_data->cf_id;

The multi-select box will return and array so to save as a string you can implode the values with a suitable distinctive separator that you can re-divide them when needed. I tend to use | or # as they rarely appear in input values.

$value_string = implode ('##', $array_name);

You can concatenate other entries on to the the ends of this string as you need to.

And use foreach to loop through the array


Bob

Great, thanks Bob!

For some reason when I used this:

<?php

$form_data = $MyForm->tablerow["bdgtbymonth"];
$mbid = $form_data->monthly_budget_id;
$year = JRequest::getVar('select_year', '', 'post');
$months = JRequest::getVar('select_month', '', 'post');

foreach ($months as $month){
$sm = "$year-$month-01";
$db =& JFactory::getDBO();
$query = '
	INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month)
		VALUES( '.$mbid.','.$sm.' )';
$db->setQuery($query);
$db->query();
}
?>

I get a

Warning: Invalid argument supplied for foreach() in C:\xampp\htdocs\andy\components\com_chronocontact\libraries\customcode.php(51) : eval()'d code on line 9

Do you see anything wrong with the foreach??

Hi samoht,

Check what value is in $months, I suspect that it may not be an array.

You may need

$months = JRequest::getVar('select_month', array(), 'post','ARRAY');

Bob

No it is an array alright. Here is the debug output:
$_POST Array: Array ( [select_year] => 2010 [select_month] => Array ( [0] => 01 [1] => 03 [2] => 04 [3] => 05 ) [cat_id] => 3 [item_id] => 26 [budget_amount] => 175 [button_8] => Submit [918a38fa51a5fdcb7da312bc470d68ec] => 1 [1cf1] => ab7f806cedec42bf187eb03160b6f169 [chronoformname] => bdgtbymonth )

But that brings up a good point. Will select_month be an array if only one month gets selected?
here is what my html for select_month looks like:

    <select class="cf_inputbox validate-selection" MULTIPLE id="select_month" size="12" title=""  name="select_month[]">
<option value="01">January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>

    </select>

Hi samoht,

I'm not convinced . . .

Try adding a line to show you exactly what is being returned by the JRequest

$months = JRequest::getVar('select_month', '', 'post');
echo '<div>$months: '.print_r($months, true).'</div>';

Bob

Hi Bob,

that will return:

$months: 01, 03, 04, 05

is that a string?

Hi samoht,

Yes it is a comma separate list.

Now please try the the same experiment with the array version of JRequest I posted earlier.

Bob

Yes that seemed to make it an array and it no longer complains about the foreach

here was the output:

$months: Array ( [0] => 01, 03, 04, 05 ) 

however, It did not insert into the other table as I hoped??

That makes the foreach value spit out this:

2010-01, 03, 04, 05-01 

So it looks Like the array should look like either
$months: Array ( [0] => 01, [1] => 03, [2] => 04, [3] => 05 )
or
$months: Array ( 01, 03, 04, 05 )

I fixed the array problem with an explode. But for some reason am still not saving to my other table?
Here is my code:

<?php
//global $mainframe;
$form_data = $MyForm->tablerow["bdgtbymonth"];
$mbid = $form_data->monthly_budget_id;
$year = JRequest::getVar('select_year', '', 'post');
$months = explode(",", JRequest::getVar('select_month', '', 'post'));
echo '<div>$months: '.print_r($months, true).'</div>';

foreach ($months as $month){
$sm = "$year-$month-01";
echo $sm;
$db =& JFactory::getDBO();
$query = '
	INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month)
		VALUES( '.$mbid.','.$sm.' )';
$db->setQuery($query);
$db->query();
}
?>

My problem is that my $form_data is empty.
Do you know why that might be??

Hi samoht,

OK - it did make it an array - but not the array that we want :-(

One more please

$months = JRequest::getVar('select_month', '', 'post');
$months = explode(', ', $months);
echo '<div>$months: '.print_r($months, true).'</div>';

Hi samoht,

OK - it did make it an array - but not the array that we want :-(

One more please

$months = JRequest::getVar('select_month', '', 'post');
$months = explode(', ', $months);
echo '<div>$months: '.print_r($months, true).'</div>';

Oh - you found this while I was having dinner :-)

The DB code looks OK from here. Use the same approach to echo out $query and see what is being generated.


Bob

Yep, the explode gets me the correct array:
$months: Array ( [0] => 01 [1] => 03 [2] => 04 [3] => 05 )

But I am still missing the $mbid for the insert. When I put
echo '<div>$query: '.print_r($query, true).'</div>';

I get nothing? If I change it to $post I get nothing??

Hi samoht,

The query echo needs to be just after the line where $query is defined. If it is indeed blank that something very strange is going on.

Bob

Hi Bob,

I just had to move the line inside the foreach but the $form_data being empty is the problem as you can see from this out put:

$months: Array ( [0] => 01 [1] => 03 [2] => 04 [3] => 05 )
2010-01-01
$query: INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month) VALUES( "$mbid","2010-01-01" )
2010-03-01
$query: INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month) VALUES( "$mbid","2010-03-01" )
2010-04-01
$query: INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month) VALUES( "$mbid","2010-04-01" )
2010-05-01
$query: INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month) VALUES( "$mbid","2010-05-01" )

that was with $mbid enclosed in double quotes and this is with $mbid concat with dot operator

$months: Array ( [0] => 01 [1] => 03 [2] => 04 [3] => 05 )
2010-01-01
$query: INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month) VALUES( "","2010-01-01" )
2010-03-01
$query: INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month) VALUES( "","2010-03-01" )
2010-04-01
$query: INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month) VALUES( "","2010-04-01" )
2010-05-01
$query: INSERT INTO bdgt_month_year_combo (monthly_budget_id, select_month) VALUES( "","2010-05-01" )

Hi samoht,

I think it should be OK as long as this code is running after the DB Connection code :-(

Bob

Hi Bob,

Sorry I was having a blond moment(s)! I forgot to change the 'run order' so that the auto gen block ran first before the OnSubmit.

Works fine now - Thanks