Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Fri Jun 18, 2004 8:36 am
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
Jean-Yves
Forum Contributor
Posts: 148 Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK
Post
by Jean-Yves » Fri Jun 18, 2004 8:47 am
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
Buddha443556
Forum Regular
Posts: 873 Joined: Fri Mar 19, 2004 1:51 pm
Post
by Buddha443556 » Fri Jun 18, 2004 8:51 am
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!!
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Fri Jun 18, 2004 8:53 am
OK let me try those methods and I will thank you guys in a second hehe, ta!
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Fri Jun 18, 2004 9:04 am
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?
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Fri Jun 18, 2004 9:50 am
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!
Jean-Yves
Forum Contributor
Posts: 148 Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK
Post
by Jean-Yves » Fri Jun 18, 2004 9:53 am
The end if "}" should be after
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);
Joe
Forum Regular
Posts: 939 Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow
Post
by Joe » Fri Jun 18, 2004 9:56 am
Yeah thanks. I wasn't looking for to much detail on the subject, lol but thanks all the same bud! (Problem Solved)