Create and manage webpages dynamically using PHP MySQL

Nityananda Maity August 18, 2022

Dynamic web page creation functionality helps to make the HTML content of the web page manageable by the admin/user. The user can create an HTML web page with dynamic content and modify the page content in the future. The HTML web page management feature is mainly used in the web application’s admin panel, which allows the site admin to create/update/delete HTML web pages dynamically.

HTML web page management functionality can be implemented with CRUD operations. PHP CRUD operations can help you to create and manage dynamic HTML pages with MySQL. In this tutorial, we will show you how to generate web pages and manage HTML content dynamically with database using PHP and MySQL.

In this example script, the following functionality will be implemented to build dynamic HTML page management system with PHP and MySQL.

Fetch page data from the database and list them on the web page.
Create an HTML page with dynamic content using PHP.
Add and insert page data in the database using PHP and MySQL.
Create dynamic URL of the webpage and allow access to the dynamic web page.
Edit and update page content with PHP.
Delete page data from the database and HTML files from the server.

Before getting started to create a CRUD application with dynamic HTML page management, take a look at the file structure.

Create Database Table

To store page information a table is required in the database. The following SQL creates a pages table with some required fields in the MySQL database.

CREATE TABLE `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `page_uri` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `content` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime NOT NULL DEFAULT current_timestamp(),
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Configuration File `(config.php)`

In the config.php file, common settings and database configuration variables are defined.

$pageDir – Specify the folder path where the page files will be stored.
$pageExtention – Specify the extension of the page file (.html/.php).
$list_excerpt_length – Character limit of the content shown in the page list.
DB_HOST – Database host.
DB_USERNAME – Database username.
DB_PASSWORD – Database password.
DB_NAME – Database name.

CRUD Handler Class

The PageHandler class is a custom PHP library that handles all the CRUD-related operations (fetch, insert, update, and delete) with MySQL.

getRows() – Fetch records from the database using PHP and MySQL.
insert() – Insert data into the database.
update() – Update existing data in the database based on specified conditions.
delete() – Remove a record from the database by ID.
isPageExists() – Check if a record is existing with the same page title in the database.
generatePageUri() – Generate page URL slug from string.

db)) {
            // Connect to the database
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
            if ($conn->connect_error) {
                die("Failed to connect with MySQL: " . $conn->connect_error);
            } else {
                $this->db = $conn;
            }
        }
    }

    /*
     * Returns rows from the database based on the conditions
     * @param array select, where, order_by, limit and return_type conditions
     */

    public function getRows($conditions = [])
    {
        $sql = 'SELECT ';
        $sql .= array_key_exists("select", $conditions) ? $conditions['select'] : '*';
        $sql .= ' FROM ' . $this->dbTable;
        if (array_key_exists("where", $conditions)) {
            $sql .= ' WHERE ';
            $i = 0;
            foreach ($conditions['where'] as $key => $value) {
                $pre = $i > 0 ? ' AND ' : '';
                $sql .= $pre . $key . " = '" . $value . "'";
                $i++;
            }
        }

        if (array_key_exists("order_by", $conditions)) {
            $sql .= ' ORDER BY ' . $conditions['order_by'];
        } else {
            $sql .= ' ORDER BY id DESC ';
        }

        if (array_key_exists("start", $conditions) && array_key_exists("limit", $conditions)) {
            $sql .= ' LIMIT ' . $conditions['start'] . ',' . $conditions['limit'];
        } elseif (!array_key_exists("start", $conditions) && array_key_exists("limit", $conditions)) {
            $sql .= ' LIMIT ' . $conditions['limit'];
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute();
        $result = $stmt->get_result();

        if (array_key_exists("return_type", $conditions) && $conditions['return_type'] != 'all') {
            switch ($conditions['return_type']) {
                case 'count':
                    $data = $result->num_rows;
                    break;
                case 'single':
                    $data = $result->fetch_assoc();
                    break;
                default:
                    $data = '';
            }
        } else {
            if ($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
                    $data[] = $row;
                }
            }
        }
        return !empty($data) ? $data : false;
    }

    /*
     * Insert data into the database
     * @param array the data for inserting into the table
     */

    public function insert($data)
    {
        if (!empty($data) && is_array($data)) {
            if (!array_key_exists('created', $data)) {
                $data['created'] = date("Y-m-d H:i:s");
            }
            if (!array_key_exists('modified', $data)) {
                $data['modified'] = date("Y-m-d H:i:s");
            }

            $placeholders = array_fill(0, count($data), '?');

            $columns = $values = [];
            foreach ($data as $key => $val) {
                $columns[] = $key;
                //$values[] = !empty($val)?$this->db->real_escape_string($val):NULL;
                $values[] = !empty($val) ? $val : null;
            }

            $sqlQ = "INSERT INTO {$this->dbTable} (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $placeholders) . "); ";
            $stmt = $this->db->prepare($sqlQ);

            $types = [str_repeat('s', count($values))];
            $params = array_merge($types, $values);

            call_user_func_array([$stmt, 'bind_param'], $params);

            $insert = $stmt->execute();
            return $insert ? $this->db->insert_id : false;
        } else {
            return false;
        }
    }

    /*
     * Update data into the database
     * @param array the data for updating into the table
     * @param array where condition on updating data
     */

    public function update($data, $conditions)
    {
        if (!empty($data) && is_array($data)) {
            if (!array_key_exists('modified', $data)) {
                $data['modified'] = date("Y-m-d H:i:s");
            }

            $placeholders = array_fill(0, count($data), '?');

            $columns = $values = [];
            foreach ($data as $key => $val) {
                $columns[] = $key;
                //$values[] = !empty($val)?$this->db->real_escape_string($val):NULL;
                $values[] = !empty($val) ? $val : null;
            }

            $whr_columns = $whr_values = [];
            $where_columns = '';
            if (!empty($conditions) && is_array($conditions)) {
                foreach ($conditions as $key => $val) {
                    $whr_columns[] = $key;
                    $whr_values[] = !empty($val) ? $this->db->real_escape_string($val) : null;
                }

                $where_columns = " WHERE " . implode('=?, ', $whr_columns) . "=? ";
            }

            $sqlQ = "UPDATE {$this->dbTable} SET " . implode('=?, ', $columns) . "=? $where_columns ";
            $stmt = $this->db->prepare($sqlQ);

            if (!empty($whr_columns)) {
                $values_where_arr = array_merge($values, $whr_values);
                $types = [str_repeat('s', count($values_where_arr))];
                $params = array_merge($types, $values_where_arr);
            } else {
                $types = [str_repeat('s', count($values))];
                $params = array_merge($types, $values);
            }

            call_user_func_array([$stmt, 'bind_param'], $params);

            $update = $stmt->execute();
            return $update ? $this->db->affected_rows : false;
        } else {
            return false;
        }
    }

    /*
     * Delete data from the database
     * @param array where condition on deleting data
     */

    public function delete($id)
    {
        $sqlQ = "DELETE FROM {$this->dbTable} WHERE id=?";
        $stmt = $this->db->prepare($sqlQ);
        $stmt->bind_param("i", $id);
        $delete = $stmt->execute();
        return $delete ? true : false;
    }

    public function isPageExists($title, $id = '')
    {
        $sqlQ = "SELECT * FROM {$this->dbTable} WHERE LOWER(title)=?";
        if (!empty($id)) {
            $sqlQ .= " AND id != ?";
        }
        $stmt = $this->db->prepare($sqlQ);

        if (!empty($id)) {
            $stmt->bind_param("si", $title_lwr, $id);
        } else {
            $stmt->bind_param("s", $title_lwr);
        }
        $title_lwr = strtolower($title);

        $stmt->execute();
        $result = $stmt->get_result();
        return $result->num_rows > 0 ? true : false;
    }

    public function generatePageUri($string, $wordLimit = 0)
    {
        $separator = '_';

        if ($wordLimit != 0) {
            $wordArr = explode(' ', $string);
            $string = implode(' ', array_slice($wordArr, 0, $wordLimit));
        }

        $quoteSeparator = preg_quote($separator, '#');

        $trans = [
            '&.+?;' => '',
            '[^\w\d _-]' => '',
            '\s+' => $separator,
            '(' . $quoteSeparator . ')+' => $separator,
        ];

        $string = strip_tags($string);
        foreach ($trans as $key => $val) {
            $string = preg_replace('#' . $key . '#iu', $val, $string);
        }

        $string = strtolower($string);

        return trim(trim($string, $separator));
    }
}

Pages CRUD Operations with PHP (userAction.php)

Using PHP and MySQL, the userAction.php file performs the CRUD operations with Handler Class (PageHandler.class.php). The code block is executed based on the requested action.

Add/Edit Page

The form is submitted by userSubmit with the POST method.
Retrieve input’s value (page title and content) using PHP $_POST method.
Validate input data with PHP.
Create page slug from title using generatePageUri() method of PageHandler class.
Get the common layout from default HTML page using file_get_contents() function in PHP.
Replace PAGE_TITLE and PAGE_CONTENT with the dynamic page data using PHP.
Generate HTML page dynamically and create a file on the server using PHP file_put_contents() function.
If an existing ID is supplied, update data in the database using the update() method of the PageHandler class. Otherwise, insert data in the database using the insert() method of the PageHandler class.

Delete Records

If delete is requested in action_type, remove page data from the database based on the id given in the query string.
Remove page files from the server using the PHP unlink() function.

After the data manipulation, the status is stored in SESSION with PHP and redirects to the respective page.

Please enter title.'; 
    }elseif($pageHandle->isPageExists($title, $id)){ 
        $errorMsg .= 'The page with the same title already exists.'; 
    } 
 
    if(empty($content)){ 
        $errorMsg .= 'Please enter page content.'; 
    } 
     
    // Submitted form data 
    $pageData = array( 
        'title' => $title, 
        'content' => $content 
    ); 
     
    // Store the submitted field values in the session 
    $sessData['userData'] = $pageData; 
     
    // Process the form data 
    if(empty($errorMsg)){ 
        // Create page file 
        $page_slug = $pageHandle->generatePageUri($title); 
        $page_file = $page_slug.$pageExtention; 
 
        $html_file = 'index.html'; 
        $html_file_content = file_get_contents($html_file); 
        $html_file_content = str_replace('[PAGE_TITLE]', $title, $html_file_content); 
        $html_file_content = str_replace('[PAGE_CONTENT]', $content, $html_file_content); 
 
        if(!file_exists($pageDir)){ 
            mkdir($pageDir, 0777); 
        } 
        $filePath = $pageDir.'/'.$page_file; 
        $create_page_file = file_put_contents($filePath, $html_file_content); 
 
        if($create_page_file){ 
            $pageData['page_uri'] = $page_file;  
            if(!empty($id)){ 
                // Get previous data 
                $cond = array( 
                    'where' => array( 
                        'id' => $id 
                    ), 
                    'return_type' => 'single' 
                ); 
                $prevPageData = $pageHandle->getRows($cond); 
 
                // Update page data 
                $cond = array( 
                    'id' => $id 
                ); 
                $update = $pageHandle->update($pageData, $cond); 
 
                if($update){ 
                    // Remove old page file 
                    if($prevPageData['page_uri'] !== $page_file){ 
                        $filePath_prev = $pageDir.'/'.$prevPageData['page_uri']; 
                        unlink($filePath_prev); 
                    } 
 
                    $sessData['status']['type'] = 'success'; 
                    $sessData['status']['msg'] = 'Page data has been updated successfully.'; 
 
                    // Remote submitted fields value from session 
                    unset($sessData['userData']); 
                }else{ 
                    $sessData['status']['type'] = 'error'; 
                    $sessData['status']['msg'] = 'Something went wrong, please try again.'; 
 
                    // Set redirect url 
                    $redirectURL = 'addEdit.php'.$id_str; 
                } 
            }else{ 
                // Insert page data 
                $insert = $pageHandle->insert($pageData); 
 
                if($insert){ 
                    $sessData['status']['type'] = 'success'; 
                    $sessData['status']['msg'] = 'Page data has been added successfully.'; 
 
                    // Remote submitted fields value from session 
                    unset($sessData['userData']); 
                }else{ 
                    $sessData['status']['type'] = 'error'; 
                    $sessData['status']['msg'] = 'Something went wrong, please try again.'; 
 
                    // Set redirect url 
                    $redirectURL = 'addEdit.php'.$id_str; 
                } 
            } 
        }else{ 
            $sessData['status']['msg'] = 'Page creation failed! Please try again.'; 
        } 
    }else{ 
        $sessData['status']['type'] = 'error'; 
        $sessData['status']['msg'] = 'Please fill all the mandatory fields.'.$errorMsg; 
 
        // Set redirect url 
        $redirectURL = 'addEdit.php'.$id_str; 
    } 
     
    // Store status into the session 
    $_SESSION['sessData'] = $sessData; 
}elseif(($_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){ 
    $id = base64_decode($_GET['id']); 
 
    // Get page data 
    $cond = array( 
        'where' => array( 
            'id' => $id 
        ), 
        'return_type' => 'single' 
    ); 
    $pageData = $pageHandle->getRows($cond); 
 
    // Delete page from database 
    $delete = $pageHandle->delete($id); 
 
    if($delete){ 
        // Remove page file 
        if(!empty($pageData['page_uri'])){ 
            $filePath = $pageDir.'/'.$pageData['page_uri']; 
            @unlink($filePath); 
        } 
 
        $sessData['status']['type'] = 'success'; 
        $sessData['status']['msg'] = 'Page has been deleted successfully.'; 
    }else{ 
        $sessData['status']['type'] = 'error'; 
        $sessData['status']['msg'] = 'Some problem occurred, please try again.'; 
    } 
 
    // Store status into the session 
    $_SESSION['sessData'] = $sessData; 
} 
 
// Redirect to the respective page 
header("Location:".$redirectURL); 
exit(); 
?>

Bootstrap Library

We will use the Bootstrap library to make the table, form, and buttons look better. You can omit it to use a custom stylesheet for HTML table, form, buttons, and other UI elements.

Include the CSS file of the Bootstrap library.

Page Listing with View & Delete Features (index.php)

Initially, all the pages are retrieved from the database and listed in a tabular format with View, Add, Edit, and Delete options.

The Add link redirects to the addEdit.php page to perform the page creation operation.
The View link opens the page file and displays the HTML content.
The Edit link redirects to the addEdit.php page to perform the page content update operation.
The Delete link redirects to the userAction.php file with action_type=delete and id params. The page data is deleted from the database based on the unique identifier (id).

getRows(); 
?>




    



    




    
        Pages
        
        
             New Page
        
    
    
    
    
        
            
                #
                Title
                Content
                Created
                Action
            
        
        
            
            
                
                
                
                    $list_excerpt_length)?substr($content, 0, $list_excerpt_length).'...':$content; 
                    ?>
                
                
                
                    view
                    edit
                    delete
                
            
            
            No page(s) found...

#	Title	Content	Created	Action
		$list_excerpt_length)?substr($content, 0, $list_excerpt_length).'...':$content; ?>		view edit delete
No page(s) found...

Create and Update Page Content (addEdit.php)

The addEdit.php handles the page creation and content update form functionality.

The Jodit plugin is used to replace textarea input field with WYSIWYG HTML Editor. It allows the user to input the page content with an HTML formatting option.

First, include the jQuery library and Jodit plugin library files.

Initialize the Jodit plugin to attach the editor with the HTML element (page_content).

Initially, an HTML form is displayed to allow input data.

If the id parameter exists on the URL, the existing page data will be retrieved from the database based on this ID and the form fields will be pre-filled.
After the form submission, the form data is posted to the userAction.php file to insert/update the page content in the database.

 [
            'id' => $page_id,
        ],
        'return_type' => 'single',
    ];
    $pageData = $pageHandle->getRows($cond);
}
$userData = !empty($sessData['userData']) ? $sessData['userData'] : $pageData;
unset($_SESSION['sessData']['userData']);

$actionLabel = !empty($page_id) ? 'Edit' : 'Add';
?>




    



    




    
         Page
    
    
            
            
                Title
                
            
            
                Content
                <?php echo !empty($userData['content']) ? $userData['content'] : ''; ?>
            
            
            Back

Conclusion

The Page CRUD functionality is very useful when you want to create HTML pages and manage web pages dynamically. Here we have tried to make the page management CRUD simple, where you can create HTML pages dynamically in PHP. All types of HTML tags and formatting can be added to the page content dynamically with PHP CMS pages management system. Not only the page creation, but also you can update and delete page content dynamically using PHP. This example code helps you to develop a content management system (CMS) with PHP and MySQL.

Create and manage webpages dynamically using PHP MySQL

Nityananda Maity August 18, 2022

Create Database Table

Configuration File `(config.php)`

CRUD Handler Class

Pages CRUD Operations with PHP (userAction.php)

Add/Edit Page

Delete Records

Bootstrap Library

Page Listing with View & Delete Features (index.php)

Pages

Create and Update Page Content (addEdit.php)

Page

Conclusion

also view other related posts,

A Comprehensive Guide to Microsoft Fabric and Its Core Features

Key Tools for Reactive Programming in Angular by RxJS Observables

How Quickly Upload Files from URL to Azure Blob Storage Using Azure CLI in 2 steps

How to Turn on Ghost Mode on Snapchat with new features 2024

Domain Driven Design Application Services

Create and manage webpages dynamically using PHP MySQL

Nityananda Maity August 18, 2022

Create Database Table

Configuration File (config.php)

CRUD Handler Class

Pages CRUD Operations with PHP (userAction.php)

Add/Edit Page

Delete Records

Bootstrap Library

Page Listing with View & Delete Features (index.php)

Pages

Create and Update Page Content (addEdit.php)

Page

Conclusion

also view other related posts,

A Comprehensive Guide to Microsoft Fabric and Its Core Features

Key Tools for Reactive Programming in Angular by RxJS Observables

How Quickly Upload Files from URL to Azure Blob Storage Using Azure CLI in 2 steps

How to Turn on Ghost Mode on Snapchat with new features 2024

Domain Driven Design Application Services

Configuration File `(config.php)`