3

I am trying to write an POLYGON into an column named SHAPE which is a geometry type. And created query which looks

$sql = "INSERT INTO rokka(zone,district,location,area,SHAPE) 
VALUES ('$zone','$district','$location','$area',PolygonFromText('$polywrite'))";

The variable $polywrite comes as

var polygon = 'Polygon((' + northeastlngLat[0] + ' ' + northeastlngLat[1] + ',' + northwestlngLat[0] + ' ' + northwestlngLat[1] + ',' + southwestlngLat[0] + ' ' + southwestlngLat[1] + ',' + southeastlngLat[0] + ' ' + southeastlngLat[1] + ',' + northeastlngLat[0] + ' ' + northeastlngLat[1] + '))';
$polywrite = "<script>document.write(polygon)</script>";

The arrays contain the coordinates. And when executing

if ($connect->query($sql) === TRUE) {
    session_start();
    header('location: admin.html');
    session_destroy();
} else {
        echo "Error: " . $sql . "<br>" . $connect->error;
    }

The error it shows is

Column 'SHAPE' cannot be nullError: INSERT INTO rokka(zone, district,location,area,SHAPE) VALUES ('Rapti', 'Rolpa','Liwang','603750', PolygonFromText('Polygon((85.06290725100276 28.00935569163021,85.06392404943365 28.0093478254886,85.06393735099435 28.01070145242192,85.06292053985693 28.010709319009717,85.06290725100276 28.00935569163021))'))
Column 'SHAPE' cannot be null

But whenever I run the query

INSERT INTO rokka(zone, district,location,area,SHAPE)
VALUES ('Rapti', 'Rolpa','Liwang','603750', PolygonFromText('Polygon((85.06290725100276 28.00935569163021,85.06392404943365 28.0093478254886,85.06393735099435 28.01070145242192,85.06292053985693 28.010709319009717,85.06290725100276 28.00935569163021))'))

in my database directly It works exactly as it should. All the values are written into the respective columns. Please help me figure out what i am doing wrong.

8
  • 2
    Are you sure in line $polywrite = "<script>document.write(polygon)</script>"; It looks like, you generate a part of html document and writes polygon via javascript. Why not to use polygon directly in $sql = ... ? Jul 29, 2015 at 11:24
  • The columns specified in INSERT statement in the error message do not match the columns in the statement you said you assigned to $sql. Is it being changed somewhere? Jul 29, 2015 at 12:14
  • dimitry.v.kiselev in the polygon the coordinates are to be transfromed that i do using proj4js (javascript library), hence need to get the value from javascript part of page.
    – poshan
    Jul 30, 2015 at 2:30
  • @EvilGenius the query has been edited. Mistakenly I had posted different query and results.
    – poshan
    Jul 30, 2015 at 2:44
  • 1
    why not to use proj4php? Jul 30, 2015 at 7:06

2 Answers 2

6

It seems you try to persist your entity and its id remains null. That's why your get a constraint violation error.

  • To simply solve this error I suggest you to try create your database table using auto-incrementation option on your SHAPE field. It should solve your problem, I guess.

    OR

  • Reading the documentation on LAST_INSERT_ID() I would suggest that the value is only updated after the last trigger runs. I also created a trigger which inserts the result of LAST_INSERT_ID() into another table and it would always insert the id of the row inserted by the INSERT statement before or 0 if there was no previous INSERT.

From within an insert or update trigger you can always refer to the state after the statement by using NEW.column where column is a column-name of your table. See the documentation for examples

4
  • @Samane The id is already an auto increment field.
    – poshan
    Aug 6, 2015 at 6:11
  • @poshan OK, did you check the second case?
    – Samane
    Aug 6, 2015 at 7:09
  • In bande khoda che gonahi karde , hey ru soalesh comment mizarim fek mikone javabi chizi umade. خخخخخ malume taze-varedi. inja ziad be ye nafar emtiaz bedi stackoverflow mifahme emtiaz manfi mide
    – Dariush
    Aug 8, 2015 at 6:40
4
+25

In line $polywrite = "<script>document.write(polygon)</script>" you are trying to use javascript code while it is imposible in php side. All php scripts run before javascript.
You can use proj4php as mentioned in the comments.
Another solution is to fill the polywrite variable at javascript side and create an Ajax request sending `polywrite' to a php page.

javascript side:

var polygon = 'Polygon((' + northeastlngLat[0] + ' ' + northeastlngLat[1] + ',' + northwestlngLat[0] + ' ' + northwestlngLat[1] + ',' + southwestlngLat[0] + ' ' + southwestlngLat[1] + ',' + southeastlngLat[0] + ' ' + southeastlngLat[1] + ',' + northeastlngLat[0] + ' ' + northeastlngLat[1] + '))';
$.ajax({
  url: "test.php",
  data: { 
    "polywrite": polygon
  },
}).done(function() {
   // do something when done
});

php page:

$polywrite = $_GET['polywrite']; 
$sql = "INSERT INTO rokka(zone,district,location,area,SHAPE) 
   VALUES('$zone','$district','$location','$area',PolygonFromText('$polywrite'))";
...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.