[SOLVED] Small Problem!

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

[SOLVED] Small Problem!

Post by Joe »

I have recently been trying to make a part on my site where uses can view the most recent forum topics just like php developers network does. The only problem is trying to select the topic and the username. Due to my mysql version I am unable to use UNION, :( but I am trying to look for another way to bypass this. My code so far is:

Code: Select all

$sql = "SELECT * FROM phpbb_topics ORDER BY topic_id DESC LIMIT 4";
 $result = mysql_query($sql) or die("Error!");
 
 while  ($row = mysql_fetch_assoc($result))
 {
  echo "<font face='verdana' size=1>";
  $topic = $row['topic_title'];
  $ID = $row['topic_id'];
  $user = $row['poster_id'];

  if (strlen($topic) > 29)
  { 
   $topic = substr($topic, 0, 29);
   $topic = $topic."...";
   echo "<img src='pics/bullet.gif'> <A href='http://www.site.com/Forums/viewtopic.php?t=$ID'>$topic</A>";
   $sql = "SELECT * FROM phpbb_users WHERE user_id = '$user'";
   $result = mysql_query($sql) or die("Error!");
   $row = mysql_fetch_assoc($result);
   $user1 = $row['username'];
   echo " By $user1<br>";
  }
}
The code only seems to show one record with no username next to it. Any help appreciated! :)


Joe 8)
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

Your if (strlen($topic) > 29) statment is wrapping around the whole of the user code end it after the truncation line (...) and it should work
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

How about a simple join?

Code: Select all

SELECT * FROM phpbb_topics AS topic, phpbb_users AS user WHERE topic.poster_id = user.user_id
WARNING POSTER IS NOT AWAKE YET!!
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

OK let me try those methods and I will thank you guys in a second hehe, ta!
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

OK I tried:

Code: Select all

$sql = "SELECT * FROM phpbb_topics AS topic, phpbb_users AS user WHERE topic.poster_id = user.user_id";
 $result = mysql_query($sql) or die("Error!");
 
 while  ($row = mysql_fetch_assoc($result))
 {
  echo "<font face='verdana' size=1>";
  $topic = $row['topic_title'];
  $ID = $row['topic_id'];
  $user = $row['username'];

  if (strlen($topic) > 29)
  { 
   $topic = substr($topic, 0, 29);
   $topic = $topic."...";
   echo "<img src='pics/bullet.gif'> <A href='http://www.site.com/Forums/viewtopic.php?t=$ID'>$topic</A> By $user<br>";
  }
 }
But all i got was a query error! Any help on this?
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

I aplogise to the both of you guys/girls for this! I made a silly mistake of missing out a db column, (silly me!). Thanks alot for everything. Worked great!
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

The end if "}" should be after

Code: Select all

$topic = $topic."...";


since you always want to display the topic, even if it less than 29 chars.

re the sql error: could you add mysql_error() to the die() so that you can print out your error?

ie

Code: Select all

$result = mysql_query($sql) or die(mysql_error() . "<p>$sql</p>"\n);
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Yeah thanks. I wasn't looking for to much detail on the subject, lol but thanks all the same bud! (Problem Solved)
Post Reply