Convert MYSQL result to form data

I am trying to learn so bare with me. I have figured out how to sum the total of a column for a particular user but I don't know how to convert the query result to be able to use curly brackets for display in emails,thank you pages, etc.
Here is the code I am using:

<?php
$user = &JFactory::getUser();
$db =& JFactory::getDBO();

// Make a MySQL Connection

$query = "SELECT *, SUM(balance) FROM my_table  WHERE userid = $user->id";  
	 
$result = mysql_query($query) or die(mysql_error());
?>

Can anyone help please? Thank you
Gabe

Hi Gabe,

Please try:

<?php
$user = &JFactory::getUser();
$db =& JFactory::getDBO();

// Make a MySQL Connection

$query = "
  SELECT *, SUM(balance) 
    FROM `#__my_table`
    WHERE `userid` = {$user->id} ;
"; 
$db->setQuery($query);
$form->data['balance'] = $db->getResult();   
?>

Then you can use {balance}

Bob

Hi Bob,
Thanks but it's not working. However, it is trying to do something because when I look at debugger, the {balance} field has no value whereas if I don't use the code, the balance returns a result but not the calculated sum value. Any thoughts?

Hi Gabe,

Try it with just SELECT SUM(balance) leaving out the *,

Bob

No go. I don't get it. The logic seems like it should work??

Hi Gabe,

I suggest that you test the query in PHPMyAdmin to see if it works there. I just got it to work on a table with an amount column.

Bob

Hi Bob
The mysql query works fine but I can't get the data to convert to use the curly brackets. Were you able to use the curly brackets with the sum result on a Thank you page?
Thanks
Gabe

Hi Gabe,

As long as the data is in the $form->data array before the Thank You Page action runs the curly replace should find it.

Bob

I got it to work! Not sure how but it does 😀
This is the code I used

<?php
$user = &JFactory::getUser();
$db =& JFactory::getDBO();

// Make a MySQL Connection

$query = "SELECT *, SUM(balance) FROM my_table  WHERE userid = $user->id"; 
$result = mysql_query($query) or die(mysql_error());
// Print out result
echo "<br />";
while($row = mysql_fetch_array($result)){
	$form->data['balance'] = $row['SUM(balance)'];
}
?>