uploading file and mysqul

[big-dog1965]

Is it possible to upload a TXT and or HTML to a site and have some sort of PHP or script use the file to populate a mysql data base automatically?

 

Here is what I’m trying to do I have a website where certain people can access. I want them to upload a TXT or HTML file then the file gets converted or populates the appropriate DB table automatically.

I already have tried the form thing but what I found is that it take to long to enter all the data and some people make to many mistakes with the data.

For example a MS Access data base with some scripts will look at the data and if this and if/else that will insert the data in tables

[ucffool]

You can use a form to get the file upload, then save the file contents temporarily, read the file (file_get_contents) into a variable which you can dump into the mysql database. Finally, delete the temporary file. Here is some info on file uploads in php: http://www.w3schools.com/PHP/php_file_upload.asp

[PC Nerd]

its its possible.

 

If i understand you correctly then your looking to have something similar to PHPMyAdmin's upload feature - where you can import an XML or CSV or SQL file into the specified database.

 

It would require a lot of work to ensure that the file is in the correct format - most likely you would want ot load the file using file() and then parse each line with regex.

 

gdlk

[big-dog1965]

Here is a sample of a TXT file that autoscore puts out. I need to beable to upload it and the data gets enter into the DB the layout is pretty much the same all the time but the information changes i.e. Date, Top Qualifier for...,  truggy / 1/8 Nitro Buggy,  A-Main, B-Main C-Main ect..., pos, car, laps, time, names,

Main R/C Raceway -- 06-14-2008 

Top Qualifier for Truggy: 
   Frank Sell jr with 9/5:26.47

-- Truggy - A  MAIN --
pos Car Laps    time     name
  1   2  34    20:25.37 Frank Sell jr                               
  2   4  31    20:27.12 Andy Groening                               
  3   8  30    20:33.49 Steve Nyquist                               
  4   5  29    20:26.61 Jeff Werner                                 
  5   0  28    20:20.05 Bill Petersen                               
  6   7  27    20:21.03 J.Johnson                                   
  7   9  26    18:55.02 Jon Tucker                                  
  8   1  25    20:07.78 Dan Roth                                    
  9   6  17    10:49.04 Chris(cool)Puller                           
10   3   7     5:27.73 Chris Pearce                                

-- Truggy - B  MAIN --
pos Car Laps    time     name
  1   9  28    20:25.50 Jon Tucker                                  
  2   4  28    20:33.43 Dan Roth                                    
  3   2  24    20:30.11 Jesse Hancock                               
--   8  --- DNS ---    Ron Roberts                                 

Top Qualifier for 1/8 Nitro Buggy: 
   Frank Sell jr with 9/5:04.46

-- 1/8 Nitro Buggy - A  MAIN --
pos Car Laps    time     name
  1   2  34    20:38.41 Jason Howell                                
  2   4  34    20:43.17 Frank Sell jr                               
  3   7  32    20:20.89 Ken Perterson                               
  4   6  32    20:32.91 Boyd Lemons                                 
  5   1  31    20:25.23 Dan Kennedy                                 
  6   5  30    20:18.04 Chris(cool)Puller                           
  7   3  29    19:37.55 Phil Barger                                 
  8   8  29    20:29.00 Daryl Roqueplot                             
  9   0  24    18:30.49 Chris Pearce                                
--   9  --- DNS ---    Keith West                                  

-- 1/8 Nitro Buggy - B  MAIN --
pos Car Laps    time     name
  1   2  31    20:05.89 Chris(cool)Puller                           
  2   5  30    20:20.45 Keith West                                  
  3   0  29    20:07.84 Sean Ormerod                                
  4   4  10     6:39.60 Marc Simon                                  
  5   3  10     6:49.28 Steve Nyquist                               
  6   1   3     1:40.57 Wallie Ormerod

[hobeau]

Helo big-dog-1965,

 

First you will want to do a secure file upload,, not just a file upload. The consequences of having a public facing file upload that has no built in security can be devistating as any hacker that finds this WILL attack you. You can find a secure file uploader here http://www.solutionbot.com/2008/12/27/secure-file-upload/.

 

 

