Jump to content

Problem with SQL Manager and " character in some value


Recommended Posts

Well i have this query:

SELECT p.reference as Codigo, 
      pl.name as Nombre, 
      GROUP_CONCAT(DISTINCT(cl.name) SEPARATOR ",") as Categorias, 
      pl.description as Sinopsis
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
LEFT JOIN ps_category_product cp ON (p.id_product = cp.id_product)
LEFT JOIN ps_category_lang cl ON (cp.id_category = cl.id_category)
LEFT JOIN ps_category c ON (cp.id_category = c.id_category)
LEFT JOIN ps_product_tag pt ON (p.id_product = pt.id_product)
WHERE pl.id_lang = 1
AND cl.id_lang = 1
AND p.id_shop_default = 1
AND c.id_shop_default = 1
GROUP BY p.id_product

and it works fine.

 

The problem is when i have a product with a " character in some value. The value STOP in the " character and broke the CSV

 

 

What can i do to solve this ?

Edited by Reco-X (see edit history)
Link to comment
Share on other sites

Hi,

 

Not sure to understand your issue.

 

Your query looks ok and it probably does not stop due to ".

 

Then possibly, what you mean is that you cannot import the created csv, because of " ? Is that the point ?

 

You can then convert to and use xls instead.

 

It also depends on what tool you use to treat your csv. If well done, the tool should "escape" the special characters and particularly the ".

Escaping mean to tell the server to threat the " as any other character and not as a ".

Usually this is done by adding a backlash before the character to escape, like this \"

Another technique is to use an mySQL function to encapsulate the string.

 

It also depends on what you want to do, keep or removing these " ?

 

In PrestaPricing for instance, there is an option to notify the program that all csv strings are bordered by two " and to ignore them when importing.  The other "  are handle automatically by the tool itself.

 

Regards

Franck

Link to comment
Share on other sites

Hi,

 

Not sure to understand your issue.

 

Your query looks ok and it probably does not stop due to ".

 

Then possibly, what you mean is that you cannot import the created csv, because of " ? Is that the point ?

 

You can then convert to and use xls instead.

 

It also depends on what tool you use to treat your csv. If well done, the tool should "escape" the special characters and particularly the ".

Escaping mean to tell the server to threat the " as any other character and not as a ".

Usually this is done by adding a backlash before the character to escape, like this \"

Another technique is to use an mySQL function to encapsulate the string.

 

It also depends on what you want to do, keep or removing these " ?

 

In PrestaPricing for instance, there is an option to notify the program that all csv strings are bordered by two " and to ignore them when importing.  The other "  are handle automatically by the tool itself.

 

Regards

Franck

 

Well i attach in this comment a example of my problem

 

This CSV is a result of a query (of course a delete a lot of rows and i only put some for show the problem )

 

I think  use an mySQL function to encapsulate the string will be the solution but i don´t know what exactly must i do.

error example.zip

Edited by Reco-X (see edit history)
Link to comment
Share on other sites

Well i was looking the way to solve this in the code.

 

I have a question

	/**
	 * Genrating a export file
	 */
	public function generateExport()
	{
		$id = Tools::getValue($this->identifier);
		if (!Validate::isFileName($id))
			die(Tools::displayError());
		$file = 'request_sql_'.$id.'.csv';
		if ($csv = fopen(_PS_ADMIN_DIR_.'/export/'.$file, 'w'))
		{
			$sql = RequestSql::getRequestSqlById($id);

			if ($sql)
			{
				$results = Db::getInstance()->executeS($sql[0]['sql']);
				foreach (array_keys($results[0]) as $key)
				{
					$tab_key[] = $key;
					fputs($csv, $key.';');
				}
				foreach ($results as $result)
				{
					fputs($csv, "\n");
					foreach ($tab_key as $name)
						fputs($csv, '"'.strip_tags($result[$name]).'";');
				}
				if (file_exists(_PS_ADMIN_DIR_.'/export/'.$file))
				{
					$filesize = filesize(_PS_ADMIN_DIR_.'/export/'.$file);
					$upload_max_filesize = Tools::convertBytes(ini_get('upload_max_filesize'));
					if ($filesize < $upload_max_filesize)
					{
						if (Configuration::get('PS_ENCODING_FILE_MANAGER_SQL'))
							$charset = Configuration::get('PS_ENCODING_FILE_MANAGER_SQL');
						else
							$charset = self::$encoding_file[0]['name'];

						header('Content-Type: text/csv; charset='.$charset);
						header('Cache-Control: no-store, no-cache');
						header('Content-Disposition: attachment; filename="'.$file.'"');
						header('Content-Length: '.$filesize);
						readfile(_PS_ADMIN_DIR_.'/export/'.$file);
						die();
					}
					else
						$this->errors[] = Tools::DisplayError('The file is too large and can not be downloaded. Please use the LIMIT clause in this query.');
				}
			}
		}
	}

That is the function that makes the export.

 

 

In this loop i think there is a mistake or i don't know why this is written in this way.

				foreach ($results as $result)
				{
					fputs($csv, "\n");
					foreach ($tab_key as $name)
						fputs($csv, '"'.strip_tags($result[$name]).'";');
				}

If you see the fputs has two " character between the value and there is cause of my problem.

 

But if I see my files that i use to Import my rows doesn't have that.

 

There is a example

 

 

The CSV that I make manually to import the database.

Activo;Codigo;Nombre;Nombre Original;Director;Actores;Duracion;Pais;Genero;Cantidad Cds;Año;Sinopsis;Link (con http);Imágenes;Precio

1;BD00446;Blancanieves y el cazador;;;;;;Bluray-Peliculas;;;;;http://localhost/prestacine/images/peliculas/bd/front/BD00446.jpg{http://localhost/prestacine/images/peliculas/bd/full/BD00446.jpg;40

The result of the CSV with the PRESTASHOP EXPORT.

Codigo;Nombre;Nombre_Original;Director;Actores;Duracion;Pais;Categorias;Cantidad_CDS;Año;Sinopsis;LINK;Precio;

"P00001";"Testigo en peligro";"Nineteen Eighty-Four";"Michael Radford";"John Hurt, Richard Burton, Suzanna Hamilton, Cyril Cusack, Gregor Fisher, James Walker,Andrew Wilde, Shirley Stefox, Phyllis Logan";"123 min";"Inglaterra";"Ciencia Ficcion";"0";"1984";"El futuro, año 1984. Winston Smith (John Hurt) soporta una abyecta existencia bajo la continua vigilancia de las autoridades en la Oceania totalitaria. No obstante, su vida se convierte en una pesadilla cuando prueba el amor prohibido y comete el crimen de pensar libremente. Enviado al siniestro “Ministerio del Amor”, se encuentra a merced de O’Brien (Richard Burton), un cruel oficial decidido a dominar sus pensamientos... y a quebrantar su voluntad.";"http://www.filmaffinity.com/es/film716877.html";"20.000000";

I think it is a bug, but i'm not sure.

Link to comment
Share on other sites

I solved the problem, i know is not the best way but at least works.

                    foreach ($tab_key as $name)
                    {
                        //Hice esta modificacion en este loop para que puedan imprimirse correctamente los resultado
                        //en los campos del CSV ya que sin los reemplazos de comillas se rompe la exportacion.
                        $nameForCsv = str_replace('"', "", $result[$name]);
                        $nameForCsv = strip_tags($nameForCsv);
                        fputs($csv, '"'.$nameForCsv.'";');
                    }
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...