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)) 
&#123; 
echo "<TR>";
foreach($row as $field); 
echo "<TD>", $field, "</TD>"; 
echo "</TR>"; 
&#125; 

?></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: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)) 
&#123; 
echo "<TR>";
foreach($row as $field) 
echo "<TD>", $field, "</TD>"; 
echo "</TR>"; 
&#125; 

?></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)) 
&#123; 
echo "<TR>";
foreach($row as $field) 
echo "<TD>", $field, "</TD>"; 
echo "</TR>"; 
&#125; 

?></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)) 
&#123; 
   echo "<TR>"; 
   foreach($row as $key=>$field) 
      echo "<TD>", $key, '=>', $field, "</TD>"; 
   echo "</TR>"; 
&#125;

while($row = mysql_fetch_assoc($result)) 
&#123; 
   echo $row&#1111;'topic_title'];
   echo " posted by ";
   echo $row&#1111;'username'];
   echo " at ";
   echo $row&#1111;'topic_time'], '<br/>';
   echo $row &#1111;'post_text'];
&#125;

?></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
}