Page 2 of 4
Posted: Mon May 26, 2003 11:59 pm
by Gappa
Ahh I guess one step closer.. but this only out puts one username from the forum.... eh... so still neeeding assistance, i guess this kinda of belongs in the database forum thingy really... i though tit wa spretty much all php but seems to be mainly MySQL.. well newbs will be newbs
Code: Select all
<table border="1"><?php
$db = mysql_connect("localhost", "Gappa", "");
mysql_select_db("Gappa",$db);
$result = mysql_query("SELECT * FROM phpbb_",$db);
$query = 'SELECT topic_title, topic_time, post_text, username FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id=post_id RIGHT JOIN phpbb_users ON topic_poster=user_id WHERE forum_id=6';
$result = mysql_query($query, $db) or die ($query .': '.mysql_error());
while($row = mysql_fetch_assoc($result))
{
echo "<TR>";
foreach($row as $field);
echo "<TD>", $field, "</TD>";
echo "</TR>";
}
?></table>
OK.. ummm setting prefix wrong or something??? Thanks Volka!!!!
Posted: Tue May 27, 2003 5:28 am
by volka
but this only out puts one username from the forum....

Posted: Tue May 27, 2003 6:59 am
by Gappa
Well this is the result im getting from that code
http://www.hostultra.com/~Gappa/test2.php
Posted: Tue May 27, 2003 10:01 am
by volka
First of all you should remove the unessaccary query
$result = mysql_query("SELECT * FROM phpbb_",$db);
it certainly fails anyway.
And then remove the ; from
foreach($row as $field);

Posted: Tue May 27, 2003 10:21 am
by Gappa
Ahhh yes!!!! Its starting to come together NOW!

:D
But there are still some more minor issues... topic_time is displaying as "1053483472" but the rest seems to be working thus far!
Thanks again volka... hehe hope you have a few more tricks up ya sleeve yet
Ok and yes, its only displaying the topics and there post_body (no replies!) which is just what I want!...
Now, a few lill nagging issues...
Getting the topics to display newest topics first.. at the moment its older first.
Fixing the layout hopefully I can work thing out myself.. that is getting it to appear like:
Topic posted by @ time
post body
rather than all in one row like this:
topic, topic_time, post, username
and one last feature i would like to add is a little link at the buttom of each news peice / topic is a "read more" link which goes to the actuall thread on the forum.
Again any help would be fantastic!!
Posted: Tue May 27, 2003 1:22 pm
by liljester
But there are still some more minor issues... topic_time is displaying as "1053483472" but the rest seems to be working thus far!
looks like a unixtimestamp.. check out the DATE_FORMAT() function on mysql.com to get this into a readalbe format =)
Posted: Tue May 27, 2003 10:39 pm
by Gappa
Ahhh, does that require me to change the actual database, if so I dont think thats a viable option, because then phpBB2 might have trouble reading it.
Or is DATE_FORMAT() a piece of code I can put in my code somewhere which converts something like "1053483472" to something like "Tue May 26, 2003 10:21 am"
Posted: Wed May 28, 2003 4:31 am
by volka
Posted: Wed May 28, 2003 5:36 am
by Gappa
Ok, i have it ordering correctly now... but the date just isnt working
Code: Select all
<table border="1"><?php
$db = mysql_connect("localhost", "Gappa", "xocunino");
mysql_select_db("Gappa",$db);
$date_formats = array('d/m/y', 'd/m/Y', 'd/m/Y H:i:s', 'D d-M-y', 'D d-M-Y', 'D d-M-Y H:i:s', 'l d F Y', 'l d F Y H:i:s');
$query = 'SELECT topic_title, DATE_FORMAT(topic_time,"%d/%m/%y") AS Topic_Date, post_text, username FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id=post_id RIGHT JOIN phpbb_users ON topic_poster=user_id WHERE forum_id=6 ORDER BY topic_time DESC';
$result = mysql_query($query, $db) or die ($query .': '.mysql_error());
while($row = mysql_fetch_assoc($result))
{
echo "<TR>";
foreach($row as $field)
echo "<TD>", $field, "</TD>";
echo "</TR>";
}
?></table>
Whether i just made an error and the code i wrote means its not displaying the topic_time (just ordering by it)... or is topic_time not actually the date?
Posted: Wed May 28, 2003 5:45 am
by volka
mysql doesn't
know it's a time-value (because it's defined as int)
use
FROM_UNIXTIME instead
Posted: Wed May 28, 2003 7:47 am
by Gappa
Ahhh nice.. ok got time working
Now for two final things... gonna need some help with this, the main thing being layout. Getting this output to look correct at the moment it aint looking to pretty ...
http://www.hostultra.com/~Gappa/test2.php
I think you'll agree haha
well how who i got about getting it to appear the way i want. That is...
TOPIC posted by USERNAME at TIME
POST BODY
any ideas? do i have to change it from a $query to a $result or something like that?
I have tryied putting html tags and stuff in the query but they just much everything up.
And one last thing, is how do i go about getting a link the the topic at the bottom in like "read further"
Thank you.

