• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

question about database query / Strings, arrays

#1
Hello,


I am trying to make a own template and I wish to use an add-on to get own strings and few arrays but somehow I not get it to work correctly could someone maybe see what I do wrong?

I created an addon by following this tutorial: Unmaintained - Global Template Variable

the content looks like this:
PHP:
<?php
class Ulyaoth_GlobalTemplateVariable_Listener
{
    public static function template_create(&$templateName, array &$ulyaoth, XenForo_Template_Abstract $template)
    {
     
        $db = XenForo_Application::get('db');
        $q = $db->fetchAll("SELECT`last_thread_title`FROM`xf_forum`");
     
        $ulyaoth['ulytest'] = $q[0];

    }
}
In my template I call it like this:
HTML:
<div id="ulyaothMain">

{$ulytest}

</div>
But I always get the error:
Code:
Template Errors: PAGE_CONTAINER

htmlspecialchars() expects parameter 1 to be string, array given in /srv/ulyaoth/library/XenForo/Template/Abstract.php(265) : eval()'d code, line 38:
37:
38: ' . htmlspecialchars($ulytest, ENT_QUOTES, 'UTF-8') . '
39:
My first question is what am I doing wrong how can I actually get database queries into my template into array or in string form?

My second question is in general what is the best way to get your own strings and arays into a template? did I do it correct as the guide showed me or is there a better way?
 
Last edited:

Thomas.B

Well-known member
#2
My first question is what am I doing wrong how can I actually get database queries into my template into array or in string form?
The template compiler complains as you are trying to output an array instead of a string. Your template variable contains an array with the format ["last_thread_title"] => "actual thread title". So in order make your code work you would need to change {$ulytest} to {$ulytest.last_thread_title}.

However, I assume you actually want all thread titles. If so use something like this:
PHP:
public static function template_create(&$templateName, array &$ulyaoth, XenForo_Template_Abstract $template)
{
  $db = XenForo_Application::get('db');
  $lastThreadTitles = $db->fetchCol("SELECT`last_thread_title`FROM`xf_forum`");
  $ulyaoth['ulytest'] = $lastThreadTitles;
}
And in your template:

<div id="ulyaothMain">
<xen:foreach loop="$ulytest" value="$lastThreadTitle">
{$lastThreadTitle} <br/>
</xen:foreach>
</div>


My second question is in general what is the best way to get your own strings and arays into a template? did I do it correct as the guide showed me or is there a better way?
Well, as the tutorial says these template variables would be available in every template. That means e.g, there would be one additional DB query for any page that loads. Now if you need these template variables only in a few templates, it would be better to pass the variables only to the templates in which they are actually needed..
 
#3
The template compiler complains as you are trying to output an array instead of a string. Your template variable contains an array with the format ["last_thread_title"] => "actual thread title". So in order make your code work you would need to change {$ulytest} to {$ulytest.last_thread_title}.

However, I assume you actually want all thread titles. If so use something like this:
PHP:
public static function template_create(&$templateName, array &$ulyaoth, XenForo_Template_Abstract $template)
{
  $db = XenForo_Application::get('db');
  $lastThreadTitles = $db->fetchCol("SELECT`last_thread_title`FROM`xf_forum`");
  $ulyaoth['ulytest'] = $lastThreadTitles;
}
And in your template:

<div id="ulyaothMain">
<xen:foreach loop="$ulytest" value="$lastThreadTitle">
{$lastThreadTitle} <br/>
</xen:foreach>
</div>




Well, as the tutorial says these template variables would be available in every template. That means e.g, there would be one additional DB query for any page that loads. Now if you need these template variables only in a few templates, it would be better to pass the variables only to the templates in which they are actually needed..

Hi thank you so much that was indeed what I was trying but it seems not to do what I expected :)

I am basically trying to make a Q/A website kind of like stackoverflow I have the template mostly finished but now I am trying to get all the posts onto the page. But with the code above it seems to only show 1 post.

How could I get it so I get a list of the last lets say 30 posts like this:
post 1
post 2
post 3

And it basically should show on "post 1" or the newest post or the latests that was replied to.

I am only trying to do this on the front page so basically it not has to be in all pages.
 
Last edited:

Thomas.B

Well-known member
#4
Yeah, that is actuailly what my code is supposed to do and it works for me. Are you sure you copied and pasted everything?

Btw, if you would like the newest on top, change the SQL query to:

