Row count related to the selection.

First of all, it was a great help from this forum to find a solution to "count" the nbr. of subscribers of a Chronoform (see earlier topics on this), thanks for this Fredrik and Bob!

Now I would like to explore if it is possible to relate the "counter" to a selected "Wedstrijddatum"

If selected date 1, it starts counting from nbr. 1 onwards
But if selected date 2, it should again start counting from nbr 1..

The reason for asking:
In this way I can keep all the data in the db-table and make a full csv download for a whole competitionseason.

I'v have taken the liberty to post this as a new topic, to have it separated from the previous one

The selected box is "Wedtrijddatum" , where the members can select the date, eg 01-01-2010, 01-02-2010 etc
This selected date is also kept in the db-table

This is the base code for the (row)counter

<?php
$db =& JFactory::getDBO();
$query = "SELECT COUNT(*) FROM " . $db->nameQuote("jos_chronoforms_CK_PITCHenPUTT");
$db->setQuery($query);
$total = $db->loadResult() + 1;
JRequest::setVar('total', $total, 'post');
?>

Gents, Hope you could help on this pls.😟

Hi SPABO,

I think that you need to add a WHERE clause to the MySQL

$query = "
  SELECT COUNT(*) 
    FROM `#__chronoforms_CK_PITCHenPUT`
    WHERE `Wedstrijddatum` = ".$db->quote('01-01-2010').";
  ";

Bob

Later: fixed miising ' after the date.

Bob, thanks,
to make sure:
should #_etc not be jos_chronoforms_etc?
for the other dates, should it than be

$query = "
  SELECT COUNT(*) 
    FROM `jos_chronoforms_CK_PITCHenPUT`
    WHERE `Wedstrijddatum` = ".$db->quote('01-01-2010).";
    WHERE `Wedstrijddatum` = ".$db->quote('01-02-2010).";
  ";

and so on??

Hi SPABO,

'#_ . . .' is correct, Joomla replaces the #_ with the prefix set for your site. 95% of the time it is 'jos' but just occasionally something else is used so the '#_' creates better code for others to use.

To extend a WHERE clause use OR*, not another WHERE

    WHERE `Wedstrijddatum` = ".$db->quote('01-01-2010)."
      OR `Wedstrijddatum` = ".$db->quote('01-02-2010).";

Bob

* or AND if you want both conditions to apply.

Bob
I have put this code in now, but it does not work (parse error)

<?php
$db =& JFactory::getDBO();
$query = "
  SELECT COUNT(*) 
    FROM  " . $db->nameQuote("jos_chronoforms_CK_PITCHenPUTT");
    WHERE `Wedstrijddatum` = ".$db->quote('19-09-2010)."
    OR `Wedstrijddatum` = ".$db->quote('29-09-2010).";
$db->setQuery($query);
$total = $db->loadResult() + 1;
JRequest::setVar('total', $total, 'post');
//Also update the email subject, as requested:
$datum = JRequest::getString('Wedstrijddatum', date('Y-m-d'), 'post');
JRequest::setVar('subject', 'Nr.' . $total . ' Nieuwe inschrijving CK Pitch en Putt dd ' . $datum, 'post');
?>

Hi SPABO ,

Sorry, I missed a ' after the date

".$db->quote('29-09-2010').";

Bob

