Display the users name rather the database user id?

  • iWiGG_2010
  • Newbie
  • Newbie
  • User avatar
  • Posts: 10
  • Loc: Australia

Post 3+ Months Ago

Hi All,

I am trying to make a basic forum page. At the moment it displays the user as a number (which is defined in the database). Was wondering how to get it to display the users name rather then their database number?

These are my tables:

'users' - user_id, email, name, password
'posts' - id, album_id, topic_id, post_creator, post_contact, post_date
'topics' - id, album_id, topic_id, topic_creator, topic_last_user, topic_date, topic_reply_date, topic_views

I can only guess that I many need to join the 'users'.user_id table with the 'posts'.post_creator. If I have to use JOINS I aways seem to get confused with this part.

Any help will do, thanks.

Here is my code:

view_topic.php
PHP Code: [ Select ]
<?php
$cid = $_GET['cid'];
$tid = $_GET['tid'];
$sql = "SELECT * FROM `topics` WHERE `album_id`='".$cid."' AND id='".$tid."' LIMIT 1";
$res = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($res) == 1) {
   echo "<table width='100%'>";
      if ($_SESSION['user_id']) {
         echo "<tr><td colspan='2'><center><input type='submit' value='Add Reply' onClick=\"window.location = 'post_reply.php?cid=".$cid."&tid=".$tid."'\" /></center><hr /><br />";
            } else {
             
               echo "<br /><tr><td colspan='2'><p>Please Log in to add a comment</p></td></tr>"; }
      while ($row = mysql_fetch_assoc($res)) {
         $sql2 = "SELECT * FROM `posts` WHERE `album_id`='".$cid."' AND `topic_id`='".$tid."' ORDER BY `post_date` DESC";  
         $res2 = mysql_query($sql2) or die(mysql_error());
         while ($row2 = mysql_fetch_assoc($res2)) {
            echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$row2['post_creator']." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
           
            <tr><td colspan='2'><hr /></td></tr>";
         }
         $old_views = $row['topic_views'];
         $new_views = $old_views + 1;
         $sql3 = "UPDATE `topics` SET `topic_views`='".$new_views."' WHERE `album_id`='".$cid."' AND id='".$tid."' LIMIT 1";
         $res3 = mysql_query($sql3) or die(mysql_error());
      }
         echo "</table>";
} else {
   echo "<p>This topic does not exist</p>";  
}
?>
 
  1. <?php
  2. $cid = $_GET['cid'];
  3. $tid = $_GET['tid'];
  4. $sql = "SELECT * FROM `topics` WHERE `album_id`='".$cid."' AND id='".$tid."' LIMIT 1";
  5. $res = mysql_query($sql) or die(mysql_error());
  6. if (mysql_num_rows($res) == 1) {
  7.    echo "<table width='100%'>";
  8.       if ($_SESSION['user_id']) {
  9.          echo "<tr><td colspan='2'><center><input type='submit' value='Add Reply' onClick=\"window.location = 'post_reply.php?cid=".$cid."&tid=".$tid."'\" /></center><hr /><br />";
  10.             } else {
  11.              
  12.                echo "<br /><tr><td colspan='2'><p>Please Log in to add a comment</p></td></tr>"; }
  13.       while ($row = mysql_fetch_assoc($res)) {
  14.          $sql2 = "SELECT * FROM `posts` WHERE `album_id`='".$cid."' AND `topic_id`='".$tid."' ORDER BY `post_date` DESC";  
  15.          $res2 = mysql_query($sql2) or die(mysql_error());
  16.          while ($row2 = mysql_fetch_assoc($res2)) {
  17.             echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$row2['post_creator']." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
  18.            
  19.             <tr><td colspan='2'><hr /></td></tr>";
  20.          }
  21.          $old_views = $row['topic_views'];
  22.          $new_views = $old_views + 1;
  23.          $sql3 = "UPDATE `topics` SET `topic_views`='".$new_views."' WHERE `album_id`='".$cid."' AND id='".$tid."' LIMIT 1";
  24.          $res3 = mysql_query($sql3) or die(mysql_error());
  25.       }
  26.          echo "</table>";
  27. } else {
  28.    echo "<p>This topic does not exist</p>";  
  29. }
  30. ?>
  31.  



