how to import csv file with current date store in database using php

Question

I am import csv file in mysql database without the date format.but i have to insert current data and time in separate column.how can I implement the code to store the date and time.

php code:

       include_once("includes/dbConnection.php");
       session_start();
         if(!isset($_SESSION['u_id']))
              {
             header("Location:membership_import_csvpage.php");
                }
               //$deleterecords = "TRUNCATE TABLE contacts";
            //empty the   table of its current records
          //mysql_query($deleterecords);

       if ($_FILES[csv][size] > 0) { 

          $file = $_FILES[csv][tmp_name]; 
          $handle = fopen($file,"r"); 
         $date = date('Y-m-d');
           do { 
             if ($data[0]) { 
          mysql_query("INSERT INTO membership (mbid, name,identification_number,date,user_id) VALUES 
            ( 
                '".addslashes($data[0])."', 
                '".addslashes($data[1])."', 
                '".addslashes($data[2])."',
                  '".addslashes($data  ['CURDATE()'])."',
                 '".addslashes($_SESSION['u_id'])."'                    
            ) "); 
             } 
        }                while ($data = fgetcsv($handle,1000,",","'")); 
             // 

              //redirect 
           echo ("<SCRIPT LANGUAGE='JavaScript'>
           window.alert('import csv Updated successfully')
   window.location.href='membership_import_csvpage.php';
          </SCRIPT>");

       die; 

     } 
     ?>

So like MySQL's native `NOW()` function then? https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html — CD001, May 12 '16 at 10:55
hi i am using now() function but it is not stored in database.however we can use another method on php .. — Vallavan J, May 12 '16 at 11:01
replace `'".addslashes($data ['CURDATE()'])."'` with just `NOW()` - shouldn't be wrapped in quotes, shouldn't be escaped in any way in the SQL - it's a native MySQL function (`CURDATE()` should work too with a `date` type). You are using an obsolete database connector though and `addslashes` is not the correct way to escape strings for insertion : http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php/12860046#12860046 — CD001, May 12 '16 at 11:12
i am new to the php. plzz tell me a acutally place to change and how can store the date and time in mysql database. — Vallavan J, May 12 '16 at 11:23
u can apply default value to that column. default can b now() — Asad, May 12 '16 at 12:16

Asad · Answer 1 · 2016-05-12T12:29:01.287

0

apply the default value constraint to that column which is storing the date and time . set the column to Not null and set it default= current_timestamp . it will auto fill the column with current date and time . and dont give the date and time from query i.e manually . it will auto fill that column

 +-------+-------------+------+-----+-------------------+-------+
 | Field | Type        | Null | Key | Default           | Extra |
 +-------+-------------+------+-----+-------------------+-------+
 | str   | varchar(32) | YES  |     | NULL              |       | 
 | date  | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
 +-------+-------------+------+-----+-------------------+-------+

edited May 12 '16 at 12:29

answered May 12 '16 at 12:22

Asad

3,070
7
23
61

i am getting a result i am using this function '".addslashes(date("m/d/Y"))."'. – Vallavan J May 24 '16 at 07:55
Thanks for all rplying me. – Vallavan J May 24 '16 at 07:56

score 0 · Answer 2 · edited Jul 06 '19 at 12:54

Try This its for csv files

<?php 
    $connect = mysqli_connect("localhost", "root", "", "testing");
    if(isset($_POST["submit"]))
{
    if($_FILES['file']['name'])
    {
        $filename = explode(".", $_FILES['file']['name']);
        if($filename[1] == 'csv')
        {
            $handle = fopen($_FILES['file']['tmp_name'], "r");
            while($data = fgetcsv($handle))
            {
                $item1 = mysqli_real_escape_string($connect, $data[0]);  
                $item2 = mysqli_real_escape_string($connect, $data[1]);
                $query = "INSERT into excel(excel_name, excel_phone) values('$item1','$item2')";
                mysqli_query($connect, $query);
            }
            fclose($handle);
            echo "<script>alert('Import done');</script>";
        }
    }
}
?>
<!DOCTYPE html>
<html>
<head>
    <title></title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>  
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
    <style>
        table{
            border-collapse: collapse;
            width: 100%;
            color: #588c7e;
            font-family: monospace;
            font-size: 25px;
            text-align: left;
        }
        th{
            background-color: #588c7e;
            color: white;
        }
        tr:nth-child(even) {background-color: #f2f2f2}
    </style>
</head>
<body>
    <form method="post" enctype="multipart/form-data">
        <div align="center">
            <label>Select CSV File:</label>
            <input type="file" name="file" />
            <br />
            <input type="submit" name="submit" value="Import" class="btn btn-info" />
        </div>
    </form><br><br>
    <table>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Phone_No</th>
            <?php
            $conn = mysqli_connect("localhost", "root", "", "testing");
            // Check connection
                if ($conn->connect_error) {
                    die("Connection failed: " . $conn->connect_error);
                } 
                $sql = "SELECT excel_id, excel_name, excel_phone FROM excel";
                $result = $conn->query($sql);
                if ($result->num_rows > 0) {
                    // output data of each row
                    while($row = $result->fetch_assoc()) {
                        echo "<tr><td>" . $row["excel_id"]. "</td><td>" . $row["excel_name"] . "</td><td>"
                        . $row["excel_phone"]. "</td></tr>";
                    }
                    echo "</table>";
                } else { echo "<script>alert('0 Results');</script>"; }
                $conn->close();
            ?>
        </tr>
    </table>
</body>
</html>

how to import csv file with current date store in database using php

2 Answers2