Still gets this error (anf thus "subject" doesnot show {total}

Parse error: syntax error, unexpected '`' in /home/spanclub/domains/golfparkspandersbosch-club.nl/public_html/components/com_chronocontact/libraries/customcode.php(64) : eval()'d code on line 6

Hi SPABO,

You have a couple of typos in this line

FROM  " . $db->nameQuote("jos_chronoforms_CK_PITCHenPUTT");

Should be like this

FROM  " . $db->nameQuote('#__chronoforms_CK_PITCHenPUTT')."

Bob

Whatever it is, it still does not not work properly
This is what I have in now

$query = "
  SELECT COUNT(*) 
    FROM  " . $db->nameQuote("jos_chronoforms_CK_PITCHenPUTT").";
    WHERE " . $db->quote('19-09-2010')."
    OR    " . $db->quote('29-09-2010').";
$db->setQuery($query);
$total = $db->loadResult() + 1;
JRequest::setVar('total', $total, 'post');

I give up now and return to the old script

Thanks anyway

Hi SPABO,

There's still a ; on the end of the FROM line.

Bob

Again spend time on thsi, but it still does not work
Let's forget the whole issue, unless tested by the moderators

Bob,
This issue still has my attention and trying to pick it up again
On the code I now hav, there is a syntax-error, but I can't find it!
Pls could you have a look?

<?php
$query = "
  SELECT COUNT(*) 
    FROM  " . $db->nameQuote("jos_chronoforms_CK_PITCHenPUTT")."
    WHERE " . $db->quote('19-09-2010')."
    OR    " . $db->quote('29-09-2010').";
$db->setQuery($query);
$total = $db->loadResult() + 1;
JRequest::setVar('total', $total, 'post');
$datum = JRequest::getString('Wedstrijddatum', date('Y-m-d'), 'post');
JRequest::setVar('subject_1', 'Nr.' . $total . ' Uw inschrijving CK P&P 2010 dd ' . $datum, 'post');
?>

Thanks in advance

Hi Spabo,

I don't immediately see a syntax error. Are you getting an error message with a line number?

But these lines won't work.

WHERE " . $db->quote('19-09-2010')."
  OR    " . $db->quote('29-09-2010')."

;
A WHERE caluse must be like WHERE `column_name` = 'value' and there is no `column_name` = part here.

Bob

In Dreamweaver it says on line 11

Getting a bit puzzled on the WHERE/OR function

I belief this came from your hand Bob

But if it should call a column_name, which column, where is it ?

Hi Spabo,

Whatever database table column you you stored the dates in. See this post from earlier in the thread.

Bob

Sorry Bob
It simply gives errors
This is what I have now
Dreamweaver tells this is something wrong in lin 12

<?php
$db =& JFactory::getDBO();
$query = "
  SELECT COUNT(*) 
  FROM  " . $db->nameQuote('jos_chronoforms_CK_PITCHenPUTT_DINER')."
    WHERE `Wedstrijddatum` = ".$db->quote('19-09-2010')."
    OR `Wedstrijddatum` = ".$db->quote('29-09-2010').";
$db->setQuery($query);
$total = $db->loadResult() + 1;
JRequest::setVar('total', $total, 'post');
$datum = JRequest::getString('Wedstrijddatum', date('Y-m-d'), 'post');
JRequest::setVar('subject', 'Nr. ' . $total . ' Nieuwe inschrijving Pitch en Puttwedstrijd dd ' . $datum, 'post');
?>

Hi Spabo,

Actually you are missing the "; from the end of the query

    OR `Wedstrijddatum` = ".$db->quote('29-09-2010')." ;
";
$db->setQuery($query);

but Dreamweaver doesn't spot it for a few lines.

Bob

Ok Bob
This seemed to be the problem.
Pls find the new code, but....it does not start from 1 onwards once a new date is selected.
It still counts the nbr of rows in teh db-table...

<?php
$db =& JFactory::getDBO();
$query = "
  SELECT COUNT(*) 
  FROM  " . $db->nameQuote('jos_chronoforms_CK_PITCHenPUTT_DINER')."
    WHERE `Wedstrijddatum` = ".$db->quote('19-09-2010')."
    OR `Wedstrijddatum` = ".$db->quote('29-09-2010').";
	";
$db->setQuery($query);
$total = $db->loadResult() + 1;
JRequest::setVar('total', $total, 'post');
$datum = JRequest::getString('Wedstrijddatum', date('Y-m-d'), 'post');
JRequest::setVar('subject', 'Nr. ' . $total . ' Nieuwe inschrijving CK Pitch en Putt dd ' . $datum, 'post');
?>

Hi Spabo,

You have the dates hard-coded in here. How are you changing them?

Bob

Not sure what you mean Bob
The dates are agreed for each new year, so in December I change the dates for each form manually.
(in the formcodes)

Hi, I was notified to view the newest post made since my last visit, but I can't see further news🤨

Hi SPABO,

I guess there was a spam post that I had deleted (they are sadly quite common).

As far as I can see the count has to reset to 0 if you change the dates in the query and there are no matching results in the database. I have no idea why it would give you any other result.

Bob

Hi Bob
All the counts have been reset to 0, but the counter keeps on counting the rows only

<?php
$db =& JFactory::getDBO();
$query = "
  SELECT COUNT(*) 
  FROM  " . $db->nameQuote('jos_chronoforms_CK_PITCHenPUTT_DINER')."
    WHERE `Wedstrijddatum` = ".$db->quote('19-09-2010')."
    OR `Wedstrijddatum` = ".$db->quote('29-09-2010').";
	";
$db->setQuery($query);
$total = $db->loadResult() + 1;
JRequest::setVar('total', $total, 'post');
?>
<p> 
U bent nr. <? echo $total; ?> die zich heeft opgegeven. 
</p>

Hi Bob
This is still an old issue pending and as we are to the next year I would appreciate if I could get this work.

Anu ideas on this yet?

Tx in advance

Hi SPABO,

I'm not sure that there is anything that I can add. It's a straigthforward MySQL query. I suggest that you echo out the query that is being created and test it in PHPMyAdmin to see why it is giving you unexpected results.

You can use this code to display the query

$mainframe->enqueuemessage('$query: '.print_r($query, true));

Bob

Hi Bob, the SQl does not show errors, it keeps on counting teh numbers of rows in table, no matter which date has been selected.

It shoudl work like this:
date1, number of subscibers is 3
but when date2 will be selected, it should start counting from 1 onwards

this SQL shows 4 when the new date is selected

Hi SPABO,

It may not be showing an error message but it isn't giving you the *right* answer so you need to debug to work out what is happening. Please add the extra line and start to debug the MySQL expression.

Bob

Dear Bob
Back to this topic.
I really hope if there is a solutions at all.

The (row)counter as such works fine, but still struggling the count the number of subscribers for a selected date
My wish
A mechanism what "counts" the number of "people" who have selected a certain date in the chronoform

I'm working with a selectbox like
<option value="">Datum</option>
<option value="10-05-2010">10-05-2010</option>
<option value="13-06-2010">13-06-2010</option>
etc etc

So, it should count teh number of records for a specific selected date(filedname"Wedstrijddatum")

Hi SPABO,

I'm sorry, the queries look fine to me, perhaps you are using them in a way that doesn't work.

Where are you using this code and where do you expect to see the updated count?

Remember that PHP in the Form HTML is run **before** the page is loaded in the browser. So it cannot be altered by changes the user makes unless you either (a) reload the page or (b) use JavaScript to make some changes in the data or display of the page or (c) use Ajax to get fresh data from the server.

Bob

Bob
The problem is (I think) you don't understand what I would like.
The sql does count ALL the rows, regardless what date was selected

I belief it should not count the "rows", but the records which holds a specific date in it.

Hi Spabo,

Where are you using this code and where do you expect to see the updated count?

Bob

Bob
It shouls be in teh OnSubmit code
Now I have this in:

<?php
$db =& JFactory::getDBO();
$query = "
  SELECT COUNT(*) 
  FROM  " . $db->nameQuote('jos_chronoforms_CK_PITCHenPUTT_DINER')."
    WHERE `Wedstrijddatum` = ".$db->quote('19-09-2010')."
    OR `Wedstrijddatum` = ".$db->quote('29-09-2010').";
	";
$db->setQuery($query);
$total = $db->loadResult() + 1;
JRequest::setVar('total', $total, 'post');
$datum = JRequest::getString('Wedstrijddatum', date('Y-m-d'), 'post');
JRequest::setVar('subject', 'Nr. ' . $total . ' Nieuwe inschrijving CK Pitch en Putt dd ' . $datum, 'post');
?>

It should be (to my opinon) something like

WHEN `Wedstrijdatum` = ".$db->quote('19-09-2010')."
$db->setQuery($query);
$total = $db->loadResult(`19-09-2010`??????) + 1;
WHEN `Wedstrijdatum` = ".$db->quote('29-09-2010')."
$db->setQuery($query);
$total = $db->loadResult(`29-09-2010`??????) + 1;
etc
etc

Dear Bob,
Sorry to bother you again, but is there no way to count the total of records, in which a specific date/text etc is mentioned (in my case `Wedstrijddatum`)
I still have the following code, but the still counts the total nbr. of records
The field (Wedstrijddatum) in the chromoforms table is type VarChar(255)

<?php 
$db=& JFactory::getDBO (); 
$query= "SELECT COUNT(*) FROM `#__chronoforms_MAANDBEKER` ". 
        "WHERE `Wedstrijddatum` = ".$db->quote ('23-11-2010')." ".
        "OR `Wedstrijddatum` = ".$db->quote('23-12-2010'); 
$db->setQuery ($query); 
$total= $db->loadResult ()+ 1; 
JRequest::setVar ('total', $total, 'post'); 
?>

Hi SPABO,

The answer you get will be the number of records that match the query you have built. If the count is wrong it is because the query is wrong.

I suggest that you output the query string and check it carefully in PHPMyAdmin to see exactly what is happening.

Bob

PS Your code appears to have a number of stray spaces in it. I'm not sure where they come from.

Yes Bob
Indeed, I belief the query is wrong, but with all my respect, this came from your hand.

Quote
I suggest that you output the query string and check it carefully in PHPMyAdmin to see exactly what is happening
Unquote

No clue what you mean by thsi, and how to check !

Hi SPABO,

It may well have come from my hand. That doesn't change the simple fact that the result you get will match the query you use.

To output the query add

$mainframe->enqueuemessage('$query: '.print_r($query, true));

just before the

$db->setQuery ($query); 

line. This should show you the query that is being executed. Copy and paste this into PHPMyAdmin to see what results you get (you will need to replace the #_ table prefix with the correct value for your site).

Bob

Pls find what was returned

Fout
Er schijnt een fout te zitten in uw SQL-query. Mocht de MySQL-server een foutmelding hebben terug gegeven, probeer dan of u hiermee uw fout kunt oplossen.
ERROR: Onbekende Punctuatie String @ 1
STR: <?
SQL: <?php 
$db=& JFactory::getDBO ();<?php 
$db=& JFactory::getDBO ();<?php 
$db=& JFactory::getDBO ();<?php 
$db=& JFactory::getDBO ();<?php 
$db=& JFactory::getDBO ();<?php 
$db=& JFactory::getDBO ();<?php 
$db=& JFactory::getDBO ();<?php 
$db=& JFactory::getDBO ();

SQL-query: 
<?php $db=& JFactory::getDBO (); 

MySQL retourneerde:  
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php 
$db=& JFactory::getDBO ()' at line 1 

Any idea???

Hi Spabo,

As you can see that is mostly junk. So what is the query that was output from the string you added? Or, if that was the output where exactly did you add the extra line of code I gave you?

Bob

This what I ran in phpMyAdmin with SQL

<?php 
$db=& JFactory::getDBO (); 
$query= "SELECT COUNT(*) FROM `jos_chronoforms_CK_PITCHenPUTT_DINER` ". 
        "WHERE `Wedstrijddatum` = ".$db->quote ('23-11-2010')." ".
        "OR `Wedstrijddatum` = ".$db->quote('23-12-2010');
$mainframe->enqueuemessage('$query: '.print_r($query, true)); 
$db->setQuery ($query); 
$total= $db->loadResult ()+ 1; 
JRequest::setVar ('total', $total, 'post'); 
?>

Hi SPABO,

Once again I have no real idea what you are doing.

The added line should show you a message like this.
[attachment=0]18-11-2010 10-40-42.png[/attachment]
You need to select and copy the MySQL query

SELECT COUNT(*) FROM `jos_chronoforms_CK_PITCHenPUTT_DINER` WHERE `Wedstrijddatum` = '23-11-2010' OR `Wedstrijddatum` = '23-12-2010'

and paste that into an SQL query in PHPMyAdmin.

Bob

I give up Bob

When I run the code with SQL in phpMyadmin, it only says : Counts 2

Hi SPABO,

Is that the 'correct' answer - that is the one that you expect to see?

You are adding 1 to it in the code so if you check the value of $total it should be 3 ??

Bob

Hi Bob
I'm afraid not, that's the issue

I would like to see it as follows
4 members subscribe with "wedstrijddatum" , say DATUM 1
The counter says $total = 4
However, when the next members choose DATUM 2, the counter should start again from 1 onwards

In other words , the script should "count" the number of records in which a specific `Wedstrijddatum` is mentioned

Hi SPABO,

Then your SELECT statement should be like

SELECT COUNT(*) FROM `jos_chronoforms_CK_PITCHenPUTT_DINER` WHERE `Wedstrijddatum` = '23-11-2010'

or

SELECT COUNT(*) FROM `jos_chronoforms_CK_PITCHenPUTT_DINER` WHERE `Wedstrijddatum` = '23-12-2010'

Bob

Sorry Bob, this gives errors again and is still not what I was asking for

Hi SPABO,

What errors exactly??

These queries will do what you asked for here:

In other words, the script should "count" the number of records in which a specific `Wedstrijddatum` is mentioned

Bob

$query: SELECT COUNT(*) FROM `jos_chronoforms_CK_PITCHenPUTT_DINER` WHERE `Wedstrijddatum` = '23-11-2010' OR `Wedstrijddatum` = '29-12-2010';
$query: SELECT COUNT(*) FROM `jos_chronoforms_CK_PITCHenPUTT_DINER` WHERE `Wedstrijddatum` = '23-11-2010' OR `Wedstrijddatum` = '29-12-2010';
$query: SELECT COUNT(*) FROM `jos_chronoforms_CK_PITCHenPUTT_DINER` WHERE `Wedstrijddatum` = '23-11-2010'

Hi SPABO,

Those are outputs from the $mainframe->enqueue code - they are diagnostics, not errors.

Bob

Well Bob
Now I have thsi code in

<?php
$db =& JFactory::getDBO();
$query = "
SELECT COUNT(*) FROM `jos_chronoforms_CK_PITCHenPUTT_DINER` WHERE `Wedstrijddatum` = '23-11-2010' 
";
$db->setQuery($query);
$total = $db->loadResult() + 1;
JRequest::setVar('total', $total, 'post');
$datum = JRequest::getString('Wedstrijddatum', date('Y-m-d'), 'post');
JRequest::setVar('subject', 'Nr. ' . $total . ' Nieuwe inschrijving CK Pitch en Putt dd ' . $datum, 'post');
?>

But....Even when I select eg Wedstrijddatum 29-12-2010 in the form , it still counts onwards from 1 > 2 etc

Hi Bob
I quess there is no sulution to get this working,
Thanks so far for your patience
Rgds
Kees

Hi Kees,

I'm sure that it's very simple to get it working. But I find it impossible to understand what the problem is so really can’t help.

Bob

Hi Bob & Kees,
I've read through this thread a few times, and I'm also struggling to understand exactly what you are doing, as well as the expected behavior. I think I've finally got a rough idea now though...

[list=1]

You have one form your visitors fill out, where they select a date.

This information is then stored in #__chronoforms_CK_PITCHenPUTT_DINER

You then have a second form, which you use to show the number of registrations for each date

On your second form page, you have a drop-down where you select a date to be counted.

You then want to use this form input to specify which entries in your database table to count

[/list:o]

If this is what you'd like, then you are going about it the wrong way. You could not use the OR operator, as you don't want to count two hardcoded dates. Further, you should not have hardcoded dates in the query; these should instead be retrieved from the posted form data.

If this is correct, then you should use a piece of code somewhat like this:

<?
...
$pst = "SELECT COUNT(*) AS %s FROM %s
  WHERE %s = %s";
$query = sprintf(
  $pst,
  $db->nameQuote('items')
  $db->nameQuote('#__chronoforms_CK_PITCHenPUTT_DINER'),
  $db->nameQuote('Wedstrijddatum'),        //This should match the table column name you are matching against
  $db->Quote(JRequest::getString('Datum')) //This should match the form input name in your "list-posts" form
);
$db->setQuery($query);
$result = $db->loadObject();

$total = $result->items;
...

/Fredrik

Hi Frederic,
No, it not so complicated as you describe

This is the idea
1.I have one form our visitors fill out, where they select a date.(SELECT BOX)
2.This information is then stored in #__chronoforms_CK_PITCHenPUTT_DINER(TABLE)
3.On submit the "visitor" should get a message : You are nr. $total etc
4.This $total should the counted records in the table which contain the selected date.
5.Also this $total can be used to get in the SUBJECT of the emails
6.When this could be inplemented, I can "store" the data, say for one year, and at the end of the year export a CSV file, to make further analysis.
7. Once I have the CSV file, I can "empty" the table again

Hope I expressed myself correctly

Not sure what you mean by "hardcoded"

The fieldname in the record of the table is type varchar(255)
This in the selectbox
name="Wedstrijddatum"
<option value="">Datum</option>
<option value="23-01-2011">23-01-2011</option>
<option value="20-02-2011">20-02-2011</option>
<option value="20-03-2011">20-03-2011</option>
</select>

Hi Kees,
Then I understand better...
Nevertheless, you should be able to use the very same code for this purpose as well. Just remember to change this line to fetch the right data:

$db->Quote(JRequest::getString('Datum'))

Should in this case be:

$db->Quote(JRequest::getString('Wedstrijddatum'))

/Fredrik

Dear Fredric
This is now in:

<?
$pst = "SELECT COUNT(*) AS %s FROM %s
  WHERE %s = %s";
$query = sprintf(
  $pst,
  $db->nameQuote('items'),
  $db->nameQuote('jos_chronoforms_PITCHenPUTT2'),
  $db->nameQuote('Wedstrijddatum'),
  $db->Quote(JRequest::getString('Wedstrijddatum')));
$db->setQuery($query);
$result = $db->loadObject();
$total = $result->items;
?> 
<p> 
U bent nr. 
<? echo $total ?> 
die zich heeft opgegeven. 
</p

This came up !

Fatal error: Call to a member function nameQuote() on a non-object in /home/spanclub/domains/golfparkspandersbosch-club.nl/public_html/components/com_chronocontact/libraries/customcode.php(51) : eval()'d code on line 6

Hi Kees,
You still need to assign the $db variable an instance of the JDatabase class, just like the previous postings of code:

$db =& JFactory::getDBO();

/Fredrik

Hi Fredric
Almost there!! But the "counter"starts with 0, how to get it started with 1

HOLD FIRE !!!

-->items + 1 !!!!

Hi Kees,
Most likely, the query is executed before the new record is entered into the database.
You could either alter the settings on the RunOrder tab (making sure "autogenerated blocks" is run prior "onsubmit blocks"), or add a mathematical expression to your query to increase the value by one

SELECT COUNT(*) + 1 AS ...

/Fredrik

EUREKA !!!
Many thanks to all for your assistance
I did some initial testing and it seems excactly what I was looking for
I will now make some csv files to see if I indeed have captured the corect data!
Pls find the final code

<?
$db =& JFactory::getDBO();
$pst = "SELECT COUNT(*) AS %s FROM %s
  WHERE %s = %s";
$query = sprintf(
  $pst,
  $db->nameQuote('items'),
  $db->nameQuote('jos_chronoforms_PITCHenPUTT2'),
  $db->nameQuote('Wedstrijddatum'),
  $db->Quote(JRequest::getString('Wedstrijddatum')));
$db->setQuery($query);
$result = $db->loadObject();
$total = $result->items+1;
?> 
<p> 
U bent nr.<? echo $total ?> die zich heeft ingeschreven. 
</p

Final call

Everything is working fine, als the email subjects etc

But how do I get the "total" in the email templates.

I used to do it with {total} , but this is not working now

Hi Kees,
That is once again due to the Run-Order in the form. The database storage (autogenerated code), as well as the "on submit - after email", are run after the email(s) has been sent.
You'd have to change the "saving data/emails order" to "before email" on the DB Connections tab. Further, you'll also have to move the php-code to the "on submit - before email" block.

/Fredrik

Hi Fredric

I think it's solved

I have added this line in the script

JRequest::setVar('total', $total, 'post');