|
Smarty
WARNING: All discussion is moving to https://reddit.com/r/smarty, please go there! This forum will be closing soon. |
|
View previous topic :: View next topic |
Author |
Message |
TGKnIght Smarty Junkie
Joined: 07 Sep 2005 Posts: 580 Location: Philadelphia, PA
|
Posted: Tue Oct 17, 2006 3:12 pm Post subject: Setting up MySQL recordset as array to be parsed by Smarty |
|
|
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 |
|
radar Smarty Regular
Joined: 06 Oct 2006 Posts: 64
|
Posted: Tue Oct 17, 2006 9:16 pm Post subject: |
|
|
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 |
|
TGKnIght Smarty Junkie
Joined: 07 Sep 2005 Posts: 580 Location: Philadelphia, PA
|
Posted: Thu Oct 19, 2006 1:18 am Post subject: |
|
|
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 |
|
arac-softec Smarty Rookie
Joined: 23 Sep 2008 Posts: 16
|
Posted: Wed Sep 24, 2008 3:18 pm Post subject: |
|
|
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 |
|
U.Tews Administrator
Joined: 22 Nov 2006 Posts: 5068 Location: Hamburg / Germany
|
Posted: Sat Sep 27, 2008 5:59 am Post subject: |
|
|
The assign_md_array should be included that it extends the Smarty class.
You have not done this. |
|
Back to top |
|
TGKnIght Smarty Junkie
Joined: 07 Sep 2005 Posts: 580 Location: Philadelphia, PA
|
|
Back to top |
|
rickdgraaff Smarty n00b
Joined: 01 May 2012 Posts: 1
|
Posted: Tue May 01, 2012 11:29 pm Post subject: |
|
|
This is not working... I just got a blank page..
The first function did work.. the otherone not |
|
Back to top |
|
|
|
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
|
|