Posted: Wed May 28, 2003 7:49 am
by Gappa
sorry i forgot got to place my current code...
Code: Select all
<table border="1"><?php
$db = mysql_connect("localhost", "Gappa", " ");
mysql_select_db("Gappa",$db);
$date_formats = array('d/m/y', 'd/m/Y', 'd/m/Y H:i:s', 'D d-M-y', 'D d-M-Y', 'D d-M-Y H:i:s', 'l d F Y', 'l d F Y H:i:s');
$query = 'SELECT topic_title, FROM_UNIXTIME(topic_time,"%W the %D of %M %r") AS Topic_Date, post_text, username FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id=post_id RIGHT JOIN phpbb_users ON topic_poster=user_id WHERE forum_id=6 ORDER BY topic_time DESC';
$result = mysql_query($query, $db) or die ($query .': '.mysql_error());
while($row = mysql_fetch_assoc($result))
{
echo "<TR>";
foreach($row as $field)
echo "<TD>", $field, "</TD>";
echo "</TR>";
}
?></table>
Posted: Wed May 28, 2003 10:58 am
by volka
when fetching a record (row) via
mysql_fetch_assoc() you receive an array
fieldname=>
value for each field there is in that record. try
Code: Select all
while($row = mysql_fetch_assoc($result))
{
echo "<TR>";
foreach($row as $key=>$field)
echo "<TD>", $key, '=>', $field, "</TD>";
echo "</TR>";
}
and you'll see what I mean. So e.g. to only print the topic title you could use
Code: Select all
while($row = mysql_fetch_assoc($result))
{
echo $row['topic_title'], '<br/>';
}
take a look at the link to this topic, it's
viewtopic.php?t=9080
so if you also query the topic_id and echo a link
http://url.to.your/board/viewtopic.php?t=<value of topic_id> it should work.
Posted: Wed May 28, 2003 7:01 pm
by Gappa
Code: Select all
<table border"1"><?php
$db = mysql_connect("localhost", "Gappa", " ");
mysql_select_db("Gappa",$db);
$date_formats = array('d/m/y', 'd/m/Y', 'd/m/Y H:i:s', 'D d-M-y', 'D d-M-Y', 'D d-M-Y H:i:s', 'l d F Y', 'l d F Y H:i:s');
$query = 'SELECT topic_title, FROM_UNIXTIME(topic_time,"%W the %D of %M %r") AS Topic_Date, post_text, username FROM phpbb_topics RIGHT JOIN phpbb_posts_text ON topic_first_post_id=post_id RIGHT JOIN phpbb_users ON topic_poster=user_id WHERE forum_id=6 ORDER BY topic_time DESC';
$result = mysql_query($query, $db) or die ($query .': '.mysql_error());
while($row = mysql_fetch_assoc($result))
{
echo "<TR>";
foreach($row as $key=>$field)
echo "<TD>", $key, '=>', $field, "</TD>";
echo "</TR>";
}
while($row = mysql_fetch_assoc($result))
{
echo $rowї'topic_title'];
echo " posted by ";
echo $rowї'username'];
echo " at ";
echo $rowї'topic_time'], '<br/>';
echo $row ї'post_text'];
}
?></table>
errr.. i can i have tweked around.. getting errors... ???? I dont know why

Posted: Wed May 28, 2003 7:15 pm
by volka
errors of what kind?
But even if there is no real error, note
Code: Select all
// this loop fetches
while($row = mysql_fetch_assoc($result))
{
// and processes all records
}
// so there's nothing left to do for this loop
while($row = mysql_fetch_assoc($result))
{
// will never be entered
}