Gfrktyl

I made this to-do list since I never seriously used jQuery (in particular way AJAX) and I need this kind of script for a project that I'm working on. Principal features:

• Use of a database to manage to-do list values (using PDO).


• Possibility to add/delete to-do list values.


• Possibility to edit to-do list value dblclicking on the text value.


• Possibility to set a "status" for a specific value (completed and tested).


• To-do list values can be separeted for category/type.


• AJAX functionality.

I want to know if all my methods applied to achieve this result it is right and mostly well written, particularly for the jQuery/AJAX part. Could I write better some logic of the PHP code? Could I avoid some useless code in the jQuery/AJAX part? Every comment on the code would be appreciated.

load.initialize.php

<?php       

$database = [

    'name'  => "name",

    'user'  => "user",

    'pass'  => "idk",

];



try

{

    $dsn = "mysql:host=127.0.0.1;dbname={$database['name']};";

    $dbh = new PDO($dsn, $database['user'], $database['pass']);



    # Disable emulation of prepared statements, use REAL prepared statements instead.

    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);



    # Set charset to uf8 (using query, that's the only way that make work SET NAMES).

    $dbh->query("SET NAMES utf8mb4");

}

catch (PDOException $e)

{

    echo 'An error occured: ' . $e->getMessage();

}

index.php [top of the file]

<?php

require_once('load.initialize.php');



// Delete to-do list value.

if (isset($_POST['delete']) && is_numeric($_POST['delete']))

{

    $stmt = $dbh->query("DELETE FROM tl_main WHERE id = {$_POST['delete']}");



    if ($stmt)

    {

        $json_data['action'] = true; # true;

    }

}



// Edit to-do list value.

if (isset($_POST['edited_text']) && isset($_POST['value']) && is_numeric($_POST['value']))

{

    $stmt = $dbh->prepare("UPDATE tl_main SET text = :text, edit_date = NOW() WHERE id = {$_POST['value']}");

    $stmt->bindParam(':text', $_POST['edited_text'], PDO::PARAM_STR);



    if ($stmt->execute())

    {

        $stmt = $dbh->query("SELECT edit_date FROM tl_main WHERE id = {$_POST['value']} LIMIT 1");



        $json_data['action'] = true; # true;

        $json_data['edit_date'] = date("d/m/y H:i", strtotime($stmt->fetchColumn())); # Send it directly formatted as we setted in the page (instead of formatting it in jQuery)

    }

}



// Add value to the to-do list

if (isset($_POST['value_text']) && !empty($_POST['value_text']) && isset($_POST['type']) && is_numeric($_POST['type']))

{

    $stmt = $dbh->prepare("INSERT INTO tl_main (type, text, added_date) VALUES({$_POST['type']}, :text, NOW())");

    $stmt->bindParam(':text', $_POST['value_text'], PDO::PARAM_STR);



    if ($stmt->execute())

    {

        $json_data['action'] = true; # true;

    }

}



// Set to-do list status to specific value.

if (isset($_POST['status']) && isset($_POST['value']) && is_numeric($_POST['value']))

{

    switch ($_POST['status'])

    {

        case "completed":

            $column_date = "completed_date";

            $status = "completed = 1, tested = 0, completed_date = NOW()";

            break;

        case "tested":

            $column_date = "tested_date";

            $status = "completed = 0, tested = 1, tested_date = NOW()";

            break;

        case "indev":

            $status = "completed = 0, tested = 0, completed_date = DEFAULT, tested_date = DEFAULT";

    }



    if ($status) {

        $stmt = $dbh->query("UPDATE tl_main SET {$status} WHERE id = {$_POST['value']}");



        if ($stmt)

        {

            if (isset($column_date))

            {

                $stmt = $dbh->query("SELECT {$column_date} FROM tl_main WHERE id = {$_POST['value']} LIMIT 1");

                $json_data[$column_date] = date("d/m/y H:i", strtotime($stmt->fetchColumn())); # Send it directly formatted as we setted in the page (instead of formatting it in jQuery)

            }



            $json_data['action'] = true; # true;

        }

    }

}



