Tutorial Management System Part 1: Getting Started
Welcome to the 5 part, in depth tutorial on making a complete tutorial management system. This is the first of the five part tutorial. Please make sure you remember that none of the parts of this tutorial will work on their own, but together they will work like a charm.
Ok, let’s start by setting everything up. First we will make a database for out tutorial system. That is your job. Next, we run this section of MySQL on that database you just set up.
--
-- Table structure for table `category`
--
CREATE TABLE `category` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`avatar` text NOT NULL default '',
`description` text NOT NULL,
`site` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;
-- --------------------------------------------------------
--
-- Table structure for table `comments`
--
CREATE TABLE `comments` (
`cid` int(10) NOT NULL auto_increment,
`id` int(10) NOT NULL default '0',
`ip` text NOT NULL,
`cat` varchar(255) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`comment` text NOT NULL,
`date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`cid`)
) TYPE=MyISAM AUTO_INCREMENT=141 ;
-- --------------------------------------------------------
--
-- Table structure for table `tutorials`
--
CREATE TABLE `tutorials` (
`id` int(10) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`keywords` text NOT NULL,
`avatar` varchar(255) NOT NULL default '',
`category` varchar(255) NOT NULL default '',
`skill` varchar(255) NOT NULL default '',
`brief` text NOT NULL,
`tutorial` text NOT NULL,
`person` varchar(255) NOT NULL default '',
`timestamp` timestamp(14) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;
This MySQL will set up 3 tables, Category which will hold all our category info, Comments which will be used to store the comments made by the public and offcourse Tutorials which will store all our tutorials.
Our next step is to get our files working. In the root directory that you want this tutorial system to be in, create a file called dbconnect.php and add this code to it.
This MySQL will set up 3 tables, Category which will hold all our category
info, Comments which will be used to store the comments made by the public and
offcourse Tutorials which will store all our tutorials.
Our next step is to get our files working. In the root directory that you want
this tutorial system to be in, create a file called dbconnect.php and add this
code to it.
$connection = mysql_connect ('localhost', 'username', 'password')
or die ('Unable to connect!');
@mysql_select_db('database') or die (mysql_error());
?>
Make sure you change ‘username’, ‘password’ and ‘database’ accordingly. If
you are unsure about what your MySQL username and pass is then contact your
server administrator, but generally with cPanel hosting it’s the same login
details that you use to get into cPanel.
Now create a folder called ‘admin’ in that root directory you just chose. This
folder will be where we store all our administration pages like edit and
delete. Inside the admin folder that you just created, create a page called
‘index.php’ and put the following script into it.
<?php
include('../dbconnect.php');
if(!isset($_GET['page'])){
$page = 1;
}else{
$page = $_GET['page'];
}
$max_results = 10;
$from = (($page * $max_results) - $max_results);
$query = "SELECT * FROM `tutorials` ORDER BY id DESC LIMIT $from, $max_results";
$result = mysql_query($query)
or die("Error in query: $query . " . mysql_error());
echo '<a href="add.php">Add Tutorial</a> | <a href="add_category.php">Add category</a> | <a href="category.php">Manage Catagories</a><hr>';
if(mysql_num_rows($result) > 0)
{
while($row = mysql_fetch_row($result))
{
?>
<table width="100%">
<tr>
<td width="40"><img src="<?php echo $row[3] ?>" alt="<?php echo $row[1]; ?>"></td>
<td><h1><?php echo $row[0]; ?>: <?php echo $row[1]; ?><br /><a href="edit.php?id=<?php echo $row[0]; ?>">Edit</a> | <a href="delete.php?id=<?php echo $row[0]; ?>" onClick="return confirm('Are you sure you want to delete this tutorial?')">Delete</a> |
<?php
$count = mysql_query("SELECT * FROM comments WHERE id='".$row[0]."' AND cat='tutorials'") or die(mysql_error());
$comments = mysql_num_rows($count);
echo '<a href="comments.php?id='.$row[0].'">'.$comments.' comments</a>';
?></h1></td>
</tr>
</table>
<hr>
<?php
}
}
else
{
echo 'No Tutorials<hr class="news">';
}
echo '<center>';
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM tutorials"),0);
$total_pages = ceil($total_results/$max_results);
for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i ";
}else{
echo "<a href='?page=$i'>$i</a> ";
} }
echo '</center><hr>';
echo '<a href="add.php">Add Tutorial</a> | <a href="add_category.php">Add category</a> | <a href="category.php">Manage Catagories</a>';
mysql_close($connection);
?>
This page won’t do anything yet because you do not have the rest of your admin
panel done. But when it is done it will contain a list of your tutorials and
have links to ways of managing them differently.
Well that’s the first section of the tutorial management system tutorial
complete. Thank you for reading and you can follow the links to the other parts
of the tutorial.
[1] [2] [3] [4] [5] Next » | Tutorial System ZIP (124)
Join our RSS feed
Digg
Del.icio.us
Reddit
Furl
16 Replies...
And none of this tutorial will work.
The author has many errors within the code.
And didnt even bother to first test this out prior to posting the tutorials.
First error is:
include(’../dbconnect.php’);
Second error is:
$sql = “SELECT * FROM `tutorials` ORDER BY id DESC LIMIT $from, $max_results”;
../ goes to the folder one level above in a file system. It works fine, feel free to try. In fact, I encourage you to.
Oh, and putting variables directly into SQL script does work. Its not the best method and I did miss it while writing this, but thats apart from the fact because it is still functional.
I guess you’re waiting for hackers to attack your site. SQL injection is an easy job because you don’t neither check the $_POST nor the $_GET variables. Just one simple SQL command and your database is gone..
Uh, I seem to have forgotten that in the download and will ad it asap. However, just create a blank database then copy and paste the entire red SQL section of this tutorial until I do have the SQL file.
The section in red at the beginning of this tutorial is the only SQL you will need to deal with (excluding the many queries within the PHP documents).
I am revamping my whole website and I want to use this tutorial system but I get this SQL error when I try to run that query
#1064 - 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 ‘–
– Table structure for table `category`
–
CREATE TABLE `categ’ at line 1
I have had this problem with SQL before and had no idea what caused it. I will put the SQL query into a .txt file and ad it to the download file. Hopefully that will help.
Its a wordpress/browser issue when you copy & paste it. Something to do with stylized text. I don’t know how to fix it.
Download the zip.
Your Web Site is really wonderful and I bookmarked it. Thank your for the hard work you must have put in to create this wonderful facility. Keep up the excellent work!
The site very professional! Keep up the good work! Oh yes, one extra comment - maybe you could add more pictures too! So, good luck to your team!
RSS feed for comments on this post. TrackBack URL
Leave a comment
