csv import problem

[ragrim]

Hi,

 

i have a script to import a csv into a database but some of my product descriptions have ' in the names, this causes mysql to error, how can i get around this?

 

here is my code.

 

if(isset($_POST['submit']))
{	$i=0;
     $fname = $_FILES['sel_file']['name'];
     
     $chk_ext = explode(".",$fname);
     
     if(strtolower($chk_ext[1]) == "csv")
     {
     	mysql_query("truncate table products") ;
         $filename = $_FILES['sel_file']['tmp_name'];
         $handle = fopen($filename, "r");
    


         while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
         {
		if($i > 0)
		{
            $sql = "INSERT into products(prod_id,prod_name,prod_description,prod_cat,prod_sub_cat,tax,prod_price,active) values('$data[0]','$data[1]','$data[9]','$data[14]','$data[15]','$data[19]','$data[35]','$data[65]')";
            mysql_query($sql) or die(mysql_error());
		}
		$i++;
         }
    
         fclose($handle);
         echo "Successfully Imported<br>";


     }
     else
     {
         echo "Invalid File";

     }    
}

[Psycho]

Use mysql_real_escape_string() which you should ALWAYS use on any user submitted values that you are going to run in queries. Also, do not do a single INSERT query for each record. That will cause a performance issue. Instead, combine all the INSERTs into one query.

 

Lastly, no need to have a value that you increment on each iteration of the loop just to skip the first line. That's not efficient. I've made some other changes in the interest of cleaner/more efficient code

if(isset($_POST['submit']))
{
    if(strtolower(strrchr($_FILES['sel_file']['name'], '.')) != ".csv")
    {
        echo "Invalid File";
    }  
    else
    {
        $filename = $_FILES['sel_file']['tmp_name'];

        //Process the data file
        $firstLine = true;
        $valuesAry = array(); //Temp array to hold insert records
        $handle = fopen($filename, "r");
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
        {
            if($firstLine)
            {
                $firstLine = false;
                continue;
            }
            //Prepare values
            $id     = intval($data[0]);
            $name   = mysql_real_escape_string(trim($data[1]));
            $desc   = mysql_real_escape_string(trim($data[9]));
            $cat    = intval($data[14]);
            $subcat = intval($data[15]);
            $tax    = floatval($data[19]);
            $price  = floatval($data[35]);
            $active = intval($data[65]);
            //Add INSERT record to temp array
            $valuesAry[] = "('$id', '$name', '$desc', '$cat', '$subcat', '$tax', '$price', '$active')";
        }
        fclose($handle);
     
        mysql_query("TRUNCATE TABLE products");
         //Create ONE INSERT query for all records
        $sql = "INSERT into products
                    (prod_id, prod_name, prod_description, prod_cat, prod_sub_cat, tax, prod_price, active)
                VALUES " . implode(', ', $valuesAry);
        mysql_query($sql) or die(mysql_error());
               
        echo "Successfully Imported<br>";
    }
}

[coolpriya073]

i was having the same problem..thnx a ton