The way SQL connection happens

[Skyler]

I am new in all this moding stuff, but how do i initialize a MySQL connection in phpBB3?
i want to create a shoutbox(sounds easy) but how to connect to the database and then submit the result to the template in {row.shout} stuff?

[A_Jelly_Doughnut]

All of the initialization is done in common.php. I'll exclude that

Code: Select all

$text = utf8_normalize_nfc(request_var('mytext', '', true));
$edit_id = request_var('id', 0);

$sql_ary = array(
   'text_here'  => $text,
   'text_id'      => $edit_id);

$sql = 'INSERT INTO ' . NEW_TABLE . ' ' . $db->sql_build_array('INSERT', $sql_ary);
$db->sql_query($sql);


That's the basics

[Sniper_E]

Jelly.... I have studied and studied codes like that right there and I just don't get it. Not even the basics.

I understand bits and pieces of it but there is no light bulb going off in my head that says, "Oh, I get it!"

I am still looking for understanding to where it all makes sense to me. And it all has to click in my mind.

I'll get there one day.... read my sig.

[Skyler]

Code: Select all

$text = utf8_normalize_nfc(request_var('mytext', '', true));
$edit_id = request_var('id', 0);

$sql_ary = array(
   'text_here'  => $text,
   'text_id'      => $edit_id);

$sql = 'INSERT INTO ' . NEW_TABLE . ' ' . $db->sql_build_array('INSERT', $sql_ary);
$db->sql_query($sql);


Let me see if i got that right.
$text - gets the imput text from a textbox.
$edit_id - dunno what it does. Why do i have to get id and from where? Database?
$sql_ary - builds the array, read that in the Coding Guidelines, but dunno how does it helps.

So, if i want to get text form a database and output it on the template, i just use SELECT for the $sql_build_array, and in the template just use {TEXT_HERE}?

[A_Jelly_Doughnut]

I kinda pulled $edit_id out of nowhere. It would actually not exist if creating a new entry. It comes from the database if you're editing an entry.

Bad Example

[Skyler]

ok..look what
i added this to index.php

Code: Select all

// Search for new topics
      $sql = "SELECT topic_id, topic_replies, forum_id, topic_title FROM " . TOPICS_TABLE . " ORDER BY topic_last_post_time DESC LIMIT 0, 5";
      $result = $db->sql_query($sql);;
      while($topics = $db->sql_fetchrow($result))
      {
                   ***********
      }
$template->assign_vars(array(
   'NEWEST_POST'   => $******,
);


How can i add the newest post id, name, and so on to the key?

I want to create Recent Topics

[Belak]

ok..look what
i added this to index.php

Code: Select all

// Search for new topics
      $sql = "SELECT topic_id, topic_replies, forum_id, topic_title FROM " . TOPICS_TABLE . " ORDER BY topic_last_post_time DESC LIMIT 0, 5";
      $result = $db->sql_query($sql);;
      while($topics = $db->sql_fetchrow($result))
      {
                   ***********
      }
$template->assign_vars(array(
   'NEWEST_POST'   => $******,
);


you should not use LIMIT in your SQL Query. It's better to use $db->sql_query_limit($sql, $limit, $start). This is to make sure you get the best results with whatever database you use. Antoher thing you would want to add is $db->sql_freeresult($result) after the while loop.

How can i add the newest post id, name, and so on to the key?

I want to create Recent Topics

I'm not quite sure what you mean. if you want to get the post id, just add "topic_last_post_id" to the SELECT part of your query. In the while loop, you can access the data via $topics['topic_last_post_id'].

[Skyler]

i got that pretty much, but i still have some questions

Code: Select all

$db->sql_query_limit($sql, $limit, $start)

$start is already defined? i mean, i should use the querry this way?

Code: Select all

$db->sql_query_limit($sql, '5', $start)

Second, i want a way do display the latest topic. So, in the while loop, i should use

Code: Select all

$template->assign_vars(array(
'N_TOPIC"    => $topics['topic_title'],
);

[Belak]

no, $start is not defined. you can place the start value there, for example

Code: Select all

$db->sql_query_limit($sql, 5, 0)

As the $start value is optional, you could also use

Code: Select all

$db->sql_query_limit($sql, 5)

this would have the same effect. to display the latest topics, you will habe to make a loop in the template. on index.php, you use

Code: Select all

$template->assign_block_vars('latest_topics', array(
    'N_TOPIC"    => censor_text($topics['topic_title']),
);

this defines a template block, which is a piece of template code, that will be used several times. you should use censor_text() to make sure no swearwords are displayed on index_body.html you will have to add:

Code: Select all

<ul>
<!-- BEGIN latest_topics -->
    <li>{latest_topics.N_TOPIC}</li>
<!-- BEGINELSE -->
    <li>{L_NO_RECENT_TOPICS}</li>
<!-- END latest topics-->
</ul>

every time the php script index.php comes to the template->assign_block_vars part, it replaces {latest_topics.N_TOPIC} with the value you assigned. if this statement is not excecuted, the BEGINELSE part will be displayed (be sure to define {L_NO_RECENT_TOPICS} somewhere )

i hope i didn't miss anything and that it will help you

[Skyler]

this is the code in index.php

Code: Select all

// Search for new topics
      $sql = "SELECT topic_id, topic_replies, forum_id, topic_title FROM " . TOPICS_TABLE . " ORDER BY topic_last_post_time DESC";
      $result = $db->sql_query_limit($sql, 5, 0);
      while($topics = $db->sql_fetchrow($result))
      {
      $template->assign_block_vars('latest_topics', array(
         'N_TOPIC'    => $topics['topic_title'],
   );

      }
   $db->sql_freeresult($result);

Code: Select all

when i remove this piece of code
$template->assign_block_vars('latest_topics', array(
         'N_TOPIC'    => $topics['topic_title'],

all works (not the mod tough), but with it i see only a blank page.

EDIT
it calls the beginelse