Moving apace, mariadb and php project from a debian site to other debian site

I want to create a bash script to backup and restore the project (apache, mariadb and php) easily. I need to work at 2 debian machine with the same configuration in 2 different location. I ask to duck.ai to solve the problem. Here is the result.

Create a file myconfig.conf

# myconfig.conf
DB_NAME="your_database_name"
DB_USER="your_username"
DB_PASS="your_password"
PROJECT_ROOT="/path/to/your/php/project"  # Add this line

Create bash script mybackup.sh 

#!/bin/bash

# Load database configuration from myconfig.conf
source myconfig.conf

# Create a backup file name for the database
DB_BACKUP_FILE="${DB_NAME}_$(date +%Y-%m-%d).sql"
# Create a zip file name for the project
PROJECT_BACKUP_FILE="$(basename "$PROJECT_ROOT")_backup_$(date +%Y-%m-%d).zip"

# Function to backup the database
backup_database() {
    echo "Backing up database: $DB_NAME"
    mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" > "$DB_BACKUP_FILE"
    if [ $? -eq 0 ]; then
        echo "Database backup successful: $DB_BACKUP_FILE"
    else
        echo "Database backup failed!"
        return 1
    fi
}

# Function to zip the project files
zip_project() {
    echo "Zipping project files from: $PROJECT_ROOT"
    zip -r "$PROJECT_BACKUP_FILE" "$PROJECT_ROOT"
    if [ $? -eq 0 ]; then
        echo "Project backup successful: $PROJECT_BACKUP_FILE"
    else
        echo "Project backup failed!"
        return 1
    fi
}

# Function to restore the database
restore_database() {
    echo "Restoring database: $DB_NAME from $1"
    mysql -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" < "$1"
    if [ $? -eq 0 ]; then
        echo "Database restore successful!"
    else
        echo "Database restore failed!"
    fi
}

# Function to unzip the project files
unzip_project() {
    echo "Unzipping project files to: $PROJECT_ROOT"
    unzip -o "$1" -d "$PROJECT_ROOT"
    if [ $? -eq 0 ]; then
        echo "Project restore successful!"
    else
        echo "Project restore failed!"
    fi
}

# Check command line arguments
if [ "$1" == "backup" ]; then
    backup_database
    zip_project
elif [ "$1" == "restore" ]; then
    if [ -z "$2" ] || [ -z "$3" ]; then
        echo "Please provide the SQL backup file and the project zip file to restore from."
        exit 1
    fi
    SQL_BACKUP_FILE="$2"
    PROJECT_BACKUP_FILE="$3"
    restore_database "$SQL_BACKUP_FILE"
    unzip_project "$PROJECT_BACKUP_FILE"
else
    echo "Usage: $0 {backup|restore [sql_backup_file] [project_backup_file]}"
    exit 1
fi

To backup

./mybackup.sh backup

It will create 2 files

1. zip for backup and restore web root project
2. sql for backup and restore mariadb database 

To restore

./mybackup.sh restore your_database_backup.sql your_project_backup_file.zip

 

How to fix error HY000/1045 after upgrading phpmyadmin

I used Debian 13 repository to upgrade phpmyadmin. During upgrading from previous version, configuration can not be continue and must be ignore. After phpmyadmin installed error messages come to web:

• mysqli::real_connect(): (HY000/1045): Access denied for user 'phpmyadmin'@'localhost' (using password: YES) 
• Connection for controluser as defined in your configuration failed.

Create user pma (default) and password:

MariaDB [(none)]> CREATE USER 'pma'@'localhost' IDENTIFIED BY 'pmapass';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON phpmyadmin.* TO 'pma'@'localhost';
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.002 sec)

Edit file /etc/phpmyadmin/config-db.php

...
$dbuser='pma';
$dbpass='pmapass';
...

Edit file /etc/phpmyadmin/config.inc.php

