You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

[AJM2]

For the life of me I can not figure out the syntax error. I was thinking it was related to DateTime field. The data comes over as a string from the api, but converting to time doesn't work. The field in the database is DateTime. Any suggestions would be appreciated.

The data from the api for reference.

array(1) {
  [0]=>
  array(3) {
    ["macAddress"]=>
    string(17) "FA:F5:C2:98:6F:29"
    ["lastData"]=>
    array(27) {
      ["dateutc"]=>
      int(1642622880000)
      ["tempinf"]=>
      float(77.9)
      ["humidityin"]=>
      int(38)
      ["baromrelin"]=>
      float(29.956)
      ["baromabsin"]=>
      float(29.132)
      ["tempf"]=>
      float(77.2)
      ["battout"]=>
      int(1)
      ["humidity"]=>
      int(47)
      ["winddir"]=>
      int(247)
      ["windspeedmph"]=>
      float(5.6)
      ["windgustmph"]=>
      float(8.1)
      ["maxdailygust"]=>
      float(15.9)
      ["hourlyrainin"]=>
      int(0)
      ["eventrainin"]=>
      int(0)
      ["dailyrainin"]=>
      int(0)
      ["weeklyrainin"]=>
      int(0)
      ["monthlyrainin"]=>
      float(0.52)
      ["totalrainin"]=>
      float(48.862)
      ["solarradiation"]=>
      float(196.47)
      ["uv"]=>
      int(1)
      ["feelsLike"]=>
      float(76.83)
      ["dewPoint"]=>
      float(55.39)
      ["feelsLikein"]=>
      float(77.2)
      ["dewPointin"]=>
      float(50.2)
      ["lastRain"]=>
      string(24) "2022-01-12T02:50:00.000Z"
      ["tz"]=>
      string(15) "America/Chicago"
      ["date"]=>
      string(24) "2022-01-19T20:08:00.000Z"
    }
    ["info"]=>
    array(2) {
      ["name"]=>
      string(18) "My Weather Station"
      ["coords"]=>
      array(5) {
        ["coords"]=>
        array(2) {
          ["lon"]=>
          float(-197.65635809999999)
          ["lat"]=>
          float(38.6587316)
        }
        ["address"]=>
        string(44) "100 Main Street, Anytown, FL 08226, USA"
        ["location"]=>
        string(10) "Anytown"
        ["elevation"]=>
        float(214.7066497802734)
        ["geo"]=>
        array(2) {
          ["type"]=>
          string(5) "Point"
          ["coordinates"]=>
          array(2) {
            [0]=>
            float(-97.65635809999999)
            [1]=>
            float(30.6587316)
          }
        }
      }
    }
  }
}

 

I take the data from the array and assign it to variables. Notice the unsuccessful attempts to convert string to DateTime.

<?php

		$_DateTime = $data[0]['lastData']['date'];
		//$_OldDateTime = $data[0]['lastData']['date'];
		//$_dateTime = strtotime($_OldDateTime);
		$_tempf = $data[0]['lastData']['tempf'];
		$_feelsLike = $data[0]['lastData']['feelsLike'];
		$_stationbarometer = $data[0]['lastData']['baromrelin'];
		$_sealevelbarometer = $data[0]['lastData']['baromabsin'];
		$_dewpoint = $data[0]['lastData']['dewPoint'];
		$_humidity = $data[0]['lastData']['humidity'];
		$_winddir = $data[0]['lastData']['winddir'];
		$_windspeed = $data[0]['lastData']['windspeedmph'];
		$_gust = $data[0]['lastData']['windgustmph'];
		$_maxdailygust = $data[0]['lastData']['maxdailygust'];
		$_hourlyrainrate = $data[0]['lastData']['hourlyrainin'];
		$_dailyrain = $data[0]['lastData']['dailyrainin'];	
		$_weeklyrain = $data[0]['lastData']['weeklyrainin'];
		$_monthlyrain = $data[0]['lastData']['monthlyrainin'];
		$_totalrain = $data[0]['lastData']['totalrainin'];
		$_lastRain = $data[0]['lastData']['lastRain'];
		//$_OldlastRain = $data[0]['lastData']['lastRain'];
		//$_lastRain = strtotime($_OldlastRain);
		$_solar = $data[0]['lastData']['solarradiation'];
		$_battout = $data[0]['lastData']['battout'];

