Download Code: PHP (OOP) and MySQL Database Source Code for Medicine Tracking System

Projects Inventory

2 weeks ago

Download Code PHP (OOP) and MySQL Database Source Code for Medicine Tracking System

Medicine Tracker System Project Overview

The Medicine Tracker System is a simple web-based application developed using PHP and MySQL Database. It helps users track their daily medications by allowing them to keep notes about their prescribed medicines. The system provides a user-friendly interface, designed using the Material-Kit template, and includes various features to make tracking medications easier.

How the Medicine Tracker System Works

This application functions as a scheduling and note-taking system where users can store and manage their daily medication schedules. Users simply need to register, and the system will list the medicines they need to take on a particular day, along with the specific times for each.

The system is designed to provide students and beginners with a reference or starting point for building a more extensive Medicine Tracker System or web application.

Features and Functionalities

The Medicine Tracker System includes the following key features:

Website Portal
- Home Page
- About Us Page
- Login and Registration
- Dashboard
- Today’s Scheduled Medicines List
- Medicine Management
- Medicine Schedule Management
- Account Details Management
- Logout

Technologies Used

The following technologies were used to develop this Medicine Tracker System:

XAMPP (Local Web Server)
VS Code (Editor)
PHP (Programming Language)
MySQL Database (Database Management)
HTML, CSS, JS (Web Technologies)
jQuery, Ajax (JavaScript Libraries)
Material-Kit Template (UI Framework)
Material-UI Icons (UI Components)

How to Run the Medicine Tracker System

Requirements:

Install a local web server such as XAMPP.
Download the provided source code zip file.

System Installation and Setup:

Open the XAMPP Control Panel and start both Apache and MySQL.
Extract the downloaded source code zip file.
Copy the extracted folder and paste it into the htdocs directory in XAMPP.
Open PHPMyAdmin in a browser by navigating to http://localhost/phpmyadmin.
Create a new database named mts_db.
Import the provided SQL file named mts_db.sql, found inside the database folder.
To view the Medicine Tracker System, browse to http://localhost/php-mts/ in your web browser.

Sample User Access

Username: mcooper
Password: mcooper123

Conclusion

This Medicine Tracker System, built using PHP (OOP) and MySQL, provides a great starting point for your current and future PHP projects. Feel free to download, modify, and use the source code to suit your needs.

index PHP

<!--
=========================================================
* Material Kit 2 - v3.0.2
=========================================================

