UPDATE query to add 1 to multiple columns

Question

I have a website for my football team. I have some statistics and so on in there, for example how many games each player has played and so on.

What I would like, is for me to write in the names of every player in the game and then update the db table with +1 in matches column for each player.

I just made a frontend with form fields like this:

<input type="text" name="playername[]"><br>
<input type="text" name="playername[]"><br>
...

and so on, for 14 players.

In the backend, I wrote this:

$jinput = JFactory::getApplication()->input;
$playername = $jinput->getString('playername');

foreach ($playername as $key => $k) {

    $fields = array(
        $db->quoteName('matches') . ' = ' . $db->quoteName('matches') . ' + 1 '
    );

    $conditions = array(
        $db->quoteName('name') . ' = ' . $db->quote($k)
    );

    $query->update($db->quoteName('my_players'))->set($fields)->where($conditions);    

    $db->setQuery($query);
    $results = $db->execute();
}

But it only adds 1 match to the first name in the list. I tried

echo $k;

and it prints out all of the names. Can you help me?

Answer 1

Change the following:

$playername = $jinput->getString('playername');

to:

$playername = $jinput->get('playername','','array');

That should give you an array with all of the names you enter into your form. You may want to sanitize that array and remove any empty results prior to your DB query so you don't end up with players that have no name. An example of this would be entering 14 players for week one, but week two several players were absent and you only entered 10 records. You would have 4 records that are empty in the array.

Answer 2

The get() parameters are: [fieldname], [default value], [filter].

Good coding practice dictates that you should always endeavor to minimize the total number of calls to the database. Iterated UPDATE calls can be avoided here.

You can bother to remove empty values and duplicates, but it won't matter with my recommended snippet, because UPDATES will only occur on name matches and we are only sending one query.

I am calling array_map() to apply $db->q() to each value -- thus correctly quoting all values in $playernames. *note the use of use($db) -- this variable must be passed into the function scope to work. After preparing the values, they are joined together using commas to create a single comma-separated string.

$jinput = JFactory::getApplication()->input;
$playernames = $jinput->get('playername', [], 'array');

$db = JFactory::getDBO();
$quoted_csv = implode(',', array_map(function($n)use($db){return $db->q($n);}, $playernames));
try {
    $query = $db->getQuery(true)
                ->update($db->qn('my_players'))
                ->set($db->qn('matches') . ' = ' . $db->qn('matches') . ' + 1')
                ->where($db->qn('name') . " IN ($quoted_csv)");
    echo $query->dump();    // of course, don't do this on your live/public site
    $db->setQuery($query);
    $db->execute();
    echo "<br>Updated " , $db->getAffectedRows() , " row(s)";
} catch (Exception $e) {
    echo $e->getMessage();  // of course, don't do this on your live/public site
}

So if you passed a $playernames array with this set of data:

array("Lionel Messi", "Cristiano Ronaldo", "Neymar", "David O'Leary");

Then the dumped query would look like this:

UPDATE `my_players`
SET `matches` = `matches` + 1
WHERE `name` IN ('Lionel Messi', 'Cristiano Ronaldo', 'Neymar', 'David O\'Leary')

And assuming you had all four of those names in your database table, they would each have their match value incremented by one.

(I only tested this snippet up to the dump() line. I didn't bother mocking up a db table and testing the affectedRows(). Here is an sqlfiddle demo if you want to see how the query works.)