Q: Table not updating

[markbhai1]

Hi,

I have been doing some work trying to get the 'PHP code to execute on form submission' working, I have worked out quite a bit, but still cant seem to get the data to update on the table and therefore think it may be somenthing I am doing wrong.

I am taking data from my form and doing some changes to it the the 'php', then I am trying to write it back to the table. The problem I have is that none of my changes are recording to the db.

Here is my code - (There is more work to do on the date function).


global $database;
$nextcall = mosGetParam($_POST, 'properties___date_nextcall');
$hma = mosGetParam($_POST, 'properties___neg_id');

if ($nextcall == '')
{
if ($hma <> '')
{
$uid = mosGetParam($_POST, 'properties___property_id');
$motivation = mosGetParam($_POST, 'properties___motivation_id');
$event = '1'; // event is now equal to Intro Call
if ($motivation == 1) $review = 'reviewa'; //motivation is high
if ($motivation == 2) $review = 'reviewb'; //motivation is medium
if ($motivation == 3) $review = 'reviewc'; //motivation is low
$sql = "SELECT $review FROM events WHERE event_id = $event";
mysql_select_db('events');
$reviewperiod = mysql_query($sql);

$nextcall = date(Y-m-d);

$sql = "UPDATE properties SET date_nextcall = $nextcall WHERE property_id = $uid";
mysql_select_db('properties');
$result = mysql_query($sql);

}
}
//echo $database->getQuery();
//echo $database->getErrorMsg();
//exit;

I think I may be writing to the db and then fabrik is overwriting with what was originally on the form???

I am using the 'after Fabrik has processed the form (simple)' option.

Thanks

Mark.

 

[CyberFabrik]

$sql = "UPDATE properties SET date_nextcall = $nextcall WHERE property_id = $uid";
mysql_select_db('properties');
$result = mysql_query($sql);

Hi Mark,

you have 2 things to check. First can you double check that your queries are correct? You can do so by using phpmyadmin. Have your code in there (with hardcoded values for the WHERE clauses) and see if they execute correctly.

If so, then it might be that Fabrik is ovewriting your data. But first make sure your php code is sound...

 

[cheesegrits]

Which version of Fabrik is this?

As long as the element you want to update is in the same table (which it looks like it is), there's no need to do any db work yourself.

Just hang the script on the "before form is processed (simple eval)", and access the $aData[] array directly. NOTE that in 2.0 it's not $aData[], I think it's $this->_data->_row[] or some such.

So instead of writing your data back to the properties table by hand, just ...

$aData['properties___date_nextcall'] = $nextcall;

... and Fabrik will go ahead and write that to the db for you as if it had been submitted with the form.

Also, not sure what you are doing with $reviewperiod? Is that supposed to be added to $nextcall?

Also, you'll need to wrap quotes around the date format, date('Y-m-d');

Also, is your events table really in a separate database, not the default Joomla db?

Also ... do you have a jump page set on this form? If so, remove it for now, so you'll see any errors thrown by your PHP. Otherwise Fabrik will redirect to the jump page without rendering those errors to the browser.

-- hugh

 

[markbhai1]

Hugh.

What a fabulous answer!

You can probably tell that I am still learning php and making some schoolboy errors, but you hint on using the $aData is the lifesaver for me as I would never have worked that out for myself.

In answer to your questions

Version 1.0.6 (so $aData it is then!)

$reviewperiod is the bit which will be added to the date to make the date x days in future . This was removed until I could figure out why my date wasn't working!

Quotes around Date - this will help towards getting my date to work! - but I also suspect I have an issue with the my SQL table which is not allowing a NULL entry.

The events table is part of the Joomal db so I guess that this bit of php will be changing!

I did have a jump page but removed it after reviewing some earlier posts.

Thanks once again for your help. - Please consider this thread closed (although I am sure that I will be asking more!).

Mark.

 

[cheesegrits]

One suggestion ... assuming the 'motivation' element is a dropdown, why not simply make the dd values be the number of days you want to add? You could do this with a static dropdown, or a database join (assuming you have am apporpriate field in the events table).

Then you wouldn't have to worry about grabbing those values in a form submit.

-- hugh

 

[markbhai1]

The motivation is a dropdown but my contact days differ on both motivation and status. eg if a property is for sale and the customer is keen to sell (high motivation) then we will contact evet 5 days with an update, however if motivation is low we will contact every 21 days. We ,ay have different periods if we have sold a property or if it is on hold.

The way I have handled this is to use a motivation table and a status table. The status table contains the periods 'reviewa' (high motivation)...'reviewb' (med motivation) etc.

Cheers

Mark.