SELECT last_thread_title FROM xf_forum ORDER BY last_post_date DESC
 
#5
Yeah, that is actuailly what my code is supposed to do and it works for me. Are you sure you copied and pasted everything?

Btw, if you would like the newest on top, change the SQL query to:

SELECT last_thread_title FROM xf_forum ORDER BY last_post_date DESC
Wow I got it to work now, but seems I had to use a different table, I am pretty new to all this so did not see there were multiple pages with tables in phpmyadmin :).

I did this now and that worked: SELECT `title` FROM `xf_thread` ORDER BY last_post_date DESC
 

Thomas.B

Well-known member
#6
Great. Yeah, using the xf_thread table makes probably more sense but (technically) it should also work with xf_forum..

Btw, you can add LIMIT x (whereas x is a number) at the end of the query to limit the number of results.
 
Last edited:
#7
Great. Yeah, using the xf_thread table makes probably more sense but (technically) it should also work with xf_forum..

Btw, you can add LIMIT x (whereas x is a number) at the end of the query to limit the number of results.
I see thank you, now how would one actually be able to use the different variable within the array?

Let's say I do this:
PHP:
$lastThreadTitles = $db->fetchCol("SELECT `first_post_likes`,`reply_count`,`view_count`,`title`,`last_post_username`,`thread_id` FROM `xf_thread` ORDER BY last_post_date DESC LIMIT 30");
How could I make it so I get:
Likes = Array[1]
Replies = Array[2]
Views = Array[3]
Thread = http://192.168.1.106/index.php?threads/Array[6]

Something like that and then loop trough it so it would fill it in automatically 30 times?

I tried something like this but seems not to work:
PHP:
<?php
class Ulyaoth_GlobalTemplateVariable_Listener
{

public static function template_create(&$templateName, array &$ulyaoth, XenForo_Template_Abstract $template)
{
  $db = XenForo_Application::get('db');
  $query = $db->fetchAll("SELECT `first_post_likes`,`reply_count`,`view_count`,`title`,`last_post_username`,`thread_id` FROM `xf_thread` ORDER BY last_post_date DESC");
 
while ($row = mysql_fetch_assoc($query)) {
  $ulyaoth[] = $row;
}

}

}
 
Last edited:

Thomas.B

