0

How should i insert texts messages in mysql database using PHP.

My Code:

<?php

$message = htmlspecialchars($_POST['message']);
$message = mysqli_real_escape_string($connection, $message);

mysqli_query($connection, "INSERT INTO messages (messages) VALUE ('$message')");
?>

My problem :

When i am entering this sign (°) in message text area, it inserts nothing.

4 Answers4

1

You should prefer prepared statements over escaping string.

$connection = new mysqli('localhost', 'user', 'password', 'database');

if (!($stmt = $connection->prepare('INSERT INTO `messages` (`messages`) VALUES(?);')))
{
  echo "error {$connection->errno} on prepare: {$mysqli->error}";
}


if (!$stmt->bind_param('s', $message))
{
  echo "error {$stmt->errno} on bind param: {$stmt->error}";
}

if (!$stmt->execute())
{
  echo "error {$stmt->errno} on execute: {$stmt->error}";
}
Pinke Helga
  • 6,378
  • 2
  • 22
  • 42
  • Don't you think that this code is a bit bloated? What you say if I show you the way to have error messages without adding three lines of code to the every mysqli statement? – Your Common Sense May 14 '16 at 09:01
  • @YourCommonSense - Introducing the PO into PDO exceptions would be overkill. That's what I'm doing in my own code or recommend advanced coders. Answers should be somehow on the level of the questioner. – Pinke Helga May 14 '16 at 09:21
0

Try to quote the value like that:

mysqli_query($connection, "INSERT INTO messages (messages) VALUE ('$message')");
faulix90
  • 170
  • 9
  • this is only the example, and thats not the problem, i am using quotes, i updated the code also. –  May 14 '16 at 08:31
0

Put this code before you execute your query

mysqli_set_charset($con,"utf8");

and set collation of your column to 

utf8_general_ci
DD77
  • 776
  • 2
  • 8
  • 25
  • what that means, are you sure it will not harm any data in database ? –  May 14 '16 at 08:40
  • It will allow to store special characters to your column. UTF-8 is the dominant character encoding(You can search more about that) and it will not harm any data but for safest way you can take backup of your database before testing anything with it. – DD77 May 14 '16 at 08:45
  • http://stackoverflow.com/questions/341273/what-does-character-set-and-collation-mean-exactly this may help you.. – DD77 May 14 '16 at 08:46
  • @ShubhamTripathi - The command sets explicitly the character set of the *connection*. This affects only the SQL communication, not how the data is stored into the table. The charset of the PHP mysql driver will be set as well as on the mysql server. – Pinke Helga May 14 '16 at 09:05
  • brother can you show me any example of how should i put mysqli_set_charset($con,"utf8"); before my query. And if i change the collation of my column to utf8_general_ci then will i have to change all the queries or anything, will i have to change the varchar limit or length ? Please tell me bro. –  May 14 '16 at 09:07
  • @ShubhamTripathi - When explicitly setting the connection encoding, you usually do that immediately after establishing the connection. – Pinke Helga May 14 '16 at 09:09
-1

Use base64_encode and base64_decode

Here is some example.

$str = '° some special chars ';

$str_encoded = base64_encode($str);
echo $str_encoded; //ready to be inserted to the DB
$str_decoded = base64_decode($str_encoded);
echo  $str_decoded; // retrieved and decoded
Petko Kostov
  • 367
  • 1
  • 9