Setting Up the Database for Your Portfolio Website : Day 3

Welcome to Day 3 of our journey to build a portfolio website with CodeIgniter! Today, we’ll focus on setting up the database for our application. A well-structured database is essential for storing and managing data efficiently. Let’s get started!

So before Setting up to the database we should know lsome basic

Why Database Setup is Important

The database will store information such as user profiles, project details, blog posts, and more. It forms the backbone of our portfolio website, enabling us to dynamically retrieve and display content to users.

Choosing a Database System

CodeIgniter supports various database systems, including MySQL, PostgreSQL, SQLite, and others. For this tutorial, we’ll use MySQL, which is widely used and easy to set up.

Step-by-Step Guide to Setting Up the Database

Step 1: Create a Database

1. Open phpMyAdmin: Access phpMyAdmin through your local server (e.g., XAMPP) by navigating to http://localhost/phpmyadmin.
2. Create a New Database: Click on “New” in the left sidebar. Enter a name for your database (e.g., portfolio_website) and choose utf8_general_ci as the collation. Click on “Create” to create the database.

Step 2: Define Tables and Structure

Now, let’s define the tables we’ll need for our portfolio website. We’ll create tables for users, projects, and blog_posts.

1. Users Table:
   • Columns: id (primary key), username, email, password_hash (hashed password), created_at, updated_at.

CREATE TABLE users ( id INT(11) AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

1. Projects Table:
   • Columns: id (primary key), user_id (foreign key to users), title, description, image, created_at, updated_at.

CREATE TABLE projects ( id INT(11) AUTO_INCREMENT PRIMARY KEY, user_id INT(11) NOT NULL, title VARCHAR(255) NOT NULL, description TEXT, image VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) );

1. Blog Posts Table:
   • Columns: id (primary key), user_id (foreign key to users), title, body, created_at, updated_at.

CREATE TABLE blog_posts ( id INT(11) AUTO_INCREMENT PRIMARY KEY, user_id INT(11) NOT NULL, title VARCHAR(255) NOT NULL, body TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) );

Step 3: Insert Sample Data (Optional)

If you’d like to populate your tables with sample data for testing purposes, you can use INSERT statements. Here’s an example for the users table:

sqlCopy codeINSERT INTO users (username, email, password_hash) VALUES
('john_doe', 'john.doe@example.com', 'hashed_password_here'),
('jane_smith', 'jane.smith@example.com', 'hashed_password_here');

Connecting CodeIgniter to the Database

Next, we’ll configure CodeIgniter to connect to our MySQL database. Open application/config/database.php and update the following settings:

  $db['default'] = array(
    'dsn'      => '',
    'hostname' => 'localhost',
    'username' => 'root', // Your database username
    'password' => '',     // Your database password
    'database' => 'portfolio_website', // Your database name
    'dbdriver' => 'mysqli',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_general_ci',
    'swap_pre' => '',
    'encrypt'  => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

Conclusion

Congratulations! You’ve successfully set up the database for your portfolio website using CodeIgniter. In Day 4, we’ll explore how to connect CodeIgniter to the database and perform basic CRUD (Create, Read, Update, Delete) operations.

Feel free to leave any questions or comments below. Happy coding!