Query a database table within a template?

[samborabora]

How do I manually query a database table inside a template, without using a variable (since it isn't defined)?

[margarett]

It's considered bad coding practice (due to the separation of computation/display) but you can do it like if it was in any other place It's just PHP code in the end
Just global $smcFunc and whatever other variables you might need.

[samborabora]

Ahh! So, whereabouts is it best to put a database query if you need it displayed at a specific point in a template? Should I define a new variable, and if so, how?

[margarett]

It depends on where you need the data

If it's everywhere, somewhere in Load.php is probably the best place. If when viewing a topic, Display.php, etc etc.
Usually what most people do is to feed the result to $context (because it is everywhere), then show its value in the template.

Eg:
In Sources (the file that you see fit): $context['my_variable'] = $result_from_your_query;
In the template: echo $context['my_variable'];

[samborabora]

Excellent, so i need to query if the "seen" column in the "js_alerts" table is 0 or greater (I assume that would be the same as checking if it is empty or not?) and if so, I'll have the alerts image change to the new alert image. I also need to grab the number in that column if it is greater than zero to display the number. What should I put in load.php, and where exactly in the file should I put it? Presumably something using $smcFunc?

[margarett]

If you don't want to add an extra query to EVERY page load, it's best to see if that table isn't already being read in Load.php Is it?

[samborabora]

If you don't want to add an extra query to EVERY page load, it's best to see if that table isn't already being read in Load.php Is it?

It isn't already in load   And it will be on each page of the mobile template since it's the menu that's calling this

[margarett]

OK, I see... In that case it's probably to just call it in maybe loadTheme? That way you can even filter by theme ID.

[samborabora]

Okay, I saw you wrote this out for someone:

require(dirname(__FILE__) . '/SSI.php');
global $db_prefix, $smcFunc;


      $request = $smcFunc['db_query']('', '
         SELECT id_board, title, start_date
         FROM {db_prefix}calendar
         WHERE id_board == '$board_id'
         AND start_date >= '$today'
         ORDER BY start_date DESC
         LIMIT 5'
      );

      while ($row = $smcFunc['db_fetch_assoc']($request))
      {
         //Do stuff
      }
      
      $smcFunc['db_free_result']($request);

Is any of that similar for what I would need to query this column? I've never actually done any SQL querying before :p

[samborabora]

And would it go in loadTheme, then be called into the template via a variable?

[samborabora]

Here is a query I'm supposed to use:

Code Select Expand

global $smcFunc, $user_info;
$result = $smcFunc['db_query']('', '
SELECT COUNT(1) AS total
FROM {db_prefix}alerts
WHERE id_member = {int:member}
AND seen = 0',
array(
'member' => $user_info['id'],
)
);

list($total) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);

I have no idea where to put this. I have been told to use $total as the variable in the template. Where should I put this query (presumably in load.php?) and how should I call it into my template?

[margarett]

Put it in loadTheme (it's really not the best place but a quick and dirty fix works here )

Find

Code Select Expand

// Set up the contextual user array.

Add before

Code Select Expand

$result = $smcFunc['db_query']('', '
SELECT COUNT(1) AS total
FROM {db_prefix}alerts
WHERE id_member = {int:member}
AND seen = 0',
array(
'member' => $user_info['id'],
)
);
list($total) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);

Find (few lines below):

Code Select Expand

'ignoreusers' => $user_info['ignoreusers'],
Add after:

Code Select Expand

'total_alerts' => !empty($total) ? $total : 0,

Now, in your template, when you want it, you can check:

Code Select Expand

if (!empty($context['user']['total_alerts']))
{
//Do your thing
}

[samborabora]

THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

This actually works, and you've helped me solve a puzzle that has plagued me for months, thank you SOOOOO much, margarett!!!!