how to post long text and insert into mysql database using PHP

BR
BR used Ask the Experts™
on
Dear Experts,
I use PHP and MySql. My form is HTML5 form.
I post my form to another page to insert the form data inside mysql database.

I clean the form using this two functions.

function test_input($data) {
  $data = trim($data);
 $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}
 	
   $comment = test_input($_POST["comment"]);
   $comment = $mysqli->real_escape_string($comment);
   
   // I clean every input with these two functions ( function test_input and real_escape_string )

My sql sentence is below
$sql = "INSERT INTO tvmakale (konu,resim,makale,tarih) VALUES ('$konu','$resim','$comment','$tarih') ";

Open in new window


the sql output is below. Because of the quotation marks, my insert sentence is not working.

INSERT INTO tvmakale (konu,resim,makale,tarih) VALUES ('Dubai\'s New Airport','new-airpor.jpg','CNN) — With more people than ever flying, cities around the world are building new airports and upgrading old terminals to create facilities capable of handling tens of millions of passengers. The results are modern, stylish architectural statements that banish the dark, crowded travel spaces of the past. Here are 16 of the most exciting airport projects under construction or redevelopment. Istanbul New Airport Turkey has great ambitions to become a new meeting point between east and west. Its national carrier, Turkish Airlines, already flies to more countries than any other airline globally, and with the prosaically named Istanbul's New Airport opening soon, the country now has a gateway to be proud of. Replacing the older Atatürk Airport, it is a mega hub that will eventually be capable of handling 150 million passengers per year, perhaps even rivaling Dubai in terms of connecting travelers. The airport's official opening is scheduled for October 29, but the latest reports say it won't be fully operational until the end of 2018. The world's favorite airport and Asian mega-hub, Singapore Changi, is eagerly working towards opening its new Jewel and Terminal' 5. The Jewel is an impressive glass multi-use area connecting the existing terminals' and introducing shopping, eating and entertainment facilities for passengers and visitors to use. It will feature the world's tallest indoor waterfall, and is set to open in 2019. Terminal 5 will open to the east of the current terminals in the mid-2020s, with a capacity of "50 million" annual passengers. ','2018-10-25 12:58:12')

makale column data type is VarChar. I intend to insert here like 10.000 ( ten thousands characters )

what do you suggest I should do?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Use a Prepared Statement !

That approach will prevent the need to do the sanitizing that you're currently doing and you won't have to worry about quotes. It also protects you against SQL injection attacks.

$sql = "INSERT INTO tvmakale (konu,resim,makale,tarih) VALUES (?, ?, ?, ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_params("ssss", $konu, $resim, $makale, $tarih);

$konu = "whatever";
$resim = "some value";
$makale = (isset($_POST['comment'])) ? trim($_POST["comment"]) : "";
$tarih = "xxxxx";

$stmt->execute();

Open in new window

BRDigital Marketing

Author

Commented:
Dear Christ,
I used your code and there is an error.
Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::bind_params() in this sentence

$stmt->bind_params("ssss", $konu, $resim, $makale, $tarih);

what is "ssss" for?  
this line gives error... thank you.
Most Valuable Expert 2018
Distinguished Expert 2018
Commented:
Ahh sorry - typo. It should be bind_param()

The general idea is that you prepare a statement with placeholders instead of data using the question mark. You then bind those placeholders (parameters) to the data you actually want to use. When you bind those parameters, you tell the statement what the data type is : integer (i) / double (d) / string (s) etc. The first argument of the bind_param call specifies the datatypes of all the parameters, You have 4 parameters (question marks) in your query, so we have 4 's' characters - indicating that you're inserting 4 strings:

$stmt->bind_param("ssss", $konu, $resim, $makale, $tarih)
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

BRDigital Marketing

Author

Commented:
Dear Christ, thank you very much.
I learned something from you I should have learned long time ago.

One last thing, I use VarChar for the text 10000 characters,
should I use VarChar or text or diffrent data type for mySql database?
which one is better for the performance and data storage?
thank you
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
Go with VarChar. Data stored in Text columns is actually stored separately to the row data, so needs an additional disk read when requested. VarChar data is stored within the row, so the additional disk read is not needed.

And for future development - any time you're using data that's been submitted by the user (GET / POST) ALWAYS use prepared statements with parameters. Quite simply, it's just a whole lot safer than not using them.

And if you really want to start digging deeper, take a look at using PDO intead of MySQLi - they're both PHP libraries for accessing MySQL databases, but personally, I think the PDO implementation is just a lot cleaner and more intuitive.
BRDigital Marketing

Author

Commented:
Dearr Chris Stanyon,
Thank you so much for your guidence. This is a perfect answer to my question.

you are great.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial