asked on

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

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?

Chris Stanyon

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

ASKER

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.

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

Chris Stanyon

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.

ASKER

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

you are great.