[SOLVED] You have an error in your SQL syntax

[jakebur01]

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());


} 

 

[DarkWater]

Why are you creating a new table on each upload?

[jakebur01]

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

[.josh]

remove the ADD

[jakebur01]

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));

 

[.josh]

So if your loop is only making one column, what does that tell you?

[jakebur01]

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 .= ");";

[.josh]

You aren't counting anything in your code.  You have a foreach loop.  foreach loops cycle through each element of an array.  So why would it only cycle through one element?

[jakebur01]

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

 

 

[jakebur01]

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.

[.josh]

If I had an array and a foreach loop was only doing 1 iteration, I think the first thing I would do is echo things out so I can see if they have what I expect them to have. 

[jakebur01]

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?

 

 

 

[.josh]

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?

 

Well, you can guarantee it starts on line 6 every time by specifying line 6, just like you did.  As far as will other text files be different... I don't know; I have no idea what your system is or how it's setup or how the files are being generated.  You would guarantee and get it to read just the columns by enforcing a format for the text file, because there's no way to have it read it accurately if there isn't some kind of standard format.

[jakebur01]

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.

 

[jakebur01]

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.