Populate field with values from table DB?

I have a form in CF 3 that is populating some fields with values from last submitted record. I don´t finf out how to do that in CF4.

I have this PHP to get the value:

<?php>
$dbfo =& JFactory::getDBO(); 
$query = "SELECT ftag FROM `#__chronoforms_my_form` WHERE cf_user_id = '$my->id' ORDER BY `cf_id` DESC LIMIT 1";
$dbfo->setQuery($query);
$foretag = $dbfo->loadResult();
?>

and this to show it in fieldvalue:

<?php echo $foretag; ?>

I tried to put the first part in a Custom Code action and the other in Fieldvalue. But it is still a blank field.

Is the way I approach the problem the correct way or am I thining wrong?

Hi JeLu,

CFv4 has a DB Record Loader action that will do this.

Bob

Ok!

I tried to reuse my CF3 code but this is perhaps better.

I tried to put cf_user_id = $my->id in WHERE statement but that does not work.

I want the values to be preloaded from previous record the user has saved to the DB table. This is meant as a help for my users. If the value has NOT changed since last record no need to fill it in again.

JeLu

I got it to populate the hole form, but I just want some fields to be prepoulated and tried to use field array but that did not work as I wanted.

JeLu

If I use Multi DB Loader I can set which fileds to use for the array but it does not populate my form with anything.

Hi JeLu,

To populate a form for editing you want to use the DB Record Loader (not the DB Multi-Record Loader).

On the Basic tab put the column name that you want to look up in the DB Field Box; select the table in the Table box; put the name of the parameter in the $form->data array that holds the look-up value in the Request Param box.

If the parameter is included in the page URL or passed from a previous form step that's all you need.

To use the current User ID put user_id in this box; save the DB Record Loader; then drag a Custom Code action into the same event and move it up before the DB Record Loader; open the Custom Code action and add this code:

<?php
$user =& JFactory::getUser();
$form->data['user_id'] = $user->id;
?>

Bob

Thank you Greyhead quick as usual in your answar. You are great!

I see that you give me this advise for editing, that is not the porpose here. I have users that create new rseords after an education for them. But in the "old" version I use today I have the service to prepopulate with some values in some fields. Not all fields. I need to be able to choose whitch field to populate. My users can change this and add individual data for each record and save it as a new record.

To edit a record I use ChronoConnectivity.

JeLu

I have now tried this and modified by "Try and Error".

I go it to populate only thoose field that I wanted by putting fields in Parameter Request in Advanced Tab, The fields I putted there was the ones I did NOT wanted to populate with. So far so good but when testing the form I relised that this had a effect I din´t want. After Validation it blanked all fields eccept thoose I populate when form opens.

I have now tried with this: (populate all fields with last enetred record for current user)

Basic Tab:

DB Field: fields that I want to populate with.

cf_user_id,ftag,adress,postnr,ort,arbled,uftag,kursort,t2

Table:
Same Table as I save this form records to.

Request Param:
user_id (don´t know why)

Model ID:
Empty

Load under Model ID:
No

Advanced Tab:

WHERE Statement:

cf_user_id = '<?php echo $form->data['cf_user_id'];?>' ORDER BY cf_id DESC

Array Fields set:
Empty

Array Separators:
Empty

Parameter Fields:
Empty
(First I tried to put thoose fields I did NOT wanted to populate with, and that seemed to work. But not after Validation)

Hi JeLu,

The DB Field box should just have cf_user_id in it, not the list of column names you have here.

And you can leave the WHERE box and the Parameters Fields box on the Advanced tab empty.

Bob

Hi again!

But if I do like this I can´t choose what fields to populate. I fixed this in CFv3 like this:

