Advanced search  

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Pages: [1] 2   Go Down

Author Topic: Coppermine photos/files in Google Earth  (Read 45232 times)

0 Members and 1 Guest are viewing this topic.

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Coppermine photos/files in Google Earth
« on: January 25, 2006, 03:53:38 pm »

This mod will generate an xml file so that gallery items can be located in Google Earth. I started this mod before working on the Google Maps mod and didn't do much besides generating the xml file and manually adding coordinates into the database. Since working with Maps, I've added a GUI to make it easy to enter coordinates. If you want to make things easier, too, you'll need to incorporate some aspects of the Maps mod here: http://forum.coppermine-gallery.net/index.php?topic=26571 You don't need to generate the first file if you don't want a regular Map.

This actually started out as a mod for minicms content to display its content in Google Earth. And that was based on work on generating an RSS feed for mincms.

Here's the demo: http://takethu.com/g-earth.kml

I'll separate the instructions into different posts for those of us with short attention spans. :)
« Last Edit: January 25, 2006, 04:27:30 pm by TranzNDance »
Logged

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Re: Coppermine photos/files in Google Earth
« Reply #1 on: January 25, 2006, 03:58:58 pm »

Maybe too obvious, but you'll need to install http://earth.google.com/ to check out the results. At this time it's not available on all OS's so make sure you can use it.
Logged

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Re: Coppermine photos/files in Google Earth
« Reply #2 on: January 25, 2006, 03:59:21 pm »

Your webhost server will need to be able to know how to handle kml files. Otherwise, it will show a regular text file instead of opening a call to Google Earth on your computer.

In the .htaccess file, add the following lines:
Code: [Select]
AddType text/vnd.google-earth.kml+xml kml
AddType text/vnd.google-earth.kmz kmz
Logged

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Re: Coppermine photos/files in Google Earth
« Reply #3 on: January 25, 2006, 04:02:12 pm »

Create a file with an extension *.kml. In it, put this code:
Code: [Select]
<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.google.com/earth/kml/2.0">
<NetworkLink>
<name>Name of Your Gallery</name>
<Url>
<href>http://yourgallery.com/mapping.php</href>
</Url>
<visibility>1</visibility>
</NetworkLink>
</kml>

Make sure to edit the stuff between the name and href tags accordingly.
Logged

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Re: Coppermine photos/files in Google Earth
« Reply #4 on: January 25, 2006, 04:05:25 pm »

Create a mapping.php file. You can call it whatever you want as long as it matches what's in the href tags in the previous step.

Put in this code:
Code: [Select]
<?php
/**************************************************
  This program is free software; you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation; either version 2 of the License, or
  (at your option) any later version.
  *************************************************
  Coppermine version: 1.4.6
  $Revision: 1.3 $
  $Author: tranzndance $
  $Date: 2006/06/02
    
  ************************
    Mod: Coppermine files mapped in Google Earth
For use in conjunction with: Coppermine for Google Maps mod:
http://forum.coppermine-gallery.net/index.php?topic=26571.0
That mod includes changes to the infrastructure to add coordinates.

http://TakeThu.com
***************************************************/
define('IN_COPPERMINE'true);
require(
'include/init.inc.php');

$query implode(' AND',$query);

if(
count($FORBIDDEN_SET_DATA) > ){
    
$forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(","$FORBIDDEN_SET_DATA).")";
} else {
    
$forbidden_set_string '';
}

$order "ORDER BY pic_latitude DESC ";
$query "SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude
FROM 
{$CONFIG['TABLE_PICTURES']} as pictures, {$CONFIG['TABLE_ALBUMS']} 
WHERE pictures.aid = 
{$CONFIG['TABLE_ALBUMS']}.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0 
$query $forbidden_set_string 
$order;";
$result cpg_db_query($query);

if (!
mysql_num_rows($result))
cpg_die(CRITICAL_ERROR$lang_errors['non_exist_ap'], __FILE____LINE__);
$place mysql_fetch_array($result);
mysql_data_seek($result,0); //put the pointer back to the first entry
 
header('Content-type: text/xml; charset=UTF-8'true);
header("Pragma: public"); 
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    print <<<EOT
<?xml version="1.0" encoding="UTF-8"?>

