How to pass in a variable that was created with date() function to a MYSQL table with php

Question

I receive a GET variable named $temp in my php code, after connecting to the server and selecting the correct database, and I am able to pass it into the table using:

 mysql_query("INSERT INTO Temperature (Temperature) VALUES ($temp)");

However if I save a time variable using:

 $time = date('G:i', time());

and try and pas it in with:

mysql_query("INSERT INTO Temperature (Temperature) VALUES ($temp)");

or even:

mysql_query("INSERT INTO Temperature (Time,Temperature) VALUES ($time,$temp)");

I am unable to get it to be passed into my table.

I am echoing both variables so I know they are being saved correctly into the variables. Also, in my table there are two columns named "Time" and "Temperature". The name of the table is "Temperature". Why won't the $time variable get passed in if it is the exact same line of code as $temperature variable except for changing the column name? Also both columns are set to recieve varchar (20) could this be the issue?

Stop using deprecated ´mysql_*` API. use mysqli_* or PDOwith prepared Statements. — Jens
– Jens, Commented Mar 19, 2016 at 20:58
Also, the table is set up to receive varchar(20) in the "Time" Column. could this be the issue? — Dan
– Dan, Commented Mar 19, 2016 at 21:05
@Dan, you should change the column type to TIME or sooner or later you will run into inconsistent data. — Jacco
– Jacco, Commented Mar 19, 2016 at 21:08

Iwnnay · Accepted Answer · 2016-03-19 21:14:48Z

1

You're very close to having it right in your example. In this line you need quotes in your query around your values.

mysql_query("INSERT INTO Temperature (Time,Temperature) VALUES ($time,$temp)");

So it would look like this:

mysql_query("INSERT INTO Temperature (Time,Temperature) VALUES ('$time', '$temp')");

But this way of creating a query is soon to be deprecated for the easier to use and more modern method of using PDO. Using PDO would looking something like this:

$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// prepare sql and bind parameters
$stmt = $conn->prepare('INSERT INTO Temparature (`time`, `temperature`) 
VALUES (:time, :temperature)');
$stmt->bindParam(':time', $time);
$stmt->bindParam(':temperature', $temp);

// insert a row
$temp = "34";
$time = date('G:i', time());
$stmt->execute();

answered Mar 19, 2016 at 21:14

Iwnnay

2,01818 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

Nevermind, with the two variables it did need the quotes. Not sure why it would work without quotes with one variable. Thank you very much

Iwnnay Over a year ago

My guess is that your $temp var is an integer and your column type is integer or numeric. You don't need quotes for that. But usually a time is a string. Which does need quotes.

Iwnnay Over a year ago

Adding the quotes won't hurt your chances of putting it in the database, either. But that'd be another advantage to using PDO, it wouldn't try to put Ints in as strings and force the database to handle it.

Jacco · Accepted Answer · 2016-03-19 21:18:12Z

0

A time column in MySQL expects a string in the format 'hh:mm:ss'.

To do this, you could do:

$timestamp = time();
$time = date('G:i', $timestamp); // assuming you need $time somewhere else
mysql_query("INSERT INTO Temperature (Time,Temperature) VALUES ( ".date('H:i:s', $timestamp).", $temp)");

If you do not need $time for any other purpose than inserting it in the database, you could use:

mysql_query("INSERT INTO Temperature (Time,Temperature) VALUES (NOW(), $temp)");

And let MySQL do the time creation for you.

To only select the hours and minutes do:

SELECT TIME_FORMAT(Time, '%H:%i') FROM Temperature;

See the MySQL Documentation for more info on formatting date and time as strings.

edited Mar 19, 2016 at 21:18

answered Mar 19, 2016 at 21:06

Jacco

23.9k18 gold badges92 silver badges106 bronze badges

2 Comments

Dan Over a year ago

I only want the "Time" column to be the hour and minute. I initialized it to take the value VARCHAR(20), not of type time

Jacco Over a year ago

Best practice is to only SELECT hours and minutes, but INSERT hours:minutes:seconds. I've updated the answer.

Collectives™ on Stack Overflow

How to pass in a variable that was created with date() function to a MYSQL table with php

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related