<?php
$my = & JFactory::getUser();
?>
$dbfo =& JFactory::getDBO(); 
$query = "SELECT ftag FROM `#__mytable` WHERE cf_user_id = '$my->id' ORDER BY `cf_id` DESC LIMIT 1";
$dbfo->setQuery($query);
$foretag = $dbfo->loadResult();
$dbad =& JFactory::getDBO(); 
$query = "SELECT adress FROM `#__mytable` WHERE cf_user_id = '$my->id' ORDER BY `cf_id` DESC LIMIT 1";
$dbad->setQuery($query);
$adress = $dbad->loadResult();
$dbpo =& JFactory::getDBO(); 
$query = "SELECT postnr FROM `#__mytable` WHERE cf_user_id = '$my->id' ORDER BY `cf_id` DESC LIMIT 1";
$dbpo->setQuery($query);
$postnr = $dbpo->loadResult();
$dbor =& JFactory::getDBO(); 
$query = "SELECT ort FROM `#__mytable` WHERE cf_user_id = '$my->id' ORDER BY `cf_id` DESC LIMIT 1";
$dbor->setQuery($query);
$ort = $dbor->loadResult();
$dbor =& JFactory::getDBO(); 
$query = "SELECT arbled FROM `#__mytable` WHERE cf_user_id = '$my->id' ORDER BY `cf_id` DESC LIMIT 1";
$dbor->setQuery($query);
$arbled = $dbor->loadResult();
$dbor =& JFactory::getDBO(); 
$query = "SELECT t1 FROM `#__mytable` WHERE cf_user_id = '$my->id' ORDER BY `cf_id` DESC LIMIT 1";
$dbor->setQuery($query);
$t1 = $dbor->loadResult();
$dbor =& JFactory::getDBO(); 
$query = "SELECT kursort FROM `#__mytable` WHERE cf_user_id = '$my->id' ORDER BY `cf_id` DESC LIMIT 1";
$dbor->setQuery($query);
$kursort = $dbor->loadResult();
$dbor =& JFactory::getDBO(); 
$query = "SELECT uftag FROM `#__mytable` WHERE cf_user_id = '$my->id' ORDER BY `cf_id` DESC LIMIT 1";
$dbor->setQuery($query);
$uftag = $dbor->loadResult();

?>

and in each field I wanted to prepopulate:

value="<?php echo $foretag; ?>"

and so on.

Can´t I do that with CFv4?

JeLu

Hi JeLu,

You could do that but it requires a lot of unnecessary database calls, There are a couple of choices.

a) You can use the DB Record Loader and then add a custom Code action to unset any values that you don't want, for example 'column1' and 'column2'

<?php
unwanted = array (
  'column1',
  'column2'
);
foreach ( $unwanted as $v ) {
  $form->data[$v] = '';
}
?>

This will put blanks into each of the unwanted values.

b) Use a Custom Code action to load just the values that you want:

<?php
$user =& JFactory::getUser();
$db =& JFactory::getDBO();
$query = "
    SELECT `columna`, `columnb`
        FROM `#__table_name`
        WHERE `cf_user_id` = '{$user->id}' ;
";
$db->setQuery($query);
$data = $db->loadArray();
$form->data = array_merge($form->data, $data);
?>

This takes a bit more code but you only load the columns that you need from the record.

Ideally the DB Record Loader action would let you select a column list but at the moment you can't do that.

Bob

Hi!

Proplem with the first version is that it will not republish on validation and the second one I can´t get to work.
(I use a server Side Validation to check that only one record with the same Social Security number is used)
I noticed that in unwanted on line 2 there was a $ missing.

The secomd solution get me this error message:

Warning: array_merge() [function.array-merge]: Argument #2 is not an array in /xxx/xxx/public_html/xxx/xxx/administrator/components/com_chronoforms/form_actions/custom_code/cfaction_custom_code.php(16) : eval()'d code on line 11

Perhaps it is not possible to do what I want but my user are now used to let the form fill out som fields in advance. And all records have been more accurat since I started that. Some of yhe user creates hubdreds of records but it is always the same on adress and so on. Some Users perhaps send in 10 records with the same adress and then have to change. That is way I really need this to work.

Thanks for your help so far

JeLu

Hi JeLu,

Yes I can see that you'll need to develop version one some more to handle the revalidation case :-( It can still be made to work with a little care though.

The PHP Warning (not an Error) in version two is probably because the $data array is empty. I'm not sure why that is but a little debugging code should pin it down.

Bob

I have now made a multipage form of it and runs the test for Social Scurity number on the first page. This works with a Custom Server Side Validation (CSSV) and if it is False an errormessage is shown. I was thining about if an existing record is crated with the unique Social Security number, users could be redirected to Edit the record instead.

