Joomla + RSform get from database

Question

I want to add number of confirmed forms from users. Every time they use the form it will show their order number - different for every logged in user.

I've adden hidden field "counter" and in Script called on form process i added code:

$db   = JFactory::getDbo(); 
$query   = $db->getQuery(true); 
$user = JFactory::getUser();
$db->setQuery("SELECT SUM(`confirmed`) AS suma FROM `#__rsform_submissions` WHERE `UserId`='id';");
$_POST['form']['counter'] = $db->loadResult()+1;

It shows 1 all the time.

So I want to show sum + 1 of confirmed submisions from logged in user. How can I change the code to make it tow work?

Answer 1

Simple mistake. It should be:

// Get a database connection.
$db   = JFactory::getDbo();
$query   = $db->getQuery(true);

// Get the current logged in user.
$user = JFactory::getUser();
echo $user->id;

$db->setQuery("SELECT SUM(`confirmed`) AS suma FROM `josez_rsform_submissions` WHERE `UserId`='$user->id';");
$_POST['form']['counter'] = $db->loadResult()+1;

Answer 2

Another way to accomplish this is to COUNT() the number of rows that contain the qualifying user id and a confirmed value of 1.

Untested Query:

$db     = JFactory::getDbo(); 
$user   = JFactory::getUser();    
$query  = $db->getQuery(true)
    ->select("COUNT(1) + 1")
    ->from("#__rsform_submissions")
    ->where([
        "UserId = " . (int)$user->id,
        "confirmed = 1"
    ]);
$db->setQuery($query);
$_POST['form']['counter'] = $db->loadResult();

MySQL can be very efficient with COUNT(). The COUNT(1) isn't actually counting 1's from any where, it is just an efficient way of saying count the number of rows.