Creating a Simple CMS [PHP + MySQL ?]

[alejandroangulo32]

I wanted to create a simple CMS but I'm not sure how to start off. I've started working on the MySQL database. All I have right now is a table titled posts.

Here's the output of describe posts;

+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field     | Type        | Null | Key | Default           | Extra                       |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| id        | int(11)     | NO   | PRI | NULL              | auto_increment              |
| title     | varchar(80) | YES  |     | Untitled          |                             |
| author    | varchar(50) | YES  |     | Anonymous         |                             |
| content   | text        | NO   |     | NULL              |                             |
| timestamp | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-------------+------+-----+-------------------+-----------------------------+

I was wondering if this was a good way to start off my database. What other tables will I have to create? I plan on creating a user system, but for now I want to create a barebones CMS.

I can figure out how to write the PHP on my own, but I'm not sure about the MySQL.

I'm not planning on using a WYSIWYG editor, instead I'll just write files by hand and insert them into the database. I was also wondering how I should handle images? Should I just create a directory for them, or insert them into the database, or insert links in the database to the path of the actual image?

I hope I'm not asking for too much. I don't need to have my hand held, I'd just like a push in the right direction.

Thanks in advance.

 

[MaestroFX1]

Hello

Any special reason to do so ? Is it some sort of experiment that you are doing or something like that?
I mean you can always take the easier way out by using a CMS like wordpress or drupal

--------
I was also wondering how I should handle images? Should I just create a directory for them, or insert them into the database, or insert links in the database to the path of the actual image?
--------
Avoid storing images in mysql databases.Links to location should be the preffered solution.

 

[alejandroangulo32]

Yeah, I'm just experimenting. WP and Drupal are bloated for me, I just need something simple. Besides, I think coding is sorta fun.

So any noob mistakes I should avoid, like forgetting to sanitize queries?

 

[creastery]

+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field     | Type        | Null | Key | Default           | Extra                       |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| id        | int(11)     | NO   | PRI | NULL              | auto_increment              |
| title     | varchar(80) | YES  |     | Untitled          |                             |
| author    | varchar(50) | YES  |     | Anonymous         |                             |
| content   | text        | NO   |     | NULL              |                             |
| timestamp | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------+-------------+------+-----+-------------------+-----------------------------+

One thing to note about using this table is that if you are intending to work on multiple pages for CMS, you should modify the table a little bit to fit your needs.
Otherwise, the table above should be a good start.

For the user system, you should at least have the structure below.
+-----------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(100) | NO | | | |
| password | varchar(100) | NO | | | |
| admin | int(11) | NO | | 0 | |
+-----------+-------------+------+-----+-------------------+-----------------------------+
Note: When `admin` is given a value of 0, the user is not admin.

To handle images, you should just create a directory for them and if needed, make a page to upload the images. That should settle your problem.

These are some mistakes I usually make, so take note of them as well!
1) Creating security loopholes (Using GET when I am supposed to use POST)
2) Messing up database while experimenting with PHP

If you need any more help, feel free to leave me a private message
Good luck on your CMS

 

[vv.bbcc19]

Here is a tutorial.Small one though..
The first step is to simply lay out the class in a file named 'simpleCMS.php' so we have a road map to work with.

<?php

class simpleCMS {
  var $host;
  var $username;
  var $password;
  var $table;

  public function display_public() {

  }

  public function display_admin() {

  }

  public function write() {

  }

  public function connect() {

  }

  private function buildDB() {

  }
}

?>

As you can see, we're creating one class with four variables and five methods. I've opted to use PHP's object-oriented approach because it makes for cleaner code in large projects, and, in my opinion, it's just good practice.
The Variables

In this case, all four variables are for connecting to the database: $host, $username, $password, and $table provide a path and access to our database on the server. For now, we'll leave those empty and move on to our database, which is constructed by the method buildDB().
Build the Database

private function buildDB() {
    $sql = <<<MySQL_QUERY
        CREATE TABLE IF NOT EXISTS testDB (
            title       VARCHAR(150),
            bodytext    TEXT,
            created     VARCHAR(100)
    )
    MySQL_QUERY;

    return mysql_query($sql);
}

This function runs a MySQL command that checks the database to see if testDB exists. If so, it simply passes along a notification of success; if not, it creates our table and assigns three columns to hold data.

Connect to the Database