// Display json infos for AJAX call for to-do list actions (delete, edit, add, status).

if (isset($_POST['delete']) || isset($_POST['edited_text']) || isset($_POST['value_text']) || isset($_POST['status']))

{

    if (!isset($json_data))

        $json_data['action'] = false;



    header('Content-type: application/json');

    echo json_encode($json_data);

    exit;

}



// Fetch to-do list types.

$sql = "SELECT * FROM tl_types";

$types = $dbh->query($sql)->fetchAll();



// Fetch to-do list content.

$sql = "SELECT * FROM tl_main ORDER BY added_date DESC";

$todolist = $dbh->query($sql)->fetchAll();



// Apply/Fetch some operations to the todolist array.

foreach ($todolist as &$value)

{

    // Formatting the text content.

    // Catching http/https links.

    $pattern = "@(http(s)?)?(://)+(([a-zA-Z])([-w]+.)+([^s.]+[^s]*)+[^,).s])@";

    $value['text'] = preg_replace($pattern, '<a href="$0" target="_blank">$0</a>', $value['text']);



    // Formatting datetimes.

    $datetimes = [

        'added_date'        => $value['added_date'],

        'edit_date'         => $value['edit_date'],

        'completed_date'    => $value['completed_date'],

        'tested_date'       => $value['tested_date']

    ];

    foreach ($datetimes as $key => $datetime)

    {

        $value[$key] = strtotime($value[$key]);

    }



    // Build an array with type => array(edit_dates).

    // Needed after for get the highest last edit for each type.

    $type_edits[$value['type']] = $value['edit_date'];

}



// Get in an array the highest edit date of the to-do list for every type in order to determine the last edit for each type.

ksort($type_edits); # A whim just to have the array with edit dates ordered by type id.

foreach ($type_edits as $type => $edits)

{

    $last_edit[$type] = date("d F H:i", max($edits));

}



// Check if last_edit array have missing types due to no content for the specific type (and so couldn't catch the last edit).

foreach ($types as $type)

{

    if (!array_key_exists($type['id'], $last_edit))

    {

        $last_edit[$type['id']] = "foo";

    }

}

index.php [bottom of the file]

<body>

    <div class="background"></div>

    <div id="container">

        <ul id="menu">

<?php foreach ($types as $type): ?>

            <li><a href="#<?= $type['name'] ?>"><?= $type['name'] ?></a></li>

<?php endforeach; ?>

            <li id="special">[ <a class="toggle_all" style="cursor:pointer">toggle all</a> ]</li>

        </ul>

        <fieldset id="legend">

            <legend>Legend</legend>

            <div id="completed" class="row">

                <span class="appearance"></span>

                <span class="explanation">Completed</span>

            </div>

            <div id="tested" class="row">

                <span class="appearance"></span>

                <span class="explanation">Tested</span>

            </div>

            <div id="indev" class="row">

                <span class="appearance"></span>

                <span class="explanation">In development</span>

            </div>

            <div id="edited" class="row">

                <span class="explanation">edited recently <span style="font-size:12px">(2 days)</span></span>

            </div>

        </fieldset>



<?php foreach ($types as $type): ?>

        <div id="<?= $type['name'] ?>" class="tab">

            <div class="title group">

                <div class="float_left">

                    <span class="main"><?= $type['name'] ?></span>

                    <span class="link">[ <a class="toggle" style="cursor:pointer">toggle</a> ]</span>

                    <span class="link">[ <a href="#<?= $type['name'] ?>">redirect</a> ]</span>

                </div>

                <div class="float_right">

                    <span class="last_edit">Last edit: <?= $last_edit[$type['id']] ?></span>

                </div>

            </div>

            <table>

                <tr>

                    <th class="subject"><span>Subject</span></th>

                    <th>Added</th>

                    <th>Mod</th>

                </tr>

                <tr>

                    <td class="subject"><textarea placeholder="Add new value..."></textarea></td>

                    <td class="date">00/00/00 00:00</td>

                    <td class="mod"><a id="add" data-type="<?= $type['id'] ?>"><i class="fas fa-plus"></i></a></td>

                </tr>