Is that possible and can I do it from PHP in CSSV action?

JeLu

Hi JeLu,

If you pass a record identifier to the input/edit form then you can use a DB Record Loader to look up and load any existing data. Please see this FAQ

Bob

Hi Bob!

Sometime I fell like a pain in the a**, But how can I use CSSV actionscript to open that form (and then populate it).

I have this Custom Validation:

<?php
$pnr = '';
if ( isset($form->data['pnr'] )) {
  $pnr = $form->data['pnr'];
}
if ( !preg_match("/^\d{6}\-\d{4}$/", $pnr) ) {
  $form->validation_errors[] = "Ett personnummer saknas eller är inte inskrivet på rätt sätt. Använd formen XXXXXX-XXXX";
  return false;
}
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `#__chronoforms_data_xxx`
    WHERE `pnr` = ".$db->quote($pnr).";
";
$db->setQuery($query);
$in_use = $db->loadResult();
if ( $in_use) {
  $form->validation_errors[] = "Personnumret används redan! Använd \"Ändra Elevfil\" istället om det är din elev. I annat fall tar du kontakt med Global Education på elevfil@mydomain.se";
  return false;
}
?>

Both this queries return False but the last Return I would like to redirect to an editform with that record = $pnr

To me it looks like I have all data in an array and it is in $form->validation_errors I could use something else but at this point I am confused and really can´t figure it out.

JeLu

Hi JeLu,

I'm not sure why you'd try to do that from a CSSV script? Are there two different forms involved to a) create and b) edit?

Bob

Yes!

I use CC4 to edit and list.

