PHP File Access with MySQL

PHP File and Class

Apply the object-oriented concept in PHP with file access. Select records from database and save result to the file content, and use the file content to insert record to the database table.

Singleton class uses PDO

• This is the class to connect to the MySQL database server.
• You need to change the username, password, and database name according to your own environment.

<?php
define('USERNAME2', 'youruser');
define('PASSWORD2', 'yourpassword');
define('DSN2', "mysql:host=localhost;dbname=socms");
 
class pdo_mysql {
 
	private static $_instance;
 
	public static function &pdo_connection() {
		if (!self::$_instance) {
			try {
				self::$_instance = new PDO(DSN2, USERNAME2, PASSWORD2);
				self::$_instance->setAttribute(PDO::ATTR_PERSISTENT, true);
				self::$_instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			} catch (PDOException $est) {
				die("pdo connection error! " . $est->getMessage() . "<br/>");
			}
		}
		return self::$_instance;
	}
 
	private function __construct() {
		
	}
 
	private function __clone() {
		
	}
}
?>

This php program to demonstrate how to insert file from and to mysql (insert2file.php)

<?php

// use the singleton class
require_once 'pdo_mysql.php';

// this called inherited from pdo_mysql class 
class insert2file extends pdo_mysql {
        // connection
	private $conn;
	private $return_sql;
	public $sql;
 
        // initialize the connection 
        // within the class constructor 
	public function __construct() {
		$this->conn = pdo_mysql::pdo_connection();
	}

        // insert function to insert to the table
        // this function will return true or false
	public function insert() {
		$db = $this->conn->prepare($this->sql);
		if (!$db->execute()) {
			return false;
		} else {
			return true;
		}
		return false;
	}

        // this function use for query the table
	public function select() {
		$db = $this->conn->prepare($this->sql);
		if (!$db->execute()) {
			return false;
		}
		$this->return_sql = $db->fetchall(PDO::FETCH_ASSOC);
		return $this->return_sql;
	}
        // this function use to save to file 
	public function save2file($file_nam=null) {
		if (!empty($file_nam)) {
			$file_name = $file_nam;
		} else {
			$file_name = 'ablec.txt';
		}
 
		$file_handler = fopen($file_name, 'w') 
                          or exit('can not save to file!!!');
 
		$column_name = $this->column();
		$string_column_name = implode(',', $column_name);
		fwrite($file_handler, $string_column_name . "\n");
 
		foreach ($this->return_sql as $key => $value) {
			$string_to_save = implode(',', $value);
			fwrite($file_handler, $string_to_save . "\n");
		}
		fclose($file_handler);
	}
        // get the table column 
	public function column() {
		$result = $this->select($this->sql . ' LIMIT 1');
		return array_keys($result[0]);
	}
        // insert from file to mysql
	public function file2insert($file_name, $table_name) {
 
		$file_handler = fopen($file_name, 'r')
                           or exit('can not open file!');
 
		$column_name = fgets($file_handler);
		$column_array = explode(",", $column_name);
 
		$column_array = explode(",", $column_name);
 
		// create binding value
		$temp = array();
		$type_bind = array();
		for ($i = 0; $i < count($column_array); $i++) {
			$temp[] = "?";
		}
		$column_value = implode(', ', $temp);
		// create binding insert statement
		$this->sql = 'INSERT INTO ' . $table_name . '(' . $column_name . 
                        ') VALUES(' . $column_value . ')';
		// total column need to bind
		$total_coln = count($temp);
 
		while (!feof($file_handler)) {
			$value = fgets($file_handler);
			if (!empty($value)) {
				$arr_v = explode(",", $value);
				$insert_statment = $this->conn->prepare($this->sql);
				$insert_statment->execute($arr_v);
			}
		}
	}
}
 
?>

Code (Test1.php)

• We select everything from the table named page and stores its values in the text file called page222.txt

<?php
$test = new insert2file(); 
$test->sql = "SELECT * FROM page";
$test->save2file('page222.txt');
?>

Code (Test2.php)

• Insert into the table from the text file.
• Please note that if the content of the record have the same duplicate primary key, it will display a warning or error.

<?php
$test = new insert2file();
$test->file2insert('page222.txt','page');
?>

Related Links

• PHP File Access and File Templates
• PHP Tutorial
• Tutorial Home

---

1. PHP File Linux vs. Windows
2. PHP File Information
3. PHP File Create
4. PHP File Close
5. PHP File Read
6. PHP File Read Reverse

1. PHP File Write
2. PHP File Write to end or append to
3. PHP File Write at Beginning
4. PHP File Write at specific location
5. PHP File Truncate
6. PHP File Replace a specific word

1. PHP File Replace a specific word with associative array
2. PHP File Copy File
3. PHP File Copy Reverse
4. PHP File Search within file
5. PHP File Delete File
6. PHP File Template

1. PHP File Email Template
2. PHP File Template with MySQL
3. PHP File Access with MySQL