<?php foreach ($todolist as $content): ?>

<?php if ($content['type'] === $type['id']): ?>

<?php if ($content['completed']): ?>

                <tr class="completed">

<?php elseif ($content['tested']): ?>

                <tr class="tested">

<?php else: ?>

                <tr>

<?php endif; ?>

                    <td class="subject">

                        <div<?= ($content['edit_date'] > strtotime('-2 days')) ? ' style="font-style:italic"' : "" ?> data-value="<?= $content['id'] ?>" data-editable><?= $content['text'] ?></div>

                    </td>

                    <td class="date">

                        <span class="showhim"><?= date("d/m/y H:i", $content['added_date']) ?></span>

                        <div class="showme">

                            <ul>

                                <li><i class="fas fa-pencil-alt"></i> <span id="edit_date"><?= date("d/m/y H:i", $content['edit_date']) ?></span></li>

                                <li><i class="far fa-thumbs-up"></i> <span id="completed_date"><?= date("d/m/y H:i", $content['completed_date']) ?></span></li>

                                <li><i class="fas fa-check"></i> <span id="tested_date"><?= date("d/m/y H:i", $content['tested_date']) ?></span></li>

                            </ul>

                        </div>

                    </td>

                    <td class="mod">

<?php if ($content['completed']): ?>

                        <a id="status" data-status="tested" data-value="<?= $content['id'] ?>"><i class="fas fa-check"></i></a>

<?php elseif ($content['tested']): ?>

                        <a id="status" data-status="indev" data-value="<?= $content['id'] ?>"><i class="fas fa-undo-alt"></i></a>

<?php else: ?>

                        <a id="status" data-status="completed" data-value="<?= $content['id'] ?>"><i class="far fa-thumbs-up"></i></a>

<?php endif; ?>

                        <a id="delete" data-value="<?= $content['id'] ?>"><i class="far fa-trash-alt"></i></a>

                    </td>

                </tr>

<?php endif; ?>

<?php endforeach; ?>

            </table>

        </div>

<?php endforeach; ?>

    </div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<script src="assets/general.js"></script>

</body>

general.js [all AJAX requests]

// Return window confirm() method for ajax requests.

function ajax_confirm(request) {

    return confirm("Are you sure?!nAJAX request: " + request);

}



// Toggle specific table.

$('.toggle').on('click', function() {

    $(this).closest('.title').next('table').toggle();

});



// Toggle all tables.

$('.toggle_all').on('click', function() {

    $('table').toggle();

});



// AJAX POST request in order to delete a specific value.

$('td.mod #delete').on('click', function() {

    // Denied confirm alert doesn't run the script.

    if (!ajax_confirm('delete')) return;



    var $this = $(this);



    var request = {

        'delete': $this.data('value')

    };



    var posting = $.post(window.location, request, 'json');



    posting.done(function(data) {

        // Check if error query occurs.

        if (!data.action) return;



        $this.closest('tr').hide('fast', function() {

            $this.closest('tr').remove();

        });

    })

});



// AJAX POST request in order to edit a specific value.

$('table').on('dblclick', 'td div[data-editable]', function() {

    var $this = $(this);



    var $textarea = $('<textarea />').height($this.height()).val($this.text());

    $this.replaceWith($textarea);



    var save = function() {

        // Denied confirm alert doesn't send the AJAX POST request.

        // Text has not changed doesn't send the AJAX POST request.

        if (!ajax_confirm('edit') || $textarea.val() == $this.text()) {

            $textarea.replaceWith($this);

            return;

        }



        var request = {

            'edited_text': $textarea.val(),

            'value': $this.data('value')

        };



        var posting = $.post(window.location, request, 'json');



        posting.done(function(data) {

            // Check if error query occurs.

            if (!data.action) {

                alert(data);

                $textarea.replaceWith($this);

                return;

            }



            $div = $this.text($textarea.val()).css('font-style', 'italic')

            $textarea.replaceWith($div);



            $this.closest('tr').find('span#edit_date').text(data.edit_date);

        }); 

    };



    /**

        We're defining the callback with `one`, because we know that

        the element will be gone just after that, and we don't want 

        any callbacks leftovers take memory. 

        Next time `div` turns into `textarea` this single callback 

        will be applied again.

    */

    $textarea.one('blur', save).focus();

});