and CFv4 for new records. I use a Multipage Form and tha first thing that shows is a page with just one field (Social Security numbar. I want this to be checked Server Side.

If it´s not in use and correct typed in it get you to the next page where validation is MooTools (CFv4 Standard).

It would br nice to offer a way for my users to edit their own studentrecords without the detour thru CCv4.

JeLu

Hi JeLU,

Is the second form different if they are editing rather than creating? If not you can use the same form with a DB Record Loader. If the record is found it will load the data; if not it will show an empty form.

Bob

I got two different forms, but my question is also how I can redirct to a form in Custom PHP if "$in_use"?

JeLu

Hi JeLU,

The If-Then-Else action plus Show Form actions will do this I think.

Bob

Show Form action can I find but is there an action with If-Then-Else? Or is it named something else 'cause I can´t find it.

You are a great help Bob and I am sorry if I ask stupid questions. It seems like, as soon as I learn something new there opens a lot of new questions :?

Regards
JeLu

Hi JeLu,

Max added it in the RC3.5 version I think. There's a list of actions here

Bob

Is it Custom Event Switcher you mean? I can´t find it and I have RC 3.5.1

JeLu

Hi JeLu,

My apologies, it was developed by another user and installed on my test site. It's not in the published version :-(

My Event Switcher [GH] action does more or less the same thing.

Bob

That one I already have and will test it.

Thanks

OK I feel stupid right now, but where does this go?

I have a Multipage From and at the first Page it will do this CSSV. and it returns OK or false in teo cases.

1. Is it typed in the correct form, if no just reload the form
2. Is it in use, if yes I would like to show a new form with the records value.

I can´t get it to work at all and I really don´t know how to start. Can tou push me in the right direction?

Regards
JeLu

Now I got that to show record values.

In the form for editing I have DB Record Loader on Load event and pnr as Parameter.

That part seems to work but how can I use your event Switcher to handle when it is mistyped?

JeLu

Hi JeLu,

It would be some thing like this:

<?php
if ( $form->data['in_use'] == 'yes' ) {
  return 'event_a';
}
?>

Then add the 'in_use' actions to Event A and leave the others in the main On Submit event after the Event Switcher [GS] action.

Bob

I´m not sure that I follow here.

I have take the CSSV-action away and use only event-switcher[GH].

In the code section I have put

<?php
$pnr = '';
if ( isset($form->data['pnr'] )) {
  $pnr = $form->data['pnr'];
}
if ( !preg_match("/^\d{6}\-\d{4}$/", $pnr) ) {
  $form->validation_errors[] = "Ett personnummer saknas eller är inte inskrivet på rätt sätt. Använd formen XXXXXX-XXXX";
  return 'event_b';
}
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `#__chronoforms_my_table`
    WHERE `pnr` = ".$db->quote($pnr).";
";
$db->setQuery($query);
$in_use = $db->loadResult();
if ( $in_use) {
  
  return 'event_a';
}

?>

event_b works. In event_a I have put a data_to_session action for pnr and a Show Form action that points to another form where user can edit it.

I´ve got it to work, a bit.

This is the code I use in Event Switcher[GH] action:

<?php

$user =& JFactory::getUser();
$user_id = $user->get('id');

$pnr = '';
if ( isset($form->data['pnr'] )) {
  $pnr = $form->data['pnr'];
}
if ( !preg_match("/^\d{6}\-\d{4}$/", $pnr) ) {
    return 'event_b';
}
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `#__chronoforms_data`
    WHERE `pnr` = ".$db->quote($pnr)." AND cf_user_id=<?php echo $user_id; ?>;
";
$db =& JFactory::getDBO();
$query2 = "
  SELECT count(*)
    FROM `#__chronoforms_data`
    WHERE `pnr` = ".$db->quote($pnr)." AND cf_user_id!=<?php echo $user_id; ?>;
";

$db->setQuery($query);
$in_use = $db->loadResult();
if ( $in_use) {
   return 'event_a';
}
$db->setQuery($query2);
$in_use2 = $db->loadResult();
elseif ( $in_use2) {
   return 'event_d';
}

else {
return 'event_c';
}
?>

And I need to check if the record already is in the table and if it is current user who has add it.

event_a works
evant_b works
event_c works

but event_d doesn´t work and I get an error. I understand that the error point out else-claus in my code but I have no idee why?

JeLu

I tried this:

$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `#__chronoforms_data`
    WHERE `pnr` = ".$db->quote($pnr)." 
";


$db->setQuery($query);
$in_use = $db->loadResult();
$in_use_user = $in_use->cf_user_id;
if ( $in_use_user == $user_id) {
   return 'event_a';
}
elseif ( $in_use_user != $user_id) {
   return 'event_d';
}


else {
return 'event_c';
}

But it returns event_c regardless if it is the users record or not. event_b is still OK and event_c but never event_a!
Edit:
Correction it does not work with event_b either.

Can´t I get a check against cf_user_id and actual user?



//JeLu

I am now trying to count rows with this:

If rows exist go to event_a
if not go to event_d

<?php

$user =& JFactory::getUser();
$user_id = $user->get('id');

$pnr = '';
if ( isset($form->data['pnr'] )) {
  $pnr = $form->data['pnr'];
}
if ( !preg_match("/^\d{6}\-\d{4}$/", $pnr) ) {
    return 'event_b';
}
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `#__chronoforms_data`
    WHERE `pnr` = ".$db->quote($pnr)." AND `cf_user_id` = '<php? echo $user_id ?>'
";
$db->setQuery($query);
$db->query();
$num_rows = $db->getNumRows();
print_r($num_rows);
$in_use = $db->loadRowList();

if ($in_use == 1) {
   return 'event_a';
}
elseif ($in_use == 0) {
   return 'event_d';
}


else {
return 'event_c';
}

?>

But I get this error:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /home/xxx/public_html/libraries/joomla/database/database/mysqli.php on line 263
Data Array:

What am I doing wrong?

JeLu

Now I have tried this:

<?php
$user =& JFactory::getUser();
$user_id = $user->get('id');
$pnr = '';
if ( isset($form->data['pnr'] )) {
  $pnr = $form->data['pnr'];
}
if ( !preg_match("/^\d{6}\-\d{4}$/", $pnr) ) {
    return 'event_b';
}
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `#__chronoforms_data`
    WHERE `pnr` = ".$db->quote($pnr)." AND `cf_user_id` = '1';
$db->setQuery($query);
 $rows = $db->loadObjectList();
$in_use = count($rows);
if ($in_use == 1) {
   return 'event_a';
}
elseif ($in_use == 0) {
   return 'event_d';
}
else {
return 'event_c';
}

?>

And get this:

Parse error: syntax error, unexpected $end in /home/xxx/public_html/administrator/components/com_chronoforms/form_actions/event_switcher_gh/event_switcher_gh.php(51) : eval()'d code on line 29

This is the debug I get from this form:

Data Array: 
Array
(
    [Itemid] => 64
    [option] => com_chronoforms
    [view] => form
    [chronoform] => test
    [event] => Page Two
    [cf_sid] => 4926fdc33e09206b7979b1fbeedaa3a0
    [pnr] => 000000-0000
    [input_submit_14] => Nästa
    [cc59959592c0d690a66847706d07248f] => 1
)
 Validation Errors: 
Array
(
)

I feel that I am lost in an endless loop of errors.😟

//JeLu

Hi JeLu,

You are missing the "; at the end of the query:

$query = "
  SELECT count(*)
    FROM `#__chronoforms_data`
    WHERE `pnr` = ".$db->quote($pnr)." AND `cf_user_id` = '1';
"; // <-- add this line

Bob

Hi Bob1

That got rid of the error but I still don´t get it to go to event_d.

<?php
$user =& JFactory::getUser();
$user_id = $user->get('id');
$pnr = '';
if ( isset($form->data['pnr'] )) {
  $pnr = $form->data['pnr'];
}
if ( !preg_match("/^\d{6}\-\d{4}$/", $pnr) ) {
    return 'event_b';
}
$db =& JFactory::getDBO();
$query = "
  SELECT count(*)
    FROM `#__chronoforms_data`
    WHERE `pnr` = ".$db->quote($pnr)." AND `cf_user_id` = '1';
"; // <-- add this line
$db->setQuery($query);
 $rows = $db->loadObjectList();
$in_use = count($rows);
if ($in_use == 1) {
   return 'event_a';
}
elseif ($in_use == 0) {
   return 'event_d';
}
else {
return 'event_c';
}

?>

Now it returns event_a and never event_d or event_c the part with count rows does not seems to work at all. Any sugestion?

Errorcheck for "not empty" value is OK
check for 000000-0000 is done and is OK here is event_b fired so that works.

//JeLu

Hi JeLu,

The query gets a count i.e. a single number; but the data you are requesting is an Object List, that is an array of the results; then you check the count() of that array. I think that the count will always be 1 regardless of the value in the array.

Please try:

<?php
$user =& JFactory::getUser();
$user_id = $user->id;
$pnr = '';
if ( isset( $form->data['pnr'] ) ) {
  $pnr = $form->data['pnr'];
}
if ( !preg_match( "/^\d{6}\-\d{4}$/", $pnr ) ) {
  return 'event_b';
}
$db =& JFactory::getDBO();
$query = "
SELECT count(*)
  FROM `#__chronoforms_data`
  WHERE `pnr` = {$db->quote( $pnr )} AND `cf_user_id` = '1';
"; // <-- add this line
$db->setQuery( $query );
$in_use = $db->loadResult();
if ( $in_use == 1 ) {
	return 'event_a';
} elseif ( $in_use == 0 ) {
	return 'event_d';
} else {
	return 'event_c';
}
?>

Bob

Hi again!

That made the part with event_a and event_d work but event_c does not work.

event_c is for showing the form if 'pnr' is not found and I can see that this part overrides that

} elseif ( $in_use == 0 ) {
   return 'event_d';

Perhaps I aproch this the wrong way?

I want it to stop the users changes to edit if the record "belongs" to someone other then the logged in user. Perhaps I can make a new check in event_d or is there a better way?

//JeLu

Hi Jelu,

But your query only checks for user_id = 1 ??

WHERE `pnr` = {$db->quote( $pnr )} AND `cf_user_id` = '1';

Maybe some debug code that didn't set put back?

Bob

Yes I tried to put

WHERE `pnr` = {$db->quote( $pnr )} AND `cf_user_id` = '<php? echo $user_id; ?>';

in this but now it seems to return 0 all the time

It looks so good and then I am at square one again.

//JeLu

Hi JeLu,

You don't need the <?php echo there, you are already inside <?php ?> tags. Please try:

WHERE `pnr` = {$db->quote( $pnr )} AND `cf_user_id` = '{$user->id}' ;

Bob

Yes that got that working but how to check if it is no record with that 'pnr' and go on to event_c (wich is an empty form for a new record)

Because I will get a 0 in result if it not is the logged in user I will never get to event_c?? Is that right?

//JeLu

I tried this but it just returns event_d

<?php
$user =& JFactory::getUser();
$user_id = $user->id;
$pnr = '';
if ( isset( $form->data['pnr'] ) ) {
  $pnr = $form->data['pnr'];
}
if ( !preg_match( "/^\d{6}\-\d{4}$/", $pnr ) ) {
  return 'event_b';
}
$db =& JFactory::getDBO();
$query = "
SELECT count(*)
  FROM `#__chronoforms_data`
  WHERE `pnr` = {$db->quote( $pnr )};
"; // <-- add this line
$db->setQuery($query);
$in_use = $db->loadResult();
if ( $in_use['cf_user_id'] == $user->id) {
   return 'event_a';
}
elseif ( $in_use['cf_user_id'] != $user->id) {
   return 'event_d';
}
else {
return 'event_c';
}?>

Now it never goes beyond event_a

Hi JeLu,

It looks to me as if you are now making code up at random :-(

Please stop and work though it line by line thinking about what you want to happen. Working through some examples with pencil and paper may help.

Bob

Ok!

That is possible. I am frustrated because I can´t really get how to achieve what I want to do.

I plan like this:

I have a multipage form and first of all my users will fill in a field with a swedish social security number. It must be like 000000-0000 (6 + 4 digits) and if that OK a check to the database table that it is not in there already. If it is in there I want to check if logged in user is the user who had posted that record originally. If so another form loads with values from the database. If NOT the form must stop and a message goes to the user "This record belongs to another user" or something like that.

Step 1
Check if it is a accepted value.
Step 2
Check if it already exist.
Step 3
Check who owns it. If current user - Load a form with values from database. If another user owns it - Stop the form and print out a message.
Step 4
If it not in database continue to page 2

That is what I want to do and I will figure it out somehow. For perhaps a try and error method is the only way to do this when my skills of reading code is not good enough.

//JeLu

I have figured out this

A Query to get values from table

$db =& JFactory::getDBO();
$query = "
SELECT *
  FROM `#__chronoforms_data`
  WHERE `pnr` = {$db->quote( $pnr )};
"; 

To get the id of the user who orginally added the record

$db->setQuery($query);
$rows = $db->loadObject();
$actual_user = $rows->cf_user_id;

To check if the user "owns" the record and load a prefilled form

if ( $actual_user == $user_id) {
   return 'event_a';
}

And if it is another user stop and print message

elseif ( $actual_user != $user_id) {
   return 'event_d';
}

But here is the check finnished and it will never get to event_c because elseif-statement is true everytime actual_user is wrong or empty

else {
return 'event_c';

Everything else seems to work ecept that I now can´t add a new record 😢

Can I ask for something else in the "elseif" part? that only is tru if pnr is true but cf_user_id does NOT match?

//JeLu

Oh my!

Bob I did what you said and it looks like it works 😀

Step by step and it works (I hope)

This is the code that seems to work for me:

<?php
$user =& JFactory::getUser();
$user_id = $user->id;
$pnr = '';
if ( isset( $form->data['pnr'] ) ) {
  $pnr = $form->data['pnr'];
}
if ( !preg_match( "/^\d{6}\-\d{4}$/", $pnr ) ) {
  return 'event_b';
}
$db =& JFactory::getDBO();
$query = "
SELECT *
  FROM `#__chronoforms_data`
  WHERE `pnr` = {$db->quote( $pnr )};
"; 
$db->setQuery($query);
$rows = $db->loadObject();
$actual_user = $rows->cf_user_id;
$pnr_exist = $rows->pnr;

if ( $actual_user == $user_id) {
   return 'event_a';
}
elseif ( $pnr_exist ) {
   return 'event_d';
}

else {
return 'event_c';
}?>

Thank you Bob! I needed someone to get me to think a bit. Does the code look OK?

//JeLu

Hi JeLu,

Well done :-)

and, yes it looks OK to me.

Bob