Member Avatar for Abdul Waseem

Read first row from csv file and create table automatically according to it(csv file fields) in mysql. Looking for PHP script? I have tried this.

<?php

$arr = array(array(),array());
$num = 0;
$row = 0;
$handle = fopen("./contacts.csv", "r");


while($data = fgetcsv($handle,1000,",")){   
    $num = count($data);
    for ($c=0; $c < $num; $c++) {
            $arr[$row][$c] = $data[$c];
    }
    $row++;
}


$con = mysql_connect('localhost','root','');
mysql_select_db("excel_database",$con);

for($i=1; $i<$row; $i++){
$sql = "INSERT INTO contacts VALUES ('".$arr[$i][0]."','".$arr[$i][1]."','".$arr[$i][2]."','".$arr[$i][3]."','".$arr[$i][4]."','".$arr[$i][5]."')";
mysql_query($sql,$con);
}

?>
  • 2 Contributors
  • 1 Reply
  • 5K Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by Bachu

Dani AI

Generated

A couple of improvements will make this approach safer and more scalable. First, avoid the legacy mysql_* API, which is removed in PHP 7; use PDO or MySQLi with transactions and prepared statements to prevent SQL injection and partial imports (PDO). When turning the header row into columns, normalize names to valid MySQL identifiers: trim, lowercase, replace spaces/punctuation with underscores, ensure they do not start with a digit, truncate to 64 chars, de-duplicate (append _2, _3, ...), and check against reserved words. Always quote identifiers with backticks and never trust the table name if it comes from user input (Identifiers, Keywords).

Rather than defaulting every column to VARCHAR(500), scan some or all rows to infer types (INT, DECIMAL, DATETIME/DATE via strtotime or regex; else VARCHAR with a length based on max observed length). Wrap the CREATE TABLE + import in a single transaction. For large files, LOAD DATA [LOCAL] INFILE is faster and can skip the header:

LOAD DATA LOCAL INFILE '/path/to/contacts.csv'
INTO TABLE contacts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

If headers need mapping, load into a staging table and then INSERT ... SELECT into your normalized table. Mind server settings like secure_file_priv and local_infile when using this feature (LOAD DATA INFILE). For CSV edge cases (embedded commas, enclosures), review PHP’s parser options in fgetcsv.

Member Avatar for Bachu

Try this

<?php
 //table Name
$tableName = "MyTable";
//database name
$dbName = "MyDatabase";


 $conn = mysql_connect("localhost", "root", "") or die(mysql_error()); 
 mysql_select_db($dbName) or die(mysql_error()); 

//get the first row fields 
$fields = "";
$fieldsInsert = "";
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    if(($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $num = count($data);
        $fieldsInsert .= '(';
        for ($c=0; $c < $num; $c++) {
            $fieldsInsert .=($c==0) ? '' : ', ';
            $fieldsInsert .="`".$data[$c]."`";
            $fields .="`".$data[$c]."` varchar(500) DEFAULT NULL,";
        }

        $fieldsInsert .= ')';
    }


    //drop table if exist
    if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$tableName."'"))>=1) {
      mysql_query('DROP TABLE IF EXISTS `'.$tableName.'`') or die(mysql_error());
    }

    //create table
    $sql = "CREATE TABLE `".$tableName."` (
              `".$tableName."Id` int(100) unsigned NOT NULL AUTO_INCREMENT,
              ".$fields."
              PRIMARY KEY (`".$tableName."Id`)
            ) ";

    $retval = mysql_query( $sql, $conn );

    if(! $retval )
    {
      die('Could not create table: ' . mysql_error());
    }
    else {
        while(($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

                $num = count($data);
                $fieldsInsertvalues="";
                //get field values of each row
                for ($c=0; $c < $num; $c++) {
                    $fieldsInsertvalues .=($c==0) ? '(' : ', ';
                    $fieldsInsertvalues .="'".$data[$c]."'";
                }
                $fieldsInsertvalues .= ')';
                //insert the values to table
                $sql = "INSERT INTO ".$tableName." ".$fieldsInsert."  VALUES  ".$fieldsInsertvalues;
                mysql_query($sql,$conn);    
        }
        echo 'Table Created';   
    }

    fclose($handle);

}

?>
Edited by Bachu because: text correction
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.