How to insert large text into mysql database in android?

Question

I have a app which allows user to insert html code in an edit text. When save is clicked this html code is sent to the mysql database as a string and inserted into a text field.The following code inserts html content into the db.

$instruction =$_POST["instruction"];
$plainHTML = str_replace(chr(194),"",$instruction);
$sql = "INSERT INTO rreadyreckoner (id, instruction)
VALUES (NULL, '$plainHTML')";

The problem here is data gets saved but when I try to retrieve the data special characters like " and \u00a0 come in response if mysqli_set_charset($con, "utf8"); this line is added. I try retrieving the data with this code :-

$query ="SELECT instruction FROM `rreadyreckoner` ORDER BY id DESC LIMIT 1;";

$res = mysqli_query($con,$query);
$result = array();
while($row = mysqli_fetch_array($res))
{
                  array_push($result,
                  array('instruction'=>$row[0]));
}
if(!$result)
{
echo "Nothing to display";
}else
{
echo json_encode(array("result"=>$result));
}

This is the actual response that I get.

{"result":[{"instruction":"<html>\n\n\u00a0 \u00a0<head><\/head>\n\n\u00a0 \u00a0<body>\n\n\u00a0 \u00a0 \u00a0 <ul>\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0<li>The Comprehensive R Archive Network<\/li>\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0<li
>A network of global web servers storing identical, up-to-date, versions of<br \/>code and documentation for R<\/li>\n\n\u00a0 \u00a0 \u00a0 <\/ul>\n\n\u00a0 \u00a0 \u00a0 <p><br \/><strong>Download and Install R:<\/strong><\/p>\n\n\u00a0 \
u00a0 \u00a0 <ul>\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0<li>Use the CRAN mirror nearest to you to download R setup at a faster<br \/>speed. Go to <a href=\"url\">\u00a0http:\/\/cran.r-project.org<\/a><\/li>\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u0
0a0<li>Select one of the three download links according to your machine.<\/li>\n\n\u00a0 \u00a0 \u00a0 <\/ul>\n\n\u00a0 \u00a0 \u00a0 <img src=\"file:\/\/\/storage\/emulated\/0\/rreadyreckoner_images\/download-r.png\" alt=\"downloadr\" widt
h=\"191\" height=\"129\" \/>\u00a0\n\n\u00a0 \u00a0 \u00a0 <ul>\n\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0<li>Run the R set up and follow the instructions of the installer.<\/li>\n\n\u00a0 \u00a0 \u00a0 <\/ul>\n\n\u00a0 \u00a0<\/body>\n\n<\/html
>\n\n"}]}

What should I do so that I can save html content as text and when I retrieve it I can display it in a webview?

Any help or suggestion is much appreciated.Thank you.

You don't need to store HTML in the database and you shouldn't also. Why don't you use HTML templates instead and fill them with data once you have the data? — B001ᛦ, Aug 22 '16 at 11:30
your sql query is (easiliy) susceptible to sql injection. Please inform yourself about prepared statements: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php — Jester, Aug 22 '16 at 11:34
@bub I don't have control over the html content so I can't use html templates. — AndroidNewBee, Aug 22 '16 at 11:38
have you tried this http://stackoverflow.com/questions/24631088/how-to-insert-html-code-into-db-using-php — Karthi, Aug 22 '16 at 11:47

score 0 · Answer 1 · answered Aug 22 '16 at 12:32

0

Thank you @KarthiVenture I first added the slashes then removed the Â in my java code using instructions[i] = instructions[i].replaceAll("Â"," ");

answered Aug 22 '16 at 12:32

AndroidNewBee

744
3
12
36

score -1 · Answer 2 · answered Aug 22 '16 at 11:32

check limits here https://www.sqlite.org/limits.html

for the large kind of text you can use BLOB type instead of TEXT type( But You need to convert Large text to byte Array when inserting/updating and convert byte array to large text back when loading from database)

Reference : http://www.sqlite.org/faq.html#q9

How to insert large text into mysql database in android?

2 Answers2