jakebur01

For some reason the deletes below are not working right. I still have part of the header in the table and I still have rows where the list price = 0. I cannot figure out what I am doing wrong. Are those incorrect queries? I do not have an identifier for the row when deleting the header... so I was trying to get it to just delete the first row in the table if the file has a header. if($header==YES) { mysql_query("DELETE FROM {$tablename} LIMIT 1"); echo"Removing header....<br /><br />"; } mysql_query("DELETE FROM {$tablename} WHERE $list='0'");

You are right.. I moved the fopen out of the loop. It wrote the 100,000 or so rows. It took a while. I also need to write to the file 4 more times. Where the item does not have dash, where item has "tb" concatenated to it, tb with dash, tb without dash. Should I do this on 4 different pages or do you think it will handle it all on this one page?

I wrote this page. It is it hacking out the calculations and writing it to a txt file on a unix server. It works perfect if I limit the selection to only a few rows. But, I need it to loop through 100,000 rows. It gives me a cgi or server not responding error even when I LIMIT 1000 rows. What can I do to get my code below to process 100,000 rows? Also, should ($fp= fopen("ftp://user:pass@myip/u/ssc/price/$filename", "a") be inside or outside the while loop? $filename=$_POST['filename']; $path=$_POST['path']; $tablename=$_POST['tablename']; $header=$_POST['header']; $list=$_POST['list']; $dealer=$_POST['dealer']; $distributor=$_POST['distributor']; $central=$_POST['central']; $part=$_POST['part']; ini_set('max_execution_time', '999'); $db = mysql_connect('localhost', 'u', 'p') or die(mysql_error()); mysql_select_db('d') or die(mysql_error()); if($header==YES) { mysql_query("DELETE FROM {$tablename} LIMIT 1"); echo"Removing header....<br /><br />"; } mysql_query("DELETE FROM {$tablename} WHERE $list='0'"); echo"Removing rows where the retail price is zero.<br /><br />Writing File... Please wait.."; $result = mysql_query("SELECT * FROM {$tablename}"); while ($row = mysql_fetch_assoc($result)) { $code="1038"; $partnumber=$row["$part"]; $targetChars=array('"', '$', ','); $partnumber=str_replace($targetChars, "", $partnumber); $tbp="TBP"; $stdpack="1"; $zero="0"; $listprice=$row["$list"]; $listprice=str_replace($targetChars, "", $listprice); $listprice = number_format($listprice, 2, '.', ''); $cost=$row["$central"]; $cost=str_replace($targetChars, "", $cost); $cost = number_format($cost, 2, '.', ''); $cost2=$row["$central"]; $cost2=str_replace($targetChars, "", $cost2); $dealerprice=$row["$dealer"]; $dealerprice=str_replace($targetChars, "", $dealerprice); $dealerprice = number_format($dealerprice, 2, '.', ''); $division=".95"; $distributorprice=$cost2/$division; $distributorprice = number_format($distributorprice, 2, '.', ''); //$row["$distributor"]; $outputstring = $code. "\t".$partnumber."\t".$tbp."\t".$tbp."\t".$stdpack."\t".$zero."\t".$zero."\t".$listprice."\t".$listprice."\t".$cost."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$dealerprice."\t".$distributorprice."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\t".$zero."\n"; //print $outputstring;echo"<br />"; $fp= fopen("ftp://user:pass@myip/u/ssc/price/$filename", "a"); fwrite($fp, $outputstring, strlen($outputstring)); }

altered table to myisam. This fixed everything. Thanks, Jake

I tried making the fields null and also tried giving them a default value in mysql. No luck yet..

Is there any way to make a default value for empty fields or to get mysql to over look this? The file has been uploaded as MTD.22610.txt Row 17 doesn't contain data for all columns code: $query = "load data infile \"$source_file\" INTO TABLE $table_name FIELDS TERMINATED BY ','";

I think I found the problem... Those extra rows were generated because of the rtf format of MAC. I uploaded other txt files and they did not have all that.

What if I select it and pass it to the next page to assure accuracy? ?><form name="row_select" method="post" action="upload3.php"><select name="education"><?php while (list ($key, $val) = each ($line)) { ?> <option value="<?php echo"$key";?>"><?php echo"$val";?></option> <?php //echo "$key -> $val <br>"; } ?></select><br /><input name="row_select" type="submit" value="submit" /></form><?php How do I display only the first 15 rows in the array? Also, on the following page is there a way to loop through and delete lines 0 through 6 of the text file? This way when we do data load infile.... Only the values will be inserted and not all of those other crazy lines.

It looks like the first row is actually not the column row. It has five or so rows of other info before it even starts the column row. This must be why it was only doing one iteration. The file has been uploaded as MTD.12289.txt0 -> {\rtf1\ansi\ansicpg1252\cocoartf949\cocoasubrtf430 1 -> {\fonttbl\f0\fswiss\fcharset0 Helvetica;} 2 -> {\colortbl;\red255\green255\blue255;} 3 -> \margl1440\margr1440\vieww9000\viewh8400\viewkind0 4 -> \pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\ql\qnatural\pardirnatural 5 -> 6 -> \f0\fs24 \cf0 "Part Number","Part Description","Disc Code","List Price","Dealer Price","Distributor Price","Central Price","Sub Part Number","IH Format","No Return Code","Stocking Code","Part Code","Rev Code","Restriction Code","Weight","Vol","Length","Width","Height"\ 7 -> "00002406","FITTING-NIPPLE","C",$2.83,$1.98,$1.77,$1.42,,,"P","E",,,"N",1.50,1.50,2.00,1.50,0.50\ 8 -> "00002528","SCREW-HEX CAP 3/8","B",$1.79,$1.08,$0.90,$0.72,,,"P","E",,,"N",0.07,0.82,0.60,2.30,0.60\ 9 -> "00002647","SPOON-AERATOR CLOS","C",$17.30,$12.11,$10.81,$8.65,,,"P","F",,,"N",0.55,17.55,1.30,9.00,1.50\ $line = explode("\n", $content); $cols = explode($del, $line[0]); //$a= array("Three", "two", "Four", "five","ten"); while (list ($key, $val) = each ($line)) { echo "$key -> $val <br>"; } If I change $cols = explode($del, $line[0]) to $line[6] and do while (list ($key, $val) = each ($cols)) { echo "$key -> $val <br>"; } it outputs this: 0 -> \f0\fs24 \cf0 "Part Number" 1 -> "Part Description" 2 -> "Disc Code" 3 -> "List Price" 4 -> "Dealer Price" 5 -> "Distributor Price" 6 -> "Central Price" 7 -> "Sub Part Number" 8 -> "IH Format" 9 -> "No Return Code" 10 -> "Stocking Code" 11 -> "Part Code" 12 -> "Rev Code" 13 -> "Restriction Code" 14 -> "Weight" 15 -> "Vol" 16 -> "Length" 17 -> "Width" 18 -> "Height"\ But... How can I guarantee that the columns will start on line 6 everytime? Will other text files be different? How could I get it to read the column in the file first?

Is this not the correct way to store the columns of the text file? $content = file_get_contents($target); $del = ","; $line = explode("\n", $content); $cols = explode($del, $line[0]); I guess I should also add some preg_replace in there to remove the quotations and dollar signs.

Not sure.. Here is the text file. I thought I was putting the text file columns into an array then looping through each one creating columns for my new table. col1, col2, col3, ...... "Part Number","Part Description","Disc Code","List Price","Dealer Price","Distributor Price","Central Price","Sub Part Number","IH Format","No Return Code","Stocking Code","Part Code","Rev Code","Restriction Code","Weight","Vol","Length","Width","Height" "00005739","FITTING-EDGE","C",$2.83,$1.98,$1.77,$1.42,,,"P","E",,,"N",1.50,1.50,2.00,1.50,0.50

Am I not counting my columns in the text file correctly? $table_name = "smith".rand(100,999).rand(100,999); //something like "temptable182765" ////////// count columns and create table here $content = file_get_contents($target); //Parse the columns by a specified deliminator $del = ","; //could be " " or ";" or anything else you want //$content = "Hi, Hello, Dog, Cat"; //for testing $line = explode("\n", $content); $cols = explode($del, $line[0]); $sql = "CREATE TABLE `".$table_name."` ("; $i=1; foreach($cols as $col){ $sql .= "col".$i++." varchar(256),"; } $sql = rtrim($sql, ",");//Get rid of that last "," in the statment $sql .= ");";

That fixed the error. But, it is only creating one column. It is supposed to be counting the columns in the text file and creating that number of columns in the mysql table. Ex, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19 The txt file I uploaded had 19 columns. Here is the query it spitted out: CREATE TABLE `smith227291` (col1 varchar(256));

Because each text file I upload will have a different number of columns. And I am only creating the table to identify the columns I need to work with and write a new text file. I will delete the table after the new files are written.' __ Brian W. helped me earlier here: http://www.phpfreaks.com/forums/index.php/topic,233183.msg1082398.html#msg1082398

I have been struggling with this for hours and I cannot get it figured out. I am getting this error. The file has been uploaded as MTD.7926.txtYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ADD (col1 varchar(256))' at line 1 here is my code if(move_uploaded_file($_FILES['uploaded']['tmp_name'], $target)) { echo "The file has been uploaded as ".$ran2.$ext; $table_name = "smith".rand(100,999).rand(100,999); //something like "temptable182765" ////////// count columns and create table here $content = file_get_contents($target); //Parse the columns by a specified deliminator $del = ","; //could be " " or ";" or anything else you want //$content = "Hi, Hello, Dog, Cat"; //for testing $line = explode("\n", $content); $cols = explode($del, $line[0]); $sql = "CREATE TABLE `".$table_name."` ADD ("; $i=1; foreach($cols as $col){ $sql .= "col".$i++." varchar(256),"; } $sql = rtrim($sql, ",");//Get rid of that last "," in the statment $sql .= ");"; //Go ahead and execute however you want. Proble "mysql_query($sql) or die(mysql_error()."<br>".$sql);" $_SESSION['table_name'] = $table_name; //Setting the table name into a session variable $db = mysql_connect('localhost', 'xxx', 'xxx') or die(mysql_error()); mysql_select_db('xxxx') or die(mysql_error()); $result = mysql_query("$sql ", $db) or die(mysql_error()); }

It was the disc quota settings under disc management.

I don't remember the message. We deleted the folder out as quick as we could. The drive has several hundred gigs of space. I have had trouble ftping under my user account though lately. I have about 20 sites hosted with my friend. I have had trouble with dreamweaver. And when I researched it.... it was about disk quota??? I think.... So I have been deleting unused files on old websites in order to put new files. How do we change the number of files you can put in a folder or for a user? That is a great point!

Is there anyway to expand or increase the size of C:\WINDOWS\TEMP\ ? I uploaded a couple of 5mb files and it locked up everything using that folder on the server. It said that the temp file is overloaded or full. Anything in .ini ? or on the server I could do? THanks, Jake

Any ideas? I need for the user to ftp from their local computer to the server via ftp using php on the server. I want to bypass the temp directory. My webhost called and said that his temp folder got overloaded.

does it not have to upload to a temporary directory on the server before ftping?

I built a php upload script. The 5mb text file was taking forever to upload. I have other text files that are around 8mb. It works well with smaller files. It just takes to long with big files. Is it possible to ftp a file off of my local machine onto the server using php?

THank you soo much.... This will really get me started.

page 1 <form enctype="multipart/form-data" action="upload2.php" method="POST"> Please choose a file: <input name="uploaded" type="file" /><br /> <input type="submit" value="Upload" /> </form> page 2 session_start(); //This function separates the extension from the rest of the file name and returns it if ($uploaded_type =="text/xls") { echo "No excel files<br>"; $ok=0; } elseif ($uploaded_type =="text/doc") { echo "No word files<br>"; $ok=0; } if ($ok==0) { Echo "Sorry your file was not uploaded"; } //If everything is ok we try to upload it else { //This applies the function to our file $ext = findexts ($_FILES['uploaded']['name']) ; $ext="txt"; //This line assigns a random number to a variable. You could also use a timestamp here if you prefer. $ran = rand () ; $ran1 = MTD.".".$ran; //This takes the random number (or timestamp) you generated and adds a . on the end, so it is ready of the file extension to be appended. $ran2 = $ran1."."; //This assigns the subdirectory you want to save into... make sure it exists! $target = "upload/"; $filename=$ran2.$ext; $_SESSION[filename]=$ran2.$ext; //This combines the directory, the random file name, and the extension $target = $target . $ran2.$ext; if(move_uploaded_file($_FILES['uploaded']['tmp_name'], $target)) { echo "The file has been uploaded as ".$ran2.$ext; ////////// count columns and create table here } else { echo "Sorry, there was a problem uploading your file."; }

Hi Brian, Thank you for helping me with this. This is to create a price file that can be imported into an inventory program that runs on a unix server. We receive our txt file (price file) from our vendor that contains several columns (which are different every time). We only need a few columns off of this file in order to build our new importable file. We usually build our importable file in excel and it takes 2 to 3 hours. You have to open the vendor file and make several calculations in the new file before it can be imported (ex. dealer cost = cost x .75). Then, we have to ftp it onto our unix box and uploaded it into the inventory program from there. Here are the steps I am wanting to take with php. Which will take only a few clicks as opposed to a few hours in excel. 1. page one ------ select file 2. page two -------- upload file into "upload" directory ...... rename file ...... store file name in session ..... look at file to see how many columns it contains ..... create mysql table with correct number of columns with columns named like col1, col2, and on up .......... then do a "load data infile" ......... now display onto the page a table that contains the columns and the first row in the table....... display a drop down menu (selecting column) {one for cost, retail, etc.} this will tell us the columns that we need and identify them (ex cost == col4, retail==col2, and so on) 3. page three --------- post retail and cost (that contains the correct columns) ....... make calculations for the new importable file ...... create file on unix box with the file name we stored in the session earlier...... then open up the file on the unix box (fopen("ftp://user:[email protected]/yourfile.txt", "w"); ) and start writing our new updated file display file name then delete txt file in upload directory and delete the mysql table

How can I... - count the columns of a tab and/or comma delimeted file based on file type. - create a mysql table named "smith_price" and add the columns to match the text file. Example... 1 Count the columns in text file ... 2 text file has 5 columns ... 3 create mysql table "smith_price" with 5 columns : col1, col2, col3, col4, col5 _____________ Thank you, Jake