simple .sql file layout problem!

[unistake]

Hi all,

 

I am trying to use phpmyadmin to upload a .sql file. I think the way I have written the .sql file is the problem as to why it is not uploading.

 

An example lines in my .sql file is:

 

001  Testing1

002  Testing12

003  Testing123

 

As you can see there are no commas or anything. Can someone tell me how it is mean to be presented.

 

Thanks

[premiso]

INSERT INTO table_name (col1, col2) VALUES ('001', 'Testing1'), VALUES ('002', 'Testing12'), VALUES ('003', 'Testing123');

 

Of course you will need to tailor that to your table and column names.

[unistake]

I have over 1600 rows like that, is there a quicker way!?

 

[premiso]

Write a looping code that reads the rows in one by one and formats them in that way...

[unistake]

could someone point me in the right direction for that?

 

I would not have a clue where to start.

 

Thanks

[Pikachu2000]

file() will read the file into an array, line-by-line. Then you can use a foreach() loop to run the insert queries, or reformat the data and save it again.

[unistake]

Can you give me an example of how the file should be formatted.

 

At the moment the file includes over 1000 lines that look like:

001  test1

002  test2

 

Could you put the commas in for an example of how it is meant to look.

 

Thanks for your help

[unistake]

anybody?

[Pikachu2000]

It should end up something like this, and you'll need to add some error checking/handling.

 

<?php
$array = file('filename.txt');
foreach( $array as $v ) {
$line = explode( ' ', $v );
$query =  "INSERT INTO `table` {$line[0]}, {$line[1]}";
$result = mysql_query( $query );
}
?>

[DavidAM]

Hi all,

 

I am trying to use phpmyadmin to upload a .sql file. I think the way I have written the .sql file is the problem as to why it is not uploading.

 

An example lines in my .sql file is:

 

001  Testing1

002  Testing12

003  Testing123

 

As you can see there are no commas or anything. Can someone tell me how it is mean to be presented.

 

Thanks

 

Putting .sql on a filename does not make it an SQL file.  This is a data file.  The example you show does not give enough detail for a definitive answer. However, with the example as shown, you could use a PHP script to process the data:

 

// Retrieve the entire data file into an array
$data = file('name of file');

// Process each element of the array - i.e. each line of the file
foreach ($data as $line) {
  // Breakup the line on the space character
  $cols = explode(' ', $line);
  // Build the INSERT statement
  $sql = sprintf("INSERT INTO table_name (col1, col2) VALUES ('%s', '%s')", 
      $cols[0], $cols[1]);
  // Execute the query
  mysql_query($sql);
}

This code is not tested, just written off the cuf.

 

There are other ways to do it:

• If the file is too big to load into memory, you can use fopen(), and fgets() to read and process one line at a time.
  
• The INSERTs could (and probably, should) be consolidated into batch insert statements for better performance
  
• If there are other columns in the file, you will need to determine the best way to split it (i.e. is the file delimited by spaces, commas or what? or are all columns a fixed length)
  
• mySql has a LoadFile (or something like that) command that might be used on a file of this nature, so a PHP script would not be necessary
  
• As I said, this is not an SQL file, so look at the other import options of phpmyadmin.  I'm not sure what is available, but it might have a means of importing data if you define the structure.

[unistake]

sorted, thanks.