Well-known member
#8
Here you are:
PHP:
public static function template_create(&$templateName, array &$params, XenForo_Template_Abstract $template)
{
  $db = XenForo_Application::get('db');
  $latestThreads = $db->fetchAll("SELECT * FROM xf_thread AS thread
                                  JOIN xf_post AS post
                                    ON thread.first_post_id = post.post_id
                                  ORDER BY thread.post_date DESC
                                  LIMIT 30");
  $params['latestThreads'] = $latestThreads;
}
Code:
<table id="latestThreads">
  <xen:foreach loop="$latestThreads" value="$thread">
    <tr>
       <td>Likes:</td> <td>{$thread.likes}</td>
    <tr>
    <tr>
      <td>Replies:</td> <td>{$thread.reply_count}</td>
    </tr>
    <tr>
      <td>Views:</td> <td>{$thread.view_count}</td>
    </tr>
    <tr>
       <td>Thread:</td> <td><a href="{xen:link threads, $thread}">{$thread.title}</a></td>
    </tr>
  </xen:foreach>
</table>
Keep in mind that with this code any thread can potentially be included, also if the user has no view perms for it..
 
Last edited:
#9
Here you are:
PHP:
public static function template_create(&$templateName, array &$params, XenForo_Template_Abstract $template)
{
  $db = XenForo_Application::get('db');
  $latestThreads = $db->fetchAll("SELECT * FROM xf_thread AS thread
                                  JOIN xf_post AS post
                                    ON thread.first_post_id = post.post_id
                                  ORDER BY thread.post_date DESC
                                  LIMIT 30");
  $params['latestThreads'] = $latestThreads;
}
Code:
<table id="latestThreads">
  <xen:foreach loop="$latestThreads" value="$thread">
    <tr>
       <td>Likes:</td> <td>{$thread.likes}</td>
    <tr>
    <tr>
      <td>Replies:</td> <td>{$thread.reply_count}</td>
    </tr>
    <tr>
      <td>Views:</td> <td>{$thread.view_count}</td>
    </tr>
    <tr>
       <td>Thread:</td> <td><a href="{xen:link threads, $thread}">{$thread.title}</a></td>
    </tr>
  </xen:foreach>
</table>
Keep in mind that with this code any thread can potentially be included, also if the user has no view perms for it..
I see I understand now so you are supposed to use the actual "database name" with the variable ".view_count".
Really thank you so much I learned allot from you today very kind of you to take the time to explain it.
 

Thomas.B

Well-known member
#10
I see I understand now so you are supposed to use the actual "database name" with the variable ".view_count".
I don't know if I understand your question correctly but I explain a litlte bit what happens anyway :)

Firstly, the $lastestThreads variable contains a two-dimensional array. The first level represents all rows of the DB query result. Each array in the second level contains the values of each column of a single row. So it look like this:
Code:
$latestThreads[0]['column_1_name'] => 'value'
              [0]['column_2_name'] => 'value'
              [0]['column_3_name'] => 'value'
              ...
              [1]['column_1_name'] => 'value'
              [1]['column_2_name'] => 'value'
              [1]['column_3_name'] => 'value'
              ...
              [2]...
Now the foreach loop does sth like this before each iteration: $thread = $latestThreads[n]. So e.g. in the first iteration it would be thread = $latestThreads[0]. So if you want to access the value of the column „view_count“ in any iteration you can do this with $thread.view_count. The dot betwen „$thread“ and „view_count“ is just XF template syntax to access an array element. Hope that answers your question. If not let me know.

Really thank you so much I learned allot from you today very kind of you to take the time to explain it.
You're welcome. I'm glad I could help :)
 
Last edited:
#11
I don't know if I understand your question correctly but I explain a litlte bit what happens anyway :)

Firstly, the $lastestThreads variable contains a two-dimensional array. The first level represents all rows of the DB query result. Each array in the second level contains the values of each column of a single row. So it look like this:
Code:
$latestThreads[0]['column_1_name'] => 'value'
              [0]['column_2_name'] => 'value'
              [0]['column_3_name'] => 'value'
              ...
              [1]['column_1_name'] => 'value'
              [1]['column_2_name'] => 'value'
              [1]['column_3_name'] => 'value'
              ...
              [2]...
Now the foreach loop does sth like this before each iteration: $thread = $latestThreads[n]. So e.g. in the first iteration it would be thread = $latestThreads[0]. So if you want to access the value of the column „view_count“ in any iteration you can do this with $thread.view_count. The dot betwen „$thread“ and „view_count“ is just XF template syntax to access an array element. Hope that answers your question. If not let me know.



You're welcome. I'm glad I could help :)
It starts to work now slightly have to work some more on the css but will do that when I have finished everything:
 

Thomas.B

Well-known member
#12
Looks nice already :)

Btw, regarding performance: you can restrict the execution of your listener to a certain template if you enter the template's name in the code event listener's „Event Hint“ field.
 
#13
Looks nice already :)

Btw, regarding performance: you can restrict the execution of your listener to a certain template if you enter the template's name in the code event listener's „Event Hint“ field.
Thank you for the advice I did the it as you suggested to limit it only to the specific template.

Hmm I have another question that suddenly appeared, I am trying to get the tags to work so I did it as following:
Code:
      <div id="tags">
      <a href="/index.php?tags/{$thread.tags}/" class="post-tag" title="" rel="tag">{$thread.tags}</a>
      </div>
However somehow this shows up as this:
Code:
a:6:{i:13;a:2:{s:3:"tag";s:3:"dog";s:7:"tag_url";s:3:"dog";}i:14;a:2:{s:3:"tag";s:6:"kitten";s:7:"tag_url";s:6:"kitten";}i:11;a:2:{s:3:"tag";s:5:"ninja";s:7:"tag_url";s:5:"ninja";}i:15;a:2:{s:3:"tag";s:4:"sexy";s:7:"tag_url";s:4:"sexy";}i:1;a:2:{s:3:"tag";s:4:"test";s:7:"tag_url";s:4:"test";}i:12;a:2:{s:3:"tag";s:6:"turtle";s:7:"tag_url";s:6:"turtle";}}
I see my test tags in there: "dog", kitten", "ninja", "test", "turtle".
But how do I get the correct tag name out there do I need to process it some other way with a separate query?
 

Thomas.B

Well-known member
#14
You need to convert the query result for the tags back to an array with the unserialize() function. It should look like this:

