I have a PHP function to save data to DB. The data contains ñ and accents and that data is extracted from a third party page. I have tried many ways and it doesn't work.
This is the function I have:
public function agregarProduct($name,$brand,$price){
$sql ="CALL spAddProduct('$name','$brand',$precio);";
$result= $this->_BD->query($sql);
$resultado->close();
$this->_BD->next_result();
}
What I have tried is:
- Place header in the file indicating utf-8.
- I have used
$name = utf8_encode($name);
. - I have also used the function
mb_convert_encoding
. - Another one I used was
iconv(mb_detect_encoding($name, mb_detect_order(), true), "UTF-8", $name)
. - I have tried changing the DB collation to
utf8_general_ci
,utf8_spanish_ci
,uft8_default collation
. - Also in my connection I have used
$BD->query("SET NAMES 'utf8'");
and$BD->set_charset("utf8");
None of the above worked for me in the database. Since, for example, if it $name
comes in the argument Maíz
, it is saved in the database as Maíz
, but if I print it on the screen it appears fine (Corn).
I made an attempt placing by default a value $name
of the form $name = "Maíz";
so that it is correctly added to the database.
One difference I found is the following:
echo mb_detect_encoding($name = 'Maíz');
//esto devuelve UTF-8 y se ingresa correctamente a la BD
echo mb_detect_encoding($name); //$name viene como argumento de la funcion.
//esto devuelve ASCII y se ingresa con los caracteres raros
With this observation I wanted to see that it returned this same function when changing the variable with the functions utf8_encode
, mb_convert_encoding
and iconv
, the result for all was ASCII
.
How else could I solve this situation?
Problem
It's great that you've checked all the character encodings (database encoding, PHP, everything) before asking the question and that you're using UTF-8. Thus, it was easier to see that you are receiving the data with HTML entities like
á
,í
, etc. directly from the HTML, and that it is not an error in the coding . In addition, you are surely seeing it well on the screen because the browser itself shows it to you accordingly.Solution
To decode (get to the corresponding character) the HTML entities we use the html_entity_decode function .
Code
In this way, we can use it for all the data you receive from the server. In your example for
$name
:Security Note: The code you are using has serious security problems. Please read how to use Prepared Statements to build a query, always using
mysqli
the improved MySQL extension .after connecting to the server, just enter one line as in the following example:
From here you can already place your queries and others that you want to use
NOTE: my mysql fields are text with collation
latin1_swedish_ci
.Another option is that you use
utf8_decode()
, you usedutf8_encode()
. For exampleWhat you enter in your input for example (stork -> has a Ü and Ñ) you save it in a variable. then before using this variable in your sql query, you apply utf8_decode() to it. For example $variable = utf8_decode($variable);
before saving to the database (unspecified collate), you can convert the characters to HTML entities
then in the php file, add the following header
In this way, it will not be necessary to transform the data again before visualizing it.