The next thing you will need to do is to read the contents of the file into a variable and parse that variable into an array. Then iterate through the array and insert the values into the database. Here's what I would say about the data. Its very very difficult to parse the data you currently have. You need to convert it somehow to at least a minimum of:

 

  1   2  34    20:25.37 Frank Sell jr                               
  2   4 31 20:27.12 Andy Groening                               
  3   8  30    20:33.49 Steve Nyquist                               
  4   5  29    20:26.61 Jeff Werner                                 
  5   0  28    20:20.05 Bill Petersen                               
  6   7  27    20:21.03 J.Johnson                                   
  7   9  26    18:55.02 Jon Tucker                                 
  8   1  25    20:07.78 Dan Roth                                   
  9   6  17    10:49.04 Chris(cool)Puller                           
10   3   7     5:27.73 Chris Pearce   
  1   9  28    20:25.50 Jon Tucker                                 
  2   4  28    20:33.43 Dan Roth                                   
  3   2  24    20:30.11 Jesse Hancock        
  1   2  34    20:38.41 Jason Howell                               
  2   4  34    20:43.17 Frank Sell jr                               
  3   7  32    20:20.89 Ken Perterson                               
  4   6  32    20:32.91 Boyd Lemons                                 
  5   1  31    20:25.23 Dan Kennedy                                 
  6   5  30    20:18.04 Chris(cool)Puller                           
  7   3  29    19:37.55 Phil Barger                                 
  8   8  29    20:29.00 Daryl Roqueplot                             
  9   0  24    18:30.49 Chris Pearce          
  1   2  31    20:05.89 Chris(cool)Puller                           
  2   5  30    20:20.45 Keith West                                 
  3   0  29    20:07.84 Sean Ormerod                               
  4   4  10     6:39.60 Marc Simon                                 
  5   3  10     6:49.28 Steve Nyquist                               
  6   1   3     1:40.57 Wallie Ormerod                

 

You will need to do this in the text file before you upload it to the database. Then when you upload it to the database you can use the following code:

 

<?php

//Read the contents of a file into a variable
$handle = fopen($filename, "r");
$a = fread($handle, filesize($filename));
fclose($handle);

// remove all double spaces
while(strpos($a, "  ") !== false)
{
$a = str_replace("  ", " ", $a);
}

// fill an array with each row
$rows = explode("\r\n", $a);
foreach ($rows as $row)
{
// fill an array with the individual column
$columns = explode(" ", trim($row), 5);

// insert the columns into the database
$conn = mysql_connect('host', 'username', 'password') or die(mysql_error());
$sql = "INSERT INTO ...statement goes here ... VALUES(" . $columns[0] . ", " . $columns[1] . "...and so on...)";
mysql_query($sql, $conn) or die(mysql_error());
mysql_close($conn);	
}
?>

 

This should read the contents of the file, break the contents up into an array, and insert each row into the database. Hope this helps!

[big-dog1965]

Got this error

Parse error: syntax error, unexpected $end in /home/_html/process.php on line 27

Also how does this know what file to read

<?php

//Read the contents of a file into a variable
$handle = fopen($filename, "r");
$a = fread($handle, filesize($filename));
fclose($handle);

// remove all double spaces
while(strpos($a, "  ") !== false)
{
$a = str_replace("  ", " ", $a);
}

// fill an array with each row
$rows = explode("\r\n", $a);
foreach ($rows as $row)
{
// fill an array with the individual column
$columns = explode(" ", trim($row), 7);

// insert the columns into the database
$conn = mysql_connect('host', 'name', 'pass') or die(mysql_error());
$sql = "INSERT INTO `tristate` (`Import_LapFree`) VALUES(" . $columns[F1] . ", " . $columns[F2] . ", " . $columns[F3] . ", " . $columns[F4] . ", " . $columns[F5] . ", " . $columns[F6] . ", " . $columns[F7] .");
mysql_query($sql, $conn) or die(mysql_error());
mysql_close($conn);	
}
___27___?>

[hobeau]

First, at the top you will need to defined $filename as I don't know what the filename is that your uploading so you will have to $filename = "whatever your file name is". Next it looks like you didn't terminate your string at $columns[F7] . ");  it should be $columns[F7] . ")";  Another thing I noticed is that you are using F1, F2, F3, F4 etc... I'm not sure I understand why the 'F' as I believe it should just be an integer to define what element of the array to use. Also, you have one column defined ('Import_LapFree') and 7 values defined. You will need to define either 1 value, or 7 columns as this is a SQL error.

[big-dog1965]

I dont know what the file name will be something.txt

terminate your string at $columns[F7] . ");  it should be $columns[F7] . ")"; gotcha

Import_LapFree is supposed to be a table and f1, f2, f3... are suppost to be columns