Easter Sale 2024

  • 1

Read this first!

We do not monitor these forums. The forum is provided to exchange information and experience with other users ONLY. Forum responses are not guaranteed.

However, please submit a ticket if you have an active subscription and wish to receive support. Our ticketing system is the only way of getting in touch with RSJoomla! and receiving the official RSJoomla! Customer Support.

For more information, the Support Policy is located here.

Thank you!

TOPIC: SQL query

SQL query 3 weeks 5 days ago #43378

Hi,
I get an error with a query included in the "Script called on form process" within the form.
I want to get the SUM of numbers of the column FieldName of submission values.

$db = JFactory::getDbo();
$db->setQuery("SELECT SUM(`FieldValue`) FROM #_rsform_submission_values WHERE FormID='1' AND `FieldName`='nbrpersons'");
$db->setQuery($query);

Error is: "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 '' at line 1"

Also, for debugging purpose, is there a way to dump $query to understand what is being executed? Currently the error occurs before the the print_r or the var_dump that I insert before the setQuery. Also, Joomla debug option maximum does not provide a clue.

Regards,
Roland
The administrator has disabled public write access.

SQL query 3 weeks 4 days ago #43379

  • iceferret
  • iceferret's Avatar
  • OFFLINE
  • Gold Boarder
  • Posts: 216
  • Thank you received: 57
Your problem is I think the #_rsform_submissions_value, you need to use a double underscore. Here's a quick explanation

The double underscore #__ is a Joomla database prefix placeholder.
By using #__ in your queries instead of hardcoding the prefix, you ensure that your queries are compatible with any Joomla installation, regardless of the specific prefix used.

For example, if your Joomla database table prefix is jos_, #__rsform_submission_values in your query would be interpreted as jos_rsform_submission_values. If your prefix is different, Joomla will substitute it accordingly.

Try this
// Get the Joomla database object
$db = JFactory::getDbo();
 
// Construct your query
$query = $db->getQuery(true);
$query->select('SUM(`FieldValue`)')
      ->from('#__rsform_submission_values')
      ->where('FormID = 1')
      ->where('FieldName = ' . $db->quote('nbrpersons'));
 
// Set the query
$db->setQuery($query);
 
// Execute the query and retrieve the result
$result = $db->loadResult();
 
// Now, $result contains the sum of FieldValue for FormID 1 and FieldName 'nbrpersons'

You should be able to see the query if you substitute the $result= line with
// Dump the constructed query
echo $query->dump();
If you can keep your head when all about you are losing theirs, then you obviously don't understand the situation!
Last Edit: 3 weeks 4 days ago by iceferret.
The administrator has disabled public write access.

SQL query 3 weeks 4 days ago #43380

Yes, indeed! It was a dumb modification where I forgot the extra _
Thanks a lot!
The administrator has disabled public write access.
  • 1

Read this first!

We do not monitor these forums. The forum is provided to exchange information and experience with other users ONLY. Forum responses are not guaranteed.

However, please submit a ticket if you have an active subscription and wish to receive support. Our ticketing system is the only way of getting in touch with RSJoomla! and receiving the official RSJoomla! Customer Support.

For more information, the Support Policy is located here.

Thank you!