[SOLVED] PHP Form Plugin - Help for complicated query needed

[marcq]

Hi,

I would like to run a query with the PHP Form plugin "after data stored, before calculation (OnBeforeCalculations)" :

I can create simple queries, but I'm stuck when it starts to be more complicated. I tried to create the query, but it doesn't work at all. I guess it is because I'm updating 2 tables at the time !?

Do someone have a little time to help me solve this issue since I don't know how to update two different tables in one query.

Thanks a lot in advance,

Cheers

Marc

$db = JFactory::getDbo();
$userid=$data['gprh_fabrik_user_registration___userid'];
$memberblock=$data['gprh_fabrik_user_registration___user_block'];
$status=$data['gprh_fabrik_user_registration___status'];
 
if ($status === 'Membre actif' AND $memberblock === 'Oui')
{
$query = $db->getQuery(true);
/* I need to update this two tables if status = Membre actif & memberblock = Oui */
$query = "UPDATE gprh_users SET block = '1' WHERE id = '$userid' AND UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
}
else if ($status === 'Membre actif' AND $memberblock === 'Non')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid' AND UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
}
else if ($status === 'Ancien Membre' AND $memberblock === 'Oui')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '1' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
}
else ($status == 'Ancien Membre' AND $memberblock === 'Non')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
}

 

[troester]

You must split into two updates
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid'";
$db->setQuery($query);
$db->execute();

$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();

 

[marcq]

You must split into two updates
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid'";
$db->setQuery($query);
$db->execute();

$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();

Thanks troester for you reply. I just tried it and only the first query is updating the values. "date_cancellation" field is never updated... But I perhaps misunderstood your solution ?!

QUERY in Form PHP plugin :

$db = JFactory::getDbo();
$userid=$data['gprh_fabrik_user_registration___userid'];
$memberblock=$data['gprh_fabrik_user_registration___user_block'];
$status=$data['gprh_fabrik_user_registration___status'];
 
if ($status === 'Membre actif' AND $memberblock === 'Oui')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '1' WHERE id = '$userid'";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
}
else if ($status === 'Membre actif' AND $memberblock === 'Non')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid'";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
}
else if ($status === 'Ancien Membre' AND $memberblock === 'Oui')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '1' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = NOW() WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
}
else ($status == 'Ancien Membre' AND $memberblock === 'Non')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = NOW() WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
}

 

[troester]

Ok, I just splitted your query.
I assume in your table gprh_fabrik_user_registration it's not the id column but some other element holding the userid, so something like
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE user_id = '$userid' ";

 

[marcq]

Ok, I just splitted your query.
I assume in your table gprh_fabrik_user_registration it's not the id column but some other element holding the userid, so something like
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE user_id = '$userid' ";

Of course it was "userid" instead of "id" ! But it still doesn't work I tried with

$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE userid = '$userid' ";
 
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = null WHERE userid = '$userid' ";

but value still remains in field instead of being update with '0000-00-00 etc' or Null, can't figure out why.

 

[troester]

The first query is working, so $userid is correct?
Try
UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE userid =one-existing-userid
directly in phpMyAdmin, is it working there?

 

[marcq]

The first query is working, so $userid is correct?
Try
UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE userid =one-existing-userid
directly in phpMyAdmin, is it working there?

Thanks troester, I tried in PhPMyAdmin and it works, strange not ?

 

[troester]

It's not necessary to repeat anything in any post.
Try
UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE userid =one-existing-userid
directly in phpMyAdmin, is it working there?

 

[marcq]

Yes it works fine troester

 

[marcq]

Last update :

Query still not working in PHP Form Plugin. I tried it in PHPMyAdmin and it works, fields are updated.

Concerning the "Block" field which need to be updated in the gprh_users (jos_users) table
I tried with the Juser Form Plugin to sync on edit the "Block" field with the "Block" field value from the gprh_fabrik_user_registration "user_block" field . And it also doesn't work.

Concerning the "date_cancellation" field from the gprh_fabrik_user_registration table
I have no clue how to set the value to Null or 0000-00-00 00:00:00.

The best way would be to use the PHP Form Plugin, but the following query isn't working :