...
/* Configure according to dbconfig-common if enabled */
if (!empty($dbname)) {
    /* Authentication type */
    $cfg['Servers'][$i]['auth_type'] = 'cookie';
    /* Server parameters */
    if (empty($dbserver)) $dbserver = 'localhost';
    $cfg['Servers'][$i]['host'] = $dbserver;

    if (!empty($dbport) || $dbserver != 'localhost') {
        $cfg['Servers'][$i]['connect_type'] = 'tcp';
        $cfg['Servers'][$i]['port'] = $dbport;
    }
    //$cfg['Servers'][$i]['compress'] = false;
    /* Optional: User for advanced features */
    $cfg['Servers'][$i]['controluser'] = $dbuser;
    $cfg['Servers'][$i]['controlpass'] = $dbpass;
    /* Optional: Advanced phpMyAdmin features */
    $cfg['Servers'][$i]['pmadb'] = $dbname;
    $cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
    $cfg['Servers'][$i]['relation'] = 'pma__relation';
    $cfg['Servers'][$i]['table_info'] = 'pma__table_info';
    $cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
    $cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
    $cfg['Servers'][$i]['column_info'] = 'pma__column_info';
    $cfg['Servers'][$i]['history'] = 'pma__history';
    $cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
    $cfg['Servers'][$i]['tracking'] = 'pma__tracking';
    $cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
    $cfg['Servers'][$i]['recent'] = 'pma__recent';
    $cfg['Servers'][$i]['favorite'] = 'pma__favorite';
    $cfg['Servers'][$i]['users'] = 'pma__users';
    $cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
    $cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
    $cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
    $cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
    $cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
    $cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';

    /* Uncomment the following to enable logging in to passwordless accounts,
     * after taking note of the associated security risks. */
    // $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

    /* Advance to next server for rest of config */
    $i++;
}

/* Authentication type */
//$cfg['Servers'][$i]['auth_type'] = 'cookie';
/* Server parameters */
//$cfg['Servers'][$i]['host'] = 'localhost';
//$cfg['Servers'][$i]['connect_type'] = 'tcp';
//$cfg['Servers'][$i]['compress'] = false;
/* Uncomment the following to enable logging in to passwordless accounts,
 * after taking note of the associated security risks. */
// $cfg['Servers'][$i]['AllowNoPassword'] = TRUE;

/**
 * phpMyAdmin configuration storage settings.
 */

/* User used to manipulate with storage */
$cfg['Servers'][$i]['controlhost'] = '';
$cfg['Servers'][$i]['controlport'] = '';
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'pmapass';

/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';
...

 

Debian Bookworm: installing Apache2, MariaDB and PHP 8.2 (default)

 Installing MariaDB database

# apt-get install mariadb-client mariadb-server  

Install apache2 and PHP

# apt-get install apache2 php libapache2-mod-php php8.2-mysql

By default, PHP version for Debian Bookworm is 8.2.

To disable apache2 and mariadb start during boot (to faster your PC start, not recommended for live server)

# systemctl disable apache2
# systemctl disable mariadb

You can start and stop manualy after boot.

Install additional php 8.2 required by CodeIgniter

# apt-get install php8.2-curl php8.2-imagick php8.2-gd php8.2-intl php8.2-mbstring php8.2-memcache php8.2-memcached php8.2-redis php8.2-xml php8.2-phpdbg

Codeigniter: query builder select, insert, update and delete

Query table:
        $this->result = $this->db->get('ca_brand',$mmax, 0);
        if ($this->result) {
            return true;
        } else {
            return false;
        }
Query table with like filter:
        $this->db->like('name', $keyword); // buildin mysql escape
        $this->result = $this->db->get('ca_brand',0, $mmax);
        if ($this->result) {
            return true;
        } else {
            return false;
        }
 Insert row in table:
        // buildin mysql escape character
        $data = array('name' => $name);
        $this->db->insert('ca_brand', $data); // return true on success

        $insert_id = $this->db->insert_id(); // get id

Update row in table for spesific id
        // buildin mysql escape character
        $id = intval($id);
        $data = array ('name' => $name);
        $this->db->where('id', $id);
        //$this->db->update('ca_brand',$data, "id = "$id ); // option 1
        $this->db->update('ca_brand',$data); // // option 2
        $numaffectedrow = $this->db->affected_rows(); // to get nummber affected row
 Delete row:
        $id = intval($id);
        $this->db->where('id', $id);
        $this->db->delete('ca_brand');
        $numaffectedrow = $this->db->affected_rows(); // to get nummber affected row

Php 7.2 and Codeigniter 3.1.10

Codeigniter: check mysql connection

