29 July 2009, 17:11

I am having trouble displaying a PostgreSQL query to a .tpl page.  I have used my own methods to query the PostgreSQL database and not how Mahara usually does this sort of thing.  Here is the code that I am using on my index.php page:

include_once 'pg_db_config.php';

$dbcon = pg_connect($pgsql_conn);

global $USER;

$user = $USER->id;

$resultarray=array(); 

$current_studies = pg_query($dbcon, "SELECT areas_of_study.description FROM areas_of_study WHERE usr = $user"); 

Then in my While loop I have the following:

 while ($query_results = pg_fetch_assoc($current_studies)){
$resultarray =array( 
array(
'description' => $query_results[description]
),
);
}

I then assign the array '$resultarray' to the bottom like so:

$smarty = smarty();
$smarty->assign('current_studies', $resultarray);
$smarty->display('account/areasofstudy.tpl');

The section in the areasofstudy.tpl that calls this array looks like this:

<--{foreach from=$current_studies item=item}-->
<--{$item.description|escape}--><br />
<--{/foreach}--> 

The only thing that displays is the last record that was selected from the database.  So, the thing that I'm having issues is that it's not displaying all of the records.  It just displays the last record that the query selected.

If anybody could shed some light on this I would very much appreciate it.  Also, I don't have to implement it in this manner.  Meaning, if someone wants to show me how to do this how Mahara regularly makes these kinds of queries, that would be ok too!  (I think if there is a database call Mahara usually uses an included index.json.php page EX. root/account/activity/ ... has a index.php as well as a index.json.php page to handle server requests that are called from the index.php page)

29 July 2009, 18:59

Hi - use the inbuilt Mahara functions and save yourself time and effort

$data = get_record('areas_of_study', 'usr', $user);  // get one row from areas_of_study where usr = $user

There's also get_records_array, insert_record, update_record, delete_records etc... see lib/dml.php for all of the functions, and all over the codebase are examples of them in use

ps: your code is broken because in your loop, you should probably be going $resultarray[] = instead of $resultarray =