public function connect() {
    mysql_connect($this->host,$this->username,$this->password) or die("Could not connect. " . mysql_error());
    mysql_select_db($this->table) or die("Could not select database. " . mysql_error());

    return $this->buildDB();
}

We call mysql_connect() to hook into our database, and then mysql_select_db() to make sure we save our data in the right place. Both of these functions are accompanied by the die() command, which essentially says, "in the event that this function fails, stop execution of this script and display a message."

Our connect() function connects to the database and gets us pointed in the right direction, then runs our buildDB() function. Remember the grammatically awkward "IF NOT EXISTS" part of our MySQL command? Because we're going to run this function every time the page is loaded, we have to make sure we're not overwriting our database with every function call, and that's exactly what that phrase requires.

Build the Form

public function display_admin() {
    return <<<ADMIN_FORM

    <form action="{$_SERVER['PHP_SELF']}" method="post">
      <label for="title">Title:</label>
      <input name="title" id="title" type="text" maxlength="150" />
      <label for="bodytext">Body Text:</label>
      <textarea name="bodytext" id="bodytext"></textarea>
      <input type="submit" value="Create This Entry!" />
    </form>

ADMIN_FORM;
  }

Again, this is a very simple function. When called, it simply returns the HTML markup to create our form. You'll notice, however, in the action attribute of the form element, that I've used the variable $_SERVER['PHP_SELF']. This is, essentially, a shortcut that references the file you're currently using (in our case, it's display.php). This is useful if you'll be reusing your code across a site and don't necessarily want to rewrite this function for each page.

I'm also going to take a second right now to talk about the method I'm using to return the HTML. It's a format used in PHP called HEREDOC syntax, and I love it.

The primary advantage of HEREDOC is that it allows you to include formatting in your output. This is extraordinarily useful for folks like me who take issue with cluttered source code. You can read more about HEREDOC syntax and its ilk in the PHP manual.

Next steps later...

 

[callumacrae]

Build the Database

private function buildDB() {
    $sql = <<<MySQL_QUERY
        CREATE TABLE IF NOT EXISTS testDB (
            title       VARCHAR(150),
            bodytext    TEXT,
            created     VARCHAR(100)
    )
    MySQL_QUERY;

    return mysql_query($sql);
}

The closing identifier must be at the beginning of the line as per php.net's heredoc documentation:

private function buildDB() {
    $sql = <<<MySQL_QUERY
        CREATE TABLE IF NOT EXISTS testDB (
            title       VARCHAR(150),
            bodytext    TEXT,
            created     VARCHAR(100)
    )
MySQL_QUERY;

    return mysql_query($sql);
}

~Callum

 

[vv.bbcc19]

Ahah..thanks for the message.I did not get until then how your posts have a code segment separately put.
I understood when you told about

Thanks for editing the post to make it look good.

[quote="callumacrae, post: 801392"]The closing identifier [B]must[/B] be at the beginning of the line as per [URL="http://uk2.php.net/manual/en/language.types.string.php#language.types.string.syntax.heredoc"]php.net's heredoc documentation[/URL]:

[php]private function buildDB() {
    $sql = <<<MySQL_QUERY
        CREATE TABLE IF NOT EXISTS testDB (
            title       VARCHAR(150),
            bodytext    TEXT,
            created     VARCHAR(100)
    )
MySQL_QUERY;

    return mysql_query($sql);
}

~Callum[/QUOTE]

 

[misson]

I wanted to create a simple CMS but I'm not sure how to start off.
[...]
What other tables will I have to create? I plan on creating a user system, but for now I want to create a barebones CMS.

Database design is a matter of modeling. Ask yourself: what are the properties of the things (e.g. pages, posts, authors/contributors/editors) you are modeling? Express this using the relational model (as you are using an RDB), where to-one properties become columns, and to-many (both one-to-many and many-to-many) properties are stored in their own table (with columns that reference rows in the object tables).

As for a users table, an "admin" column (which should be a BOOL or INT(1)) isn't enough. Better is to have a "row" column (of ENUM or VARCHAR type), or (better still) a separate table relating users to the roles they have.

I was also wondering how I should handle images? Should I just create a directory for them, or insert them into the database, or insert links in the database to the path of the actual image?

MaestroFX has the short of it; for more, see "Storing Images in DB - Yea or Nay?"