|
|
Joined: Sep 2004
Posts: 152
member
|
|
member
Joined: Sep 2004
Posts: 152 |
With my very limited PHP programming skills (don't laugh!) I have somehow managed to pull data from the forum database and make my own custom islands showing full postings: www.fabulaemarrii.com/guildnews2.php...using this code:
<?php
$link = mysql_connect('topsecretserver','topsecretdatabasename','topsecretpassword');
if (!$link)
{
die('Cannot connect to server: '.mysql_error());
}
$db = mysql_select_db('topsecretdatabase',$link);
if (!$db)
{
die('Cannot select database: '. mysql_error());
}
$query = "SELECT * FROM ubbt_TOPICS where FORUM_ID=18 ORDER BY POST_ID DESC";
$result = mysql_query($query);
if (!$result)
{
die('Cannot read from database: '.mysql_error());
}
$num_results = mysql_num_rows($result);
echo '<i>Found '.$num_results.' results. </i>';
echo '</tr><tr bgcolor="#555555"><td>';
for ($i=0; $i <$num_results; $i++)
{
$rowid = mysql_fetch_array($result);
$postid=$rowid['POST_ID'];
$queryposting = "SELECT * FROM ubbt_POSTS where POST_ID=".$postid."";
$resultposting = mysql_query($queryposting);
if (!$result)
{
die('Cannot read from database: '.mysql_error());
}
$rowposting = mysql_fetch_array($resultposting);
$subject=$rowposting['POST_SUBJECT'];
$time=$rowposting['POST_POSTED_TIME'];
$body=$rowposting['POST_BODY'];
$userid=$rowposting['USER_ID'];
if ($userid=='211')
{
$username="Catkin";
}
elseif ($userid=='2')
{
$username="Yomarbalthasar";
}
else
{
$username="We have no idea, really";
}
echo '<b><font color="white">'.$time.': '.$subject.'</b></font><br>';
echo '</td></tr><tr bgcolor="#CCCCCC"><td>'.$body.'<br><i>Posted by '.$username.'</i>';
echo '</td></tr><tr bgcolor="#555555"><td>';
}
?>
So far so good. Now the last thing I need to do to get this working is convert the time of the posting. I can pull it from the database, but I get some illegible number. How do I convert this number to a more legible format, preferably YYYYMMDD HHMM (24-hour format)? There's probably one command for this, but to find out what works, I need to know in what format this data is stored in the first place.
Last edited by Yomar; 10/10/2006 3:32 AM.
|
|
|
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116 |
The date in the database is stored as a Unix Epoch, you can manipulate date() to convert from unix epoch as: date("format", $time);
|
|
|
|
|
Joined: Sep 2004
Posts: 152
member
|
|
member
Joined: Sep 2004
Posts: 152 |
Fantastic Gizmo! It's finally working!
Thank you very much.
|
|
|
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116 |
Not a problem... The sad thing is that I know all of this odd stuff offhand lol... I had to make a date converter when recoding things for one of my RPG help sites, the old system stored stuff as physical dates vs Unix Epoch and i wanted to change the format and figured the best way would be to store as unix epoch and change it on the fly... Let me know how your modding goes  .
|
|
|
|
|
Joined: Sep 2004
Posts: 152
member
|
|
member
Joined: Sep 2004
Posts: 152 |
Final version:
<?php
$link = mysql_connect('server','username','password');
if (!$link)
{
die('Cannot connect to server: '.mysql_error());
}
$db = mysql_select_db('database',$link);
if (!$db)
{
die('Cannot select database: '. mysql_error());
}
$query = "SELECT * FROM ubbt_TOPICS where FORUM_ID=18 ORDER BY POST_ID DESC";
$result = mysql_query($query);
if (!$result)
{
die('Cannot read from database: '.mysql_error());
}
$num_results = mysql_num_rows($result);
echo '<i>Found '.$num_results.' results. </i>';
echo '</tr><tr bgcolor="#555555"><td>';
for ($i=0; $i <$num_results; $i++)
{
$rowid = mysql_fetch_array($result);
$postid=$rowid['POST_ID'];
$queryposting = "SELECT * FROM ubbt_POSTS where POST_ID=".$postid."";
$resultposting = mysql_query($queryposting);
if (!$result)
{
die('Cannot read from database: '.mysql_error());
}
$rowposting = mysql_fetch_array($resultposting);
$subject=$rowposting['POST_SUBJECT'];
$time=$rowposting['POST_POSTED_TIME'];
$timeconvert=date("d-m-Y, H:i", $time);
$body=$rowposting['POST_BODY'];
$userid=$rowposting['USER_ID'];
if ($userid=='211')
{
$username="Catkin";
}
elseif ($userid=='2')
{
$username="Yomarbalthasar";
}
else
{
$username="We have no idea, really";
}
echo '<b><font color="white">'.$timeconvert.': '.$subject.'</b></font><br>';
echo '</td></tr><tr bgcolor="#CCCCCC"><td>'.$body.'</td></tr>';
echo '<tr bgcolor="#CCCCCC"><td align="right"><i>Posted by '.$username.'</i>
<a href="http://www.fabulaemarrii.com/ubbthreads7/ubbthreads.php?ubb=showflat&Number='
.$postid.'">Respond!</a>';
echo '</td></tr><tr bgcolor="#555555"><td>';
}
?>
I'm definitely not a professional PHP programmer, but at least it works. Feel free to adapt the script and use it for your own purposes. The words server, username, password and database must of course be replaced with your own data. Basically this script pulls all postings from forum number 18, then reverses the order of those postings (newest postings on top), links the ID's of those postings with the actual bodies of the postings, and then wraps the result in a nice table. It also pulls/converts the timestamp of the posting and links the user ID of the poster to the actual user name (in our case only 3 users have the right to post on said forum, so chances are you need to change this into something more dynamic and sophisticated). Last but not least a link to said posting is added, so that people can post straight from the content island. Note that this wrap only works in a specific context, as the beginning of the table is not defined. So you will also need to tweak the <td>, <tr> and <table> tags a bit.
Last edited by Yomar; 10/10/2006 3:47 PM.
|
|
|
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116 |
Not too shabby, I'm sure I'll adapt the world out of it  ...
|
|
|
|
|
Joined: Jul 2006
Posts: 2,143
Pooh-Bah
|
|
Pooh-Bah
Joined: Jul 2006
Posts: 2,143 |
I think i'd use a LIMIT on the select for topics.
Now expand on this to make it server friendly. Write the results to a file. Check the timestamp of the file. If it's younger than 5 minutes old server the content right off the file instead of doing the database hit. If the file is older than 5 minutes unlink it, do the database work, write the file, then server it.
|
|
|
|
|
Joined: Sep 2004
Posts: 152
member
|
|
member
Joined: Sep 2004
Posts: 152 |
Aye, I will definitely implement a LIMIT function - when the time is there, as it will take a year or so before that actually becomes a problem The same for the server-friendliness. We're not that high-profile (maybe like 50 visitors a day), so we can handle this. But I totally agree you need to work with files and timestamps if you have a site with many visitors.
|
|
|
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116 |
'eh I'm not good with caching myself or i'd help lol... Though I may get super bored, so who knows...
|
|
|
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116 |
Ok, so I got bored and made my own rendition of UBBNews for 7.0 (albeit sloppy, but hey it works, although no caching just like yours)... UBBDev: UBB.News 7.0
|
|
|
|
|
Joined: Nov 2006
Posts: 3,095 Likes: 1
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095 Likes: 1 |
Looks like it's up to David Dreezer to create one with proper caching We have one on our board too but no caching either.
|
|
|
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116 |
Ian Spence made one that's in private testing right now (I'm running it at UGN Security, and Allen is running it on PraiseCafe) and it does do caching and respects permissions; it's quite nice...
|
|
|
|
|
Joined: Nov 2006
Posts: 3,095 Likes: 1
Carpal Tunnel
|
|
Carpal Tunnel
Joined: Nov 2006
Posts: 3,095 Likes: 1 |
Cool, maybe soon it can come out of private testing and go public testing then maybe in ubb source
|
|
|
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116
|
|
Joined: Jun 2006
Posts: 16,299 Likes: 116 |
I doubt Rick would have plans for that, unless it where to act as an external island ... You could try asking Ian Spence if you could test his out
|
|
|
Bots
by Outdoorking - 04/13/2024 5:08 PM
|
|
|
|
|
|
|
|
|
|
|
0 members (),
898
guests, and
155
robots. |
|
Key:
Admin,
Global Mod,
Mod
|
|
|
|
![[Linked Image from navaho.infopop.cc]](http://navaho.infopop.cc/progress.gif)
