Increment Field Based on Another Fields value

[abel408]

Hello all,

I've been struggling on how to do this. I would like to have a field automatically increment itself if another fields value is equal to a certain value.

For example, if the field "score" is equal to 3 on any record, I want to then increment the field "wins" automatically. The wins field is located in another list.

Would anyone know how to create something like this? Thanks for any help you can provide.

-Chris

 

[cheesegrits]

You'd have to do this with a PHP form submission plugin. Check the submitted data, and manually update the other table. Not entirely trivial, although you should be able to find past threads on the forums which give examples of doing this.

-- hugh

 

[abel408]

Thanks Hugh!

I'm not the best at scripting... Is this the right track?

$home_score=$views = $formModel->_formData['schedule___home_score'];
$visitor_score=$views = $formModel->_formData['schedule___visitor_score'];

if ($home_score = 3){
$winning_team_name=$views = $formModel->_formData['schedule___home_team'];
$lossing_team_name=$views = $formModel->_formData['schedule___visitor_team'];

$sql = "UPDATE team SET wins = wins + 1 WHERE team_name=$winning_team_name";
$database->setQuery($sql);
$database->query();
$sql = "UPDATE team SET losses = losses + 1 WHERE team_name=$losing_team_name";
$database->setQuery($sql);
$database->query();
}

else {
$winning_team_name=$views = $formModel->_formData['schedule___visitor_team'];
$lossing_team_name=$views = $formModel->_formData['schedule___home_team'];

$sql = "UPDATE team SET wins = wins + 1 WHERE team_name=$winning_team_name";
$database->setQuery($sql);
$database->query();
$sql = "UPDATE team SET losses = losses + 1 WHERE team_name=$losing_team_name";
$database->setQuery($sql);
$database->query();
}

Would it be OnAfterProcess? What if someone goes back and edits the game? I wouldn't want the code to update the value then... Only when someone is submitting a score for the first time.

 

[cheesegrits]

That's close, a few issues though.

We changed from _formData to formData (no _ prefix) a while back

Not sure why you are doing the =$views thing, get rid of that.

If any of those formData[] fields are database joins or dropdowns, you'll want the _raw placeholder, and they'll probably be arrays, so you'll need to check, like this ...

$winning_team_name = $formModel->formData['schedule___home_team_raw'];
$winnind_team_name = is_array($winning_team_name) ? $winning_team_name[0] : $winning_team_name;