$db = JFactory::getDbo();
$userid=$data['gprh_fabrik_user_registration___userid'];
$memberblock=$data['gprh_fabrik_user_registration___user_block'];
$status=$data['gprh_fabrik_user_registration___status'];
 
if ($status === 'Membre actif' AND $memberblock === 'Oui')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '1' WHERE id = '$userid'";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE userid = '$userid' ";
$db->setQuery($query);
$db->execute();
}
else if ($status === 'Membre actif' AND $memberblock === 'Non')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid'";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = '0000-00-00 00:00:00' WHERE userid = '$userid' ";
$db->setQuery($query);
$db->execute();
}
else if ($status === 'Ancien Membre' AND $memberblock === 'Oui')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '1' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = NOW() WHERE userid = '$userid' ";
$db->setQuery($query);
$db->execute();
}
else ($status == 'Ancien Membre' AND $memberblock === 'Non')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation= NOW() WHERE userid = '$userid' ";
$db->setQuery($query);
$db->execute();
 
}

 

[troester]

In some post above you said that "only the first query is updating the values", so I didn't check your code in detail.
Is it really working?

Here is how to access form data in php plugin
http://fabrikar.com/forums/index.php?wiki/php-form-plugin/#accessing-form-data
For debugging add
var_dump($your-var1,$your-var2);exit;
to see if you really get what you need.

Here is how to setup a (database type independend) update (a $query ="UPDATE ..."; should do also)
http://fabrikar.com/forums/index.php?wiki/common-php-tasks/#update
For debugging add
$result = $db->execute();
and var_dump $result.

Do one step after the other, set Joomla error reporting to max.

Synchronizing the "block" should do if you have set up the jUser plugin correctly.

 

[marcq]

Thanks troester, sorry to bother you with this.
I will do one step after the other and I'll get back to you.

 

[marcq]

Hi troester, I followed your instructions :

Var_dump

I get the value I need (please see screenshot -> values displayed)

Parameter used :

$db = JFactory::getDbo();
$userid=$data['gprh_fabrik_user_registration___userid'];
$memberblock=$data['gprh_fabrik_user_registration___user_block'];
$status=$data['gprh_fabrik_user_registration___status'];
 
var_dump($userid,$memberblock,$status);exit;

Queries

I have setup the queries, but it doesn't work. Neither the "Block" field is updated in the gprh_users (jos_users) nor the "date_cancellation" field in the gprh_fabrik_user_registration table.

Could you please just check my query ?

$userid = '{gprh_fabrik_user_registration___userid}';
$memberblock = '{gprh_fabrik_user_registration___user_block}';
$status = '{gprh_fabrik_user_registration___status}';
 
$db = JFactory::getDbo();
 
