1

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?

  • Is this all of the code? It appears that you are missing your closing bracket for the foreach loop. – maleman Apr 20 '18 at 19:42
  • Oh .. no I have the closing bracket :) Just forgot it here .. – Daniel Jensen Apr 23 '18 at 6:00
0

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.

  • The OP is executing an UPDATE query, so if any of the playernames are empty, they simply will not apply to any existing rows. Perhaps you mistakenly read the question as being an INSERT query or something. – mickmackusa Oct 8 at 20:53
  • Update or Insert doesn't matter. The OP is submitting an array in his form. getString is looking for a single string, not an array of values. For any operation he would need to iterate through the array, which is why only a single update is taking place. – Terry Carter Oct 9 at 21:21
  • I am referring to this statement in your answer: "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." ...okay, I guess I am misunderstanding your meaning -- you mean "no name elements" versus "no name rows in the db table". Okay. – mickmackusa Oct 9 at 21:44
  • No. Example: OP enters player names for name field 1,2,3, and then 5 skipping field 4. The OP should loop through the array BEFORE inserting or updating any records to remove any elements of the array that have an empty name field. If the OP is hard coding the name fields into his form those will always be submitted empty or not. Why waste the resources trying to update/insert an empty record? – Terry Carter Oct 11 at 15:08
  • A simple way to clear out any array elements that are empty would be to use $playername = array_filter($playername); before the update loop. – Terry Carter Oct 11 at 15:14
0

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.