<kml xmlns="http://www.google.com/earth/kml/2.0" charset="UTF-8">
<Document>
<name>{$CONFIG['gallery_name']}</name>
<description>{$CONFIG['gallery_description']}</description>
EOT;

while ($place = mysql_fetch_array($result)) {
    $CURRENT_PIC_DATA = $place; //send a copy to get_pic_url it messes with the vars
    $place['thumb_url'] = get_pic_url($CURRENT_PIC_DATA, 'thumb');
    $place['thumb_link'] = 'displayimage.php?pos='.(-$place['pid']);
    $place['caption'] = strtr($place['caption'],$HTML_SUBST_DECODE); //used to reverse Coppermines init.inc.php gpc processing
    $RFC822 = '%a, %d %b %y %T %Z';
    $place['modified'] = localised_date($place['modified'], $RFC822);

if ($place['title'] == NULL) {
$place['title'] = $place['filename'];
}

    print <<<EOT
<Placemark>
<name>{$place['title']}</name>
<description>
<![CDATA[
{$place['content']}
<a href="{$CONFIG['ecards_more_pic_target']}{$place['thumb_link']}">[more]</a>
<br/>
<p><a href="{$CONFIG['ecards_more_pic_target']}{$place['thumb_link']}"><img src="{$CONFIG['ecards_more_pic_target']}{$place['thumb_url']}"/></a></p>
<br/>
{$place['modified']}
]]>
</description>
<Point>
<extrude>1</extrude>
<altitudeMode>relativeToGround</altitudeMode>
<coordinates>{$place['pic_longitude']},{$place['pic_latitude']}</coordinates>
</Point>
</Placemark>
EOT;
}

mysql_free_result($result);
ob_end_flush();
?>
</Document>
</kml>

2006-06-02: Fixed query to account for new coordinates fields in albums table, resulting in ambiguity and mysql error.
« Last Edit: June 03, 2006, 07:55:59 am by TranzNDance »
Logged

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Re: Coppermine photos/files in Google Earth
« Reply #5 on: January 25, 2006, 04:09:35 pm »

If you can't get your server to handle the kml file, you will need to provide the URL to the mapping.php file (or whatever you named it) to people so they can manually enter it in Google Earth. Because that's what the kml file does... it opens the file from the browser or file explorer. The php file (in this case) does all the work.
Logged

tibu

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 59
    • Creative Foto Solutions
Re: Coppermine photos/files in Google Earth
« Reply #6 on: June 03, 2006, 07:04:04 am »

I get an error on your demo site as well as my own. I think Google may have changed something, possibly for line 2:
<kml xmlns="http://earth.google.com/kml/2.0">

I tried changing this in both the g-earth.kml file and the mapping.php file but it still gets an error. I can't figure it out.

Error message attached.
Logged

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Re: Coppermine photos/files in Google Earth
« Reply #7 on: June 03, 2006, 07:57:29 am »

Thanks for bringing it to my attention. It turned out to be a mysql error that arose after some changes I made for the Maps mod. Sorry.

I've updated mapping.php so that it works now.
Logged

tibu

  • Contributor
  • Coppermine regular visitor
  • ***
  • Offline Offline
  • Gender: Male
  • Posts: 59
    • Creative Foto Solutions
Re: Coppermine photos/files in Google Earth
« Reply #8 on: June 04, 2006, 10:40:27 am »

Thanks, works great!
Logged

javog

  • Coppermine newbie
  • Offline Offline
  • Posts: 4
Re: Coppermine photos/files in Google Earth
« Reply #9 on: June 14, 2006, 07:32:50 pm »

I have a problem since I installed the new version of Google Earth.

Google Earth gives me an error message, which says "Fetch of Networklink failed" and it says that my mapping.php file has missing or bad root element.

If I use your demo link everything works perfectly.  ??? ???
Logged

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Re: Coppermine photos/files in Google Earth
« Reply #10 on: June 15, 2006, 06:51:15 am »

Could you post a link to your kml file?
Logged

javog

  • Coppermine newbie
  • Offline Offline
  • Posts: 4
Re: Coppermine photos/files in Google Earth
« Reply #11 on: June 15, 2006, 11:55:24 am »

