|
PHP - Change Date Format Upon Mysql Import
Hi,
I am importing data from a csv file into mysql. Everything works fine except that the 'order_date' column in CSV is in the following format mm/dd/yy. Upon import I would like for that format to be changed to yyyy-mm-dd I am not sure how to modify the following code to be able to do so. Any help would be appreciated. <? include "connect.php"; if(isset($_POST['submit'])) { $filename=$_POST['filename']; $handle = fopen("$filename", "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $date = date("Y-m-d"); $import="INSERT into table (order_id, order_status, order_date, todays_date) values ('$data[0]', '$data[1]', '$data[2]', '$date')"; mysql_query($import) or die(mysql_error()); } fclose($handle); print "Import done"; } else { print "<form action='importnew.php' method='post'>"; print "Type file name to import:<br>"; print "<input type='text' name='filename' size='20'><br>"; print "<input type='submit' name='submit' value='submit'></form>"; } ?> Similar TutorialsI have tried a large number of "solutions" to this but everytime I use them I see 0000-00-00 in my date field instead of the date even though I echoed and can see that the date looks correct. Here's where I'm at: I have a drop down for the month (1-12) and date fields (1-31) as well as a text input field for the year. Using the POST array, I have combined them into the xxxx-xx-xx format that I am using in my field as a date field in mysql. <code> $date_value =$_POST['year'].'-'.$_POST['month'].'-'.$_POST['day']; echo $date_value; </code> This outputs 2012-5-7 in my test echo but 0000-00-00 in the database. I have tried unsuccessfully to use in a numberof suggested versions of: strtotime() mktime Any help would be extremely appreciated. I am aware that I need to validate this data and insure that it is a valid date. That I'm okay with. I would like some help on getting it into the database. I need to change the date format to DD-MM-YYYY. Please help. $SomeVar = $_POST['finco']; $query = "SELECT * FROM news_home WHERE code = '".$SomeVar."' ORDER BY timestamp DESC"; $results = mysql_query($query); $returnS=""; while($line = mysql_fetch_array($results)) { $returnS.= $line["subject"].",,".$line["timestamp"].",,".$line["News"].",,".$line["links"].",,,"; } echo $returnS; mysql_close($link); ?> Hi i've been trying to change the format of the date which comes from the database row [ news-date ] this what i tried but di not work help please $query = "SELECT * FROM news, date_format [news_date](date,'%M %d, %Y') AS news_date "; $result = mysql_query($query); // see if any rows were returned if (mysql_num_rows($result) > 0) { // yes so print them one after another echo "<table cellpadding=10 border=1>"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>".$row["news_id"]."</td>"; echo "<td>".$row["subject"]."</td>"; echo "<td>" .$row['news_date']. "</td>"; echo "<td>".$row["news_artical"]."</td>"; echo "<td><a href=delete2.php?id=".$row["id"].">Delete</a></td>"; echo "<td><a href=modifyform.php?id=".$row["id"].">Modify</a></td>"; echo "<td><a href=add.php?id=".$row["id"].">Add Row</a></td>"; echo "</tr>"; } //End While echo "</table>"; } //End if rows echo "<p> <strong>{news.SUBJECT} </strong></p>"; ?> I pass a variable (date) which contains the date in format of 03-02-2011 (m-d-Y) how could i quickly change that date format to show 02-MAR-2011 (d-M-Y) Is there a function that can quick do this? Hi there, I have a string '12/04/1990', that's in the format dd/mm/yyyy. I'm attempting to convert that string to a Date, and then insert that date into a MySQL DATE field. The problem is, every time I try to do so, I keep getting values like this in the database: 1970-01-01. Any ideas? Much appreciated. i need a javascript date picker that will put a date in a text flied in the format of Y-m-d so that it can be posted to mysql also i need some kind of php to validite this Hi, I'm trying to insert this $inceptiondate = date("Y-m-d 12:00:00"); to the database, the Y-m-d does make it to database, but the 12:00pm doesn't. Whenever it reaches the database, it becomes 00:00:00 Where did I got wrong? Thanks Hi all - I am parsing info from mysql into a php table but the date being returned is in YYYY-MM-DD format and I want it DD-MM-YYYY. How would I do this? Code at the moment is: Code: [Select] $link = connect(); function get_user_posts(){ $getPosts = mysql_query('SELECT a.id as id,a.post as question, a.answer as answer, a.created as created, a.updated as updated, b.vclogin as admin FROM user_posts as a, admin as b WHERE a.admin_id=b.adminid',connect()); return $getPosts; } Code: [Select] <table class="list"> <tr> <th><?php echo getlocal("upost.post") ?></th> <th><?php echo getlocal("upost.answer") ?></th> <th><?php echo getlocal("upost.admin") ?></th> <th><?php echo getlocal("upost.created") ?></th> <th><?php echo getlocal("upost.updated") ?></th> <th></th> <th></th> </tr> <?php $all_questions = get_user_posts(); while ($question = mysql_fetch_assoc($all_posts)): ?> <tr> <td><?php echo $post['post']?></td> <td><?php echo $post['answer']?></td> <td><?php echo $post['admin']?></td> <td><?php echo $post['created']?></td> <td><?php echo $post['updated']?></td> <td><a href="javascript:user_post('<?php echo $post['id']?>')">edit</a></td> <td><a href="javascript:delete_post('<?php echo $post['id']?>')">delete</a></td> </tr> THANKS! I am a developer in PHP but have run into an obstacle and was hoping for some insight. I am attempting to do something a little complicated I fear but perhaps there is a solution. A client of mine is offering CSV templates for customers to use to BATCH UPLOAD item's to their account. This is self explanatory in the fact that I know how to import a .CSV with PHP and I know how to properly insert it into the database. My question is this: What if we wanted to offer a way for the customer to insert an image into the .CSV template. By the time the .csv reached my PHP code would the image still be in a format where it could be uploaded to a directory on the server? I have not found anything on the web where anyone has tried this. Again, the customer will insert an image from their hard drive into a row within the .CSV template. My code will parse the .CSV row data and if the image is still in tact...will then upload the image to a director with more code. Thanks for any help. Joe Hello Everyone I am working on a website for my favorite hobby. I have created the dynamic pages and they are ready to go. I am having trouble however with the dates. I want all the dates to show up as MM-DD-YYYY. Not sure how to do that with a while loop. I am also trying to figure out how to make the date say "Lost Info" if the date is showing the default.(0000-00-00) Here is my code so far Code: [Select] <?php include('../../includes/config.php'); $sql = 'SELECT id, l_name, f_name, date_sent, date_return, item_return FROM `ttmautos` WHERE `l_name` LIKE \'a%\' AND `category` LIKE \'baseball\' ORDER BY `date_return` DESC'; $link_result = mysql_query($sql, $connection) or die(mysql_error()); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <link rel="stylesheet" type="text/css" href="../../css/reset.css"/> <link rel="stylesheet" type="text/css" href="../../css/top_nav.css"> <link rel="stylesheet" type="text/css" href="../../css/main_layout.css"/> <script src="../../scripts/jquery-1.4.4.js" type="text/javascript"></script> <script src="../../scripts/top_nav.js" type="text/javascript"></script> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Baseball Autographs A</title> </head> <body> <div id="wrapper"> <?php include('../../includes/page/header.php'); ?> <?php include('../../includes/page/top_nav.php'); ?> <?php include('../../includes/page/left_sidebar_autopages.php'); ?> <?php include('../../includes/page/right_sidebar.php'); ?> <div id="content"> <ul class="list_heading"> <li class="title">player name</li> <li>date sent</li> <li>date recievied</li> <li class="return">item recievied</li> </ul> <ul class="list"> <?php while ($link=mysql_fetch_array($link_result)){ echo "<li>$link[f_name] $link[l_name]</li> <li>$link[date_sent]</li> <li>$link[date_return]</li> <li class=\"return\"><a href=\"/auto_pages/baseball/baseball-autographs.php?l_name=$link[l_name]&f_name=$link[f_name]\">$link[item_return]</a></li>"; } if($link[date_sent]=="0000-00-00") { echo "Lost Info"; } if (!($link = mysql_fetch_array($link_result))) { return "Currently our database has no autographs listed for here"; } ?> </ul> </div> <!--END content div--> </div> <!--END wrapper div--> <?php include('../../includes/page/footer.php'); ?> </body> </html> Hello everyone, I am not sure if I am googling this wrong or what. I have several dates in a mysql database that I would like to display in a dynamically made page. Can someone help me or point me in the right direction. I am very unfamiliar with the MySql date function. I would also like to calculate the days that have passed between two different dates. Example would be: 11-27-2011 and 12-15-2011 = # of days I have mysql exported in the following sql format: Code: [Select] - CREATE TABLE IF NOT EXISTS `pma_history` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '', `db` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '', `table` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '', `timevalue` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `sqlquery` text COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`), KEY `username` (`username`,`db`,`table`,`timevalue`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='SQL history for phpMyAdmin' AUTO_INCREMENT=3761 ; -- -- Dumping data for table `pma_history` -- INSERT INTO `pma_history` (`id`, `username`, `db`, `table`, `timevalue`, `sqlquery`) VALUES (2, 'root', 'information_schema', 'FILES', '2010-03-04 19:26:33', 'SELECT * FROM `FILES`') Just as an example anyway. What I need to be able to do, is import this using PHP. Does anyone know of a suitable class for doing this? I could make my own, but I cant quite work out the best way to seperate the queries out? Thanks I have a table I am trying to import to mySQL I am having trouble assigning the right variable which represents a column header to the correct column header in the mySQL table. <?php /********************************/ /* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/ /* Edit the entries below to reflect the appropriate values /********************************/ $databasehost = "xxx"; $databasename = "xxx"; $databasetable = "names"; $databaseusername ="xxx"; $databasepassword = "xxx"; $fieldseparator = "\t"; $lineseparator = "\n"; $csvfile = "AddEnt2.txt"; $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error()); @mysql_select_db($databasename) or die(mysql_error()); $fcontents = file ('AddEnt2.txt'); # expects the csv file to be in the same dir as this script for($i=0; $i<sizeof($fcontents); $i++) { $line = trim($fcontents[$i],'\t'); // '\t' for tab delimeted $arr = explode('\t', $line); // '\t' for tab delimeted $name=implode("','",$arr); $PA=implode("','",$line); # if your data is comma separated # instead of tab separated # change the '\t' above to ',' $sql = "insert into $databasetable values ('$PA','$name')"; $sql = str_replace("''", mysql_escape_string("NULL"), $sql); $sql = str_replace("' '", mysql_escape_string("NULL"), $sql); mysql_query($sql); echo $sql ."<br>\n"; if(mysql_error()) { echo mysql_error() ."<br><b><font color=red>\n</b>"; } } ?> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=358188.0 Hello I am trying to do a simple CSV import of a file and then upload to mysql. I am basing my code on an example I have found and I am getting the error message 'invalid file'. The example used includes a text box for the file import field and I am referencing the file from my computer in that box 'Users/James/name.csv' My code is as follows: HTML <form action="results.php?data=upload" method='post'> Import File : <input type='text' name='sel_file' size='20'> <input type='submit' name='submit' value='submit'> </form> PHP if ($b == 'upload') { $fname = $_FILES['sel_file']['name']; $chk_ext = explode(".",$fname); if(strtolower($chk_ext[1]) == "csv") { $filename = $_FILES['sel_file']['tmp_name']; $handle = fopen($filename, "r"); while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) { $sql = "INSERT into ".$db['name']."(field1,field2,field3,field4) values('$data[0]','$data[1]','$data[2]', '$data[3]')"; mysql_query($sql) or die(mysql_error()); } fclose($handle); echo "Successfully Imported"; } else { echo "Invalid File"; } } my file format is: 1,23,1,5 1,24,2,5 1,25,3,5 1,26,4,5 1,27,5,5 1,28,6,5 1,29,7,5 1,30,8,5 1,31,9,5 1,32,10,5 If anybody can help that would be good - or perhaps point out if I am going about this the wrong way - cheers I have a problem with my project. i want to import Excel sheet directly into mysql database for PHP language without any conversion of Excel sheet into another format. i am very fed about this from last two weeks. So Please help me How I Do it. any expert please help me Hey guys, I'm having a nightmare at the moment.
I am trying to import a CSV file into a MySQL table, some of the data is interested properly into the rows however some of the CSV row do not get imported.
Here is my PHP script:
<?php
$databasehost = "localhost";
$databasename = "name";
$databasetable = "import";
$databaseusername="username";
$databasepassword = "password";
$fieldseparator = ",";
$lineseparator = "\n";
$enclosedbyquote = '"';
$csvfile = "db-core/feed/csv/test.csv";
if(!file_exists($csvfile)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename",
$databaseusername, $databasepassword,
array(
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
} catch (PDOException $e) {
die("database connection failed: ".$e->getMessage());
}
$pdo->exec("TRUNCATE TABLE `$databasetable`");
$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable`
FIELDS OPTIONALLY ENCLOSED BY ".$pdo->quote($enclosedbyquote)."
TERMINATED BY ".$pdo->quote($fieldseparator)."
LINES TERMINATED BY ".$pdo->quote($lineseparator)."
IGNORE 1 LINES");
echo "Loaded a total of $affectedRows records from this csv file.\n";
?>
As you can see the script is importing the CSV into the table however only 40 of the rows are added to the SQL table when there are actually 80 rows in the CSV. I am also telling the script to IGNORE 1 LINES if that has anything to do with the issue.
Here is one of the rows from the CSV file:
"AETA83919","AETV34458584","SD57VUP","BLACK","Diesel","2007","76778","Hatchback","3","VAUXHALL","CORSA","1.3 CDTi [90] SXi 3dr [AC]","1200","3989","","Manual","http://images.autoexposure.co.uk/AETA83919/AETV34458584_1b.jpg","0","3","","0","2007 57 reg VAUXHALL CORSA 1.3 CDTi [90] SXi 3dr [AC], Manual, Hatchback, Diesel, 76,778 miles. BLACK, Comprehensive Warranty Included In Price. HPI Checked! Pre Delivery Health Check!, 5 seats, LOVELY VAUXHALL CORSA SXI 1.3CDTI SXI WITH AIR CONDITIONING!! VERY WELL CARED FOR AND MAINTAINED AND JUST SERVICED BY DRIVEN CAR SALES LTD AT 76K!! GREAT COMBINATION OF PERFORMANCE AND ECONOMY!! VIEWING AND TEST DRIVING IS RECOMMENDED!! EACH CAR COMES WITH A MININIUM 9 MONTHS MOT. FULL MECHANICAL HEALTH CHECK GUARANTEES CAR IS PREPARED TO THE HIGHEST STANDARD. COMPREHENSIVE RAC WARRANTY INCLUDED. DRIVEN CAR SALES WELCOME ANY INSPECTION. BUY WITH CONFIDENCE AND REASSURANCE AS DRIVEN CAR SALES LTD ARE OFFICIAL MEMBERS OF RETAIL MOTOR INDUSTRY FEDERATION. VIEW OUR REVIEWS ON TRUSTPILOT. 3,989","","0","1","","UK","0","","0","0","","","",""As far as I can see everything looks fine with the row, all of the rows in the CSV end like this. One thing I have noticed is it looks like the rows that are missing are in fact being placed in the last column of every other row here is an example: So as you can see it looks like one row is being inserted into the last column of some rows and I am so confused to why this might be happening? Any ideas? i was working on a mini project that imports csv file to the database through ajax and it's working fine
<?php
if(!empty($_FILES["marks_file"]["name"]))
{
$connect = mysqli_connect("localhost", "root", "", "dbname");
$output = '';
$allowed_ext = array("csv");
$extension = end(explode(".", $_FILES["marks_file"]["name"]));
if(in_array($extension, $allowed_ext))
{
$file_data = fopen($_FILES["marks_file"]["tmp_name"], 'r');
fgetcsv($file_data);
while($row = fgetcsv($file_data))
{
$name = mysqli_real_escape_string($connect, $row[0]);
$Physics = mysqli_real_escape_string($connect, $row[1]);
$Maths = mysqli_real_escape_string($connect, $row[2]);
$Chemistry = mysqli_real_escape_string($connect, $row[3]);
$Biology = mysqli_real_escape_string($connect, $row[4]);
$SST = mysqli_real_escape_string($connect, $row[5]);
$query = "
INSERT INTO csv
(name, Physics, Maths, Chemistry, Biology, SST)
VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST')
";
mysqli_query($connect, $query);
}
$select = "SELECT * FROM csv ORDER BY id DESC";
$result = mysqli_query($connect, $select);
$output .= '
<table class="table table-bordered">
<tr>
<th width="25%" >name</th>
<th width="15%" >Physics</th>
<th width="15%" >Maths</th>
<th width="15%" >Chemistry</th>
<th width="15%" >Biology</th>
<th width="15%" >SST</th>
</tr>
';
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td>'.$row["name"].'</td>
<td>'.$row["Physics"].'</td>
<td>'.$row["Maths"].'</td>
<td>'.$row["Chemistry"].'</td>
<td>'.$row["Biology"].'</td>
<td>'.$row["SST"].'</td>
</tr>
';
}
$output .= '</table>';
echo $output;
}
else
{
echo 'errorx';
}
}
else
{
echo "errory";
}
?>
however the imported csv files inserts null values in the tables because the format of all csv files assigned to me are in the exact same format: ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,,,,,, ,,,Fields,Physics~75,Maths~50,Chemistry~65,Bio~85,SST~100 ,,,Name1,10,25,35,42,62 ,,,Name2,80,45,45,45,25 ,,,Name3,63,25,63,36,36 ,,,Name4,82,36,75,48,42 ,,,Name5,45,45,78,25,24 ,,,Name6,36,36,15,75,36 ,,,Name7,99,45,24,24,45 ,,,Name8,45,85,85,85,96 i changed my code a bit modified the functions to espace blank spaces and not return null values in the data tables
while (($row = fgetcsv($file_data, 1000, ",")) !== FALSE)
{
if ((string) $row[0] != '0' and empty($row[0]))
{
continue;
}
$name = mysqli_real_escape_string($connect, $row[0]);
$Physics = mysqli_real_escape_string($connect, $row[1]);
$Maths = mysqli_real_escape_string($connect, $row[2]);
$Chemistry = mysqli_real_escape_string($connect, $row[3]);
$Biology = mysqli_real_escape_string($connect, $row[4]);
$SST = mysqli_real_escape_string($connect, $row[5]);
$query = "
INSERT INTO csv
(name, Physics, Maths, Chemistry, Biology, SST)
VALUES ('$name', '$Physics', '$Maths', '$Chemistry', '$Biology' , '$SST')
";
mysqli_query($connect, $query);
}
But it doesn't work with the specified csv files i think the ajax call breaks and that's why nothing happens.However it works fine with csv files without blank spaces Edited March 19, 2019 by zetastreak <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Import a CSV File with PHP & MySQL</title> </head> <body> <form action="" method="post" enctype="multipart/form-data" name="csv" id="csv"> Choose your file: <br /> <input name="csv" type="file" id="csv" /> <input type="submit" name="Submit" value="Submit" /> </form> </body> Hi! I hope somebody can help me what im do wrong. i have checked that the data from the file is in $source_file but nothing imports to the database Code: [Select] <?php include('config.php'); include('opendb.php'); if(isset($_POST['upload'])) { $source_file = @$_POST['userfile']; //$source_file = fopen('http://localhost/test/upload/test.csv', 'r'); $target_table = 'foretag'; function csv_file_to_mysql_table($source_file, $target_table, $max_line_length=10000) { if (($handle = fopen("$source_file", "r")) !== FALSE) { $columns = fgetcsv($handle, $max_line_length, ","); foreach ($columns as $column) { $column = str_replace(".","",$column); } $insert_query_prefix = "INSERT INTO $target_table (".join(",",$columns).")\nVALUES"; while (($data = fgetcsv($handle, $max_line_length, ";")) !== FALSE) { while (count($data)<count($columns)) array_push($data, NULL); $query = "$insert_query_prefix (".join(",",quote_all_array($data)).");"; mysql_query($query); } fclose($handle); } } function quote_all_array($values) { foreach ($values as $key=>$value) if (is_array($value)) $values[$key] = quote_all_array($value); else $values[$key] = quote_all($value); return $values; } function quote_all($value) { if (is_null($value)) return "NULL"; $value = "'" . mysql_real_escape_string($value) . "'"; return $value; } } include('closedb.php'); echo "<br>done<br>"; ?> |
|||||||