PHP Classes

File: MyCSV_MySQL.class.php

Recommend this page to a friend!
  Classes of Thiemo Kreuz   TM::MyCSV   MyCSV_MySQL.class.php   Download  
File: MyCSV_MySQL.class.php
Role: Class source
Content type: text/plain
Description: Extended Class
Class: TM::MyCSV
Manage CSV files like database tables
Author: By
Last change:
Date: 14 years ago
Size: 4,853 bytes
 

Contents

Class file image Download
<?php

require_once("MyCSV.class.php");

/**
 * MyCSV from and to MySQL import and export methods.
 *
 * @author Thiemo Mättig (http://maettig.com/)
 * @version 2003-12-28
 * @package TM
 */
class MyCSV_MySQL extends MyCSV
{
   
/**
     * Imports a MyCSV table from a MySQL table.
     *
     * Any data already in the MyCSV object will be lost. Returns false on
     * failure.
     *
     * @param source mixed
     * @return bool
     */
   
function fromMySQL($source = "")
    {
       
// Assume source is a string containing the table name.
       
if (! is_resource($source))
        {
           
// Use the internal table name already set in the constructor, hopefully.
           
if (! $source) list($source) = explode(".", basename($this->filename));
           
$source = mysql_query("SELECT * FROM " . $source);
        }
       
// Break if the source is still false.
       
if (! $source) return false;
       
$this->fields = array("id");
       
$this->data = array();
        while (
$row = mysql_fetch_assoc($source)) $this->insert($row);
    }

   
/**
     * Exports a MyCSV table into a MySQL table.
     *
     * Any data already in the MySQL table will be lost.
     *
     * @param tablename string
     * @return bool
     */
   
function toMySQL($tablename = "")
    {
       
$sqlArray = $this->toSQL($tablename);
        while (
$sql = each($sqlArray)) if (! mysql_query($sql)) return false;
    }

   
/**
     * Prints a SQL dump of the table.
     *
     * @param tablename string
     * @return bool
     */
   
function dumpSQL($tablename = "")
    {
        echo
implode("\n", $this->toSQL($tablename));
    }

   
/**
     * Creates an array of SQL statements.
     *
     * @param tablename string
     * @return array
     */
   
function toSQL($tablename = "")
    {
        if (!
$tablename)
        {
           
// Use the CSV filename as the tablename if required.
           
$tablename = preg_replace('/\.[^.]*$/', '', basename($this->filename));
        }

       
$types = array(
           
0 => "TINYINT", // -128 to 127
           
1 => "SMALLINT", // -32768 to 32767
           
2 => "INT", // -2147483648 to 2147483647
           
3 => "DOUBLE",
           
4 => "VARCHAR(255)",
           
5 => "LONGTEXT",
           
6 => "LONGBLOB");
        foreach (
$this->fields as $field) $t[$field] = 0;
       
reset($this->data);
        while (
$row = $this->each())
        {
            foreach (
$row as $field => $value)
            {
                if (
preg_match('/[\x00-\x06\x08\x0B-\x0C\x0E-\x13\x16-\x1F]/', $value))
                   
$t[$field] = 6;
                elseif (
strlen($value) > 255 || strpos($value, "\n") !== false)
                   
$t[$field] = max($t[$field], 5);
                elseif (! empty(
$value) && ! is_numeric($value))
                   
$t[$field] = max($t[$field], 4);
                elseif (!
preg_match('/^[+-]?\d{0,9}$/s', $value))
                   
$t[$field] = max($t[$field], 3);
                elseif (!
preg_match('/^[+-]?\d{0,4}$/s', $value))
                   
$t[$field] = max($t[$field], 2);
                elseif (!
preg_match('/^[+-]?\d{0,2}$/s', $value))
                   
$t[$field] = max($t[$field], 1);
            }
        }

       
// Drop the MySQL table if exists.
       
$sqlArray = array("DROP TABLE IF EXISTS " . $this->_backquote($tablename) . ";");
       
$sql = "CREATE TABLE " . $this->_backquote($tablename) . " (\n";
        foreach (
$this->fields as $field)
        {
           
$sql .= " " . $this->_backquote($field) . " ";
           
$sql .= $types[$t[$field]] . " NOT NULL";
            if (
strtolower($field) == "id") $sql .= " AUTO_INCREMENT";
           
$sql .= ",\n";
        }
       
$sql .= " PRIMARY KEY (" . $this->_backquote("id") . ")\n);";
       
$sqlArray[] = $sql;

       
reset($this->data);
        while (
$row = $this->each())
        {
            foreach (
$row as $field => $value)
            {
               
$rowSql[$this->_backquote($field)] = "'" . mysql_escape_string($value) . "'";
            }
           
$sql = "INSERT INTO " . $this->_backquote($tablename) . " (";
           
$sql .= implode(", ", array_keys($rowSql));
           
$sql .= ") VALUES (" . implode(", ", $rowSql) . ");";
           
$sqlArray[] = $sql;
        }

        return
$sqlArray;
    }

   
/**
     * Quotes database and table names with back quotes. Maximum length is 64
     * characters. Allowed characters are "any character that is allowed in a
     * file name", except "/", ".", ASCII(0), ASCII(255) and the quoting
     * character (from the MySQL manual).
     *
     * @param name string
     * @return string
     */
   
function _backquote($name)
    {
       
$name = strtr($name, chr(0) . "./`" . chr(255), " _-'y");
        return
"`" . substr($name, 0, 64) . "`";
    }
}

?>