And you should always quote any variables you use in your queries ...

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->update('team')->set('losses = losses + 1')->where('team_name = ' . $myDb->quote($losing_team_name);
$myDb->setQuery($myQuery);
$myDb->execute();

-- hugh

 

[abel408]

Thanks Hugh,

This helps a lot. Unfortunately it isn't working yet. Is there a way to debug what is not working with the php? Not sure if fabrikdebug=1 will help me here....

$home_score= $formModel->formData['schedule___home_score'];
$visitor_score= $formModel->formData['schedule___visitor_score'];

if ($home_score = "3"){
$winning_team_name= $formModel->formData['schedule___home_team_raw'];
$winnind_team_name = is_array($winning_team_name) ? $winning_team_name[0] : $winning_team_name;
$losing_team_name= $formModel->formData['schedule___visiting_team_raw'];
$losing_team_name = is_array($losing_team_name) ? $losing_team_name[0] : $losing_team_name;

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->update('teams')->set('wins = wins + 1')->where('team_name = ' . $myDb->quote($winning_team_name);
$myDb->setQuery($myQuery);
$myDb->execute();
$myQuery->update('teams')->set('losses = losses + 1')->where('team_name = ' . $myDb->quote($losing_team_name);
$myDb->setQuery($myQuery);
$myDb->execute();
}

else {
$winning_team_name= $formModel->formData['schedule___visiting_team_raw'];
$winnind_team_name = is_array($winning_team_name) ? $winning_team_name[0] : $winning_team_name;
$losing_team_name= $formModel->formData['schedule___home_team_raw'];
$losing_team_name = is_array($losing_team_name) ? $losing_team_name[0] : $losing_team_name;

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->update('team')->set('wins = wins + 1')->where('team_name = ' . $myDb->quote($winning_team_name);
$myDb->setQuery($myQuery);
$myDb->execute();
$myQuery->update('team')->set('losses = losses + 1')->where('team_name = ' . $myDb->quote($losing_team_name);
$myDb->setQuery($myQuery);
$myDb->execute();
}

Thanks!

 

[cheesegrits]

Install ...

http://extensions.joomla.org/extension/j-dump

... (remember to enable the plugin after installing), then you can do ...

dump($losing_team, 'Losing team');

... and after submitting the form, you'll get a popup with any values you dump out. You'll probaby have to tell your browser to allow popups for your site.

Start dumping out the values of your variables, make sure they look right.

You can also dump out the queries, by casting them to a string ...

dump((string) $myQuery, 'loss query');

... and try running the query by hand in phpMyAdmin, make sure it does what it should.

-- hugh

 

[cheesegrits]

Oh, and whenever you re-use a query, you have to clear it ...

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
// OOOPS I missed a close paren
$myQuery->update('team')->set('wins = wins + 1')->where('team_name = ' . $myDb->quote($winning_team_name));
$myDb->setQuery($myQuery);
$myDb->execute();
// CLEAR the query before re-using it, otherwise you are just adding clauses to the previous usage
// OOPS I missed a close paren
$myQuery->clear()->update('team')->set('losses = losses + 1')->where('team_name = ' . $myDb->quote($losing_team_name));
$myDb->setQuery($myQuery);
$myDb->execute();

Also I just notices I missed a closing paren on a few lines. Same-same in the other chunk of code.

-- hugh

 

[abel408]

Thanks. Where would I call the dump? I tried to call to it inside my if and else statement, but it doesn't do anything. No popup shows up. I have the plugin enabled and my popup blocker turned off. Thanks for the help so far!

if ($home_score = "3"){
$winning_team_name= $formModel->formData['schedule___home_team_raw'];
$winnind_team_name = is_array($winning_team_name) ? $winning_team_name[0] : $winning_team_name;
$losing_team_name= $formModel->formData['schedule___visiting_team_raw'];
$losing_team_name = is_array($losing_team_name) ? $losing_team_name[0] : $losing_team_name;

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->update('varsity')->set('wins = wins + 1')->where('team_name = ' . $myDb->quote($winning_team_name);
$myDb->setQuery($myQuery);
$myDb->execute();
$myQuery->clear()->update('varsity')->set('losses = losses + 1')->where('team_name = ' . $myDb->quote($losing_team_name);
$myDb->setQuery($myQuery);
$myDb->execute();
dump($losing_team_name, 'Losing team');
}

 

[abel408]

Ok... so if I take out this part:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->update('teams')->set('wins = wins + 1')->where('team_name = ' . $myDb->quote($winning_team_name);
$myDb->setQuery($myQuery);
$myDb->execute();
$myQuery->clear()->update('teams')->set('losses = losses + 1')->where('team_name = ' . $myDb->quote($losing_team_name);
$myDb->setQuery($myQuery);
$myDb->execute();

j!dump seems to work, so something is probably wrong there although I cannot figure it out.

Also, I have to use $formModel->fullFormData['field_name'] instead of just $formModel->formData['field_name']. When I do this, J!Dump shows the correct scores and teams.


I ran :

UPDATE teams SET wins = wins + 1 WHERE team_name=My Team Name;

And everything runs smoothly in mysql.

 

[abel408]

Finally got this to work. Something was wrong with the sql query. This is what I came up with:

$home_score = $formModel->fullFormData['schedule___ht_match_score_raw'];
$home_score = is_array($home_score) ? $home_score[0] : $home_score;
$visitor_score = $formModel->fullFormData['schedule___vt_match_score_raw'];
$visitor_score = is_array($visitor_score) ? $visitor_score[0] : $visitor_score;


if ($home_score == 3){
$winning_team_name = $formModel->fullFormData['schedule___home_team_raw'];
$winning_team_name = is_array($winning_team_name) ? $winning_team_name[0] : $winning_team_name;
$losing_team_name = $formModel->fullFormData['schedule___visiting_team_raw'];
$losing_team_name = is_array($losing_team_name) ? $losing_team_name[0] : $losing_team_name;

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
      -> update('teams')
      -> set('wins = wins + 1')
      -> where('id = '. $db->quote($winning_team_name));
$db->setQuery($query);
$found = (int) $db->execute();
$query
      -> clear()
      -> update('teams')
      -> set('losses = losses + 1')
      -> where('id = '. $db->quote($losing_team_name));
$db->setQuery($query);
$found = (int) $db->execute();
}

else {
$winning_team_name = $formModel->fullFormData['schedule___visiting_team_raw'];
$winning_team_name = is_array($winning_team_name) ? $winning_team_name[0] : $winning_team_name;
$losing_team_name = $formModel->fullFormData['schedule___home_team_raw'];
$losing_team_name = is_array($losing_team_name) ? $losing_team_name[0] : $losing_team_name;


$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
      -> update('teams')
      -> set('wins = wins + 1')
      -> where('id = '. $db->quote($winning_team_name));
$db->setQuery($query);
$found = (int) $db->execute();
$query
      -> clear()
      -> update('teams')
      -> set('losses = losses + 1')
      -> where('id = '. $db->quote($losing_team_name));
$db->setQuery($query);
$found = (int) $db->execute();
}

Only problem I see with this is that it will run every time the record is updated... Meaning that if someone makes a mistake to the recorded game and wants to edit the record, the php script will run again and give an additional win and loss to each team.