I fixed my own problem. Here's what I changed and it worked...
mysql_query("CREATE TABLE `tbl_temp` (
`recno` int(11) NOT NULL auto_increment,
`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 default '0.00',
`qty` char(3) NOT NULL default '-',
`transdate` varchar(10) NOT NULL ,
PRIMARY KEY (`recno`),
KEY `studentno` (`studentno`)
) ENGINE=MyISAM ;");
$tempsql = "insert into tbl_temp values ('','". implode("','",
$temparr) ."','$date_fmt2')";
$sql = "insert into ctkdata values ('','". implode("','",
$arr) ."','$date_fmt2')";
Thanks!
--- In [email protected], "William Watson" <[EMAIL PROTECTED]>
wrote:
>
> 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/
