Upload CSV to mySQL via php preprocessor

[dazman]

Hi,

I am uploading a CSV file into a database, and want to ask a question. I am unsure if this is a syntax problem but can you please help.

 

My code is as follows:

 

<?php 
$mysqlconnection = mysql_connect('localhost', 'php', 'php');
mysql_select_db('bob', $mysqlconnection);

$row = 1;
$myfile = fopen("countryandcodes.csv", 'r');
while (($countryandcodesdata = fgetcsv($myfile, 0, ",")) !== FALSE) {

mysql_query("INSERT INTO country (`Country` `CountryCode`) VALUES ($countryandcodesdata[0], $countryandcodesdata[1]);") or die();
        $row++;
}
fclose($myfile);
mysql_close();
?>

 

So as you see my table has 2 fields, Country and CountryCode and my data seems to read off the file correctly, it doesnt go into the database. I am not sure if it is the type of quotes I have used around my mysql_query("" or '')..

 

My connection to the database is definitely working because I can run other scripts such as:

 

mysql_query('CREATE TABLE `bob`.`country` (`Country` VARCHAR(45)  NOT NULL, `CountryCode` VARCHAR(3)  NOT NULL, PRIMARY KEY (`Country`)) ENGINE = InnoDB;') or die ();

 

This creates a table for me.

[roopurt18]

My guess is it's the missing comma between `Country` `CountryCode` in your query.  The quotes you surround the query with, single or double, have no effect.  mysql_query() accepts a string as its first argument and in PHP strings are enclosed in either single or double quotes.

 

In your case double quotes are appropriate since you have chosen to include variables inside the string.

[dazman]

In your case double quotes are appropriate since you have chosen to include variables inside the string.

 

Thanks for your quick response.

If I had use single quotes would I not be able to refer to call the variable as a value in that SQL statement?

[dazman]

Hi,

I am uploading a CSV file into a database, and want to ask a question. I am unsure if this is a syntax problem but can you please help.

 

My code is as follows:

 

<?php 
$mysqlconnection = mysql_connect('localhost', 'php', 'php');
mysql_select_db('bob', $mysqlconnection);

$row = 0;
$myfile = fopen("countryandcodes.csv", 'r');
while (($countryandcodesdata = fgetcsv($myfile, 0, ",")) !== FALSE) {

mysql_query("INSERT INTO country (`Country`, `CountryCode`) VALUES ($countryandcodesdata[0], $countryandcodesdata[1]);") or die();
       $row++
}
fclose($myfile);
mysql_close();
?>

 

So as you see my table has 2 fields, Country and CountryCode and my data seems to read off the file correctly, it doesnt go into the database. I am not sure if it is the type of quotes I have used around my mysql_query("" or '')..

 

My connection to the database is definitely working because I can run other scripts such as:

 

mysql_query('CREATE TABLE `bob`.`country` (`Country` VARCHAR(45)  NOT NULL, `CountryCode` VARCHAR(3)  NOT NULL, PRIMARY KEY (`Country`)) ENGINE = InnoDB;') or die ();

 

This creates a table for me.

[roopurt18]

If I had use single quotes would I not be able to refer to call the variable as a value in that PHP string?

Fixed.  It's a string variable.  Regardless of the fact that it also happens to be a SQL statement.

 

You can only put variables inside double quoted strings.  It will not work with single quoted strings.

$name = 'Larry';

echo "Hello, {$name}." . PHP_EOL; // just one string with variable inside
echo 'Hello, ' . $name . '.' . PHP_EOL; // have to use multiple strings and concatenate

[roopurt18]

Anyways, that is not your problem.

 

mysql_query("INSERT INTO country (`Country` `CountryCode`) VALUES ($countryandcodesdata[0], $countryandcodesdata[1]);") or die();
                                             //   ^--- WHERE IS THE COMMA BETWEEN THE COLUMN NAMES?

[gevensen]

Wouldn't you need to assign a variable

For ex

$country='USA';

$countrycode=1;

Then the end of the insert statement would be

('$country','$countrycode') making sure as stated the comma is there?

[roopurt18]

The OP is already assigning a variable:

($countryandcodesdata = fgetcsv($myfile, 0, ","))