Greetings,

1. Thanks for getting the 5.1 version of this out: wonderful. I'm using it especially so users and groups can set their own location. But there's a problem:

2. Whenever I try to set location via a map (apart from the very first one I did for the main admin account) I'm getting e.g.:

user warning: Duplicate entry '0' for key 1 query: INSERT INTO location (eid, type, latitude, longitude, source) VALUES (7, 'user', 54.15278427641255, -2.116241455078125, 1) in c:\program files\easyphp1-8\www\ukcohouse\drupal-5.1\includes\database.mysql.inc on line 172.

Which is odd, because those values don't exist in the table. Having done a little searching on this, I found posts like:

http://drupal.org/node/65665

which show similar problems - and if the solution is anything like this, its sounding a little too risky to me. I was wondering if there's some obvious reason for the problem? Something easy I can do in phpMyAdmin, as the above post suggests? (E.g. delete, rebuild table, change primary keys?)

Does the above mean its trying to write over the first entry rather than adding a new one?

I'm using Gmap with the location module / windows / mySQL.

Thanks...

Dan

CommentFileSizeAuthor
#10 gmap_location_1.patch1.88 KBdenney
#9 gmap_location_0.patch1.39 KBdenney
#7 gmap_location.module.txt26.1 KBemeij

Comments

danaktivix’s picture

danaktivix commented

Just to add: I don't get this problem when adding location data for other nodes. Just chosen a location via a google map for an organic group node, and that was fine. New entry into the location table, no problem at all. Hmm...

danaktivix’s picture

danaktivix commented
Title: Database error when setting user location via Gmap » Database error when setting user location via Gmap: one answer

Greetings,

Not sure where I'm supposed to be posting this, but I've cobbled together a fix for this problem. With this addition, users can now set their location via their 'my account' link. Line 622-623 in gmap_location.module. The problem was that the primary key (lid in the 'location' table) wasn't being set, so I just pull out the maximum value of that field and add one. Only other change, then, is to add lid into the INSERT query.

$query = db_query("SELECT MAX(lid) FROM {location}");

	$lid = db_result($query);
		
	$lid++;
	
        db_query("INSERT INTO {location} (eid, lid, type, latitude, longitude, source) VALUES (%d, %d, 'user', %s, %s, 1)",
          $user->uid,
          $lid,
	  gmap_decimal($edit['gmap_location_latitude']),
          gmap_decimal($edit['gmap_location_longitude']));
tasis’s picture

tasis commented

I had the same problem in ver. 5.1 and I would like to confirm that it is due to the lack of auto_increment in the key of the 'location' table.

It can be fixed simply by executing the following statement in the MySQL database:

ALTER TABLE `location` CHANGE `lid` `lid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT

The 'include' module needs to be updated and include the auto_increment during the creation of the table.

Lupin3rd’s picture

Lupin3rd commented

The right way to do this is use db_next_id function, then at line 782 of gmap_location.module use this code:

else {
      $lid = db_next_id('{location}_lid');
      // based on location.module LOCATION_LATLON_USER_SUBMITTED=1
      db_query("INSERT INTO {location} (eid, lid, type, latitude, longitude, source) VALUES (%d, %d, '%s', '%f', '%f', '%d')",
      $object->eid, $lid, $type, $object->gmap_location_latitude,$object->gmap_location_longitude, 1);
          }

In this way the lid is stored in sequences table.

emeij’s picture

emeij commented

No, this will *not* fix things. When you either enable AUTO_INCREMENT or use db_next_id the table will not update and you will get multiple entries for the same user. IMO the bug is not properly setting $user->gmap_location_set. Hence, the module will call `INSERT INTO location` instead of `UPDATE location`.

Just my 2p..

Edgar Meij

niclasforsen’s picture

niclasforsen commented

Seeing the same thing as Edgar. When a user edits their location, no marker for their current location is seen and when they add a new marker, they suddenly have two, then three, then x markers on the usermap. The gmap_location_set doesn't seem to be assigned to the user.

Also, in IE, link to userprofile in info window isn't clickable.

Don't seem to be able to change marker image for users and nodes, all are shown as red.

Other than that, I'm happy with how everything goes forward and that the map no longer defaults to 0,0 :)

Niclas

emeij’s picture

emeij commented
Status: Active » Reviewed & tested by the community
StatusFileSize
new gmap_location.module.txt26.1 KB

I have a fix.. Update gmap_location.module to call the DB first:

function gmap_location_user($op, &$edit, &$user, $category = NULL) {

    $result=db_query("SELECT * FROM {location} WHERE eid = %d AND type='user'", $user->uid);
    while ($u=db_fetch_object($result)) {
      $user->gmap_location_longitude = $u->longitude;
      $user->gmap_location_latitude = $u->latitude;
      $user->gmap_location_set = true;
    }

  if (variable_get('gmap_user', 0) && user_access('set user location')) {
    switch ($op) {
      case 'categories':
...

The diff:

574a575,581
>
> $result=db_query("SELECT * FROM {location} WHERE eid = %d AND type='user'", $user->uid);
> while ($u=db_fetch_object($result)) {
> $user->gmap_location_longitude = $u->longitude;
> $user->gmap_location_latitude = $u->latitude;
> $user->gmap_location_set = true;
> }

Can anyone else please verify? I've attached the modified version of the file for convenience (chop off the '.txt').

emeij’s picture

emeij commented

Two additional notes; (1) The snippet is incorrect: the DB call should be after the if (variable_get('gmap_user', 0) && user_access('set user location')) {:

function gmap_location_user($op, &$edit, &$user, $category = NULL) {
  if (variable_get('gmap_user', 0) && user_access('set user location')) {

    $result=db_query("SELECT * FROM {location} WHERE eid = %d AND type='user'", $user->uid);
    while ($u=db_fetch_object($result)) {
      $user->gmap_location_longitude = $u->longitude;
      $user->gmap_location_latitude = $u->latitude;
      $user->gmap_location_set = true;
    }

    switch ($op) {
      case 'categories':
...

And, second, you do need to enable either AUTO_INCREMENT or apply the above mentioned db_next_id patch.

Edgar

denney’s picture

denney commented
StatusFileSize
new gmap_location_0.patch1.39 KB

I have tested the above code with the db_next_id patch and they work fine. There doesn't appear to be any other side effects to using this method (besides the extra DB call).

I have also attached a patch that includes emeij's code and Lupin3rd's db_next_id patch.

The patch was made against the latest 5.x-1.x-dev release (13.03.07).

denney’s picture

denney commented
Status: Reviewed & tested by the community » Needs review
StatusFileSize
new gmap_location_1.patch1.88 KB

The above patch fixed the problem when a single user was updating their profile. It didn't fix the problem of when a new user tried to set their location AFTER someone else had set their location.

This new patch fixes that.

bdragon’s picture

bdragon commented
Assigned: Unassigned » bdragon
Status: Needs review » Fixed

I committed a temporary fix based on #10. (Thanks, and sorry I forgot to attribute in the log. Doh!)
I say "temporary" because the code in question is going away as soon as
http://drupal.org/node/125001
is committed.

The fix will be in the next snapshot of DRUPAL-5.

Please help review http://drupal.org/node/125001. Thanks.

--Brandon

bdragon’s picture

bdragon commented
Title: Database error when setting user location via Gmap: one answer » [master] User location

Marking as master issue regarding the user location brokenness.

bdragon’s picture

bdragon commented

Duplicates marked:
http://drupal.org/node/119396

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)