I don't want to make the public. I will send you the link by PM.
Logged

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Re: Coppermine photos/files in Google Earth
« Reply #12 on: June 15, 2006, 04:52:56 pm »

Well, when I viewed mapping.php, it said the album didn't exist. Please make sure that you have copied the code exactly as posted above. If you have confirmed it, make sure you have files with valid coordinates values. If you still have trouble, please rename a copy of your mapping.php with .txt extension and attach to your next post.
Logged

javog

  • Coppermine newbie
  • Offline Offline
  • Posts: 4
Re: Coppermine photos/files in Google Earth
« Reply #13 on: June 15, 2006, 07:14:54 pm »

I've solved my problem  :)

Since all my albums are private, I had to replace the following part in mapping.php:
Code: [Select]
if(count($FORBIDDEN_SET_DATA) > 0 ){
    $forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
} else {
    $forbidden_set_string = '';
}

with:
Code: [Select]
//if(count($FORBIDDEN_SET_DATA) > 0 ){
//    $forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
//} else {
    $forbidden_set_string = '';
//}

Now, mapping.php doesn't check anymore if the album is private or public.
Logged

PrivatePyle

  • Coppermine newbie
  • Offline Offline
  • Posts: 1
Re: Coppermine photos/files in Google Earth
« Reply #14 on: June 27, 2006, 10:17:53 am »

First of all, thank you so much for this mod... it's really exactly what I've been looking for...


In the code of thte exportfile for google earth, i had to make 2 modifications to make it work with my album....

first of all, i have over 300 pictures with coordinates now.... so the list in google earth became quite long... I wanted to have the links organised in folders named after my coppermine albums...

the second problem was, that i have a lot of special characters (,, and so on...) in my picture descriptions....  those are not accepted in the xml file, so i have to replace them with the HTML-codes, before putting them into the file...


here is the adapted version of your mapping.php script, in case somebody is looking for something like that too...


you can see the result here: http://www.hess.li/cgi-bin/pictures/mapping4.php (sorry, but my webserver doesn't know kml-files... yet...)

Code: [Select]
<?php
/**************************************************
  This program is free software; you can redistribute it and/or modify
  it under the terms of the GNU General Public License as published by
  the Free Software Foundation; either version 2 of the License, or
  (at your option) any later version.
  *************************************************
  Coppermine version: 1.4.6
  $Revision: 1.3 $
  $Author: tranzndance $
  $Date: 2006/06/02
    
  ************************
    Mod: Coppermine files mapped in Google Earth
For use in conjunction with: Coppermine for Google Maps mod:
http://forum.coppermine-gallery.net/index.php?topic=26571.0
That mod includes changes to the infrastructure to add coordinates.

http://TakeThu.com
***************************************************/

//Replace Special Characters MOD
//fill in all the special character that need to be replaced to match the xml standard
//Array(Needle, Replace-Text "HTML", Replace-Text "XML-Tag-Title")
$ReplaceArray[] = Array ("","&auml;","ae");
$ReplaceArray[] = Array ("","&ouml;","oe");
$ReplaceArray[] = Array ("","&uuml;","ue");
$ReplaceArray[] = Array ("","&Auml;","Ae");
$ReplaceArray[] = Array ("","&Ouml;","Oe");
$ReplaceArray[] = Array ("","&Uuml;","Ue");
$ReplaceArray[] = Array ("","&szlig;","ss");
$ReplaceArray[] = Array ("","&eacute;","e");
$ReplaceArray[] = Array ("","&egrave;","e");
$ReplaceArray[] = Array ("","&Eacute;","E");
$ReplaceArray[] = Array ("","&Egrave;","E");
$ReplaceArray[] = Array ("","&ecirc;","e");
$ReplaceArray[] = Array ("","&Ecirc;","E");
$ReplaceArray[] = Array ("","&agrave;","a");
$ReplaceArray[] = Array ("","&Agrave;","A");
$ReplaceArray[] = Array ("","&acirc;","a");
$ReplaceArray[] = Array ("","&Acirc;","A");
$ReplaceArray[] = Array ("","&ocirc;","o");
$ReplaceArray[] = Array ("","&Ocirc;","O");
$ReplaceArray[] = Array ("","&ograve;","o");
$ReplaceArray[] = Array ("","&Ograve;","O");
$ReplaceArray[] = Array ("","&ucirc;","u");
$ReplaceArray[] = Array ("","&Ucirc;","U");
$ReplaceArray[] = Array ("","&ccedil;","c");