view_topic_parse.php
PHP Code: [ Select ]
<?php
if (isset($_POST['topic_submit'])) {
   if (($_POST['topic_title'] == "") && ($_POST['topic_content']) == "") {
      echo "<p>Sorry, something went wrong! You did not fill in both fields. Please return to the previous plage.</p>";
      exit();
   } else {
      $cid = $_POST['cid'];
      $title = $_POST['topic_title'];
      $content = $_POST['topic_content'];
      $creator = $_SESSION['user_id'];
      $sql = "INSERT INTO topics (album_id, topic_title, topic_creator, topic_date, topic_reply_date) VALUES ('".$cid."', '".$title."', '".$creator."', now(), now())";
      $res = mysql_query($sql) or die(mysql_error());
      $new_topic_id = mysql_insert_id();
      $sql2 = "INSERT INTO posts (album_id, topic_id, post_creator, post_content, post_date) VALUES ('".$cid."', '".$new_topic_id."', '".$creator."', '".$content."', now())";
      $res2 = mysql_query($sql2) or die(mysql_error());
      $sql3 = "UPDATE albums SET last_post_date=now(), `last_user_posted`='".$creator."' WHERE `album_id`='".$cid."' LIMIT 1";
      $res3 = mysql_query($sql3) or die(mysql_error());
      if (($res) && ($res2) && ($res3)) {
         header("Location: view_topic.php?cid=".$cid."&tid=".$new_topic_id);
      } else {
         echo "There was a problem creating your topic. Please try again!";
      }
   }
}
?>
 
 
 
  1. <?php
  2. if (isset($_POST['topic_submit'])) {
  3.    if (($_POST['topic_title'] == "") && ($_POST['topic_content']) == "") {
  4.       echo "<p>Sorry, something went wrong! You did not fill in both fields. Please return to the previous plage.</p>";
  5.       exit();
  6.    } else {
  7.       $cid = $_POST['cid'];
  8.       $title = $_POST['topic_title'];
  9.       $content = $_POST['topic_content'];
  10.       $creator = $_SESSION['user_id'];
  11.       $sql = "INSERT INTO topics (album_id, topic_title, topic_creator, topic_date, topic_reply_date) VALUES ('".$cid."', '".$title."', '".$creator."', now(), now())";
  12.       $res = mysql_query($sql) or die(mysql_error());
  13.       $new_topic_id = mysql_insert_id();
  14.       $sql2 = "INSERT INTO posts (album_id, topic_id, post_creator, post_content, post_date) VALUES ('".$cid."', '".$new_topic_id."', '".$creator."', '".$content."', now())";
  15.       $res2 = mysql_query($sql2) or die(mysql_error());
  16.       $sql3 = "UPDATE albums SET last_post_date=now(), `last_user_posted`='".$creator."' WHERE `album_id`='".$cid."' LIMIT 1";
  17.       $res3 = mysql_query($sql3) or die(mysql_error());
  18.       if (($res) && ($res2) && ($res3)) {
  19.          header("Location: view_topic.php?cid=".$cid."&tid=".$new_topic_id);
  20.       } else {
  21.          echo "There was a problem creating your topic. Please try again!";
  22.       }
  23.    }
  24. }
  25. ?>
  26.  
  27.  
  28.  
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

Quote:
Code: [ Select ]
      echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$row2['post_creator']." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
     
      <tr><td colspan='2'><hr /></td></tr>";
  1.       echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$row2['post_creator']." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
  2.      
  3.       <tr><td colspan='2'><hr /></td></tr>";

I think that's where you want to change the code. The $row2['post_creator'] is the user id, right? You would need to do an SQL query in that loop to retrieve the username and use that instead.

Code: [ Select ]
     while ($row2 = mysql_fetch_assoc($res2)) {
      $user_name = mysql_fetch(mysql_query("SELECT username FROM usertable WHERE userid = {$row2['post_creator']}"));
      echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$row2['post_creator']." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
     
      <tr><td colspan='2'><hr /></td></tr>";
     }
  1.      while ($row2 = mysql_fetch_assoc($res2)) {
  2.       $user_name = mysql_fetch(mysql_query("SELECT username FROM usertable WHERE userid = {$row2['post_creator']}"));
  3.       echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$row2['post_creator']." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
  4.      
  5.       <tr><td colspan='2'><hr /></td></tr>";
  6.      }

