INSERT private message system PDO php

I am trying to build a little private message system in PHP, i am using PDO as connection to the database. i have got the most to work excpet the most important part.

I cannot get the insert message to write anything to the database so i would love some help.

I will first post my database so you all can see what we are working with here. starting with my users

id int(10) UN AI PK 
username varchar(30) 
password varchar(100) 
regdate datetime

This is my message table which im trying to write the message to

id int(11) AI PK 
from_user varchar(45) 
to_user varchar(45) 
subject varchar(400) 
message text 
date date 
read tinyint(4)

I have tried a couple of diffrent code to get it to work i will now paste the code that according to my error check says that the message was succesfully sent...

$sql = "
INSERT INTO private_messages VALUES('',$user, 
$to_user','$subject',$message','$date','0')
";

    echo "Youre message was succesfully sent...";

But this does not work and i know that this is probably not the right way when using PDO.

I have also tried this

$query =$dbh->prepare("INSERT INTO private_messages(user, to_user, subject, 
message,
date);
VALUES('',$user, $to_user','$subject',$message','$date','0')
");

$query->bindParam(":user", $user);
$query->bindParam(":to_user", $to_user);
$query->bindParam(":subject", $subject);
$query->bindParam(":message", $message);

if ($query->execute()) {
    $success = true;
}

but then i get Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064

I will also post the form im using if that can be of any value

echo "
<form action='compose.inc.php' method='POST'>
<table>
<tr>
<td>To: </td>
<td><input type='text' name='to_user'/></td>
</tr>
<tr>
<td>Subject: </td>
<td><input type='text' name='subject'/></td>
</tr>
<tr>
<td>Message:</td>
<td><textarea name='message' rows='10' cols='50'></textarea></td>
</tr>
<tr>
<td></td>
<td colspan='2'><input type='submit' name='submit' value='Send Message'/>
</td>
</r>
</table>
</form>

  ";

When doing this message system ive been following this https://www.youtube.com/watch?v=BED_yMC7qv0 , its using mysqli and i have been trying to decode it to PDO, i think the problem lies therein

So short recap My error check says that my message are sent. Nothing gets written to my database. im trying to use PDO.

If a i cant find a solution i would love to get pointers to tutorial or books or anything that are about message system with PDO. Best regards /Robert

Solution

Instead of inserting the variables into the query you do this by binding them.

$query =$dbh->prepare("INSERT INTO private_messages (user,to_user,subject,message,date)
VALUES(:user,:to_user,:subject,:message,NOW())");

$query->bindParam(":user", $user);
$query->bindParam(":to_user", $to_user);
$query->bindParam(":subject", $subject);
$query->bindParam(":message", $message);

if ($query->execute()) {
    $success = true;
}

Also you should match the amount of values to the amount of columns.