// AJAX POST request in order to add a value.

$('td.mod #add').on('click', function() {

    // Denied confirm alert doesn't run the script.

    if (!ajax_confirm('add')) return;



    var $this = $(this);

    var $textarea = $this.closest('tr').find('textarea');



    // Check if textarea is empty

    if (!$.trim($textarea.val())) {

        $this.closest('table').before('<div id="error" class="notice" style="display:none">Please fill out the textarea value.</div>');

        $('#error').stop(true).fadeIn().delay(5000).fadeOut(function() {

            $('#error').remove();

        });

        return;

    }



    var request = {

        'value_text': $textarea.val(),

        'type': $this.data('type')

    };



    var posting = $.post(window.location, request, 'json');



    posting.done(function(data) {

        // Check if error query occurs.

        if (!data.action) return;



        $this.closest('table').before('<div id="success" class="notice" style="display:none">Value added succesfully, refresh the page to view it.</div>');

        $('#success').stop(true).fadeIn().delay(5000).fadeOut(function() {

            $('#success').remove();

        });



        // Reset textarea value

        $textarea.val('');

    });

});



// AJAX POST request in order to set the status of a specific value.

$('td.mod #status').on('click', function() {

    // Denied confirm alert doesn't run the script.

    if (!ajax_confirm('status')) return;



    var $this = $(this);



    var request = {

        'status': $this.data('status'),

        'value': $this.data('value')

    };



    var posting = $.post(window.location, request, 'json');



    posting.done(function(data) {

        // Check if error query occurs.

        if (!data.action) return;



        switch (request.status) {

            case "completed":

                /*

                 * If completed:

                 * Add completed class to tr.

                 * Update data-status to the next status (tested).

                 * Update icon class to the next status (tested) icon.

                 * Update completed datetime.

                 */

                $this.closest('tr').addClass('completed');



                $this.data('status', 'tested');



                $this.children('svg').attr('data-prefix', 'fas').attr('data-icon', 'check');



                $this.closest('tr').find('span#completed_date').text(data.completed_date);

                break;

            case "tested":

                /*

                 * If tested:

                 * Remove completed class from tr. / Add tested class to tr.

                 * Update data-status to the next status (indev).

                 * Update icon class to the next status (indev) icon.

                 * Update tested datetime.

                 */

                $this.closest('tr').removeClass('completed');

                $this.closest('tr').addClass('tested');



                $this.data('status', 'indev');



                $this.children('svg').attr('data-prefix', 'fas').attr('data-icon', 'undo-alt');



                $this.closest('tr').find('span#tested_date').text(data.tested_date);

                break;

            case "indev":

                /*

                 * If in-dev:

                 * Remove tested class from tr. / No need to add class since indev take default background-color.

                 * Update data-status to the next status (completed).

                 * Update icon class to the next status (completed) icon.

                 * Remove completed and tested datetime.

                 */

                $this.closest('tr').removeClass('tested');



                $this.data('status', 'completed');



                $this.children('svg').attr('data-prefix', 'far').attr('data-icon', 'thumbs-up');



                $this.closest('tr').find('span#completed_date').text("foo");

                $this.closest('tr').find('span#tested_date').text("foo");

                break;

        }

    })

});

If can be useful, the two tables that I used.
tl_main to store all the values, tl_types to list the categories.

CREATE TABLE IF NOT EXISTS `tl_main` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `type` tinyint(3) unsigned NOT NULL,

  `text` mediumtext CHARACTER SET utf8 NOT NULL,

  `added_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `edit_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `completed_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `tested_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

  `completed` tinyint(1) NOT NULL DEFAULT '0',

  `tested` tinyint(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=87 ;



CREATE TABLE IF NOT EXISTS `tl_types` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(45) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;