I think that's what you want to do from a quick look at it.
  • iWiGG_2010
  • Newbie
  • Newbie
  • User avatar
  • Posts: 10
  • Loc: Australia

Post 3+ Months Ago

Thanks Bogey,

It appears to look like what I am trying to do, but it still not showing the name (only the user id). Yes $row2['post_creator'] is the user's id.

Maybe I am doing something wrong? Sorry really new to php/mysql

PHP Code: [ Select ]
               echo "<br /><tr><td colspan='2'><p>Please Log in to add a comment</p></td></tr>"; }
      while ($row = mysql_fetch_assoc($res)) {
         $sql2 = "SELECT * FROM `posts` WHERE `album_id`='".$cid."' AND `topic_id`='".$tid."' ORDER BY `post_date` DESC";  
         $res2 = mysql_query($sql2) or die(mysql_error());
                  while ($row2 = mysql_fetch_assoc($res2)) {
          $user_name = mysql_fetch_assoc(mysql_query("SELECT name FROM users WHERE user_id = {$row2['post_creator']}"));
          echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$row2['post_creator']." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
       
         <tr><td colspan='2'><hr /></td></tr>";
         }
 
  1.                echo "<br /><tr><td colspan='2'><p>Please Log in to add a comment</p></td></tr>"; }
  2.       while ($row = mysql_fetch_assoc($res)) {
  3.          $sql2 = "SELECT * FROM `posts` WHERE `album_id`='".$cid."' AND `topic_id`='".$tid."' ORDER BY `post_date` DESC";  
  4.          $res2 = mysql_query($sql2) or die(mysql_error());
  5.                   while ($row2 = mysql_fetch_assoc($res2)) {
  6.           $user_name = mysql_fetch_assoc(mysql_query("SELECT name FROM users WHERE user_id = {$row2['post_creator']}"));
  7.           echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$row2['post_creator']." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
  8.        
  9.          <tr><td colspan='2'><hr /></td></tr>";
  10.          }
  11.  
  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8399
  • Loc: USA

Post 3+ Months Ago

You got to change the $row2['post_creator'] to $user_name on line 6... I forgot to change that in my example on my first post.
PHP Code: [ Select ]
               echo "<br /><tr><td colspan='2'><p>Please Log in to add a comment</p></td></tr>"; }
      while ($row = mysql_fetch_assoc($res)) {
         $sql2 = "SELECT * FROM `posts` WHERE `album_id`='".$cid."' AND `topic_id`='".$tid."' ORDER BY `post_date` DESC";  
         $res2 = mysql_query($sql2) or die(mysql_error());
                  while ($row2 = mysql_fetch_assoc($res2)) {
          $user_name = mysql_fetch_assoc(mysql_query("SELECT name FROM users WHERE user_id = {$row2['post_creator']}"));
          echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$user_name." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
     
        <tr><td colspan='2'><hr /></td></tr>";
         }
  1.                echo "<br /><tr><td colspan='2'><p>Please Log in to add a comment</p></td></tr>"; }
  2.       while ($row = mysql_fetch_assoc($res)) {
  3.          $sql2 = "SELECT * FROM `posts` WHERE `album_id`='".$cid."' AND `topic_id`='".$tid."' ORDER BY `post_date` DESC";  
  4.          $res2 = mysql_query($sql2) or die(mysql_error());
  5.                   while ($row2 = mysql_fetch_assoc($res2)) {
  6.           $user_name = mysql_fetch_assoc(mysql_query("SELECT name FROM users WHERE user_id = {$row2['post_creator']}"));
  7.           echo "<tr><td valign='top' style='border: 1px sold #FFF;'><div style='min-height: 125px;'><br /><p>".$row['topic_title']."<br /><br />by ".$user_name." - ".$row2['post_date']."<br />".$row2['post_content']."</p></div></td>
  8.      
  9.         <tr><td colspan='2'><hr /></td></tr>";
  10.          }

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: demonmaestro and 82 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.