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.

  • 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 = ... ? – dmitry.v.kiselev Jul 29 '15 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? – Evil Genius Jul 29 '15 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 '15 at 2:30
  • @EvilGenius the query has been edited. Mistakenly I had posted different query and results. – poshan Jul 30 '15 at 2:44
  • 1
    why not to use proj4php? – dmitry.v.kiselev Jul 30 '15 at 7:06
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'))";
...
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

  • @Samane The id is already an auto increment field. – poshan Aug 6 '15 at 6:11
  • @poshan OK, did you check the second case? – Samane Aug 6 '15 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 '15 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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