To check error in mysql connection, we need to change ENVIRONMENT to Production. Here is the example:
    //define('ENVIRONMENT', isset($_SERVER['CI_ENV']) ? $_SERVER['CI_ENV'] : 'development');
    //define('ENVIRONMENT', isset($_SERVER['CI_ENV']) ? $_SERVER['CI_ENV'] : 'testing');
    define('ENVIRONMENT', isset($_SERVER['CI_ENV']) ? $_SERVER['CI_ENV'] : 'production');
There are two ways to catch error in our controller:
1. Using dblib
        $this->load->dbutil();
        if ($this->dbutil->database_exists('myca')) {
            // Connection ok
        } else {
            // Connection fail
        }
2. Checking error in db
        $this->load->database();
        $errhandle = $this->db->error();
        if ($errhandle['code']==0) {
            // Connection ok
        } else {
            // Connection fail
            // to dump error code var_dump( $this->db->error());
        }
Tested on PHP 7.2 and Codigniter 3.1.10

PHP >= 5.5: password hashing

To hash password (using default algorithm bcrypt)

$mypass = "password";
$myhash = password_hash($mypass, PASSWORD_DEFAULT);

To verify password

$brutepass = "test";
password_verify ($brutepass, $myhash); // true or false

Storing password in database

"Therefore, it is recommended to store the result in a database column that can expand beyond 60 characters (255 characters would be a good choice)." Maybe varchar(255)

Reference:

• http://www.php.net/manual/en/faq.passwords.php
• http://id1.php.net/manual/en/book.password.php

PHP 5: GeoIP

Install PHP GeoIP

# apt-get install geoip-bin geoip-database geoip-database-extra php5-geoip php5-geos

Updating GeoIP database from SID (choose your mirror)

# wget http://kambing.ui.ac.id/debian/pool/main/g/geoip-database/geoip-database-extra_20160912-1_all.deb

# wget http://kambing.ui.ac.id/debian/pool/main/g/geoip-database/geoip-database_20160912-1_all.deb

# dpkg -i geoip-database_20160912-1_all.deb

# dpkg -i geoip-database-extra_20160912-1_all.deb  

To convert IP to integer

$ip = ip2long('119.249.54.66');

To convert integer to IP

$hostip = long2ip($ip);

To get 3 chars country code 

echo geoip_country_code3_by_name($hostip);

To get country name 

echo geoip_country_name_by_name($hostip);

To get country code and region

echo var_dump(geoip_region_by_name($hostip))."<br>";

 Error: mod_fcgid: stderr: PHP Warning:  geoip_region_by_name(): Required database not available at /usr/share/GeoIP/GeoIPRegion.dat. ??? May be required subscription premium service ??? 

Reference:

http://www.php.net/manual/en/book.geoip.php 

PHP >5.3: DateTime

Example construct Datetime object

$datetime = new DateTime("now");
$datetime = new DateTime('2000-01-01');

Example to print Datetime

echo $datetime->format('Y-m-d\TH:i:s');

Example to add a hour

$datetime->add(new DateInterval('PT1H'));

Example to add 10 day

$datetime->add(new DateInterval('P10D'));

Note:

• P: Period
• T: Time

Comparing 2 Datetime

$datetime = new DateTime('2016-10-10');
$datetime2 = new DateTime('2016-10-9');
echo var_dump($datetime > $datetime2).' $datetime > $datetime2 <br>'; // bool(true) $datetime > $datetime2 
echo var_dump($datetime < $datetime2).' $datetime < $datetime2 <br>'; // bool(false) $datetime < $datetime2 

References:

• http://www.php.net/manual/en/class.datetime.php 

Simple Pagination With PHP and MySQL

This is my personal note to create simple pagination using php and mysql. I used mysqli object oriented style for future php support. Version MySQL and PHP are:

• MySQL Version 5.5
• PHP Version 5.6

