MySQL Query

[Xherziu]

Hey guys, do you all see anything wrong with my query here...

 

$query = mysql_query("

 

SELECT COUNT( * ) AS `Rows` , `topic_id`, 'topic_title', 'topic_poster', 'topic_time', 'username',

FROM `phpbb_topics` 'phpbb_users'

GROUP BY `topic_id`

ORDER BY `topic_id` DESC

LIMIT 0 , 30 ")

 

or die ('Error: '.mysql_error ());

[AndyB]

Hey guys, do you all see anything wrong with my query here...

 

FROM `phpbb_topics` 'phpbb_users'  looks suspiciously like a mistake

[Xherziu]

I'm trying to mimic this guy's query right here..

 

    $topics_query = $db->sql_query("

        SELECT

            {$table_prefix}topics.topic_title,

            {$table_prefix}users.username AS poster_name,

            {$table_prefix}topics.topic_time,

            {$table_prefix}posts_text.post_text AS topic_text,

            {$table_prefix}topics.topic_views,

            {$table_prefix}topics.topic_replies,

            {$table_prefix}topics.topic_id,

            {$table_prefix}posts_text.post_id,

            {$table_prefix}topics.topic_poster AS poster_id

        FROM

            {$table_prefix}topics,

            {$table_prefix}users,

            {$table_prefix}posts_text

        WHERE

            {$table_prefix}topics.forum_id = {$forum_id} AND

            {$table_prefix}users.user_id =

                {$table_prefix}topics.topic_poster AND

            {$table_prefix}posts_text.post_id =

                {$table_prefix}topics.topic_first_post_id

        ORDER BY topic_time DESC

        LIMIT {$limit_count};");

 

I'm having trouble with the joins though, this is what I got so far..

 

$query = mysql_query("

 

SELECT topic_id, topic_title, topic_poster, topic_time, username

FROM phpbb_topics, phpbb_users

WHERE

ORDER BY topic_id DESC

LIMIT 0 , 5

 

") or die ('Error: '.mysql_error ());

[Xherziu]

I'm getting some results with this..

 

SELECT COUNT( * ) AS `Rows` , topic_id, topic_title, topic_poster, topic_time, username

FROM phpbb_topics, phpbb_users

GROUP BY `topic_id`

ORDER BY `topic_id` DESC

LIMIT 0 , 9

 

My problem is getting the username to show correclty.  With this the username is the same as topic_poster so can I get results by joining the two?

 

Such as... WHERE username = topic_poster

 

 

[Xherziu]

SELECT COUNT( * ) AS `Rows` , topics.topic_id, topics.topic_title, topics.topic_poster, topics.topic_time, users.username

FROM phpbb_topics, phpbb_users

GROUP BY `topic_id`

WHERE phpbb_users.user_id = phpbb_topics.topic_poster

ORDER BY `topic_id` DESC

LIMIT 0 , 9

 

Tried this but got an error.  It works without the WHERE statement.

 

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE phpbb_users.user_id = phpbb_topics.topic_poster ORDER BY `topic_id` DESC L' at line 4

[Xherziu]

Since I can't get the join to work I made two sql statements but I'm getting an error with one...

 

39 $usersql = "

40

41      SELECT COUNT ( * ) AS 'Rows' , user_id , username

42      FROM phpbb_users

43      WHERE user_id = '" . &id . "'

44

45      ";

 

Parse error: syntax error, unexpected '&' in /home/crazy4ja/public_html/php/forum.php on line 43

[Xherziu]

AHAHAHAHA, ITS ALIVE!!!

 

(no thanks to you guys) 

 

$usersql = "

 

SELECT COUNT( * ) AS 'Rows' , user_id , username

FROM phpbb_users

WHERE user_id = '" . $id . "'

GROUP BY 'user_id'

 

";