Build a basic, bare-bones CRUD Template (PHP/MySQL)

Posted by admin at December 27, 2016

In this tutorial we are going to setup a very simple template for performing CRUD operations on MySQL tables using PHP.

Setting up the template directory

Create a directory structure that looks like this:

Connection to database (conn.php)


<?php

$host = 'localhost';
$db = 'DB_NAME';
$user = 'DB_USERNAME';
$pass = 'DB_PASS';

// setup the connection
$conn = mysql_pconnect($host, $user, $pass) or trigger_error(mysql_error(), E_USER_ERROR); 

// select the database
mysql_select_db($db, $conn);

?>

Support functions (support.php)


<?php
/**
* Support functions for CRUD.
*
* @package    support.php
* @subpackage 
* @author     BlinkWiki
* @version    1.0
* Copyright 2016 BlinkWiki
* 
*/
?>
<?php
    
// function that retrieves the last index in a table
function rec_get_next($tbl, $tblid, $conn)
{
    $sql = get_read_sql($tbl)
        .' ORDER BY `'.$tblid.'`'
        .' DESC LIMIT 1'
    ;
    $rs = mysql_query($sql, $conn) or die(mysql_error());
    $row = mysql_fetch_assoc($rs);
    $total_rows = mysql_num_rows($rs);
    
    return intval($row[$tblid] + 1);
}
    
// function to populate select boxes
function selectbox($tbl, $opt_value, $opt_text, $rid, $conn)
{
    // build the read query
    $sql = get_read_sql($tbl);
    $rs = mysql_query($sql, $conn) or die(mysql_error());
    $row = mysql_fetch_assoc($rs);
    $total_rows = mysql_num_rows($rs);
    
    $sel = ($rid == 0) ? 'selected' : '';
    $html = '<option value="0" '.$sel.'>--select--</option>';
    
    for ($i=0; $i<$total_rows; $i++)
    {
        $sel = ($row[$opt_value] == $rid) ? 'selected' : '';
        
        $html .= '<option value="'.$row[$opt_value].'" '.$sel.'>'.$row[$opt_text].'</option>';
        
        // get the next row
        $row = mysql_fetch_assoc($rs);
    }
    
    return $html;
}

// function to ensure all values in an array are submitted
function ensure_submit($post, $mand_str)
{
    // make the string an array
    $post_arr = explode(',', $mand_str);
    
    // initialise the result to true
    $res = true;
    
    // if mandantory fields were entered
    if (count($post_arr) > 0)
    {
        // loop through looking for empty strings
        foreach ($post_arr as $key => $value)
        {
            if (
                strlen($post[$value]) <= 0
                || $post[$value] == 0
               )
            {
                // submitted value is empty
                $res = false;
            }
        }
    }
    
    return $res;
}

// function to submit $_POST values
function submit_form($post, $conn)
{
    $msg = 'error submitting form!';
    if (isset($post['hd_frm']))
    {
        echo 'form submitted....
';

        // make sure all the mandatory fields are fields
        $form_is_valid = false;
        if (isset($_POST['mand_flds']))
        {
            // make sure mandatory fields are set
            $form_is_valid = ensure_submit($post, $_POST['mand_flds']);
        }
        else
        {
            // set form submission as valid
            $form_is_valid = true;
        }
        if (
            $form_is_valid == true
        )
        {
            $msg = 'all mandatory fields submitted!';
        }

        if ($form_is_valid)
        {
            // get the query
            if ($post['hd_frm'] == 'frm_u')
            {
                $SQL = get_update_sql($post, $conn);
            }
            else
            {
                $SQL = get_create_sql($post, $conn);
            }

            // submit the query
            $rs = mysql_query($SQL, $conn) or die(mysql_error());
            if ($rs)
            {
                $row = mysql_fetch_assoc($rs);
                $msg = 'form submitted successfully!';
            }
            else
            {
                $msg = 'error submitting form!';
            }
        }

        // redirect: the javascript way
        echo '<script type="text/javascript">window.location = "?msg='.$msg.'";</script>';

    }
}
    
// submit the form
submit_form($_POST, $conn);
   
?>

index.php


<?php
// For make the connections to the database
require('inc/conn.php');
// import the required CRUD functions
require('inc/crud.php');
// import CRUD support
require('inc/support.php');
?><html lang="en-US" style="height: 100%;">
<head>
<title>CRUD App Template</script>
<link rel="stylesheet" href="css/style.css" type="text/css" />
</head>
<body>
    
<strong>CRUD App Template</strong><br>

...insert app body text that will appear on all CRUD pages
    
    
<?php
    $get_act = mysql_real_escape_string($_GET['a']);
    $get_act = (strlen($get_act) > 0) ? $get_act : 'r';
?>
    
<?php if ($get_act == 'c') { ?>
    
<?php //*********************************************CREATE ?>

    <?php include ('inc/c.php') ;?>
    
<?php //*********************************************CREATE ?>
    
<?php } else if ($get_act == 'r'){ ?>
    
<?php //*********************************************READ ?>
    
    <?php include ('inc/r.php') ;?>
    
<?php //*********************************************READ ?>

<?php } else if ($get_act == 'u') { ?>
    
<?php //*********************************************UPDATE ?>

    <?php include ('inc/u.php') ;?>
    
<?php //*********************************************UPDATE ?>
    
    <?php } else if ($get_act == 'd'){ ?>
    
<?php //*********************************************DELETE ?>

    <?php include ('inc/d.php') ;?>
    
<?php //*********************************************DELETE ?>

<?php } ?>
    
    <hr>
    © 2016 BlinkWIki
    
</body>
</html>

0 likes

Build a basic, bare-bones CRUD Template (PHP/MySQL)

Archives

Links