php curl csv file load into mysql

What I am trying to do is load a csv file into mysql. I am using curl but I am absolutely stuck on the part where it converts into the database. Would I still use a fgetcsv? I'm am absolutely lost on that part. Any pointers are greatly appreciated.

$url = "http://xxx.xxx.xxx.xxx/file.csv";
function curl($url){
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL,$url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
    return curl_exec($ch);
    curl_close ($ch);
}


$csv = "http://xxx.xxx.xxx.xxx/file.csv";


echo $filenameb  = curl($csv);



    if (file_exists($filenameb)){
       
        mysql_select_db("transfer",$sqlconnect);
       
     
        echo "$filenameb</br>";
        ///$handle = fopen("$filenameb", "r") or die("no file");
        while (($data = fgetcsv($filenameb, 50000, "," )) !== FALSE ){    //echo "<pre>"; print_r($data);die;
           
        $id = $data[0];
        //$id = $data[0];
        //goes off off of no as number number is set to 0
            //$id = $data[0];
            //echo $id;die;
            $existSql = "Select * from push_it where no='".$id."'";
            $result = mysql_query($existSql);
            if(@mysql_num_rows($result)>0) {
                $updateSql = "UPDATE push_it set
                `key` = '".mysql_real_escape_string($data[1])."' ,
                `Int1` = '".mysql_real_escape_string($data[2])."' ,
                `Int2` = '".mysql_real_escape_string($data[3])."' ,
                `Int3` = '".mysql_real_escape_string($data[4])."' ,
                `string` = '".mysql_real_escape_string($data[5])."' ,
                `Date1` = '".mysql_real_escape_string($data[6])."'  WHERE NO='$id'";
                mysql_query($updateSql) or die("insert" . mysql_error());
            }
            else {
   
                $import="INSERT INTO push_it (`no` , `key` , `Int1` , `Int2` , `Int3` , `string` , `Date1`)
                        values(
                        '".mysql_real_escape_string($data[0])."' ,
                        '".mysql_real_escape_string($data[1])."',
                        '".mysql_real_escape_string($data[2])."',
                        '".mysql_real_escape_string($data[3])."',
                        '".mysql_real_escape_string($data[4])."',
                        '".mysql_real_escape_string($data[5])."',
                        '".mysql_real_escape_string($data[6])."')";     
       
                  mysql_query($import) or die("insert" . mysql_error());
          }
    }
    fclose($handle);
    echo "super";
}
else{
    echo "terrible";
}

PHP:

 

I'm just going to take a couple steps back in case you are a little confused.

A CSV is a flat text file containing rows of delimited text, usually separated by a comma.
Think of a spreadsheet named "Employees" and you want to save the Name, Age, and Title of each employee. It would look like this:

"John","39","Sales"
"Sally","28","Receptionist"
"Peter","44","Manager"

Now let's say you want to save the CSV data to a mySQL database. You created a database table named "Employees" and you have 3 columns named Name, Age, and Title (just like our spreadsheet). The CSV file is located on a different server. You would do these following steps:

1. Fetch the CSV from the server. I would use CURL. You could then save it locally or just load it in to memory.

2. Loop through the CSV line by line using fgetcsv() to parse the contents.
Example:

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
   // $data is an array holding the parsed contents of each row (line)
   // $data[0] holds John (or Sally Or Peter depending on the row)
   // $data[1] holds 39 (depending on the row)
   // $data[2] holds the title (Sales, Manager or Receptionist in this example)
}

PHP:

3. While you are looping through the parsed CSV you can INSERT the parsed code in to your mySQL data.

Your CSV isn't converting in the database. You are simply looping through a CSV file and parsing the contents then storing each item as a new record in your database.

 

Close, but not quite... You want str_getcsv if you're using curl. Curl gets you the string, so you need to process as a string, not as a file.

http://www.php.net/manual/en/function.str-getcsv.php

Though I would be remiss in failing to mention this is 2013, not 2005 -- you shouldn't be using mysql_ functions in the first place, since we've been told for eight years to stop using them, and for a year and a half now they've had these nice big red warning boxes telling us not to use them.

... and really since you're pulling from csv, prepared queries via PDO could REALLY work in your favor.

Though trying to make sense of your code... I'm lost... I think it's your variable names that don't make any sense or something, you're acting like CURL is getting the filename instead of the file CONTENTS. Your curl function has a problem too, since the close will NEVER execute being after your return! Also, STOP using fieldnames that are reserved words like 'key', then you don't need the reverse single quotes -- it's bad practice.

This is guessing somewhat and untested, but:

/* note, using php 5.4+ arrays */

$csvFile = "http://xxx.xxx.xxx.xxx/file.csv";

$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $csvFile);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$fileRows = explode("\n", curl_exec(ch));
curl_close($ch);

$db = new PDO(
	'mysql:host=localhost;dbname=transfer',
	'username',
	'password'
);

/* side note, STOP making field names that are reserved words! */

$countStmt = $db->prepare('
	SELECT count(*) FROM push_it
	WHERE no = :no
');

$updateStmt = $db->prepare('
	UPDATE push_it SET
		`key` = :key,
		int1 = :int1,
		int2 = :int2,
		int3 = :int3,
		`string` = :int4,
		date1 = :date1,
	WHERE no = :no
');

$insertStmt = $db->prepare('
	INSERT INTO push_it (
		no, `key`, int1, int2, int3, `string`, date1
	) VALUES (
		:no, :key, :int1, :int2, :int3, :string, :date1
	)
');

$fields = [
	':no', ':key', ':int1', ':int2', ':int3', ':string', ':date1'
];
	
foreach ($fileRows as $row) {
	$row = array_combine($fields, str_getcsv($row));
	$countStmt->execute([$row[':no']]);
	if ($countStmt->fetchColumn()) { 
		$updateStmt->execute($row);
		echo $row[':no'], ' Updated<br />';
	} else {
		$insertStmt->execute($row);
		echo $row[':no'], ' Inserted<br />';
	}
}

Code (markup):

.. is roughly how I'd go about that. Prepared queries rock in this scenario since (if you are using native prepared as opposed to emulated) you're sending less back and forth to the engine... and it's cleaner code-wise since you can make the queries and let PDO plug them in over and over, instead of building the result string each time. Merge in keys to each row, and you don't even have to play time inside the loop doing a whole lot of anything apart from running the queries.

 

thanks and yes I know about the mysqli is updated