if ($status === 'Membre actif' AND $memberblock === 'Oui')
{
// SET BLOCK VALUE TO 1 (BLOCKED)
$query = $db->getQuery(true);
 
$query
-> update('gprh_users AS u')
-> set('u.block = '1'')
-> where('u.id = '. (int) $userid);
 
$db->setQuery($query);
 
// Run the query
$Run = (int) $db->execute();
 
// SET CANCELLATION DATE TO NULL OR 0
$query = $db->getQuery(true);
 
$query
-> update('gprh_fabrik_user_registration AS r')
-> set('r.date_cancellation = '0000-00-00 00:00:00'')
-> where('u.userid = '. (int) $userid);
 
$db->setQuery($query);
 
// Run the query
$Run = (int) $db->execute();
}
else if ($status === 'Membre actif' AND $memberblock === 'Non')
{
// SET BLOCK VALUE TO 0 (UNBLOCKED)
$query = $db->getQuery(true);
 
$query
-> update('gprh_users AS u')
-> set('u.block = '0'')
-> where('u.id = '. (int) $userid);
 
$db->setQuery($query);
 
// Run the query
$Run = (int) $db->execute();
 
// SET CANCELLATION DATE TO NULL OR 0
$query = $db->getQuery(true);
 
$query
-> update('gprh_fabrik_user_registration AS r')
-> set('r.date_cancellation = '0000-00-00 00:00:00'')
-> where('u.userid = '. (int) $userid);
 
$db->setQuery($query);
 
// Run the query
$Run = (int) $db->execute();
}
else if ($status === 'Ancien Membre' AND $memberblock === 'Oui')
{
// SET BLOCK VALUE TO 1 (BLOCKED)
$query = $db->getQuery(true);
 
$query
-> update('gprh_users AS u')
-> set('u.block = '1'')
-> where('u.id = '. (int) $userid);
 
$db->setQuery($query);
 
// Run the query
$Run = (int) $db->execute();
 
// SET CANCELLATION DATE TO NOW
$query = $db->getQuery(true);
 
$query
-> update('gprh_fabrik_user_registration AS r')
-> set('r.date_cancellation = NOW()')
-> where('u.userid = '. (int) $userid);
 
$db->setQuery($query);
 
// Run the query
$Run = (int) $db->execute();
}
else ($status == 'Ancien Membre' AND $memberblock === 'Non')
{
// SET BLOCK VALUE TO 0 (UNBLOCKED)
$query = $db->getQuery(true);
 
$query
-> update('gprh_users AS u')
-> set('u.block = '0'')
-> where('u.id = '. (int) $userid);
 
$db->setQuery($query);
 
// Run the query
$Run = (int) $db->execute();
 
// SET CANCELLATION DATE TO NOW
$query = $db->getQuery(true);
 
$query
-> update('gprh_fabrik_user_registration AS r')
-> set('r.date_cancellation = NOW()')
-> where('u.userid = '. (int) $userid);
 
$db->setQuery($query);
 
// Run the query
$Run = (int) $db->execute();
}

 

[troester]

As I've said: step by step (one case, one query...)

- include debug output in your if/else if parts (echo 'something'; or var_dump(...);exit; )
- debug the execute result
- use the code highlighter to show your code in the post

-> set('u.block = '1'')

this is wrong (use ' " " ' or vice versa)
- try with && instead of AND

if ($status === 'Membre actif' && $memberblock === 'Oui')

 

[marcq]

troester thanks for your tips. I think I found something strange after having worked on it this morning.

I have installed the J!Dump extension in order to see the value that are sent after form submission.

I have used the original query syntax that works well.

I have tested one query and when the query works, I added a new one and so on.

When I used the "if", "else if" statements nothing works, but after having made a mistake by letting "if" instead of "else if" I found out that it works :

$db = JFactory::getDbo();
$userid=$data['gprh_fabrik_user_registration___userid'];
$memberblock=$data['gprh_fabrik_user_registration___user_block'];
$status=$data['gprh_fabrik_user_registration___status'];
$cancellation=$data['gprh_fabrik_user_registration___date_cancellation'];
 
dump($userid, 'userid');
dump($memberblock, 'memberblock');
dump($status, 'status');
dump($cancellation, 'date cancellation');
 
if ($status === 'Membre actif' AND $memberblock === 'Oui')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '1' WHERE id = '$userid'";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = null WHERE userid = '$userid' ";
$db->setQuery($query);
$db->execute();
}
if ($status === 'Membre actif' AND $memberblock === 'Non')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid'";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = null WHERE userid = '$userid' ";
$db->setQuery($query);
$db->execute();
}
if ($status === 'Ancien membre' AND $memberblock === 'Oui')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '1' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = NOW() WHERE userid = '$userid' ";
$db->setQuery($query);
$db->execute();
}
if ($status === 'Ancien membre' AND $memberblock === 'Non')
{
$query = $db->getQuery(true);
$query = "UPDATE gprh_users SET block = '0' WHERE id = '$userid' ";
$db->setQuery($query);
$db->execute();
 
$query = $db->getQuery(true);
$query = "UPDATE gprh_fabrik_user_registration SET date_cancellation = NOW() WHERE userid = '$userid' ";
$db->setQuery($query);
$db->execute();
}

So if I use "if" instead of "else if" this is working.

My problem is solved, but since I'm not a sql or a professional developer is this the way to write a query and using "if" statements instead of "else if" ? I found nothing on the Interet which explains that we can use multiple "if" statement...

Thanks anyway a lot troester to have pushed me to be more concentrated and to follow a straight method of debugging, it helps me.

Cheers,

Marc