function 
uml($ReplaceString,$ReplaceType
    {global $ReplaceArray;
 foreach($ReplaceArray as $Replace)
       
{$ReplaceString ereg_replace($Replace[0],$Replace[$ReplaceType], $ReplaceString);}
 return $ReplaceString;
}

//Replace Special Characters MOD


define('IN_COPPERMINE'true);
require(
'include/init.inc.php');

$query implode(' AND',$query);

if(
count($FORBIDDEN_SET_DATA) > ){
    
$forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(","$FORBIDDEN_SET_DATA).")";
} else {
    
$forbidden_set_string '';
}

$order "ORDER BY foldertitle ASC, pic_latitude DESC ";
$query "SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, pictures.ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude, albums.title AS foldertitle, albums.description  AS folderdesc
FROM 
{$CONFIG['TABLE_PICTURES']} as pictures, {$CONFIG['TABLE_ALBUMS']} as albums
WHERE pictures.aid = albums.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0 
$query $forbidden_set_string 
$order;";
$result cpg_db_query($query);

if (!
mysql_num_rows($result))
cpg_die(CRITICAL_ERROR$lang_errors['non_exist_ap'], __FILE____LINE__);
$place mysql_fetch_array($result);
mysql_data_seek($result,0); //put the pointer back to the first entry
 
header('Content-type: text/xml; charset=UTF-8'true);
header("Pragma: public"); 
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    print <<<EOT
<?xml version="1.0" encoding="UTF-8"?>

<kml xmlns="http://www.google.com/earth/kml/2.0" charset="UTF-8">
<Document>
<name>{$CONFIG['gallery_name']}</name>
<description>{$CONFIG['gallery_description']}</description>
EOT;

while ($place = mysql_fetch_array($result)) {

//Replace Special Characters MOD
//Replace all special characters in the database data, that will be put in the xml stream
    $place['caption'] = uml($place['caption'],1);
    $place['content'] = uml($place['content'],1);
    $place['title'] = uml($place['title'],2);
    $place['foldertitle'] = uml($place['foldertitle'],1);
    $place['folderdesc'] = uml($place['folderdesc'],1);
    $place['modified'] = uml(localised_date($place['modified'], $RFC822),1); //localised_date will return special characters in some languages (german, french...)
//Replace Special Characters MOD
   
   
    $CURRENT_PIC_DATA = $place; //send a copy to get_pic_url it messes with the vars
    $place['thumb_url'] = get_pic_url($CURRENT_PIC_DATA, 'thumb');
    $place['thumb_link'] = 'displayimage.php?pos='.(-$place['pid']);
    $place['caption'] = strtr($place['caption'],$HTML_SUBST_DECODE); //used to reverse Coppermines init.inc.php gpc processing
    $RFC822 = '%a, %d %b %y %T %Z';
 
     
if ($place['title'] == NULL) {
$place['title'] = $place['filename'];
}

   
//FOLDER-Mod
    if($lastfolder==$place['foldertitle']) //detect change of folder title
      {$folderheader = '';
      }

     else{$folderheader = '<Folder><name>'.$place['foldertitle'].'</name><description>'.$place['folderdesc'].'</description>'; //fill in data for the header of the foldertag
  if ($lastfolder!='') //close the folder after each change of the FOLDERTITLE, but not in the first "while-run"
     {$folderheader = '</Folder>'.$folderheader;
         }    
         }

    $lastfolder=$place['foldertitle']; //remember the name of the folder, so a folderchange can be detected in the next "while run"
//Folder-Mod



    print <<<EOT
{$folderheader}
<Placemark>
<name>{$place['title']}</name>
<description>
<![CDATA[
{$place['content']}
<a href="{$CONFIG['ecards_more_pic_target']}{$place['thumb_link']}">[more]</a>
<br/>
<p><a href="{$CONFIG['ecards_more_pic_target']}{$place['thumb_link']}"><img src="{$CONFIG['ecards_more_pic_target']}{$place['thumb_url']}"/></a></p>
<br/>
{$place['modified']}
]]>
</description>
<Point>
<extrude>1</extrude>
<altitudeMode>relativeToGround</altitudeMode>
<coordinates>{$place['pic_longitude']},{$place['pic_latitude']}</coordinates>
</Point>
</Placemark>
EOT;
}


mysql_free_result($result);
ob_end_flush();

?>
</Folder>
</Document>
</kml>
Logged

Cino

  • Coppermine newbie
  • Offline Offline
  • Posts: 7
Re: Coppermine photos/files in Google Earth
« Reply #15 on: September 21, 2006, 09:58:32 pm »

thanks for another great mod.. got it working right away
Logged

pocketmon

  • Coppermine newbie
  • Offline Offline
  • Posts: 12
Re: Coppermine photos/files in Google Earth
« Reply #16 on: December 14, 2006, 03:21:43 pm »

I use PrivatePyle's version, and it did work. However, something strange happens.
Since google earth cannnot load the file, I tried to open the mapping.php in browser,
I saw "Critical error": error while process database query.
Then, I tried to log in, and after entering user/password, the browser was directed to mapping.php,
which produce correct XML output???
So, I found that after logged in, mapping.php runs correctly, but it would run into trouble without loggin in.

The error message was:
Code: [Select]
hile executing query "SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, pictures.ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude, albums.title AS foldertitle, albums.description  AS folderdesc
FROM cpg1410n_pictures as pictures, cpg1410n_albums as albums
WHERE pictures.aid = albums.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0
  AND cpg1410n_albums.aid NOT IN (3,4,5,6,7,8)
ORDER BY foldertitle ASC, pic_latitude DESC ;" on 0

mySQL error: Unknown column 'cpg1410n_albums.aid' in 'where clause'


and debug message was
Code: [Select]
USER:
------------------
Array
(
    [ID] => 2210868b41b7708f60d71fbf180691af
    [am] => 1
    [lang] => chinese_big5
    [liv] => Array
        (
            [0] => 6
            [1] => 3
            [2] => 727
            [3] => 670
            [4] => 669
        )

)

==========================
USER DATA:
------------------
Array
(
    [user_id] => 0
    [user_name] => Guest
    [groups] => Array
        (
            [0] => 3
        )

    [group_quota] => 0
    [can_rate_pictures] => 1
    [can_send_ecards] => 0
    [can_post_comments] => 0
    [can_upload_pictures] => 0
    [can_create_albums] => 0
    [pub_upl_need_approval] => 1
    [priv_upl_need_approval] => 1
    [upload_form_config] => 0
    [num_file_upload] => 5
    [num_URI_upload] => 3
    [custom_user_upload] => 0
    [disk_max] => 0
    [disk_min] => 0
    [ufc_max] => 0
    [ufc_min] => 0
    [has_admin_access] => 0
    [group_name] => Anonymous
    [can_see_all_albums] => 0
    [group_id] => 3
)

==========================
Queries:
------------------
Array
(
    [0] => SELECT extension, mime, content, player FROM cpg1410n_filetypes; (0.001s)
    [1] => select * from cpg1410n_plugins order by priority asc; (0.001s)
    [2] => delete from `coppermine`.cpg1410n_sessions where time<1166145098 and remember=0; (0.001s)
    [3] => delete from `coppermine`.cpg1410n_sessions where time<1164939098; (0.001s)
    [4] => select user_id from `coppermine`.cpg1410n_sessions where session_id=md5("913b77ab5ecc0f9c62685662fbd5ced2f09879e13abe497d60176b8a70761d3d"); (0.002s)
    [5] => select user_id as id, user_password as password from `coppermine`.cpg1410n_users where user_id=0 (0.001s)
    [6] => SELECT MAX(group_quota) as disk_max, MIN(group_quota) as disk_min, MAX(can_rate_pictures) as can_rate_pictures, MAX(can_send_ecards) as can_send_ecards, MAX(upload_form_config) as ufc_max, MIN(upload_form_config) as ufc_min, MAX(custom_user_upload) as custom_user_upload, MAX(num_file_upload) as num_file_upload, MAX(num_URI_upload) as num_URI_upload, MAX(can_post_comments) as can_post_comments, MAX(can_upload_pictures) as can_upload_pictures, MAX(can_create_albums) as can_create_albums, MAX(has_admin_access) as has_admin_access, MIN(pub_upl_need_approval) as pub_upl_need_approval, MIN( priv_upl_need_approval) as  priv_upl_need_approval FROM cpg1410n_usergroups WHERE group_id in (3) (0.001s)
    [7] => SELECT group_name FROM  cpg1410n_usergroups WHERE group_id= 3 (0.001s)
    [8] => update `coppermine`.cpg1410n_sessions set time='1166148698' where session_id=md5('913b77ab5ecc0f9c62685662fbd5ced2f09879e13abe497d60176b8a70761d3d'); (0.003s)
    [9] => DELETE FROM cpg1410n_banned WHERE expiry < '2006-12-15 02:11:41' (0.001s)
    [10] => SELECT * FROM cpg1410n_banned WHERE (ip_addr='61.217.109.159' OR ip_addr='61.217.109.159' OR user_id=0) AND brute_force=0 (0.001s)
    [11] => SELECT aid FROM cpg1410n_albums WHERE visibility != '0' AND visibility !='10000' AND visibility NOT IN (3) (0.001s)
    [12] => SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, pictures.ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude, albums.title AS foldertitle, albums.description  AS folderdesc
FROM cpg1410n_pictures as pictures, cpg1410n_albums as albums
WHERE pictures.aid = albums.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0
  AND cpg1410n_albums.aid NOT IN (3,4,5,6,7,8)
ORDER BY foldertitle ASC, pic_latitude DESC ; (0.002s)
)

==========================
GET :
------------------
Array
(
)

==========================
POST :
------------------
Array
(
)

==========================
Page generated in 3.543 seconds - 13 queries in 0.017 seconds - Album set : AND aid NOT IN (3,4,5,6,7,8) ; Meta set: ;
Logged

pocketmon

  • Coppermine newbie
  • Offline Offline
  • Posts: 12
Re: Coppermine photos/files in Google Earth
« Reply #17 on: December 15, 2006, 03:21:41 pm »

solved by modifying
Code: [Select]
if(count($FORBIDDEN_SET_DATA) > 0 ){
    $forbidden_set_string =" AND {$CONFIG['TABLE_ALBUMS']}.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
} else {
    $forbidden_set_string = '';
}

to
Code: [Select]
if(count($FORBIDDEN_SET_DATA) > 0 ){
    $forbidden_set_string =" AND albums.aid NOT IN (".implode(",", $FORBIDDEN_SET_DATA).")";
} else {
    $forbidden_set_string = '';
}

I don't know much about SQL, but it works.
Logged

Tranz

  • Dev Team member
  • Coppermine addict
  • ****
  • Country: 00
  • Offline Offline
  • Gender: Female
  • Posts: 6149
Re: Coppermine photos/files in Google Earth
« Reply #18 on: December 15, 2006, 05:32:56 pm »

Interesting. Do you not have prefixes in your table names? That might be the reason why cpg1410n_albums.aid couldn't be found. If you don't use prefixes, could you delete the setting in include/config.inc.php and see if the previous code still works?
Logged

pocketmon

  • Coppermine newbie
  • Offline Offline
  • Posts: 12
Re: Coppermine photos/files in Google Earth
« Reply #19 on: December 16, 2006, 10:34:39 am »

Dear TranzNDance,

Your code is fine without any problems. I think this trouble should only happen in PrivatePyle's.

It comes to me when I checking the code below:
Code: [Select]
$query = "SELECT *, pictures.title as title, pictures.caption as content, pictures.filename as filename, pictures.ctime as modified, pictures.latitude AS pic_latitude, pictures.longitude AS pic_longitude, albums.title AS foldertitle, albums.description  AS folderdesc
FROM {$CONFIG['TABLE_PICTURES']} as pictures, {$CONFIG['TABLE_ALBUMS']} as albums
WHERE pictures.aid = albums.aid AND
ABS(pictures.latitude) + ABS(pictures.longitude) > 0
$query $forbidden_set_string
$order;";

The "{$CONFIG['TABLE_ALBUMS']} as albums " should be the cause. I am not familar with SQL, but I read a little English. So I just try and luckily it works.
Logged
Pages: [1] 2   Go Up
 

Page created in 0.029 seconds with 20 queries.