upload db to MySQL

1064 appears to be a syntax error.  You need to post your SQL for us to help you find it.  Are both versions of MySQL the same?  Same OS?  Did the database and/or tables already exist in the second server?

Hi DaveBaldwin,

Here's the full syntax error:

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'video_id,video_name,ftp_folder,html_file
1234,able.mov,videos/english,index/his' at line 1

I created the database and tables directly on the server.
Then I added one row, again directly on the MySQL server.
I exported the database to my computer. The database was exported and I was able to open it with Excel.
Once the file was opened with Excel, I added 4 other rows, saved it into .SQL format and tried to upload it back on the server. And there I saw the error 1064.

I'm wondering if your original export is in CSV instead of SQL format because I don't think Excel will read MySQL format either.  And I'm pretty sure that Excel will not export in MySQL format.  MySQL format is not exactly the same as Microsoft SQL format.  

In any case, I won't be able to tell what is wrong until you show me the SQL file.

Here's the SQL file
myvideos.sql

The file you uploaded is actually a CSV file.  MySQL can not import that directly.  phpMyAdmin can be used to import it but Only if the database and table are already created.  I have attached the MySQL dump file which is in MySQL format and that you can use to upload to MySQL but you must have the database created because this is only the table that you sent in CSV format converted to MySQL format.  You can open the two files in a text editor and clearly see the difference.  Note that Excel can not be used to edit the SQL files.
myvideos.sql

Actually what I did was to use phpMyAdmin to export the database and I exported it into a CSV format and I was then able to open the same file using Excel.

You mentioned that I must have the database created. Actually this database was created with the table and both were created with phpMyAdmin.

At the same file that I exported in CSV from phpMyAdmin, I modified it by adding some extra rows then again, I used phpMyAdmin to import it back on the server.

I am still a newbie to MySQL so...how I can use the dump file you attached and update the database by doing an import directly in phpMyAdmin ?

Thanks again

Hi theGhost_k8,

I guess I can execute this line here "Run SQL query/queries on server custsqlmoo10"

But in order to replace the old table with the updated one, how I can upload the file? I guess the only option I have in mind would be to upload it via FTP, then indicate the path to the sql file ?

Hey you export db as cvs first export db as sql then it is use to import

nop! What you can do is create a copy of old table for time being.
- you may use mysqldump
- use export of php myadmin
- execute following mysql queries:
create table old_mymovie like mymovie; insert into old_mymovie select * from mymovie; truncate table mymovie;

And then you may execute the query.

Here is a script that will usually work to export a data base table into a CSV file.

<?php // RAY_db_to_excel.php
error_reporting(E_ALL);
echo "<pre>\n";


// DEMONSTRATE HOW TO EXPORT A TABLE SO THAT IT CAN BE USED IN EXCEL


// SET YOUR TABLE NAME HERE - OR MAYBE USE THE URL GET ARGUMENT?
$table_name = '???';


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// OPEN THE CSV FILE - PUT YOUR FAVORITE NAME HERE
$csv = 'EXPORT_' . date('Ymdhis') . "_$table_name" . '.csv';
$fp  = fopen($csv, 'w');

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
if (mysql_num_rows($res) == 0)
{
    die("WTF? $table_name HAS NO COLUMNS");
}
else
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
    while ($show_columns = mysql_fetch_assoc($res))
    {
        $my_columns[] = $show_columns["Field"];
    }
    // var_dump($my_columns); ACTIVATE THIS TO SEE THE COLUMNS
}

// WRITE THE COLUMN NAMES TO THE CSV
if (!fputcsv($fp, $my_columns)) die('DISASTER');

// GET THE ROWS OF DATA
$sql = "SELECT * FROM $table_name";
$res = mysql_query($sql);
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// ITERATE OVER THE DATA SET
while ($row = mysql_fetch_row($res))
{
    // WRITE THE COMMA-SEPARATED VALUES.  MAN PAGE http://php.net/manual/en/function.fputcsv.php
    if (!fputcsv($fp, $row)) die('CATASTROPHE');
}