To create your own demo, create your database and populate it.
CREATE TABLE `kci_logipv4` (
  `logdate` datetime DEFAULT NULL,
  `logipv4` int(11) unsigned DEFAULT NULL,
  `logmsg` varchar(1000) DEFAULT NULL,
  `logfrom` int(11) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `kci_logipv4` VALUES ('2016-09-28 23:22:11',3104583205,'SSH',1),('2016-09-28 23:22:11',3104583205,'SSH',1),('2016-09-29 09:07:50',1347119510,'SSH',1),('2016-09-29 09:07:51',1347119510,'SSH',1),('2016-09-29 20:28:27',3743330746,'SSH',1),('2016-09-29 20:28:28',3743330746,'SSH',1),('2016-09-29 20:59:51',1033072143,'SSH',1),('2016-09-29 20:59:53',1033072143,'SSH',1),('2016-09-29 21:06:55',1907273049,'SSH',1),('2016-09-29 21:06:57',1907273049,'SSH',1),('2016-09-30 03:20:08',3075559489,'SSH',1),('2016-09-30 03:20:09',3075559489,'SSH',1),('2016-09-30 11:47:19',1323303364,'SSH',1),('2016-09-30 11:47:21',1323303364,'SSH',1),('2016-09-30 21:59:33',3565240261,'SSH',1),('2016-09-30 21:59:35',3565240261,'SSH',1),('2016-10-01 03:09:18',1794631394,'SSH',1),('2016-10-01 03:09:18',1794631394,'SSH',1),('2016-10-01 10:02:35',2746017717,'SSH',1),('2016-10-01 10:02:36',2746017717,'SSH',1),('2016-10-01 10:29:18',1963359914,'SSH',1),('2016-10-01 10:29:19',1963359914,'SSH',1),('2016-10-01 10:57:30',2000423175,'SSH',1),('2016-10-01 10:57:31',2000423175,'SSH',1),('2016-10-01 21:33:06',1757971329,'SSH',1),('2016-10-01 21:33:07',1757971329,'SSH',1),('2016-10-02 21:00:31',1033072141,'SSH',1),('2016-10-02 21:00:34',1033072141,'SSH',1),('2016-10-03 02:35:10',2065638212,'SSH',1),('2016-10-03 02:35:11',2065638212,'SSH',1),('2016-10-03 06:18:41',3754290014,'SSH',1),('2016-10-03 06:18:43',3754290014,'SSH',1),('2016-10-03 15:14:53',1912501632,'SSH',1),('2016-10-03 15:14:53',1912501632,'SSH',1),('2016-10-04 00:04:44',3281659836,'SSH',1),('2016-10-04 00:04:45',3281659836,'SSH',1),('2016-10-04 01:45:32',1168426137,'SSH',1),('2016-10-04 01:45:33',1168426137,'SSH',1),('2016-10-04 12:04:05',2065638165,'SSH',1),('2016-10-04 12:04:05',2065638165,'SSH',1),('2016-10-04 13:11:16',457715326,'SSH',1),('2016-10-04 13:11:16',457715326,'SSH',1),('2016-10-04 16:50:40',1839072489,'SSH',1),('2016-10-04 16:50:40',1839072489,'SSH',1),('2016-10-05 09:20:48',1760484871,'',1),('2016-10-05 09:20:48',1760484871,'',1);
Here is the kci_logread.php code
<?php

/*
database: database
table: kci_logipv4
logdate datetime
logipv4 int(11)
logmsg varchar(1000)
logfrom int
*/

// host, user, password, database, [port]
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
    die('Internal Server Error maybe database');
}

//This is the page number want to display
//retrieve from user navigation via method get or post
$page = 1;
if(isset($_GET["page"])){
    $page = intval($_GET["page"]); // sanitize input
}
if(isset($_POST["page"])){
    $page = intval($_POST["page"]); // sanitize input
}
//This is the number of results displayed per page
$page_rows = 15;

//count all result
//must equal to query to display data
$res = $mysqli->query("select Count(*) as total from kci_logipv4");
$rows = $res->num_rows;
//number of data available
$total=0;
if($rows) {
    $row = $res->fetch_assoc();
    $total = $row['total'];
}

//this is which first row should be retrieve
$start = ($page_rows * $page) - $page_rows;
//query to display data
//read $page_row data start from row $start 
$res = $mysqli->query("SELECT * FROM kci_logipv4 order by logdate desc Limit $start, $page_rows ");
//number of data to display
$rows = $res->num_rows;

//This tells us the page number of our last page
$last = ceil($total/$page_rows);