// this is where we insert into the database 
$sql = "INSERT INTO weather_data
		(datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, 
		winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, 
		weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) 
		VALUES (
		$_DateTime,
		$_tempf,
		$_feelsLike,
		$_stationbarometer,
		$_sealevelbarometer,
		$_dewpoint,
		$_humidity,
		$_winddir,
		$_windspeed,
		$_gust,
		$_maxdailygust,
		$_hourlyrainrate,
		$_dailyrain,	
		$_weeklyrain,
		$_monthlyrain,
		$_totalrain,
		$_lastRain,
		$_solar,
		$_battout
		)";	
		
		//mysqli_query($conn, $sql);
		
		if (mysqli_query($conn, $sql)) {
			echo "Success!";    //json_encode(array("statusCode"=>200));
		} 
		else {
			echo "Error: " . $sql . "<br>" . mysqli_error($conn);
		}
		mysqli_close($conn);
?>

Error message:

Error: INSERT INTO weather_data (datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) VALUES ( 2022-01-24T15:40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, 1.3, 2.2, 8.1, 0, 0.161, 0.161, 0.681, 49.024, 2022-01-24T15:34:00.000Z, 29.48, 1 )
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, ' at line 6 

[Barand]

A date time value is a string and therefore needs to be in quotes

VALUES (
        '$_DateTime',
        $_tempf,
        ...

Better still, use prepared queries to protect from SQL injection attacks. Don't put variables in the SQL query string.

[ginerjm]

I don't see the attempt to convert string to date.  The only uses of strtotime have been commented out so I don't see at all how you even get a value for it in your query which is another question.  

[Phi11W]

(Being a Character Representations of their actual Data Type) Datetime literals must be enclosed in single quotes, in exactly the same way as you do for Character literals. 

The error message is definitive. 

Error: INSERT INTO weather_data ... VALUES ( 2022-01-24T15:40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, 1.3, 2.2, 8.1, 0, 0.161, 0.161, 0.681, 49.024, 2022-01-24T15:34:00.000Z, 29.48, 1 )
                                                          \_________/
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, ' at line 6

That said, you should not be embedding variables directly into SQL - you are leaving yourself wide open to SQL Injection Attacked. 
Use a Parameterised Query (Prepared Statement) instead. 

Obligatory XKCD Reference - Little Bobby Tables. 

Regards. 
   Phill  W.

 

[AJM2]

Error: INSERT INTO weather_data (datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) VALUES ( '2022-01-24T16:08:00.000Z', 48, 48, 30.056, 29.232, 45.79, 92, 331, 1.8, 2.2, 8.1, 0, 0.161, 0.161, 0.681, 49.024, 2022-01-24T15:34:00.000Z, 47.22, 1 )
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':34:00.000Z, 47.22, 1 )' at line 22 

This is what I get putting quotes around the DateTime.

[AJM2]

Error: INSERT INTO weather_data (datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) VALUES ( '1643040660', 48, 48, 30.053, 29.23, 45.79, 92, 299, 1.1, 1.1, 8.1, 0, 0.161, 0.161, 0.681, 49.024, 2022-01-24T15:34:00.000Z, 43.8, 1 )
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':34:00.000Z, 43.8, 1 )' at line 22 

This is the error converting string to datetime with strtotime.

[Barand]

Looks like you have a second datetime later on in the query (lastRain). You need to quote both.

[AJM2]

Quoting both datetime fields produces this error.

Unknown column 'tempf' in 'field list'

[Barand]

1 minute ago, AJM2 said:

Quoting both datetime fields produces this error.

No it doesn't. Referencing field names that aren't in the table produces that error.

[AJM2]

Doh!

Thank you and to all others who responded. I will look into parameterized queries.

[ginerjm]

At the same time look into using PDO instead of MYSQLI.  Much easier to use.  Here is a brief example:

$pdo = (connection statement);  // do this ONCE before beginning db operations

$q = 'select col1, col2, col3 from mytable where col1 = :arg1';
$qst = $pdo->prepare($q);
// define the parms
$parms = array(
		':arg1'=>$val1
		);
if (!$qst->execute($parms))
{
	(handle an error condition)
}
else
{
	while($row = $qst->fetch())
	{
		(handle each row of the results)
	}
}

There is very good info on setting up the connection logic in the manual as well as all of the available functions but what I have showed you is enough to do most exercises.