PHP:
  $latestThreads = $db->fetchAll...
   
  // unserialize tags
  foreach ($latestThreads AS $thread)
  {
    $thread['tags'] = unserialize($thread['tags']);
   
  }
  
 $params['latestThreads'] = $latestThreads;
In your template you can access all tags with foreach loop(s):
Code:
<xen:foreach loop="$latestThreads" value="$thread">

  [...]

  <div id="tags">
    <a href="/index.php?tags/" class="post-tag" title="" rel="tag">
      <xen:foreach loop="$thread.tags" value="$tag"> {$tag.tag}, </xen:foreach>
    </a>   
  </div>

 [...]

</xen:foreach>
I hope that works ^^ I couldn't really test it as I have no thread tags. But I think you got the idea?
 
#15
You need to convert the query result for the tags back to an array with the unserialize() function. It should look like this:

PHP:
  $latestThreads = $db->fetchAll...
  
  // unserialize tags
  foreach ($latestThreads AS $thread)
  {
    $thread['tags'] = unserialize($thread['tags']);
  
  }
 
$params['latestThreads'] = $latestThreads;
In your template you can access all tags with foreach loop(s):
Code:
<xen:foreach loop="$latestThreads" value="$thread">

  [...]

  <div id="tags">
    <a href="/index.php?tags/" class="post-tag" title="" rel="tag">
      <xen:foreach loop="$thread.tags" value="$tag"> {$tag.tag}, </xen:foreach>
    </a>  
  </div>

[...]

</xen:foreach>
I hope that works ^^ I couldn't really test it as I have no thread tags. But I think you got the idea?
Thank you so much for the example, I added this to the Listener does that look correct?:
Code:
<?php
class Ulyaoth_GlobalTemplateVariable_Listener
{

public static function template_create(&$templateName, array &$params, XenForo_Template_Abstract $template)
{
  $db = XenForo_Application::get('db');
  $latestThreads = $db->fetchAll("SELECT * FROM xf_thread AS thread
                                  JOIN xf_post AS post
                                      ON thread.first_post_id = post.post_id
                                  ORDER BY thread.post_date DESC
                                  LIMIT 30");

foreach ($latestThreads AS $thread)
{
  $thread['tags'] = unserialize($thread['tags']);

}

  $params['latestThreads'] = $latestThreads;
}

}
Because it complains about "$thread['tags']" so i am not 100% sure if my order is correct?
 
#17
PHP:
foreach ($latestThreads AS $thread)
has to be
PHP:
foreach ($latestThreads AS &$thread)
That did indeed the trick :) thank you! what does this "&" actually mean?

Just in case someone else reads this in the template I did it like this:
Code:
      <td>
      <div id="posttitle">
      <a href="{xen:link threads, $thread}">{$thread.title}</a>
      </div>
      <xen:foreach loop="$thread.tags" value="$tag">
      <a class="tag" href="/index.php?tags/{$tag.tag}/">{$tag.tag}</a>
      </xen:foreach>
      </td>
looks like this:


Now only need to figure out how to use the shiny Xenforo css hehe :)
 
Last edited:

Thomas.B

Well-known member
#18
Oh yeah, my bad. I indeed forgot the &. It was pretty late yesterday.. (is that a good excuse? :D)

FYI: The & means that $thread will contain a reference instead of a copy of the current element of $latestThreads. So in the latter case, regardless of what you assign to $thread it will be overwritten in the next iteration. But if $thread is a reference, everything you assign to it will not be saved in $thread but in the corresponding element of $latestThread.

So it seems you are almost finished. Looks pretty good. Congrats :)
 
#19
Oh yeah, my bad. I indeed forgot the &. It was pretty late yesterday.. (is that a good excuse? :D)

FYI: The & means that $thread will contain a reference instead of a copy of the current element of $latestThreads. So in the latter case, regardless of what you assign to $thread it will be overwritten in the next iteration. But if $thread is a reference, everything you assign to it will not be saved in $thread but in the corresponding element of $latestThread.

So it seems you are almost finished. Looks pretty good. Congrats :)
Aah I understand thank you for explaining!

Yes it is going along I appreciate the help people give me so much really kind, now got the post layout almost done and seems to work.


Will try make the template slightly more useful and put it on github then!
 

Thomas.B

Well-known member
#20
Nice :)

Btw, I've noticed that there are raw timestamps. You can convert them with the datetime function: {xen:datetime $timestamp, html}