How to achieve this

I'm struggling a bit with a 'simple' scenario (at least I think) and read through tons of post of the forum, but still have not found the final solution :-(

Scenario:
I have a table with two colums 'lotnummer' (varchar) and 'gebruikt' (text) in a jos_tablename
Users can enter a unique code in the form, which then should be validated against jos_tablename.lotnummer, but only if the status of column 'gebruikt' is 'no'
In SQL: select * from tablenaam where gebruikt= 'no'
When the number entered in the form matches a record in the table, the record in the table must be updated: gebruikt='yes'

Then an email is send and so more stuff is done.

The issue is that I can't find the right syntax/structure to accomplish this.

In my form I have an DB Multi Record loader and by using that, I can select the entries from my table (they are shown in the form because I use Datadisplay on during develop/test.
But how to I get the returned data (array) in the next part of my form, the on Submit?
In there I have Custom Server Side Validation that for now reads:
<?php
$ok_values = array(
'12345',
'ABCDE',
'Wimpie'
);

if ( !in_array($form->data['lotnummer'], $ok_values) ) {
$form->validation_errors['lotnummer'] = "Hier komt een foutmelding";
return false;
}
?>

But instead of using these hardcoded values, I want to use the data from the On Load section of the form. How ???

And then the second step is how to update the table in the Custom Code piece of the form so the lotnumnumer gets the status gebruikt=yes (so it will not be selectable for the next time).

Attached is a screenprint of my form as it currently is.

I hope I've expressed cleary enough what I want to achieve, if not feel free to ask for more info.

Thanks and kind regards,
Sjaak

Hello svherpen,
I'm not a Chrono professional, but after checking your new post, I think that the following links may help:
How do I show a 'loading' message when the form submits?
How do I show a form in a module?
How do I show a form in an article?
How do I prevent a list of previous results showing?
How can I show/hide a textarea when a checkbox is clicked?
How can I show a thanks message if I use the plug-in or module?
How can I show a form in a lightbox?
How can I build a chart to show data?
How can I show a form on my site?
How can I show a Thanks Message then redirect the user?
How can I show data linked to a drop-down option?
P.S: I'm just an automated service😉

As you said, the may help but not be accurate. 😟
That's completely the case in this particular situation, but nice service by having an autoresponder!

Hi Sjaak,

The screenshot didn't make into your post. In these forums you have to upload the file before submitting the post.

One way to do this is to repeat the DB Multi Record Loader in the On Submit event. Or you could just add Custom Code to query the table and check the current value.

Bob

Hmm, to bad that I did follow the wrong sequence because now I cannot attach a permanent image to this post
But I'll try it slightly differently, by uploading it to my webserver and then insert the url here:
http://www.vhac.nl/TMP/screenprint.png

It's about the Customer Server Side Validation where I want to put a dynamic array based on the query
select * from jos_tablename where gebruikt= 'no'
If this returns data, I know a valid number was entered and I can continue in the OnSuccess section, if not then I'll end up in the OnFail which displays an error message.

The problem is that I'm not a pure PHP programmer, so I don't know the correct syntax to be used for the SQL query.

The same problem for my second question, how to use the correct PHP syntax in the OnSuccess section (also via Custom Code) to update the jos_tablename.
It has to be something like
<?php
update jos_tablename set gebruikt='yes' where lotnummer=$form->data['lotnummer'];
?>

So if you can point me in the wright direction by maybe providing me a code example I can try to get this accomplished.

Hope this will make it more clear and let me tell you that I really appriciate any support you can give me!

Regards,
Sjaak

Hi Bob,
Trying to implement your suggestion to work with DB Multi Record Loader in the On Submit event.
Still no luck ...

Debugger shows the next output:
Data Array:
Array
(
[option] => com_chronoforms
[tmpl] => component
[chronoform] => Sweepstake-Copy
[event] => submit
[Itemid] =>
[Naam] => sjaak
[Email] => svherpen@gmail.com
[lotnummer] => 12345
[Controleer] => Controleer
[930af426944a85e9f1b92d965f336c17] => 1
)
Validation Errors:
Array
(
)

Debug Data
db_multi_record_loader
SELECT `temp`.* FROM `jos_lotnummers` AS `temp` WHERE temp.gebruikt='nee' AND $form->data['lotnummer']=temp.lotnummer

And then I end up in the OnEmpty result, which is wrong because the combination of lotnummer=12345 and gebruikt=nee does exisit in the table jos_lotnummers.

In the configuration of DB Multi record loader I've only selected to use ModelID = temp, the databasetable jo_lotnummer and the where clause, all other fields have been left default.

So I guess it must be something wrong in the syntax of the WHERE clause, but I can't find the correct usage in other posts of the forum.

As always, any suggestion to help me find the right direction is more than welcome!

Sjaak

Hi Sjaak,

Exactly, the where is wrong, here it how it should be:

AND temp.lotnummer = "<?php echo $form->data['lotnummer']; ?>

If you are still starting with Chronoforms of if this is a new form then I suggest you use v5 instead, which can be installed with v4, just keep your form as its and install v5 then try this.

Regards,
Max

Thanks Max, this hint brings me 1 step further, I'm now able to perform the correct next step.

That next step will be to update the table jos_lotnummers and set the field gebruikt='yes' where the lotnummer corresponds with the filled in number on the form.
I thought of doing that with a piece of custom code in the On Record Found section like this:

<?php echo "Update jos_lotunmmers set jos_lotnummers.gebruikt='ja' where jos_lotnummers.lotnummer=$form->data['lotnummer']"; ?>

But that seems to be wrong, as I nothing gets updated and I get a debugmessage showing:
Array
(
)
Update jos_lotunmmers set jos_lotnummers.gebruikt='ja' where jos_lotnummers.lotnummer=Array['lotnummer']

And that array is obviously not working, it should be a single value included in ''.

Once I have this form up and running, I will make a backup of my site and try to upgrade to V5 as you suggest, but my first goal is to get this form active.

Thanks again,
Sjaak

Hi Sjaak,

you can update any field by populating its value in the data array and then saving the data again using a "db save" action, it should update the record as long as the primary key value exists in the data array.

Please post a screenshot for your form's events section and the current debug data!

Regards,
Max

Hi Max,
Thanks again for your patience and willingness to help, I do appriciate it.
I also like to send/share my form once it's completed and works as expected, since it does some very basic, straight forward stuff that might help a lot of other community users when trying to achieve the same.
It will also save you as forum administrators time, since you can simply refer to the Showcase section where the form can be downloaded.

I've embedded the URL to a 2 page PDF form that explains/shows the steps I'm trying to achieve as you requested. Hope this will give you enough information to clearly understand my problem.
http://www.vhac.nl/TMP/SweepstakeForm.pdf

This code will not execute:

<?php echo "Update `jos_lotnummers` set `jos_lotnummers.used`='yes' where 
`jos_lotnummers.lotnummer`='$form->data['lotnummer']'" ?>

Please try this:

<?php
$db = JFactory::getDbo();
$sql = "Update `jos_lotnummers` set `jos_lotnummers.used`='yes' where 
`jos_lotnummers.lotnummer`='".$form->data['lotnummer']."'";
$db->setQuery$sql);
$result = $db->query();
?>