// ALL DONE
fclose($fp);

// SHOW THE CLIENT A LINK
echo "<p><a href=\"$csv\">$csv</a></p>\n";

Select allOpen in new window

Here is a script that will usually work to import a CSV file into a data base table.

<?php // RAY_csv_to_db.php
error_reporting(E_ALL);
echo "<pre>\n";  //READABILITY FOR var_dump()



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES



// TEST DATA, SAVED FROM THE POST AT EE
$csv = "http://laprbass.com/RAY_csv_to_db.csv";
$fpo = fopen($csv, 'r');
if (!$fpo ) die('CRUMP');

// GET THE FIELD NAMES FROM THE TOP OF THE CSV FILE
$top = fgetcsv($fpo);
$cnt = count($top);

// SET UP KEY NAMES FOR USE IN OUR QUERY
$query_cols = implode(',', $top);

// SET A ROW COUNTER
$counter = 0;

// KEEP TRACK OF ROWS THAT HAVE THE WRONG NUMBER OF FIELDS
$errors = array();

// LOOP THROUGH THE CSV RECORDS PERFORMING CERTAIN TESTS
while (!feof($fpo))
{
    $counter++;

    // GET A RECORD
    $csvdata = fgetcsv($fpo);

    // SKIP OVER EMPTY ROWS
    if (empty($csvdata)) continue;

    // CHECK THE NUMBER OF FIELDS
    if ($cnt != count($csvdata))
    {
        $errors[] = $counter;
        continue;
    }

    // MAYBE ASSIGN KEYS TO THE ROW OF FIELDS - ACTIVATE THIS TO SEE THE ASSOCIATIVE ARRAY
    //  $csvdata = array_combine($top, $csvdata);
    //  var_dump($csvdata);

    // ESCAPE THE INFORMATION FOR USE IN THE QUERY
    foreach ($csvdata as $ptr => $value)
    {
        $csvdata[$ptr] = mysql_real_escape_string($value);
    }

    // SET UP VALUE FIELDS
    $query_data = "'" . implode("', '", $csvdata) . "'";

    // SET UP A QUERY
    $sql = "REPLACE INTO myTable ( $query_cols ) VALUES ( $query_data )";

    // RUN THE QUERY HERE....
    var_dump($sql);

}


// SHOW THE NUMBER OF ROWS PROCESSED
echo "<br/>RECORDS PROCESSED $counter \n";

// SHOW THE NUMBERS OF THE ROWS WITH THE WRONG NUMBER OF FIELDS
if (count($errors))
{
    echo "<br/>ROWS WITH THE WRONG NUMBER OF FIELDS: \n";
    var_dump($errors);
}

Select allOpen in new window

Run it once to see if the SQL statements look OK, then add your query at line 88.

Hi Ray_Paseur,

I ran your query, the one for the import. I ran it into phpMyAdmin window. Unfortunately, it did not work and there was an error at line 1, but it may not really be at this line cause it looks ok for me.

Here's the error:

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 1
STR: <?
SQL: <?php // RAY_csv_to_db.php
error_reporting(E_ALL);


SQL query:

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<?php // RAY_csv_to_db.php
error_reporting(E_ALL)' at line 1

Hey Ray_Paseur,

I thought your script could run in a phpMyAdmin window. I don't even have MySQL on my computer so basically, I have to run it directly on the server. I also use Excel a lot and I was wondering if I can use it to update the entire database without having all the pain to go into phpMyAdmin.

And thanks for suggesting this book. I'll try to find it online and get some background info.

Hi theGhost_k8,

Attached is the export dump from phpMyAdmin window.

I ran your query like this 'C:/WebDeveloper/myvideos.sql' into table myvideos fields terminated by ',' lines terminated by '\n' ignore 1 lines;

But still ran into the same error 1064.
export-phpMyAdmin.txt

I had to change the ";" in the MySQL syntax and everything worked.