Here we go! Off to create a completely database driver News script system. This is my first ever tutorial, there will more than likely be some edits along the way. Everything I write will be in OOP (Object Oriented Programming). Basically, we're going to use classes and functions to make sure everything works the way it should. I've posted a display, showing you how I structure my folders while I work on websites.
1
2
3
4
5
6
7
8
9
10
11
12
13
| ROOT or PUBLIC_HTML -
|
images (Folder) -
|
All of my images.
|
inc (Folder) -
|
Any of my class files and includes will go here.
|
styles (Folder) -
|
Any styles I have will go here. |
We'll start off by making sure you have a few bits of code that are very usefully. I'll start by showing you a simple Mysql Class file. You can follow the comments in the code for more help. I'll go over everything after the code. Make sure you save this file (Mysql.php) in the inc folder. If you choose not to make sure you keep up with your paths.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
| <?php
define( DBHOST, "your host goes here", true );
define( DBUSER, "your username", true );
define( DBPASS, "your password", true );
define( DBNAME, "your database", true );
class Mysql
{
public function __construct()
{
mysql_connect( DBHOST,DBUSER,DBPASS ) or die( mysql_error() );
mysql_select_db( DBNAME ) or die( mysql_error() );
}
public function select($fields,$table,$where='',$orderby='',$limit='')
{
if($where != '') $where = " WHERE $where";
if($orderby != '') $orderby = " ORDER BY $orderby";
if($limit != '') $limit = " LIMIT $limit";
$recordSet =
mysql_query(
"SELECT $fields FROM $table" . $where . $orderby . $limit
) or die(
"Selecting $table - SELECT $fields FROM $table" . $where . $orderby . $limit .
" - " . mysql_error()
);
if (!$recordSet)
{
return "Record Set Error";
}
else
{
$recordSet = new MysqlRecordSet( $recordSet );
}
return $recordSet;
}
public function insert($table, $fields, $where='')
{
if($where != '') $where = " WHERE $where";
$query =
mysql_query(
"INSERT INTO $table SET $fields" . $where
) or die(
"Insert Error - INSERT INTO $table SET $fields" . $where . " - " . mysql_error()
);
if($query)
{
return true;
}
return false;
}
public function update($table,$fields,$where='')
{
if($where != '') $where = " WHERE $where";
$query =
mysql_query(
"UPDATE $table SET $fields" . $where
) or die(
"Update Error - UPDATE $table SET $fields" . $where . " - " . mysql_error()
);
if($query)
{
return true;
}
return false;
}
public function delete($table,$where)
{
$query =
mysql_query(
"DELETE FROM $table WHERE $where"
) or die(
"Delete Error - DELETE FROM $table WHERE $where" . " - " . mysql_error()
);
if($query)
{
return true;
}
return false;
}
public static function getInsertId()
{
return mysql_insert_id();
}
}
class MysqlRecordSet
{
var $recordSet;
function MysqlRecordSet( &$recordSet ) { $this->recordSet = $recordSet; return; }
function __construct( &$recordSet ) { $this->recordSet = $recordSet; return; }
function getRecordCount() { return mysql_num_rows($this->recordSet); }
function seek( $recordIndex ) { return mysql_data_seek( $this->recordSet, $recordIndex ); }
function getFirstRecord() {
mysql_data_seek( $this->recordSet, 0 );
return mysql_fetch_array( $this->recordSet ); }
function getNextRecord() { return mysql_fetch_array( $this->recordSet ); }
function getLastRecord() {
mysql_data_seek( $this->recordSet, mysql_num_rows($this->recordSet)-1 );
return mysql_fetch_array( $this->recordSet ); }
function free() { return mysql_free_result( $this->recordSet ); }
}
?> |
Ok, now to explain some of the functions in simple terms.
1
| public function select() |
This function works just the same way mysql_query("SELECT ....") would work. It return a MysqlRecordSet which allows you to iterate and control the mysql resource better than using mysql_fetch_array() throughout your code. I Have to give credit to My Boss and fellow developer for the MysqlRecordSet class. You can visit his website here at mmckinneysystems.com.
1
2
3
| public function insert()
public function update()
public function delete() |
All of these functions work just like the function name says. They run the according mysql command, returning true or false.
The next step in this process is to create your news class. I'll place the code here and put comments in it. I'll explain more after the code. Make sure you save the file (News.php) in the inc folder.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
| <?php
require_once 'Mysql.php';
class News
{
var $mysql;
public function __construct()
{
$this->mysql = new Mysql();
}
public function getNews()
{
$rset = $this->mysql->select('*',"cs_news","postDateTime <= NOW()","postDateTime DESC");
return $rset;
}
public function getNewsById($id)
{
$rset = $this->mysql->select('*',"cs_news","newsId='$id'");
return $rset;
}
public function buildFields($post, $sep=" ")
{
$fields = "";
foreach($post as $key => $value)
{
$value = mysql_escape_string($value);
if($i == 0)
$fields .= "$key='$value'";
else
$fields .= $sep . "$key='$value'";
$i++;
}
return $fields;
}
public function addNews($post)
{
$fields = $this->buildFields($post, ", ");
if( $this->mysql->insert("cs_news",$fields) )
{
return true;
}
else
{
return false;
}
}
public function editNews($post)
{
$fields = $this->buildFields($post, ", ");
$newsId = $post['newsId'];
if( $this->mysql->update("cs_news",$fields,"newsId='$newsId'") )
{
return true;
}
else
{
return false;
}
}
public function deleteNews($newsId)
{
if( $this->mysql->delete("cs_news","newsId='$newsId'") )
{
return true;
}
else
{
return false;
}
}
}
?> |
Alright there is your news class. Its fairly simple. Gives you five different options. You can get all of the records, one of the record, add, edit, and delete. All of the basic functions you would likely need. Now all we need is some databases to retreive and add this information too. So in this next section i'll explain how to create your database.
First we'll begin by creating a database. I'll show you three different ways you could do it. The first way is using a php file. Below is the code for that. Save the file in your root directory. You can name it createDb.php, you won't use it after this.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
| <?php
require_once 'inc/Mysql.php';
$mysql = new Mysql();
$sql = "
CREATE TABLE `cs_news` (
`newsId` int(10) unsigned NOT NULL auto_increment,
`companyId` int(10) unsigned NOT NULL,
`userId` int(10) unsigned NOT NULL,
`newsTitle` varchar(45) NOT NULL,
`newsSummary` varchar(100) NOT NULL,
`newsArticle` longtext NOT NULL,
`postDateTime` datetime NOT NULL,
`currentStatus` enum('Enabled','Disabled') collate latin1_general_ci NOT NULL default 'Disabled',
PRIMARY KEY USING BTREE (`newsId`)
) ENGINE=MyISAM AUTO_INCREMENT=7;
";
mysql_query( $sql ) or die( mysql_error() );
$sql = "
SHOW COLUMNS FROM cs_news
";
$rset = mysql_query( $sql );
$record = mysql_fetch_array( $rset );
?><pre><?php print_r( $record ); ?></pre><?php
?> |
If you have access to a script called phpMyAdmin, which can be found on most web servers now. You can use phpMyAdmin to create the database. Either using that create statement from the previous code section or using there functions. The last way you can create this database is by downloading the free Mysql Software tools. They have a set of tools that are designed to help the average and professional developers. You can find the software here. Once, you download it and have installed it, its fairly simple to use. When you have it up the first thing its going to ask you the information for your Mysql server. Fill out the information and press connect. Once you've connected towards the right side of the program, you'll right click and and say create new database. Fill out the name of your database. Then right clicking in the same area and select create new table. Here you can fill in the table name and each one of the fields.
This tool is a lot to figure out your first time around. I'll create more tutorials on this program and mysql at a later date. If you feel like this method is to complex or you dont have remote access to your server use the first method.
The next part we are going to touch on is going to be the php pages that will display your new found new and mysql classes. The first page we are going to create is going to be our index or home page. You can name it which ever you want. I am going to use index for this example. Make sure you save it in your root directory. I am going to show you the code, then explain it as we go. I'll explain it in detail after the code.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
| <?php
require_once 'inc/News.php';
$news = new News();
$newsRecordSet = $news->getNews();
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {color: #FFFFFF}
-->
</style></head>
<?php
?>
<body>
<table width="500" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="3" align="center" valign="middle" bgcolor="#000000"><span class="style1">News</span></td>
</tr>
<?php
while( ($record = $newsRecordSet->getNextRecord()) !== false )
{
?>
<tr>
<td colspan="2">
<strong><?=$record['newsTitle']?></strong>
</td>
<td width="36" align="center" valign="top">
<a href="editNews.php?id=<?=$record['newsId']?>">Edit</a> </td>
</tr>
<tr>
<td colspan="3" align="left" valign="top">
<?=$record['newsArticle']?>
</td>
</tr>
<tr>
<td colspan="3" align="left" valign="top">
<?=$record['userId']?>
</td>
</tr>
<?php
}
?>
</table>
<br /><br />
<a href="addNews.php">Add News</a>
</body>
</html> |
This code is extremely simple. This is the part where you're creativity comes in hand. Make your own table or site to display the news information. All you have to make sure you have is the php and php echos in there html somewhere. Remember.
That code will display the $variable's VALUE for that KEY.
Now we have at least one page finished. For now you wont see anything on that page. You're going to need to create the addNews.php page and add a record to the database before something shows up. This next page will do just that. As with every other page in this tutorial i'll explain as much as I can in this code and go over it again afterwards.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
| <?php
require_once 'inc/News.php';
$news = new News();
if(isset($_POST['addNews']))
{
$post['newsTitle'] = $_POST['newsTitle'];
$post['newsArticle'] = $_POST['newsArticle'];
$post['postDateTime'] = date("Y-m-d H:i:s", time());
$post['userId'] = 1;
$post['currentStatus'] = 'Enabled';
if($news->addNews($post))
{
echo 'The News was Added Succesfully';
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {color: #FFFFFF}
-->
</style>
</head>
<body>
<a href="index.php">Index<a><br />
<form id="form1" name="form1" method="post" action="addNews.php">
<table width="500" border="0" cellspacing="0" cellpadding="0">
<tr>
<td colspan="2" align="center" valign="top" bgcolor="#000000"><span class="style1">Add News</span></td>
</tr>
<tr>
<td width="92">News Title :</td>
<td><label>
<input type="text" name="newsTitle" id="newsTitle" />
</label></td>
</tr>
<tr>
<td>News Article :</td>
<td><label>
<textarea name="newsArticle" cols="50" id="newsArticle"></textarea>
</label></td>
</tr>
<tr>
<td colspan="2" align="center" valign="top"><label>
<input type="submit" name="addNews" id="addNews" value="Add News" />
</label></td>
</tr>
</table>
</form>
</body>
</html> |
Very very very simple. Not a whole lot to explain on this one. There isn't a lot to explain with the editNews.php page either. Its exactly the same page except at the beginning of the page you check to see if an id is passed to you in the url. Take the id do getNewsById($id) and display the newsTitle and newsArticle in the approprieate fields. Here is the code for editNews.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
| <?php
require_once 'inc/News.php';
$news = new News();
$id = $_GET['id'];
if(isset($_POST['editNews']))
{
$post['newsId'] = $id;
$post['newsTitle'] = $_POST['newsTitle'];
|
Views: 4734
based on 0 votes.
