Smarty Forum Index Smarty
WARNING: All discussion is moving to https://reddit.com/r/smarty, please go there! This forum will be closing soon.

Setting up MySQL recordset as array to be parsed by Smarty

 
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Smarty Forum Index -> Tips and Tricks
View previous topic :: View next topic  
Author Message
TGKnIght
Smarty Junkie


Joined: 07 Sep 2005
Posts: 580
Location: Philadelphia, PA

PostPosted: Tue Oct 17, 2006 3:12 pm    Post subject: Setting up MySQL recordset as array to be parsed by Smarty Reply with quote

Here is an example of how to setup your MySQL queries so that they can easily be passed into and parsed by Smarty.

The result set will be a multidimensional array. The first dimension of the array is numeric and represents each row of your result set. The second dimension of the array is an associative array where the names of the indexes are the field names you specified in your query.
Code:

//Init the array to hold the results
$results = Array();

//Try to connect to the DB
$mysql = mysql_connect($host, $user, $password) or die('Could not connect: '.mysql_error());

//Setup the SQL statement
$sql = 'SELECT SQL_CALC_FOUND_ROWS name, address, city, state, zip FROM users';

//Run the query
$result = mysql_query($sql, $mysql);

//Get the results as an associative array and put each row into the numerically indexed array
while ($row = mysql_fetch_assoc($myresult))
   $results[] = $row;

//Now we get set up the query to get the number of results that were returned
$sql = 'SELECT FOUND_ROWS() as foundrows';

//Run the query
$result = mysql_query($sql, $mysql);

//Store the resulting row
$row = mysql_fetch_assoc($result);

//Get the total rows
$total = $row['foundrows'];

//Assign the results to smarty
$smarty->assign('results', $results);
$smarty->assign('total', $total);

//Display the Smarty template
$smarty->display('index.tpl');


Your resultset should look something like this:
Code:

Array (2)
0 => Array (5)
  name => John Doe
  address => 123 Anonymous Way
  city => Springfield
  state => IL
  zip => 62707
1 => Array (5)
  name => Jane Doe
  address => 777 Luxury Way
  city => Beverly Hills
  state => CA
  zip => 90210


And here is an example using {section} to parse the results
Code:

Displaying {$total} records :<br />
{section name=nr loop=$results}
   {$results[nr].name}
   {$results[nr].address}
   {$results[nr].city}
   {$results[nr].state}
   {$results[nr].zip}<br />
{sectionelse}
   <h1>No results found!</h1>
{/section}

_________________
Smarty site with one index.php controller file
Working with MySQL and Smarty
SmartyColumnSort
Custom Smarty Javascript Debug Template


Last edited by TGKnIght on Thu Oct 19, 2006 1:48 am; edited 1 time in total
Back to top
View user's profile Send private message Visit poster's website
radar
Smarty Regular


Joined: 06 Oct 2006
Posts: 64

PostPosted: Tue Oct 17, 2006 9:16 pm    Post subject: Reply with quote

here is a continuation of this... I figured out another way to do this and it works great... I wrote a function to do all the processing for me....

Code:

function assign_md_array($query, $cnt, $result) {
for ( $row = 0; $row < $cnt && $array = mysql_fetch_assoc($query); $row++ ) {
   foreach ($array as $key => $value) {
  $data[$row][$key] = $value;
  }
   }
   $this->assign($result, $data);
   }


Okay so as you can tell my function is short and to the point... it works beautifully and does everything dynamically so that you can use it on any query. The reason why I put in the $result is so that you can use it on anything... This means if you wanted it to show the array on your logs.tpl you'd put in a result of say 'logs' and if you wanted it to show up on your users.tpl you'd throw in a result of like 'users'...

