Inserting Variable into MySQL database

Question

I'm trying to insert a variable into a mysql database. I've read the various posts about how to do this however these don't seem to work for me, the following code doesn't write anything to the database (the variable seems to break the query) however does if I don't use variables, can anyone tell me what I've doing wrong here?

$dbhost = 'zzzzzzzzz';
$dbuser = 'xxxxxxxxx';
$dbpass = 'yyyyyyyyy';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);


// VARIABLE SETUP;

$variable = 'variable';
$variable = mysql_real_escape_string($variable);

if(! $conn ) {
   die('Could not connect: ' . mysql_error());
}

$sql = 'INSERT INTO db_variables_insert'.
       '(time, variable) '. 
       'VALUES ( "2016-02-19 04:23:44", '$variable')';

mysql_select_db('wwwwww');
$retval = mysql_query( $sql, $conn );

if(! $retval ) {
   die('Could not enter data: ' . mysql_error());
}

echo "Entered data successfully\n";

mysql_close($conn);

Please learn PDO or mysqli and stop using mysql_ already. See: http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Ekin, Feb 20 '16 at 01:17
I have no intention to be mean, the problem with your code does not end with your issue. — Ekin, Feb 20 '16 at 01:19
OK, but your comment is not helpful in any way. Do you have a reference of how to do this "properly"? — user1419810, Feb 20 '16 at 01:21
Yes, please review the answers provided on the link in my first comment. You'll find the reason behind this and many useful links and information. — Ekin, Feb 20 '16 at 01:23

Ekin · Answer 1 · 2016-04-15T03:06:49.470

First of all, for your issue you could try:

$sql = "INSERT INTO db_variables_insert".
       "(time, variable) ". 
       "VALUES ( '2016-02-19 04:23:44', '".$variable."')";

However, you could rewrite this more sane as a prepared statement like:

/* mysqli_* to connect - ** See note on PDO below ** */

$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

/* Prepared statement, stage 1: prepare */
$sql = "INSERT INTO db_variables_insert (time, variable) VALUES (?,?)";
if ($stmt = $mysqli->prepare($sql)) {

    /* Prepared statement, stage 2: bind parameters and execute */
    $time = '2016-02-19 04:23:44';
    // Assuming you already define $variable here

    if ($stmt->bind_param("ss", $time, $variable)) {
        /* Here this bit   ^ can be "i" (integer), "s" (string) etc  */

        $execute = $stmt->execute();

        if($execute === FALSE) {
            echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
        }
        /* ^ you are done executing the sql if no errors */

    } else {
        echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
    }
} else {
    echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* explicit close recommended */
$stmt->close();

Notes:

/** Recommended: Connect with PDO **/

$conn = new PDO('mysql:host=localhost;dbname=my_db;charset=UTF-8','user', 'pwd');

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

^ useful to visit and practice:

score 0 · Accepted Answer · answered Feb 20 '16 at 01:34

You need to use . to concatenate strings in PHP, you can't just put the variable next to the string. And if the variable column is a string, you need to put its value in quotes.

$sql = 'INSERT INTO db_variables_insert'.
       '(time, variable) '. 
       'VALUES ( "2016-02-19 04:23:44", "' . $variable'")';

You can also write this as a single string with variable substitution, using double quotes around the PHP string.

$sql = "INSERT INTO db_variables_insert
        (time, variable)  
        VALUES ( '2016-02-19 04:23:44', '$variable')";

Inserting Variable into MySQL database

2 Answers2