?>
<html>
<body>
<p>Server Farm Info</p>
<?php
if ($res) {
    echo "<p>Total: $total</p>";
    if ($rows>0) {
?>
<table border="1">
    <tr>
        <th>From</th>
        <th>Datetime</th>
        <th>IP</th>
        <th>Comment</th>
    </tr>
<?php
        while ($row = $res->fetch_assoc()) {
?>
    <tr>
        <td>Server farm <?php //echo $row['logfrom']; ?></td>
        <td><?php echo $row['logdate']; ?></td>
        <td align="center"><?php echo long2ip($row['logipv4']); ?></td>
        <td><?php echo $row['logmsg']; ?></td>
    </tr>
<?php
        }
    } else {
        echo "<p>No Data</p>";
    }
?>
</table>
<?php
    if ($last>1) {
        echo "<P>";
        if ($page>1) {
            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=1\">First</a> ";
        }
        if ($page>2 && $page<=$last) {
            echo "... ";
        }
        if ($page>1) {
            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=".($page-1)."\">".($page-1)."</a> ";
        }
        echo "$page ";
        if ($page+1<=$last) {
            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=".($page+1)."\">".($page+1)."</a> ";
        }
        if ($page+1<$last) {
            echo "... ";
        }
        if ($page<$last) {
            echo "<a href=\"".htmlspecialchars($_SERVER["PHP_SELF"], ENT_QUOTES, "utf-8")."?page=$last\">Last</a> ";
        }
        echo "| Number of pages: $last </p>";
} else {
    echo "<p>Query Fail</p>";
}
?>
</body>
</html>

How to integrated web template into codeigniter 3.0.6 (with upgrade to 3.1.2)

Prerequistes:

• download codeigniter 3.1.2 (my version 3.0.6) from https://www.codeigniter.com/download
  
• download web template for example Frequent Flyer a Travel Category Flat Bootstrap Responsive Web Template from https://w3layouts.com/frequent-flyer-travel-category-flat-bootstrap-responsive-web-template/
  

After you extract codeigniter 3.1.2 you may have these structure directory:
[root codeigniter]
application
system
userguide
To upgrade codeigniter 3.0.6 to 3.1.2, extract codeigniter 3.1.2 and copy/replace system folder into [root codeigniter] folder.
Extract your template into codeigniter folder [root codeigniter].
[root codeigniter]
application
system
userguide
fft--css
--fonts
--images
--js
--video
--index.html
--w3layouts-License.txt
Create file test.php file in folder controller.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Test extends CI_Controller {
  public function __construct() {
    parent::__construct();
  }
  public function view($page = 'Test')
  {
    $this->load->helper('html');
    $this->load->helper('url');
    if ( ! file_exists(APPPATH.'views/fft/'.$page.'.php'))
    {
      show_404();
    }
    $data['title'] = $page; // Capitalize the first letter
    $this->load->view('fft/templates/pageheader',$data);
    $this->load->view('fft/'.$page, $data);
    $this->load->view('fft/templates/pagefooter');
  }
}
?>
At folder views, create folder fft.
application
--------bodymenu.php
--------pageheader.php
------test.php
We split file views->test.php. The other supporting files we put them into folder  [root codeigniter]->application->templates.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
?>
<!-- banner -->
  <div data-vide-bg="<?php echo base_url(); ?>fft/video/sea">
    <div class="center-container">
      <div class="container">
        <!-- load body header -->
        <?php $this->load->view('fft/templates/bodyheader'); ?>
        <div class="logo animated wow zoomIn" data-wow-delay="900ms">
          <h1><a href="/web/joomla/index.html">Frequent <span>flyer</span><i>take me anywhere</i></a></h1>
        </div>
        <div class="start animated wow bounce" data-wow-delay="700ms">
          <a href="#about" class="hvr-bounce-to-bottom scroll">Get Started</a>
        </div>
      </div>
    </div>
  </div>
Final directory will like this:
[root codeigniter]
------views
------templates
--------bodyheader.php
--------pagefooter.php
------about.php
------test.php
--system
--userguide
--fft
----css
----fonts
----images
----js
----video
----index.html
----w3layouts-License.txt

References:

• https://www.codeigniter.com/download 
• https://w3layouts.com/frequent-flyer-travel-category-flat-bootstrap-responsive-web-template/
• http://www.codeigniter.com/user_guide/installation/upgrade_310.html