How can I insert php variables in a sql query?

Question

I've found a lot of answers for this that don't seem to work for me. When I have apostrophes around the variables $message and $email and $date like

    'VALUES ('$message', '$email', '$date')';

it tells me

Parse error: syntax error, unexpected '$message' (T_VARIABLE)

When I remove them, I get something like Could not enter data: Unknown column '$message' in 'field list'. I've tried to insert

    $message = mysql_real_escape_string($message);    
    $email = mysql_real_escape_string($email);
    $date = mysql_real_escape_string($date);

with " " around the variables like

'VALUES ("$message", "$email", "$date")';

which gets rid of the error message but now, instead of the input from the html form, i'm getting literally "$message" in my database.

What is it that I'm doing wrong? My simple objective is just to take an email, a message, and the date, and put it in a database. Please help! Thank you!

Here is the complete code I have:

     <?php
if($_POST && isset($_POST['email'], $_POST['essay'])) {


    $dbhost = 'localhost';
    $dbuser = 'root';
    $dbpass = 'password';

    $conn=mysql_connect($dbhost, $dbuser);
    if(! $conn)
    {
        die('Could not connect: ' . mysql_error());
    }

    mysql_select_db("Ink", $conn);

    date_default_timezone_set("America/New_York");
    $message = $_POST['essay'];
    $email = $_POST['email'];
    $date = date("y-m-d h:i:sa");


    $sql = 'INSERT INTO inktable '.
            '(writings, email, date) '.
            'VALUES ('$message', '$email', '$date')';

    mysql_select_db('ink');
    $retval = mysql_query($sql, $conn);
    if(! $retval)
    {
        die('Could not enter data: ' .mysql_error());
    }

    mysql_close($conn);
}
?>

Solution 1

I think you need those little dots:

  ('.$message.', '.$email.', '.$date.')';

Or:

 ("'.$message.'", "'.$email.'", "'.$date.'")';

Solution 2

Also, it is better to use the PDO, as the easiest way to minimize problems I think ;) Using prepared statements, you can minimize the risk of SQL injections, as Biffen said.

http://php.net/manual/en/ref.pdo-mysql.php

For example, your code with PDO:

<?php

if($_POST && isset($_POST['email'], $_POST['essay'])) {

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'Ink';

date_default_timezone_set("America/New_York");

try {
// Try to connect
    $dbh = new PDO('mysql:host='.$dbhost.';dbname='.$dbname, $dbuser, $dbpass);

// Data 
    $message = $_POST['essay'];
    $email = $_POST['email'];
    $date = date("y-m-d h:i:sa");

// query
    $sql = "INSERT INTO inktable (message,email,date) VALUES (:message,:email,:date)";
    $q = $dbh->prepare($sql);
    $q->execute(array(':message'=>$message,
                      ':email'=>$email,
                      ':date'=>$date));

// Null connection
    $dbh = null;
} catch (PDOException $e) { // if exception
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

?>

Comments

[Biffen]

Has anyone ever told you about SQL injections?

[Oli Soproni B.]

try INSERT INTO inktable '. '(writings, email, date) '. 'VALUES ('{$message}', '{$email}', '{$date}')'; and use PDO if you can

[frunkad]

Use mysqli_ instead of mysql_ . Also you can use pdo. Mysql is deprecated and not at all recommended

[tehcpu]

Fix it ;) First code was without check, sorry. P.s.: trouble was actually in this line: $dbh = new PDO('mysql:host='.$dbhost.';dbname='.$dbname, $dbuser, $dbpass); Pay attention to quotes, @jojo-wang