Hi; I hope somebody can help me on this:
I tried to build a forum, and I finished everything, just remains one thing: I couldn’t connect categories and topics, so whenever the user want to display topics inside specific category it fails. I only succeeded to display all categories and all topics, but each one in different page, I failed even to gather them in one single page. I think its a problem with retrieving data from the database. any way I am gonna show you the complete structure of my database and most of the code I hope can find help me:
First Database: Each table name is corresponding to its function:
Users table:
CREATE TABLE users (
user_id INT(8) NOT NULL AUTO_INCREMENT,
user_name VARCHAR(30) NOT NULL,
user_pass VARCHAR(255) NOT NULL,
user_email VARCHAR(255) NOT NULL,
user_date DATETIME NOT NULL,
user_level INT(8) NOT NULL,
UNIQUE INDEX user_name_unique (user_name),
PRIMARY KEY (user_id)
) TYPE=INNODB;Categories table:
CREATE TABLE categories (
cat_id INT(8) NOT NULL AUTO_INCREMENT,
cat_name VARCHAR(255) NOT NULL,
cat_description VARCHAR(255) NOT NULL,
UNIQUE INDEX cat_name_unique (cat_name),
PRIMARY KEY (cat_id)
) TYPE=INNODB; Topics table:
CREATE TABLE topics (
topic_id INT(8) NOT NULL AUTO_INCREMENT,
topic_subject VARCHAR(255) NOT NULL,
topic_date DATETIME NOT NULL,
topic_cat INT(8) NOT NULL,
topic_by INT(8) NOT NULL,
PRIMARY KEY (topic_id)
) TYPE=INNODB; Posts table:
CREATE TABLE posts (
post_id INT(8) NOT NULL AUTO_INCREMENT,
post_content TEXT NOT NULL,
post_date DATETIME NOT NULL,
post_topic INT(8) NOT NULL,
post_by INT(8) NOT NULL,
PRIMARY KEY (post_id)
) TYPE=INNODB; Those are all tables, and in the following lines are alterations on the tables to create foreign keys and connections between tables:
Linking topics to categories:
ALTER TABLE topics ADD FOREIGN KEY(topic_cat) REFERENCES categories(cat_id) ON DELETE CASCADE ON UPDATE CASCADE;
Linking topics to users:
ALTER TABLE topics ADD FOREIGN KEY(topic_by) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE;
Linking posts to topics:
ALTER TABLE posts ADD FOREIGN KEY(post_topic) REFERENCES topics(topic_id) ON DELETE CASCADE ON UPDATE CASCADE;
Linking posts to users:
ALTER TABLE posts ADD FOREIGN KEY(post_by) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE;
Above is all the formation of the database. Below is all the PHP code:
connect.php file “You can see I have tried different methods and only one work form me”:
<?php
//$link = mysql_connect('localhost', 'root', 'wireless');
//if (!$link) {
//die('Could not connect: ' . mysql_error());
//}
//echo 'Connected successfully';
//$server = 'localhost';
// $username = 'malakusc_root';
// $password = 'wireless';
// $database = 'malakusc_forum';
// Create connection
//$con=mysqli_connect("localhost","root","wireless","forum");
// Check connection
//if (mysqli_connect_errno($con))
//{
//echo "Failed to connect to MySQL: " . mysqli_connect_error();
//}
//echo 'Connected successfully';
$link = mysql_connect('localhost', 'malakusc_root', 'wireless');
if (!$link) {
die('Not connected : ' . mysql_error());
}
$db_selected = mysql_select_db('malakusc_forum', $link);
if (!$db_selected) {
die ('Can\'t use foo : ' . mysql_error());
}
//connect.php
// $server = 'localhost';
// $username = 'root';
// $password = 'wireless';
// $database = 'forum';
// if(!mysql_connect($server, $username, $password))
// {
// exit('Error: could not establish database connection');
//}
// if(!mysql_select_db($database)
// exit('Error: could not select the database');
// }
?> Signup.php file:
<?php
include 'connect.php';
//include 'header.php';
echo '<h3>Sign up</h3>';
if($_SERVER['REQUEST_METHOD'] != 'POST')
{
echo '<form method="post" action="">
<p> Username: <input type="text" name="user_name" /> </p>
<p> Password: <input type="password" name="user_pass"> </p>
<p> Password again: <input type="password" name="user_pass_check"> </p>
<p> E-mail: <input type="email" name="user_email"> </p>
<p> <input type="submit" value="Add User" /> </p>
</form>';
}
else
{
$errors = array(); /* declare the array for later use */
if(isset($_POST['user_name']))
{
if(!ctype_alnum($_POST['user_name']))
{
$errors[] = 'The username can only contain letters and digits. Press Back to return.';
}
if(strlen($_POST['user_name']) > 30)
{
$errors[] = 'The username cannot be longer than 30 characters. Press Back to return.';
}
}
else
{
$errors[] = 'The username field must not be empty.';
}
if(isset($_POST['user_pass']))
{
if($_POST['user_pass'] != $_POST['user_pass_check'])
{
$errors[] = 'The two passwords did not match. Press Back to return.';
}
}
else
{
$errors[] = 'The password field cannot be empty. Press Back to return.';
}
if(!empty($errors))
{
echo 'A couple of fields are not filled in correctly. Press Back to return.';
echo '<ul>';
foreach($errors as $key => $value)
{
echo '<li>' . $value . '</li>';
}
echo '</ul>';
}
else
{
$sql = "INSERT INTO
users(user_name, user_pass, user_email ,user_date, user_level)
VALUES('" . mysql_escape_string($_POST['user_name']) . "',
'" . sha1($_POST['user_pass']) . "',
'" . mysql_escape_string($_POST['user_email']) . "',
NOW(),
1)";
$result = mysql_query($sql);
if(!$result)
{
echo 'Something went wrong while registering. Please try again later.';
//echo mysql_error();
}
else
{
echo 'Successfully registered. You can now <a href="signin.php">sign in</a> and start posting! :-)';
}
}
}
?>Signin.php code:
<?php
include 'connect.php';
echo '<h3>Sign in</h3>';
//echo 'Please you need to sign-in to login to our Forum: enter your username followed by your password:';
echo "<p> </p>";
if(isset($_SESSION['signed_in']) && $_SESSION['signed_in'] == true)
{
echo 'You are already signed in, you can <a href="signout.php">sign out</a> if you want.';
}
else
{
if($_SERVER['REQUEST_METHOD'] != 'POST')
{
echo '<form method="post" action="">
<p> Username: <input type="text" name="user_name" /> </p>
<p> Password: <input type="password" name="user_pass"> </p>
<ul> <input type="submit" value="Sign in" />
</form>';
}
else
{
$errors = array();
if(!isset($_POST['user_name']))
{
$errors[] = 'The username field must not be empty.';
}
if(!isset($_POST['user_pass']))
{
$errors[] = 'The password field must not be empty.';
}
if(!empty($errors))
{
echo 'Uh-oh.. a couple of fields are not filled in correctly..';
echo '<ul>';
foreach($errors as $key => $value)
{
echo '<li>' . $value . '</li>';
}
echo '</ul>';
}
else
{
$sql = "SELECT
user_id,
user_name,
user_level
FROM
users
WHERE
user_name = '" . mysql_escape_string($_POST['user_name']) . "'
AND
user_pass = '" . sha1($_POST['user_pass']) . "'";
$result = mysql_query($sql);
if(!$result)
{
echo 'Something went wrong while signing in. Please try again later.';
//echo mysql_error();
}
else
{
if(mysql_num_rows($result) == 0)
{
echo 'You have supplied a wrong user/password combination. Please try again.'.' <a href="signin.php">try to sign-in</a>';
}
else
{
$_SESSION['signed_in'] = true;
while($row = mysql_fetch_assoc($result))
{
$_SESSION['user_id'] = $row['user_id'];
$_SESSION['user_name'] = $row['user_name'];
$_SESSION['user_level'] = $row['user_level'];
}
echo 'Welcome, ' . $_SESSION['user_name'] . '. <a href="indexforum.php">Proceed to the forum overview</a>.';
}
}
}
}
}
?>Index forum: This is like the homepage of the forum, once you login it should be displayed:
<?php
include 'connect.php';
echo 'DATE: ' . date("Y-m-d") . "<br>";
$sql = "SELECT *
FROM
categories";
$result = mysql_query($sql);
if(!$result)
{
echo 'The categories could not be displayed, please try again later.';
}
else
{
if(mysql_num_rows($result) == 0)
{
echo 'No categories defined yet.';
echo 'To start posting in our Forum, first Go to Add forum Category to create a Category';
}
else
{
echo '<table border="1">
<tr>
<th>Category</th>
<th>Last topic</th>
</tr>';
while($row = mysql_fetch_assoc($result))
{
echo '<tr>';
echo '<td class="leftpart">';
echo '<h3><a href="">' . $row['cat_name'] . '</a></h3>' . $row['cat_description'];
echo '</td>';
echo '<td class="rightpart">';
echo '<a href="topic.php?id=">Last Topic</a> at 10-10';
echo '</td>';
echo '</tr>';
}
}
}
?> create_cat.php to create categories:
<?php
include 'connect.php';
if($_SERVER['REQUEST_METHOD'] != 'POST')
{
echo "<form method='post' action=''>
<p> Category name: <input type='text' name='cat_name' /> </p>
<p> Category description: <textarea name='cat_description' /></textarea> </p>
<input type='submit' value='Add category' />
</form>";
}
else
{
$sql = "INSERT INTO categories(cat_name, cat_description)
VALUES('" . mysql_real_escape_string($_POST['cat_name']) . "',
'" . mysql_real_escape_string($_POST['cat_description']) . "')";
$result = mysql_query($sql);
if(!$result)
{
echo 'Error' . mysql_error();
}
else
{
echo 'New category successfully added.' . '. <a href="indexforum.php">return to forum Homepage</a>.';
}
}
?>create_topic.php o create topics into categories “I have disabled the session function as it is not working for me”:
<?php include 'connect.php'; echo 'Create a topic
'; //if($_SESSION['signed_in'] == false) //{ // echo 'Sorry, you have to be signed in to create a topic.'; //} //else //{ if($_SERVER['REQUEST_METHOD'] != 'POST') { $sql = "SELECT cat_id, cat_name, cat_description FROM categories"; $result = mysql_query($sql); if(!$result) { echo 'Error while selecting from database. Please try again later.'; } else { if(mysql_num_rows($result) == 0) { if($_SESSION['user_level'] == 1) { echo 'You have not created categories yet.'; } else { echo 'Before you can post a topic, you must wait for an admin to create some categories.'; } } else { echo 'Subject:
Category:'; echo '' ; while($row = mysql_fetch_assoc($result)) { echo '' . $row['cat_name'] . ''; } echo ''; echo 'Message:
'; } } } else { $query = "BEGIN WORK;"; $result = mysql_query($query); if(!$result) { echo 'An error occured while creating your topic. Please try again later.'; } else { $sql = "INSERT INTO topics(topic_subject, topic_date, topic_cat, topic_by) VALUES('" . mysql_escape_string($_POST['topic_subject']) . "', NOW(), '" . mysql_escape_string($_POST['topic_cat']) . "', 1 )"; $result = mysql_query($sql); if(!$result) { echo 'An error occured while inserting your data. Please try again later.' . mysql_error(); $sql = "ROLLBACK;"; $result = mysql_query($sql); } else { $topicid = mysql_insert_id(); $sql = "INSERT INTO posts(post_content, post_date, post_topic, post_by) VALUES ('" . mysql_real_escape_string($_POST['post_content']) . "', NOW(), " . $topicid . ", 1 )"; $result = mysql_query($sql); if(!$result) { echo 'An error occured while inserting your post. Please try again later.' . mysql_error(); $sql = "ROLLBACK;"; $result = mysql_query($sql); } else { $sql = "COMMIT;"; $result = mysql_query($sql); echo 'You have successfully created your new topic.'; } } } } ?>
category.php is used to display each category separately with its own topics “IT IS NOT WORKING” and here’s is the major problem:
<?php
include 'connect.php';
//first select the category based on $_GET['cat_id']
$sql = "SELECT
cat_id,
cat_name,
cat_description
FROM
categories
WHERE
cat_id = " . mysql_real_escape_string($_GET['cat_id']) . "";
$result = mysql_query($sql);
if(!$result)
{
echo 'The category could not be displayed, please try again later.' . mysql_error();
}
else
{
if(mysql_num_rows($result) == 0)
{
echo 'This category does not exist.';
}
else
{
//display category data
while($row = mysql_fetch_assoc($result))
{
echo '<h2>Topics in ′' . $row['cat_name'] . '′ category</h2>';
}
//do a query for the topics
$sql = "SELECT
topic_id,
topic_subject,
topic_date,
topic_cat
FROM
topics
WHERE
topic_cat = " . mysql_real_escape_string($_GET['topic_id']) . "";
$result = mysql_query($sql);
if(!$result)
{
echo 'The topics could not be displayed, please try again later.';
}
else
{
if(mysql_num_rows($result) == 0)
{
echo 'There are no topics in this category yet.';
}
else
{
//prepare the table
echo '<table border="1">
<tr>
<th>Topic</th>
<th>Created at</th>
</tr>';
while($row = mysql_fetch_assoc($result))
{
echo '<tr>';
echo '<td class="leftpart">';
echo '<h3><a href="topic.php?id=' . $row['topic_id'] . '">' . $row['topic_subject'] . '</a><h3>';
echo '</td>';
echo '<td class="rightpart">';
echo date('d-m-Y', strtotime($row['topic_date']));
echo '</td>';
echo '</tr>';
}
}
}
}
}
?> So above is all the code I used to build the forum, The major problem is to display each category with its topics, and this is not working. and them I need to create a file for displaying each topic separately. I tr that but not working also.
Thanks;
D. Cate