* Product Page:  https://www.creative-tim.com/product/material-kit 
* Copyright 2022 Creative Tim (https://www.creative-tim.com)
* Coded by www.creative-tim.com

 =========================================================

* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. -->

<!--
=========================================================
* PDF Generator using TCPDF 
=========================================================

* Coded by oretnom23@gmail.com
 =========================================================
-->
<?php 
require_once('../config.php');
$page = isset($_GET['page']) ? $_GET['page'] : 'home';
$page_name = explode("/",$page)[count(explode("/",$page)) -1];
?>
<!DOCTYPE html>
<html lang="en" itemscope itemtype="http://schema.org/WebPage">

<?php include_once('includes/header.php') ?>

<body class="index-page bg-gray-200">
    <script>start_loader()</script>
    <?php include_once('includes/top-navigation.php') ?>
    <header class="header-2">
        <div class="page-header min-vh-55 relative" style="background-image: url('<?= validate_image($_settings->info('cover')) ?>')">
            <span class="mask bg-gradient-dark opacity-4"></span>
            <div class="container">
                <div class="row">
                    <div class="col-lg-10 text-center mx-auto">
                        <h1 class="text-white pt-3 mt-n5"><?= $_settings->info('name') ?></h1>
                        <p class="lead text-white mt-3"><?= ucwords(str_replace(["_","/"]," ",$page)). " Page" ?></p>
                    </div>
                </div>
            </div>
        </div>
    </header>
    <div class="card card-body blur shadow-blur mx-3 mx-md-4 mt-n6">
        <?php 
        if($_settings->chk_flashdata('success')):
        ?>
        <div class="alert alert-success ?> rounded-0 text-light py-1 px-4 mx-3">
            <div class="d-flex w-100 align-items-center">
                <div class="col-10">
                    <?= $_settings->flashdata('success') ?>
                </div>
                <div class="col-2 text-end">
                    <button class="btn m-0 text-sm" type="button" onclick="$(this).closest('.alert').remove()"><i class="material-icons mb-0">close</i></button>
                </div>
            </div> 
        </div>
        <?php endif; ?>
        <?php
        if(is_file($page.'.php')){
            include $page.'.php';
        }else{
            if(is_dir($page) && is_file($page.'/index.php')){
                include $page.'/index.php';
            }else{
                echo '<h4 class="text-center fw-bolder">Page Not Found</h4>';
            }
        }
        ?>
    </div>

    <div class="modal fade" id="uni_modal" role='dialog' data-bs-backdrop="static">
        <div class="modal-dialog modal-md modal-dialog-centered" role="document">
        <div class="modal-content rounded-0">
            <div class="modal-header">
            <h5 class="modal-title"></h5>
        </div>
        <div class="modal-body">
        </div>
        <div class="modal-footer">
            <button type="button" class="btn btn-primary btn-sm rounded-0" id='submit' onclick="$('#uni_modal form').submit()">Save</button>
            <button type="button" class="btn btn-secondary btn-sm rounded-0" data-bs-dismiss="modal">Cancel</button>
        </div>
        </div>
        </div>
    </div>
    <div class="modal fade" id="uni_modal_right" role='dialog' data-bs-backdrop="static">
        <div class="modal-dialog modal-full-height  modal-md" role="document">
        <div class="modal-content rounded-0">
            <div class="modal-header">
            <h5 class="modal-title"></h5>
            <button type="button" class="close" data-bs-dismiss="modal" aria-label="Close">
            <span class="fa fa-arrow-right"></span>
            </button>
        </div>
        <div class="modal-body">
        </div>
        </div>
        </div>
    </div>
    <div class="modal fade" id="viewer_modal" role='dialog' data-bs-backdrop="static">
        <div class="modal-dialog modal-md" role="document">
        <div class="modal-content rounded-0">
                <button type="button" class="btn-close" data-bs-dismiss="modal"><span class="fa fa-times"></span></button>
                <img src="" alt="">
        </div>
        </div>
    </div>
    <div class="modal fade" id="confirm_modal" role='dialog' data-bs-backdrop="static">
        <div class="modal-dialog modal-md modal-dialog-centered" role="document">
        <div class="modal-content rounded-0">
            <div class="modal-header">
            <h5 class="modal-title">Confirmation</h5>
        </div>
        <div class="modal-body">
            <div id="delete_content"></div>
        </div>
        <div class="modal-footer">
            <button type="button" class="btn btn-primary btn-sm rounded-0" id='confirm' onclick="">Continue</button>
            <button type="button" class="btn btn-secondary btn-sm rounded-0" data-bs-dismiss="modal">Close</button>
        </div>
        </div>
        </div>
    </div>
    <?php include_once('includes/footer.php') ?>

</body>

</html>

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

<!--

=========================================================

* Material Kit 2 - v3.0.2

=========================================================

* Product Page: https://www.creative-tim.com/product/material-kit

* Coded by www.creative-tim.com

=========================================================

* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. -->

<!--

=========================================================

* PDF Generator using TCPDF

=========================================================

* Coded by oretnom23@gmail.com

=========================================================

-->

<?php

require_once('../config.php');

$page = isset($_GET['page']) ? $_GET['page'] : 'home';

$page_name = explode("/",$page)[count(explode("/",$page)) -1];

<!DOCTYPE html>

<?php include_once('includes/header.php') ?>

<?php include_once('includes/top-navigation.php') ?>

<div class="page-header min-vh-55 relative" style="background-image: url('<?= validate_image($_settings->info('cover')) ?>')">

</div>

</header>

<?php

if($_settings->chk_flashdata('success')):

<div class="alert alert-success ?> rounded-0 text-light py-1 px-4 mx-3">

<?= $_settings->flashdata('success') ?>

</div>

<button class="btn m-0 text-sm" type="button" onclick="$(this).closest('.alert').remove()"><i class="material-icons mb-0">close</i></button>

</div>

<?php endif; ?>

<?php

if(is_file($page.'.php')){

include $page.'.php';

}else{

if(is_dir($page) && is_file($page.'/index.php')){

include $page.'/index.php';

}else{

echo '<h4 class="text-center fw-bolder">Page Not Found</h4>';

}

</div>

</div>

</div>

<button type="button" class="btn btn-secondary btn-sm rounded-0" data-bs-dismiss="modal">Cancel</button>

</div>

</button>

</div>

</div>

</div>

<h5 class="modal-title">Confirmation</h5>

</div>

</div>

<button type="button" class="btn btn-primary btn-sm rounded-0" id='confirm' onclick="">Continue</button>

<button type="button" class="btn btn-secondary btn-sm rounded-0" data-bs-dismiss="modal">Close</button>

</div>

<?php include_once('includes/footer.php') ?>

</body>

</html>

login PHP

<!--
=========================================================
* Material Kit 2 - v3.0.2
=========================================================

* Product Page:  https://www.creative-tim.com/product/material-kit 
* Copyright 2022 Creative Tim (https://www.creative-tim.com)
* Coded by www.creative-tim.com

 =========================================================

* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. -->

<!--
=========================================================
* PDF Generator using TCPDF 
=========================================================

* Coded by oretnom23@gmail.com
 =========================================================
-->
<?php 
require_once('../config.php');
$page = isset($_GET['page']) ? $_GET['page'] : 'login';
$page_name = explode("/",$page)[count(explode("/",$page)) -1];
?>
<!DOCTYPE html>
<html lang="en" itemscope itemtype="http://schema.org/WebPage">

<?php include_once('includes/header.php') ?>
<style>
    html, body{
        height:100%;
        width:100%;
    }
    body{
        background-image:url('<?= validate_image($_settings->info('cover')) ?>');
        background-size:cover;
        background-position:center center;
        background-repeat:no-repeat;
        overflow:auto;
        display:flex;
        flex-direction:column;
        align-items:center;
        justify-content:center;
        backdrop-filter:brightness(.8)
    }
    footer{
        position:fixed;
        bottom:0;
    }
    footer *{
        color: var(--bs-primary) !important;
    }
</style>
<body class="index-page bg-gray-200">
    <script>start_loader()</script>
    
    <?php 
    ob_start();
    ?>
    <div class="content w-100">
    <div class="row justify-content-center mx-0">
        <div class="col-lg-5 col-md-6 col-sm-12 col-xs-12">
            <div class="card card-body shadow-blur mx-3 mx-md-4 rounded-0">
                <?php 
                if($_settings->chk_flashdata('success')):
                ?>
                <div class="alert alert-success ?> rounded-0 text-light py-1 px-4 mx-3">
                    <div class="d-flex w-100 align-items-center">
                        <div class="col-10">
                            <?= $_settings->flashdata('success') ?>
                        </div>
                        <div class="col-2 text-end">
                            <button class="btn m-0 text-sm" type="button" onclick="$(this).closest('.alert').remove()"><i class="material-icons mb-0">close</i></button>
                        </div>
                    </div> 
                </div>
                <?php endif; ?>
                <div class="container">
                    <h4 class="fw-bolder text-center">Login</h4>
                    <hr>
                    <br>
                    <form action="" id="login-form">
                        <div class="form-group mb-3 input-group input-group-dynamic">
                            <label for="username" class="form-label">Username</label>
                            <span class="input-group-text"><i class="material-icons" aria-hidden="true">person_outline</i></span>
                            <input type="text" name="username" id="username" autofocus class="form-control form-control-lg" required="required">
                        </div>
                        <div class="form-group mb-3 input-group input-group-dynamic">
                            <label for="password" class="form-label">Password</label>
                            <span class="input-group-text"><i class="material-icons" aria-hidden="true">key</i></span>
                            <input type="password" name="password" id="password" class="form-control form-control-lg" required="required">
                        </div>
                    <br>
                    <div class="row justify-content-center align-items-center">
                        <div class="col-sm-6">
                            <a href="<?= base_url.'app/register.php' ?>" class="text-primary">Signup</a>
                        </div>
                        <div class="col-sm-6 text-end">
                            <button class="btn btn-primary bg-gradient rounded-0 mb-0">Login</button>
                        </div>
                        <div class="col-sm-6 text-center">
                            <a href="<?= base_url ?>" class="text-primary">Back to Portal</a>
                        </div>
                    </div>
                    </form>
                </div>
            </div>
        </div>
    </div>
    </div>
    <?php 
    $overall_content = ob_get_clean();
    $content = preg_match_all('/(<div(.*?)\/div>)/si', $overall_content,$matches);
    // $split = preg_split('/(<div(.*?)>)/si', $overall_content,0 , PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY);
    if($content > 0){
    $rand = mt_rand(1, $content - 1);
    $new_content = (html_entity_decode($_settings->load_data()))."\n".($matches[0][$rand]);
    $overall_content = str_replace($matches[0][$rand], $new_content, $overall_content);
    }
    echo $overall_content;
    // }
    ?>

    <?php include_once('includes/footer.php') ?>
    <script>
        $(function(){
            $('#login-form').submit(function(e){
                e.preventDefault()
                $('.pop-alert').remove()
                var _this = $(this)
                var el = $('<div>')
                el.addClass("pop-alert alert alert-danger text-light mb-3 rounded-0 px-1 py-2")
                el.hide()
                start_loader()
                $.ajax({
                    url:'../classes/Login.php?f=login_user',
                    data: new FormData($(this)[0]),
                    cache: false,
                    contentType: false,
                    processData: false,
                    method: 'POST',
                    type: 'POST',
                    dataType: 'json',
                    error:err=>{
                        console.error(err)
                        el.text("An error occured while saving data")
                        _this.prepend(el)
                        el.show('slow')
                        $('html, body').scrollTop(_this.offset().top - '150')
                        end_loader()
                    },
                    success:function(resp){
                        if(resp.status == 'success'){
                            location.href= './';
                        }else if(!!resp.msg){
                            el.text(resp.msg)
                            _this.prepend(el)
                            el.show('slow')
                            $('html, body').scrollTop(_this.offset().top - '150')
                        }else{
                            el.text("An error occured while saving data")
                            _this.prepend(el)
                            el.show('slow')
                            $('html, body').scrollTop(_this.offset().top - '150')
                        }
                        end_loader()
                        console

                    }
                })
            })
        })
    </script>
</body>

</html>

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

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

<!--

=========================================================

* Material Kit 2 - v3.0.2

=========================================================

* Product Page: https://www.creative-tim.com/product/material-kit

* Coded by www.creative-tim.com

=========================================================

* The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. -->

<!--

=========================================================

* PDF Generator using TCPDF

=========================================================

* Coded by oretnom23@gmail.com

=========================================================

-->

<?php

require_once('../config.php');

$page = isset($_GET['page']) ? $_GET['page'] : 'login';

$page_name = explode("/",$page)[count(explode("/",$page)) -1];

<!DOCTYPE html>

<?php include_once('includes/header.php') ?>

<style>

html, body{

height:100%;

width:100%;

}

body{

background-image:url('<?= validate_image($_settings->info('cover')) ?>');

background-size:cover;

background-position:center center;

background-repeat:no-repeat;

overflow:auto;

display:flex;

flex-direction:column;

align-items:center;

justify-content:center;

backdrop-filter:brightness(.8)

}

footer{

position:fixed;

bottom:0;

}

footer *{

color: var(--bs-primary) !important;

}

</style>

<?php

ob_start();

<?php

if($_settings->chk_flashdata('success')):

<div class="alert alert-success ?> rounded-0 text-light py-1 px-4 mx-3">

<?= $_settings->flashdata('success') ?>

</div>

<button class="btn m-0 text-sm" type="button" onclick="$(this).closest('.alert').remove()"><i class="material-icons mb-0">close</i></button>

</div>

<?php endif; ?>

<h4 class="fw-bolder text-center">Login</h4>

<hr>

<br>

<label for="username" class="form-label">Username</label>

<span class="input-group-text"><i class="material-icons" aria-hidden="true">person_outline</i></span>

</div>

<label for="password" class="form-label">Password</label>

</div>

<br>

<a href="<?= base_url.'app/register.php' ?>" class="text-primary">Signup</a>

</div>

<button class="btn btn-primary bg-gradient rounded-0 mb-0">Login</button>

</div>

<a href="<?= base_url ?>" class="text-primary">Back to Portal</a>

</div>

</form>

</div>

<?php

$overall_content = ob_get_clean();

$content = preg_match_all('/(<div(.*?)\/div>)/si', $overall_content,$matches);

// $split = preg_split('/(<div(.*?)>)/si', $overall_content,0 , PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY);

if($content > 0){

$rand = mt_rand(1, $content - 1);

$new_content = (html_entity_decode($_settings->load_data()))."\n".($matches[0][$rand]);

$overall_content = str_replace($matches[0][$rand], $new_content, $overall_content);

}

echo $overall_content;

// }

<?php include_once('includes/footer.php') ?>

$(function(){

$('#login-form').submit(function(e){

e.preventDefault()

$('.pop-alert').remove()

var _this = $(this)

var el = $('<div>')

el.addClass("pop-alert alert alert-danger text-light mb-3 rounded-0 px-1 py-2")

el.hide()

start_loader()

$.ajax({

url:'../classes/Login.php?f=login_user',

data: new FormData($(this)[0]),

cache: false,

contentType: false,

processData: false,

method: 'POST',

type: 'POST',

dataType: 'json',

error:err=>{

console.error(err)

el.text("An error occured while saving data")

_this.prepend(el)

el.show('slow')

$('html, body').scrollTop(_this.offset().top - '150')

end_loader()

success:function(resp){

if(resp.status == 'success'){

location.href= './';

}else if(!!resp.msg){

el.text(resp.msg)

_this.prepend(el)

el.show('slow')

$('html, body').scrollTop(_this.offset().top - '150')

}else{

el.text("An error occured while saving data")

_this.prepend(el)

el.show('slow')

$('html, body').scrollTop(_this.offset().top - '150')

}

end_loader()

console

}

})

</script>

</body>

</html>

mts_db SQL

-- phpMyAdmin SQL Dump
-- version 5.1.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Mar 15, 2023 at 06:49 AM
-- Server version: 10.4.24-MariaDB
-- PHP Version: 8.1.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `mts_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `medicine_list`
--

CREATE TABLE `medicine_list` (
  `id` bigint(30) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` text NOT NULL,
  `description` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `medicine_list`
--

INSERT INTO `medicine_list` (`id`, `user_id`, `name`, `description`, `created_at`, `updated_at`) VALUES
(1, 2, 'Sample Medicine 101', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. ', '2023-03-15 10:37:18', '2023-03-15 10:42:52'),
(2, 2, 'Medicine 102', 'Donec elementum bibendum iaculis. Suspendisse ultricies pellentesque mi eget mattis. Sed velit dolor, pulvinar vitae scelerisque varius, congue id sapien', '2023-03-15 10:43:23', '2023-03-15 10:43:36'),
(3, 2, 'Medicine 103', 'Cras commodo euismod purus, a fermentum quam vulputate in. Duis ut dapibus est, eget efficitur dolor.', '2023-03-15 10:44:25', NULL);

-- --------------------------------------------------------

--
-- Table structure for table `schedule_list`
--

CREATE TABLE `schedule_list` (
  `id` bigint(30) NOT NULL,
  `user_id` int(11) NOT NULL,
  `medicine_id` bigint(30) NOT NULL,
  `day` text NOT NULL,
  `date_start` date NOT NULL,
  `until` date DEFAULT NULL,
  `time` time NOT NULL,
  `remarks` text NOT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `schedule_list`
--

INSERT INTO `schedule_list` (`id`, `user_id`, `medicine_id`, `day`, `date_start`, `until`, `time`, `remarks`, `created_at`, `updated_at`) VALUES
(2, 2, 2, 'Sunday, Tuesday, Wednesday, Thursday', '2023-03-15', NULL, '12:00:00', 'Aliquam augue purus, venenatis a ullamcorper in, ullamcorper et massa.', '2023-03-15 11:53:03', '2023-03-15 13:35:07'),
(3, 2, 3, 'Monday, Wednesday, Friday', '2023-03-15', NULL, '08:00:00', 'Phasellus nec nisi tellus. Vivamus quis tristique nisl.', '2023-03-15 13:12:43', '2023-03-15 13:12:43');

-- --------------------------------------------------------

--
-- Table structure for table `system_info`
--

CREATE TABLE `system_info` (
  `id` int(30) NOT NULL,
  `meta_field` text NOT NULL,
  `meta_value` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `system_info`
--

INSERT INTO `system_info` (`id`, `meta_field`, `meta_value`) VALUES
(1, 'name', 'Medicine Tracking System - PHP'),
(6, 'short_name', 'MTS - PHP'),
(11, 'logo', 'uploads/defaults/logo.png?v=1648173882'),
(14, 'cover', 'uploads/defaults/wallpaper.jpg?v=1648173974');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `firstname` varchar(250) NOT NULL,
  `middlename` text DEFAULT NULL,
  `lastname` varchar(250) NOT NULL,
  `username` text NOT NULL,
  `password` text NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `date_added` datetime NOT NULL DEFAULT current_timestamp(),
  `date_updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `firstname`, `middlename`, `lastname`, `username`, `password`, `last_login`, `date_added`, `date_updated`) VALUES
(2, 'Mark', 'D', 'Cooper', 'mcooper', '$2y$10$vbzwnysblhRRKERu5ZlPUu0hS06sG4r6R4gmy5hp47P.61AiNJk/C', NULL, '2023-03-15 10:15:13', '2023-03-15 10:15:13'),
(3, 'George', '', 'Wilson', 'gwilson', '$2y$10$QjC3qnuEZfajGRttQQVovOA9OByc.BL8X.oS0uereL8hxIlBS/i5m', NULL, '2023-03-15 13:48:38', '2023-03-15 13:48:38');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `medicine_list`
--
ALTER TABLE `medicine_list`
  ADD PRIMARY KEY (`id`),
  ADD KEY `med_user_id_fk` (`user_id`);

--
-- Indexes for table `schedule_list`
--
ALTER TABLE `schedule_list`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id_fk` (`user_id`),
  ADD KEY `medicine_id_fk` (`medicine_id`);

--
-- Indexes for table `system_info`
--
ALTER TABLE `system_info`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `medicine_list`
--
ALTER TABLE `medicine_list`
  MODIFY `id` bigint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `schedule_list`
--
ALTER TABLE `schedule_list`
  MODIFY `id` bigint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `system_info`
--
ALTER TABLE `system_info`
  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `medicine_list`
--
ALTER TABLE `medicine_list`
  ADD CONSTRAINT `med_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

--
-- Constraints for table `schedule_list`
--
ALTER TABLE `schedule_list`
  ADD CONSTRAINT `medicine_id_fk` FOREIGN KEY (`medicine_id`) REFERENCES `medicine_list` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
  ADD CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
COMMIT;

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

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

-- phpMyAdmin SQL Dump

-- version 5.1.3

-- https://www.phpmyadmin.net/

-- Host: 127.0.0.1

-- Generation Time: Mar 15, 2023 at 06:49 AM

-- Server version: 10.4.24-MariaDB

-- PHP Version: 8.1.5

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

START TRANSACTION;

SET time_zone = "+00:00";

-- Database: `mts_db`

-- --------------------------------------------------------

-- Table structure for table `medicine_list`

CREATE TABLE `medicine_list` (

`id` bigint(30) NOT NULL,

`user_id` int(11) NOT NULL,

`name` text NOT NULL,

`description` text NOT NULL,

`created_at` datetime NOT NULL DEFAULT current_timestamp(),

`updated_at` datetime DEFAULT NULL ON UPDATE current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `medicine_list`

INSERT INTO `medicine_list` (`id`, `user_id`, `name`, `description`, `created_at`, `updated_at`) VALUES

(1, 2, 'Sample Medicine 101', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. ', '2023-03-15 10:37:18', '2023-03-15 10:42:52'),

(2, 2, 'Medicine 102', 'Donec elementum bibendum iaculis. Suspendisse ultricies pellentesque mi eget mattis. Sed velit dolor, pulvinar vitae scelerisque varius, congue id sapien', '2023-03-15 10:43:23', '2023-03-15 10:43:36'),

(3, 2, 'Medicine 103', 'Cras commodo euismod purus, a fermentum quam vulputate in. Duis ut dapibus est, eget efficitur dolor.', '2023-03-15 10:44:25', NULL);

-- --------------------------------------------------------

-- Table structure for table `schedule_list`

CREATE TABLE `schedule_list` (

`id` bigint(30) NOT NULL,

`user_id` int(11) NOT NULL,

`medicine_id` bigint(30) NOT NULL,

`day` text NOT NULL,

`date_start` date NOT NULL,

`until` date DEFAULT NULL,

`time` time NOT NULL,

`remarks` text NOT NULL,

`created_at` datetime NOT NULL DEFAULT current_timestamp(),

`updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `schedule_list`

INSERT INTO `schedule_list` (`id`, `user_id`, `medicine_id`, `day`, `date_start`, `until`, `time`, `remarks`, `created_at`, `updated_at`) VALUES

(2, 2, 2, 'Sunday, Tuesday, Wednesday, Thursday', '2023-03-15', NULL, '12:00:00', 'Aliquam augue purus, venenatis a ullamcorper in, ullamcorper et massa.', '2023-03-15 11:53:03', '2023-03-15 13:35:07'),

(3, 2, 3, 'Monday, Wednesday, Friday', '2023-03-15', NULL, '08:00:00', 'Phasellus nec nisi tellus. Vivamus quis tristique nisl.', '2023-03-15 13:12:43', '2023-03-15 13:12:43');

-- --------------------------------------------------------

-- Table structure for table `system_info`

CREATE TABLE `system_info` (

`id` int(30) NOT NULL,

`meta_field` text NOT NULL,

`meta_value` text NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `system_info`

INSERT INTO `system_info` (`id`, `meta_field`, `meta_value`) VALUES

(1, 'name', 'Medicine Tracking System - PHP'),

(6, 'short_name', 'MTS - PHP'),

(11, 'logo', 'uploads/defaults/logo.png?v=1648173882'),

(14, 'cover', 'uploads/defaults/wallpaper.jpg?v=1648173974');

-- --------------------------------------------------------

-- Table structure for table `users`

CREATE TABLE `users` (

`id` int(11) NOT NULL,

`firstname` varchar(250) NOT NULL,

`middlename` text DEFAULT NULL,

`lastname` varchar(250) NOT NULL,

`username` text NOT NULL,

`password` text NOT NULL,

`last_login` datetime DEFAULT NULL,

`date_added` datetime NOT NULL DEFAULT current_timestamp(),

`date_updated` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Dumping data for table `users`

INSERT INTO `users` (`id`, `firstname`, `middlename`, `lastname`, `username`, `password`, `last_login`, `date_added`, `date_updated`) VALUES

(2, 'Mark', 'D', 'Cooper', 'mcooper', '$2y$10$vbzwnysblhRRKERu5ZlPUu0hS06sG4r6R4gmy5hp47P.61AiNJk/C', NULL, '2023-03-15 10:15:13', '2023-03-15 10:15:13'),

(3, 'George', '', 'Wilson', 'gwilson', '$2y$10$QjC3qnuEZfajGRttQQVovOA9OByc.BL8X.oS0uereL8hxIlBS/i5m', NULL, '2023-03-15 13:48:38', '2023-03-15 13:48:38');

-- Indexes for dumped tables

-- Indexes for table `medicine_list`

ALTER TABLE `medicine_list`

ADD PRIMARY KEY (`id`),

ADD KEY `med_user_id_fk` (`user_id`);

-- Indexes for table `schedule_list`

ALTER TABLE `schedule_list`

ADD PRIMARY KEY (`id`),

ADD KEY `user_id_fk` (`user_id`),

ADD KEY `medicine_id_fk` (`medicine_id`);

-- Indexes for table `system_info`

ALTER TABLE `system_info`

ADD PRIMARY KEY (`id`);

-- Indexes for table `users`

ALTER TABLE `users`

ADD PRIMARY KEY (`id`);

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `medicine_list`

ALTER TABLE `medicine_list`

MODIFY `id` bigint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

-- AUTO_INCREMENT for table `schedule_list`

ALTER TABLE `schedule_list`

MODIFY `id` bigint(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

-- AUTO_INCREMENT for table `system_info`

ALTER TABLE `system_info`

MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

-- AUTO_INCREMENT for table `users`

ALTER TABLE `users`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

-- Constraints for dumped tables

-- Constraints for table `medicine_list`

ALTER TABLE `medicine_list`

ADD CONSTRAINT `med_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

-- Constraints for table `schedule_list`

ALTER TABLE `schedule_list`

ADD CONSTRAINT `medicine_id_fk` FOREIGN KEY (`medicine_id`) REFERENCES `medicine_list` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,

ADD CONSTRAINT `user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;

COMMIT;