Smarty

[shandley14]

I have a page that queries a MySQL db. I need to turn this into a function so that i don't have to write it 1020 times. (17 stat_codes and 60 player_nubers) Because i use the sum(stat_value) in the query, I don't think i can GROUP BY more than one field. If i can GROUP BY more than one field, then that is a MySQL issue and would love an answer anyhow. If not then here is the problem.


Here's the Query

function get_stat_for_player($s_code, $p_id)

-----------------------------------------------------
SELECT id, f_name, l_name,
event_stat_code, event_player_number
stat_code, sum(stat_value),
player_number
FROM users, events, stats, players
WHERE event_stat_code=stat_code
AND stat_code=".$s_code."
AND event_player_number=player_number
AND player_number=".$p_id."
GROUP BY player_number;

-----------------------------------------------------
s_code and p_id are passed in the function
how do i make it a function and assign it a smarty tag

So then all i have to do is call the function with the right code and number.
------------------------------------------------------
get_stat_for_player(X,4);
get_stat_for_player(X,5);
get_stat_for_player(L,4);
get_stat_for_player(L,5);
get_stat_for_player(H,2);
------------------------------------------------------

I still have to write the call 1020 times but not the entire query each time.

or maybe i could do something like
{get_stat_for_player: L,X}
on the template and that would work
Which would be nice, but don't know if possible

Thanks for the time
_________________
wolfram 30 --- 6-170 ---- upper v

[boots]

Well, there are several approaches to this problem, certainly.

For one thing, you indeed can group by more than one field, but in that case, your sum() would only be for the portion indicated by all grouped fields.

Some will suggest that you handle the mechanics of this purely in PHP, forming an array of result sets that you assign to Smarty. Not a bad idea, but I don't think that is what you are getting at.

If I read you right, you are hinting at the plugin template functions which are possible due to Smarty's API. I assume you can write the meat of the function that you are talking about (since you seem to have all the guts of it already) and what is missing is how to wrap that function.

Here's the general idea of how to get something like the following to work:

[shandley14]

Thanks for the help on the function part

Here is the page
function.get_stat_for_player.php

I added a game_id to it

<?php

/*
* Smarty plugin
* -------------------------------------------------------------
* Type: function
* Name: get_stat_code
* Purpose: get stat_code ($stat_code) value for player with # ($j_id) for game ($game_id)
* -------------------------------------------------------------
*/

function smarty_function_get_stat_for_player($params, &$smarty)
{
extract($params);

if (empty($stat_code)) {
$smarty->trigger_error("get_stat_for_player: missing 'stat_code' parameter");
return;
}
if (empty($j_id)) {
$smarty->trigger_error("get_stat_for_player: missing 'j_id' parameter");
return;
}
if (empty($game_id)) {
$smarty->trigger_error("get_stat_for_player: missing 'game_id' parameter");
return;
}


// INSERT YOUR CODE TO DO SOMETHING WITH $code ID $id
$sql = "SELECT player_id, player_l_name,
roster_jersey_id, roster_team_id, playing_time,
game_id, home_team_id,
team_id, team_name,
event_game_id, event_j_number, event_team_id, event_stat_code,
stat_name, stat_desc, stat_code, sum( stat_value )
FROM players, rosters, games, teams,`events` , stats
WHERE
game_id=event_game_id
AND event_game_id=roster_game_id
AND roster_game_id=".$game_id."
AND event_team_id=roster_team_id
AND roster_team_id=home_team_id
AND home_team_id=team_id
AND event_stat_code=stat_code
AND stat_code=".$stat_code."
AND event_j_number=roster_jersey_id
AND roster_jersey_id=".j_id."
AND roster_player_id=player_id
GROUP BY roster_jersey_id";
// get all the products from the table
$res = mysql_query($sql);
$results = array();
$i=0;
while ($r=mysql_fetch_array($res)) {
$tmp = array(
'stat_value'=> $r['sum( stat_value )']
);
$results[$i++] = $tmp;
}

// pass the results to the template

return $results;
}
?>

This is how i call it in the template

{get_stat_for_player stat_code="Q" j_id="14" game_id="2"}

This is the error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/home/inhance/public_html/powerstats/libs/plugins/function.get_stat_for_player.php on line 55
Array

I think the array thing is the problem.

I may need assign it differently or should i have a {section} tag in there.

Thanks for your help
_________________
wolfram 30 --- 6-170 ---- upper v

[shandley14]

fixed the j_id to read $j_id

didn't help
_________________
wolfram 30 --- 6-170 ---- upper v

[boots]

Ahhh...in that case, you'll need to make another step -- but it is straight forward.

Plugin functions are expected to return strings (which are inserted into the template in place of the template function call). You can assign a template variable which can then be accessed from within the template. You can do this by adding another parameter, say, var (or assign, or name ... what-have-you).
[php:1:e619ec1ab4]...
if (empty($var)) {
$smarty->trigger_error("get_stat_for_player: missing 'var' parameter");
return;
}
...[/php:1:e619ec1ab4]
then instead of returning your result, you would do this:
[php:1:e619ec1ab4]...
$smarty->assign($var, $results);
...[/php:1:e619ec1ab4]
In your template:

[shandley14]

Thank you for your help.

The data is displayed in a excel type format, rows and columns. The main problem is that the sum function in mysql returns a NULL value when there is nothing to sum. which means i end up with a different number of columns for each player, because they don't always score in each stat_code.

So this will work for now, I will tackle it later

Two questions before i respond to yours. (greedy I know)

Does smarty have a png chart function or pie function? (using gd)

Where would i find info on how to take an array from a query and work it into a png function?


Wolfram 30 = Stephen Wolfram has this theory called Rule 30 it basically says that the design of life can be determined by mathematical equations. Take for instance a certain kind of sea shell, he has a math equation that will produce this shell. he says there are only six ways for it to come out, oceanographers say there are twelve ways, but the other six have been lost or phased out. His goal was to refute science as we currently understand it. check out mathematica.com

6-170

is one of the most sought after classes at mit

Upper-V
Basically the most difficult spot to hit and to defend in soccer.

A place outside the box, A thing that helps you get outside the box, and the only place worth anything inside the box.
_________________
wolfram 30 --- 6-170 ---- upper v

[boots]

thanks for the reply re: your tagline. I guessed at the wolfram bit but for the rest -- I did not know that

For the graphing, that sounds like the type of thing that google can help with- I recall reading articles at the various PHP sites which featured GD or Imagick as a means to create graphs. Bart Bons created a page at the Wiki, PNG image with Alpha Transparency, which might give you a head start [edit: actually, on closer inspection, it probably isn't what you are after].

As for the SQL null issue, it occurs to me that you can try something like sum(isnull(stat_value,0)) AS stat_value -- well, that's the syntax I'd try on SQL Server/Sybase, but I think MySQL isn't too different. The main thing is to make sure that the sum function is always evaluating a number and never a null value. It's late, though, so I apologize in advance if it doesn't work for you. Alternatively, if you follow my foreach example, the missing column would probably be stat_value, so you might get traction with {$stat_row.stat_value|default:0} as the template code for that particular column.

Greetings.