0

I am building a forum, on my main_forum.php page, I am attempting to display the user who has posted a topic, I get all the data but the username is hosted on another table. How do I get it to display?

<?php

include ('includes/session.php');
include ('includes/header.php');

$host = "localhost";
$username = "fses16g6";
$password = "fses16g6";
$db_name="fses16g6"; // Database name 
$tbl_name="forum_question"; // Table name 
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");
$sql="SELECT * FROM $tbl_name ORDER BY id DESC";
//$query="SELECT * FROM users"
// OREDER BY id DESC is order result by descending 

$result=mysql_query($sql);
?>

<table width="90%" border="0" align="center" cellpadding="3" cellspacing="1"     bgcolor="#202531">
<tr>
<td width="6%" align="center" bgcolor="#202531"><strong>#</strong></td>
<td width="50%" align="center" bgcolor="#202531"><strong>Topic</strong></td>
<td width="11%" align="center" bgcolor="#202531"><strong>User</strong></td>
<td width="11%" align="center" bgcolor="#202531"><strong>Views</strong></td>
<td width="11%" align="center" bgcolor="#202531"><strong>Replies</strong>    </td>
<td width="11%" align="center" bgcolor="#202531"><strong>Date/Time</strong>    </td>
</tr>

<?php

while($rows=mysql_fetch_array($result)){
?>

<tr>
<td bgcolor="#202531"><?php echo $rows['id']; ?></td>
<td bgcolor="#202531"><a href="view_topic.php?id=<?php echo $rows['id']; ?    >"><?php echo $rows['topic']; ?></a><BR></td>
<td align="center" bgcolor="#202531"><?php echo $rows['first_name']; ?></td>
<td align="center" bgcolor="#202531"><?php echo $rows['view']; ?></td>
<td align="center" bgcolor="#202531"><?php echo $rows['reply']; ?></td>
<td align="center" bgcolor="#202531"><?php echo $rows['datetime']; ?></td>
</tr>
<?php
// Exit looping and close connection 
}

mysql_close();
?>

<tr>
<td colspan="7" align="right" bgcolor="#000000"><a href="create_topic.php">    <strong>Create New Topic</strong> </a></td>
</tr>
</table>



<?php

if ($is_admin) {
                echo '<button type="button">EDIT</button>';
                echo '<button type="button">DELETE</button>';
            }

include ('includes/footer.html');
?>

