Jump to content

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


AJM2
 Share

Go to solution Solved by Barand,

Recommended Posts

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 
Link to comment
Share on other sites

(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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.