You may still have some problems with the $sql string, please check the URL below for how to update records using the Joomla api:
http://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase

Regards,
Max

Hi Max,
Nearly there, I've found the correct construction to update the table using a fixed value '12345', but now that needs to be replaced by the formdata.

<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
// Fields to update.
$fields = $db->quoteName('used') . ' = "yes"';
// Conditions for which records should be updated.
$conditions = $db->quoteName('lotnummer') . ' = "12345" ';
$query->update($db->quoteName('#__lotnummers'))->set($fields)->where($conditions);
$db->setQuery($query);
$result = $db->query();
?>

So how do I write the correct syntax in the $conditions line?
I tried

$conditions = $db->quoteName('lotnummer') . ' = "$form->data['lotnummer']" ';

but that does not work, it gives an error:

Parse error: syntax error, unexpected T_STRING in /home/deb25520n3/domains/sojohank.nl/public_html/administrator/components/com_chronoforms/form_actions/custom_code/custom_code.php(19) : eval()'d code on line 10

HI svherpen,

Please try

$conditions = "{$db->quoteName('lotnummer')} = '{$form->data['lotnummer']}' ";

which adds quotes round the lotnummer, these are needed if the value is a string rather than a number.

Bob

Thanks very much Max and GreyHead for you replies, ultimately my form is working as expected !!

Can I share/upload this to your forum so others can benefit from my experience and therefore save you time for not having to reply to posts but simply refer to a working sample??

Sjaak

One more question about the validation statement in the DB Record loader section.
I noticed that the validation seems to be case independant, i.e. using Upper/Lower characters does not give a difference.
Is that normal behavior? Can it be forced to be case sensitive?

Debug Data
db_record_loader
SELECT * FROM `jos_lotnummers` AS `temp` WHERE used='no' AND temp.lotnummer = "abcDE"

This results in record found, where the database value actually is 'ABCDE'

Its a MySQL thing, you can find more info here:
http://stackoverflow.com/questions/3936967/mysql-case-insensitive-select

Try to change your code to:

AND BINARY temp.lotnummer = "abcDE"

and check if it works ?