Jacob Jacob - 1 year ago 35
MySQL Question

PHP string not updating mySQL table


PHP 7, mySQL, phpMyAdmin, localhost, MAMP, Mac


I am parsing some html text to a mySQL database on my localhost and the string does not seem to update the table as planned.

I tested the code and was able to narrow down the problem root as the string contained in a variable that I am trying to then update in mySQL table.

I tested the variable and the query syntax by replacing the string within the variable manually as 'Test test test' and this loaded successfully when I ran the code.

I also tried changing the mySQL table structure from 'text' to 'long text' even though the string was clearly short enough to fit in a text field but this did not help.

I am fairly convinced the issue is with the string itself and possibly the table/ field structure.

It is also worth mentioning that the query update worked with other strings through this code and it only fails in some cases (although quite often)

What I am asking

At this point and after trying a few things and researching for info, I am a little stuck as to what the issue could be so any advice would be useful if you have encountered this issue before.
Below is a bit more info about the code and the string.


Here is the string:

$Definition = verb (used without object) , abode or abided, abiding. 1. to remain; continue; stay: abide with me. 2. to have one's abode; dwell; reside: to abide in a small scottish village. 3. to continue in a particular condition, attitude, relationship, etc.; last. verb (used with object) , abode or abided, abiding. 4. to put up with; tolerate; stand: i can't abide dishonesty! 5. to endure, sustain, or withstand without yielding or submitting: to abide a vigorous onslaught. 6. to wait for; await: to abide the coming of the lord. 7. to accept without opposition or question: to abide the verdict of the judges. 8. to pay the price or penalty of; suffer for. verb phrases 9. abide by, to act in accord with. to submit to; agree to: to abide by the court's decision. to remain steadfast or faithful to; keep: if you make a promise, abide by it.;


$Definition = implode('', $InterArray); //I also tested the array to string conversion works fine
//$Definition = 'Test test test'; I tested this and it worked
$Definition = (string) $Definition;

$conn = mysqli_connect("localhost:8889","root","root","myDB");
$sql="INSERT INTO myTable (DEFINITION) VALUES('".$Definition."');";

SQL table:
DEFINITION, Type: Long text, Collation: latin_swedish_ci, Default: none

Answer Source

to avoid any confusion, it's preferable to use mysqli_real_escape_string() than to escape the string manually. mysqli_real_escape_string() function takes care of the escaping itself.

according to php.net:

"This function is used to create a legal SQL string that you can use in an SQL statement. The given string is encoded to an escaped SQL string, taking into account the current character set of the connection."

as Magnus said, Prepared Statement is always the best choice. you can never be too sure about the security of your aplication.