I have a data file with 9 columns which are separated by commas.
After I select this data file for upload to the Web server, I am
having trouble getting the data file (which has 9 columns) into a
table that has 10 columns.
The last field in the table that I would like to add to this bit of
code (see below) is a $date field in order to have the field to sort
on. The reason I need to have this extra date field is because the
date format in the data file (which cannot be changed) looks like
01102006 (1 October 2006) instead of 10012006 (October 1 2006). The
problem with this is when I try to sort the dates in ASCENDING
ORDER, the dates are not grouped by the "month" portion of the date.
Rather they are grouped by the "day" portion of the date. This
causes the months to be out of sequence when they are displayed on
the Web page. I am having to use the following query to accommodate
the upload of the data file. Are there any suggestions? I have
provided my full code below. Thanks in advance!!! Feel free to use
the code!!! :-)
$sql = "insert into ctkdata values ('". implode("','", $arr) ."')";
// BEGIN PHP CODE
<?php
session_start();
if (@$_SESSION['userauth'] != "yes")
{
header("Location: login.php");
exit();
}
include("dbconnect.php");
$connection = mysql_connect($host,$user,$password)
or die ("Couldn't connect to server");
$db = mysql_select_db($database,$connection)
or die ("Couldn't select database");
error_reporting(0);
$dateposted = date("n/j/Y");
$today = date("dmY");
$importdate = $_POST['day'].$_POST['month'].$_POST['year'];
$target_path = "data/";
$target_path = $target_path . basename($_FILES
['uploadedfile']['name']);
$_FILES['uploadedfile']['tmp_name'];
$target_path = "data/";
$target_path = $target_path . basename($_FILES
['uploadedfile']['name']);
if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'],
$target_path))
{
$query = "SELECT datefrom FROM ctkdata";
$result = mysql_query($query) or die ("Could Not
Select Data!");
$nrows = mysql_num_rows($result);
while ($row = mysql_fetch_array($result))
{
extract($row);
}
mysql_query("CREATE TABLE `tbl_temp` (
`machnum` VARCHAR( 7 ) NOT NULL ,
`datefrom` VARCHAR( 10 ) NOT NULL ,
`dateto` VARCHAR( 10 ) NOT NULL ,
`studentno` VARCHAR( 7 ) NOT NULL ,
`lastname` VARCHAR( 50 ) NOT NULL ,
`firstname` VARCHAR( 50 ) NOT NULL ,
`description` VARCHAR( 150 ) NOT NULL ,
`cost` decimal( 9,2 ) NOT NULL ,
`qty` CHAR( 3 ) NOT NULL ,
INDEX ( `studentno` )
) TYPE = MYISAM ;");
$myfilequery = "SELECT filepath FROM
ctkinfo";
$myfileresult = mysql_query($myfilequery) or
die ("Could Not Select File Path!");
$myfilenrows = mysql_num_rows($myfileresult);
while ($myfilerow = mysql_fetch_array
($myfileresult))
{
extract($myfilerow);
}
$myfilecontents = file($filepath);
for($x=0; $x<sizeof($myfilecontents); $x++)
{
$templine = trim($myfilecontents
[$x]);
$tempformat = ereg_replace("\"", "",
$templine);
$temparr = explode(",",
$tempformat);
$tempsql = "insert into tbl_temp
values ('". implode("','", $temparr) ."')";
mysql_query($tempsql);
if(mysql_error())
{
echo mysql_error() ."<br>\n";
}
$datequery = "SELECT * FROM
tbl_temp";
$dateresult = mysql_query
($datequery) or die ("Could Not Select Dates To Validate!");
$datenrows = mysql_num_rows
($dateresult);
while ($daterow =
mysql_fetch_array($dateresult))
{
extract($daterow);
}
$dmY2 = $importdate;
$d2 = substr($dmY2, 0,2);
$m2 = substr($dmY2,2,2);
$y2 = substr($dmY2,4,4);
$date_fmt2 =
$m2 ."/".$d2."/".$y2;
$dmY1 = $datefrom;
$d1 = substr($dmY1, 0,2);
$m1 = substr($dmY1,2,2);
$y1 = substr($dmY1,4,4);
$date_fmt1 =
$m1 ."/".$d1."/".$y1;
if ($datefrom != $importdate)
{
echo "<font face=\"Arial\"
color=\"#CC0000\" size=\"4\">Error!</font> <font face=\"Arial\"
color=\"#000000\" size=\"4\">The date you selected does not match
the date in the export file!</font><br><br>";
echo "<font face=\"Arial\"
color=\"#000000\" size=\"4\"><b>The Date That You Selected To Upload
The Export File For Is:</b> " . ltrim(date
($date_fmt2),"0") ."</font><br>\n";
echo "<font face=\"Arial\"
color=\"#000000\" size=\"4\"><b>The Date In The Export File
Is:</b> " . ltrim(date($date_fmt1),"0") ."</font><br><br>\n";
echo "<font face=\"Arial\"
color=\"#000000\" size=\"4\">Please <a href=\"upload.php\">click
here </a>to try again...</font>\n";
mysql_query("DROP TABLE
`tbl_temp`");
exit();
}
}
if ($datefrom = $importdate && $importdate !
= $today)
{
$query2 = "DELETE FROM ctkdata WHERE
datefrom = '$importdate'";
$result2 = mysql_query($query2) or die
("Could Not Delete Data!");
}
else if ($datefrom = $importdate &&
$importdate = $today)
{
$query3 = "DELETE FROM ctkdata WHERE
datefrom = '$importdate'";
$result3 = mysql_query($query3) or die
("Could Not Delete Data!");
}
$filequery = "SELECT filepath FROM ctkinfo";
$fileresult = mysql_query($filequery) or die
("Could Not Select File Path!");
$filenrows = mysql_num_rows($fileresult);
while ($filerow = mysql_fetch_array
($fileresult))
{
extract($filerow);
}
$fcontents = file($filepath);
for($i=0; $i<sizeof($fcontents);
$i++)
{
$line = trim($fcontents[$i]);
$format = ereg_replace("\"", "",
$line);
$arr = explode(",", $format);
$sql = "insert into ctkdata values
('". implode("','", $arr) ."')";
mysql_query($sql);
if(mysql_error())
{
echo mysql_error() ."<br>\n";
}
mysql_query("UPDATE dataupdate SET
dateposted = '$dateposted' WHERE recno = '1'");
mysql_query("UPDATE ctkdata SET qty
= '-' WHERE description = 'DEPOSIT'");
echo "<script
language='javascript'>";
echo "location.href='data_import_success.php'";
echo "</script>";
mysql_query("DROP TABLE `tbl_temp`");
}
}
?>
// END PHP CODE
The php_mysql group is dedicated to learn more about the PHP/MySQL web database
possibilities through group learning.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/php_mysql/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/php_mysql/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
