Showing a list of subscribers by date

Dear Frederic, Bob,
Now we have finally succeeded to get a "counter" in the form, a nwe challenge, at least for me.
Based on Frederic's comments in teh old topic, I quess it would be possible to show in a diffenend form who (the"members) have suvbscribed to play teh game at a selected date (Wedstrijddatum)

But I have no clue whare or how to start....
It would be nice if the clubmember selects a date (Wedstrijddatum)
and get on his screen
nr. firstname middlename lastname
firstname middlename lastname>>all varchar(255) > string?

This is the code I have now in

<?
$db =& JFactory::getDBO();
$pst = "SELECT COUNT(*) AS %s FROM %s
  WHERE %s = %s";
$query = sprintf(
  $pst,
  $db->nameQuote('items'),
  $db->nameQuote('jos_chronoforms_TABLENAME'),
  $db->nameQuote('Wedstrijddatum'),
  $db->Quote(JRequest::getString('Wedstrijddatum')));
$db->setQuery($query);
$result = $db->loadObject();
$total = $result->items+1;
JRequest::setVar('total', $total, 'post');

?>

Gents, any idea's on this topic..🙄

Hi Spabo,

I think it will work if you change the MySQL query to get the names of the players who are subscribed. Please check the MySQL docs for the syntax to use.

Bob

How, what ??

Hi Spabo & Bob,
Sorry for the delay..

At a first thought, this sounds like an application for ChronoConnectivity, which is designed to list the records (or a given subset of them) of a database table.

Doing it the hard way though, I'd probably do it something like this:

<?php
//The form code

//Fetch a list of different dates with registrations
$db = JFactory::getDBO();
$query = 'SELECT %2$s FROM %1$s GROUP BY %2$s';
$pst = sprintf($query,
  $db->nameQuote('jos_chronoforms_TABLENAME'),
  $db->nameQuote('Wedstrijddatum')
);
$db->setQuery($pst);
$items = $db->loadObjectList();
?><select name="datum">
 <option value="">Select a date</option>
<?php
foreach ($items as $item) {
  echo('<option value="' . $item->Wedstrijddatum . '">' . $item->Wedstrijddatum . '</option>');
}
?>
</select>
<input type="submit" value="Show registrants" />

<?php
//The on submit code

$db = JFactory::getDBO();
$query = 'SELECT %s, %s, %s FROM %s WHERE %s = %s';
$pst = sprintf($query,
  $db->nameQuote('firstname'),
  $db->nameQuote('middlename'),
  $db->nameQuote('lastname'),
  $db->nameQuote('jos_chronoforms_TABLENAME'),
  $db->nameQuote('Wedstrijddatum'),
  $db->Quote(JRequest::getString('datum'))
);
$db->setQuery($pst);
$data =& $db->loadObjectList();

?><table><thead><tr>
<th>First name</th><th>Middle name</th><th>Last name</th></tr></thead>
<tbody>
<?php
foreach ($items as $item) {
  echo('<tr><td>' . $item->firstname . '</td><td>' . $item->middlename . '</td><td>' . $item->lastname . '</td></tr>');
}
?>
</tbody></table>

/Fredrik

Hi Fredric
It gives this error
Warning: Invalid argument supplied for foreach() in /home/spanclub/domains/golfparkspandersbosch-club.nl/public_html/components/com_chronocontact/libraries/customcode.php(51) : eval()'d code on line 18
Based on your on submit code, it would be in this part

<?php
foreach ($items as $item) {
  echo('<tr><td>' . $item->firstname . '</td><td>' . $item->middlename . '</td><td>' . $item->lastname . '</td></tr>');
}
?>

Next to this, should this:
$db->Quote(JRequest::getString('datum'))not be
$db->Quote(JRequest::getString('Wedstrijddatum'))

Hi Spabo,
No, I just mixed up a few variable names... In the first code, I used $items for the result set, and iterated over it using a foreach-loop.
In the second code, I used $data instead, but still iterated over $items..
Locate the line below:

$data =& $db->loadObjectList();

And change it into:

$items =& $db->loadObjectList();

It should be "..JRequest::getString('datum')...", since that's what I called the input in the form code. You could use Wedstrijddatum as well, but you'd have to change the form html code accordingly then.

/Fredrik

I owe you a couple of beers !!!
Excellent !!
WOW🤔 🤔 🤔

Just need to re-style the form, but the mechanism works !!