Here is the table 'forum_questions'

 CREATE TABLE `forum_question` (
`id` int(4) NOT NULL auto_increment,
`topic` varchar(255) NOT NULL default '',
`detail` longtext NOT NULL,
`name` varchar(65) NOT NULL default '',
`email` varchar(65) NOT NULL default '',
`datetime` varchar(25) NOT NULL default '',
`view` int(4) NOT NULL default '0',
`reply` int(4) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

I have successfully connected the table, to the php but there is no username value connected to the table so it doesn't display a user name.

I need to grab the tuple 'first_name' from the table 'users' and apply it to the main_forum.php portion where it denotes which user has created a topic.

Thanks for your help.

RHK
  • 3
  • 3
  • 1
    All `mysql_` functions are deprecated since PHP 5.5 and they have been removed in PHP 7. Use PDO (or MySQLi) instead. (And upgrade to PHP 7.) – Arjan Apr 30 '16 at 21:27
  • Also, which column of the `users` table matches with which column from the `forum_question` table? It doesn't have a `user_id` column, which would be the most obvious choice. – Arjan Apr 30 '16 at 21:31
  • you should save the userid in the forum_question table and then use an inner join to join the forum_question table and users table to get more user information. – Matt Apr 30 '16 at 21:32
  • You "get it to display" by executing an appropriate query, retrieving the row from the resultset, and outputting the values as part of the generated HTML. That's how you'd "get it to display". – spencer7593 Apr 30 '16 at 21:50
  • @spencer7593, Sorry, if you find my post frustrating. Would I be able to replace the select query with a join query? I've tried to implement a join and it has not yet worked properly, was looking for some direction, thanks. – RHK Apr 30 '16 at 21:55
  • @Matt, would I be able to do conduct the inner join on phpmyadmin or would I be able to do it directly within the php file itself? – RHK Apr 30 '16 at 21:56
  • @RHK when you do the `SELECT * FROM $tbl_name ORDER BY id DESC` query you can do a query called INNER JOIN and you merge 2 tables basic on a clause. If you add the userid to to the `forum_question` and update post I will make the query for you. – Matt Apr 30 '16 at 22:10
  • @Matt, I've created a new column `alter table forum_question` `add user_id integer` – RHK Apr 30 '16 at 22:18
  • `SELECT * FROM $tbl_name INNER JOIN $table_name2 ON $tbl_name.user_id = $tbl_name2.user_id ORDER BY id DESC` this will join the two tables and you can get the info from both of them now. – Matt Apr 30 '16 at 22:39

2 Answers2

1

I think the spirit of your question has more to do with SQL than it has to do with PHP. You might consider checking out this question, which shows up as related to your question. That will get you started on the required SQL syntax.

From a table design perspective, you'll want to make sure that you have some way to associate the table housing your users' data with the table housing your forum question data. As a relational database, the power of having a MySQL database is the ability to relate data together, presumably from different tables. A quick Google search unearthed this primer on MySQL database design, but there are a plethora of resources available to you with some quick searches.

In short, you'll probably want to explore creating a foreign key that relates your users to your forum questions. You can then use the appropriate SQL syntax to get query results that include your users' information.

I hope that helps guide you toward the right path!

Community
  • 1
  • 1
Derek
  • 827
  • 11
  • 23
0

If you add the posers userID to the forum)question table you can then add a new variable which is your users table.

$table2 = users

then change your $sql to:

$sql="SELECT * FROM $tbl_name INNER JOIN $table_name2 ON $tbl_name.user_id = $tbl_name2.user_id ORDER BY id DESC";

now you can access data from both the users table and the forum_question table.

Matt
  • 1,749
  • 2
  • 12
  • 26
  • i've added `$table_name2 = "users";` and swapped out the select statement to what you have, now the forum is not displaying any results? Any advice? – RHK Apr 30 '16 at 22:50
  • is the user_id column for both tables called user_id? Also, have you filled the user_id column in for the forum_questions table to relate to the correct user? – Matt Apr 30 '16 at 22:52
  • Yes to the first question, for the second part, I'm currently working on making that connection. – RHK Apr 30 '16 at 22:55
  • Once you have filled in the forum_question table try again. if nothing let me know – Matt Apr 30 '16 at 22:58
  • I've added a new column named first_name, this col also exists on "users", I'm replacing user_id with first_name as it would be clearer for people to realize who is creating posts, I've made all necessary substitutions. I'm not entirely sure why, when I make a new post the first_name col stays at null. – RHK Apr 30 '16 at 23:19
  • from the innerjoin you get the first_name from the user table anyway. have you edited your posting of posts so first_name is added? – Matt Apr 30 '16 at 23:22
  • Ok, I was able to connect first_name and now it populates and properly stores the value within the table. BUT, there is still nothing displaying on the main forum page. – RHK Apr 30 '16 at 23:30
  • do a var_dump on $rows and see if first_name is within it. it should be though. – Matt Apr 30 '16 at 23:35
  • Yes, it is. I'm not entirely sure why nothing is populating, what I eventually want to accomplish is using $SESSION to capture the first_name of the logged in user and automatically store it in the forum_question table. – RHK Apr 30 '16 at 23:43
  • Everything is working now, I removed the join statement and went back to the select statement because I added first_name into the table anyway so it was redundant. Now I have to get the first_name to populate based off of how the user is logged in. Thanks for your help. – RHK May 01 '16 at 00:08