PHP News Script

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post 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!!!!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

but this only out puts one username from the forum....
:?:
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post by Gappa »

Well this is the result im getting from that code

http://www.hostultra.com/~Gappa/test2.php
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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);
;)
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post 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!!
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post 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 =)
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post 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"
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

mysql doesn't know it's a time-value (because it's defined as int)
use FROM_UNIXTIME instead
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post 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. :)
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post 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>
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
Gappa
Forum Contributor
Posts: 119
Joined: Fri May 23, 2003 10:02 am

Post 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 :(
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
}
Post Reply