So here is the basic syntax (the way I'm doing it on my hacklogs case)...

Code:

<?php
$data = mysql_query("SELECT * FROM hacklogs");
            $cnt = mysql_num_rows($data);
            $turbo->assign('cnt', $cnt);
            if ($cnt != "0") {
            $turbo->assign_md_array($data, $cnt, 'data');
            }
            $page = "hacklogs";            ?>


Anyway thats it - hope you find it useful in this fashion as well..
Back to top
View user's profile Send private message
TGKnIght
Smarty Junkie


Joined: 07 Sep 2005
Posts: 580
Location: Philadelphia, PA

PostPosted: Thu Oct 19, 2006 1:18 am    Post subject: Reply with quote

Should you decide to implement Pagination, the way I have set it up in the first post will get you the total records that match the query as if the LIMIT clause wasn't there.

I assume your function was meant to be a part of a subclass of Smarty. If you are going to try to encapsulate logic, you should encapsulate ALL of it, not just part... Here is a what you were trying to do:
Code:

/**
* Setup query as multidimensional array and pass into Smarty
* @param String $query The SQL query
* @param String $result_name (Optional) What you want the result to be called in Smarty (if not specified the result set will be returned from this function and can be assigned)
* @return Int $count Number of results found in query
*/
function assign_md_array($query, $result_name='') {
   //Setup the data object
   $data = mysql_query($query);

   //Pump the data into an array
   $results = array();
   while ($row = mysql_fetch_assoc($data))
      $results[] = $row;

   //If a result name was passed in let's assign it to Smarty with that name and also the count of the records
   if ($result_name != '') {
      $this->assign($result_name, $results);
      $this->assign($result_name.'_count', count($results));
   } else
      return $results;
}


Now you can do

Code:

$sql = 'SELECT * FROM hacklogs';
$smarty->assign_md_array($sql, 'data');


And also because we are always passing in an Array for the results whether or not there are any $results, you can now use {foreachelse} or {sectionelse} without the need for something like {if $cnt == 0}
_________________
Smarty site with one index.php controller file
Working with MySQL and Smarty
SmartyColumnSort
Custom Smarty Javascript Debug Template
Back to top
View user's profile Send private message Visit poster's website
arac-softec
Smarty Rookie


Joined: 23 Sep 2008
Posts: 16

PostPosted: Wed Sep 24, 2008 3:18 pm    Post subject: Reply with quote

TGKnIght wrote:
Should you decide to implement Pagination, the way I have set it up in the first post will get you the total records that match the query as if the LIMIT clause wasn't there.

I assume your function was meant to be a part of a subclass of Smarty. If you are going to try to encapsulate logic, you should encapsulate ALL of it, not just part... Here is a what you were trying to do:
Code:

/**
* Setup query as multidimensional array and pass into Smarty
* @param String $query The SQL query
* @param String $result_name (Optional) What you want the result to be called in Smarty (if not specified the result set will be returned from this function and can be assigned)
* @return Int $count Number of results found in query
*/
function assign_md_array($query, $result_name='') {
   //Setup the data object
   $data = mysql_query($query);

   //Pump the data into an array
   $results = array();
   while ($row = mysql_fetch_assoc($data))
      $results[] = $row;

   //If a result name was passed in let's assign it to Smarty with that name and also the count of the records
   if ($result_name != '') {
      $this->assign($result_name, $results);
      $this->assign($result_name.'_count', count($results));
   } else
      return $results;
}


Now you can do

Code:

$sql = 'SELECT * FROM hacklogs';
$smarty->assign_md_array($sql, 'data');


And also because we are always passing in an Array for the results whether or not there are any $results, you can now use {foreachelse} or {sectionelse} without the need for something like {if $cnt == 0}


Well, i tried to implement this on my site, and it gives an error:
Quote:
Fatal error: Call to undefined method Smarty::assign_md_array() in ........\index.php on line 80


The code i'm using is this one, on index.php:
Code:
...
require_once('include/func_var.php');

//Constrói o array dos paises
$sql = 'SELECT distinctrow pais.cod, pais.desc FROM '.DB_NAME.'.pais
 INNER JOIN '.DB_NAME.'.series ON pais.cod=series.Pais ORDER BY pais.Desc ASC';
$smarty->assign_md_array($sql, 'pdata');
...


on func_var.php:
Code:

....
function assign_md_array($query, $result_name='') {
   //Setup the data object
   $data = mysql_query($query);

   //Pump the data into an array
   $results = array();
   while ($row = mysql_fetch_assoc($data))
      $results[] = $row;

   //If a result name was passed in let's assign it to Smarty with that name and also the count of the records
   if ($result_name != '') {
      $this->assign($result_name, $results);
      $this->assign($result_name.'_count', count($results));
   } else
      return $results;

....


What i'm i doing wrong here, since i've only changed a variable????
Back to top
View user's profile Send private message
U.Tews
Administrator


Joined: 22 Nov 2006
Posts: 5068
Location: Hamburg / Germany

PostPosted: Sat Sep 27, 2008 5:59 am    Post subject: Reply with quote

The assign_md_array should be included that it extends the Smarty class.

You have not done this.
Back to top
View user's profile Send private message
TGKnIght
Smarty Junkie


Joined: 07 Sep 2005
Posts: 580
Location: Philadelphia, PA

PostPosted: Thu Oct 02, 2008 4:36 am    Post subject: Reply with quote

Yes, the function assign_md_array is meant to be a part of your custom class extending the Smarty class.

The code in the original post doesn't require you to create the custom class if that is the way you want to do it.
_________________
Smarty site with one index.php controller file
Working with MySQL and Smarty
SmartyColumnSort
Custom Smarty Javascript Debug Template
Back to top
View user's profile Send private message Visit poster's website
rickdgraaff
Smarty n00b


Joined: 01 May 2012
Posts: 1

PostPosted: Tue May 01, 2012 11:29 pm    Post subject: Reply with quote

This is not working... I just got a blank page..
The first function did work.. the otherone not Sad
Back to top
View user's profile Send private message
Display posts from previous:   
This forum is locked: you cannot post, reply to, or edit topics.   This topic is locked: you cannot edit posts or make replies.    Smarty Forum Index -> Tips and Tricks All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group
Protected by Anti-Spam ACP