X-Git-Url: https://git.nubati.net/cgi-bin/gitweb.cgi?p=photo-tags.git;a=blobdiff_plain;f=getjson.php;h=ff41ac46ee498655186d30ecd6643786a5bd79c6;hp=cbb9feea559cc144bf756dfb1a89e021e92dd65f;hb=HEAD;hpb=78ab005390d15e706f68d2beabfa0a4ad79e0703 diff --git a/getjson.php b/getjson.php index cbb9fee..ff41ac4 100644 --- a/getjson.php +++ b/getjson.php @@ -1,58 +1,201 @@ + + This file is part of photo-tags. + + Photo-tags 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 3 of the License, or + (at your option) any later version. + + Photo-tags is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with Photo-tags. If not, see . + + **/ + +$N=30; + /* parse ini -file */ $iniarray=parse_ini_file("config.ini"); $DBFILE=$iniarray["fspotdb"]; $usePDO=$iniarray["usePDO"]; +$N=$iniarray["pics_per_page"]; /* end parse ini-file */ -if (isset($_REQUEST["O"])) - $O = "".$_REQUEST["O"].","; -else - $O=""; - -if (isset($_REQUEST["N"])) - $N = "".$_REQUEST["N"]; -else - $N= 25; - if($usePDO) $DB = new PDO("sqlite:$DBFILE"); else $DB = new SQlite3($DBFILE); -$result = $DB->query("SELECT * FROM photos LIMIT $O $N"); -$row = array(); +/* do database query */ +if (isset($_REQUEST["S"])) + { + /* single tag or part of tag */ + $tag = sqlite_escape_string($_REQUEST["S"]); + + $result = $DB->query("SELECT name FROM tags"); + } + else if (isset($_REQUEST["NP"])) + { + /* get +- 3 pics from ordered list to show next to a large image */ -$i = 0; + /* first create a temp table with all images and then use rowid to get +-5 images */ -if(!$usePDO) + if (isset($_REQUEST["T"])) + { + /* single tag or part of tag */ + $tags = $_REQUEST["T"]; + $tags = explode(",",$tags); + $nrtags = count($tags); + + foreach ($tags as $key => $value) + $tags[$key]=sqlite_escape_string(trim($value)); + $tags = "'".implode("','",$tags)."'"; + + $DB->query("CREATE TEMP TABLE NEXTPREV AS SELECT base_uri, filename, p.id as id FROM photo_tags pt, photos p, tags t". + " WHERE pt.tag_id = t.id". + " AND (t.name COLLATE NOCASE IN ($tags))". + " AND p.id = pt.photo_id ". + " GROUP BY p.id HAVING COUNT( p.id )=$nrtags"); + } + else + { + $DB->query("CREATE TEMP TABLE NEXTPREV AS SELECT base_uri, filename, p.id as id FROM photos p"); + }; + + if (isset($_REQUEST["ID"])) + { + $ID=intval($_REQUEST["ID"]); + $result = $DB->query("SELECT * FROM NEXTPREV". + " WHERE rowid > (select rowid from NEXTPREV where id=$ID) -3". + " AND rowid < (select rowid from NEXTPREV where id=$ID) +3"); + } + else + { + $result = $DB->query("SELECT 1 where 1=2"); + } + + } + else if (isset($_REQUEST["ID"]) && !isset($_REQUEST["C"])) { - /* convert results into array */ - $tmp=array(); - while($res = $result->fetchArray(SQLITE3_ASSOC)){ - $tmp[]=$res; - } - $result=$tmp; + $id = intval($_REQUEST["ID"]); + $result = $DB->query("SELECT base_uri, filename, id, description, time FROM photos". + " WHERE id=$id"); + } + else if (isset($_REQUEST["IDT"])) + { /* tags of a single image */ + $id = intval($_REQUEST["IDT"]); + $result = $DB->query("SELECT t.name as name FROM photo_tags pt ". + " LEFT JOIN tags t on t.id=pt.tag_id". + " WHERE pt.photo_id=$id"); + } + else if (isset($_REQUEST["CLOUD"])) + { + $result = $DB->query("SELECT t.name as name, count(*) as count FROM photo_tags pt ". + " LEFT JOIN tags t on t.id=pt.tag_id". + " GROUP BY t.id"); } + else + { + if (isset($_REQUEST["P"])) + $OFFSET = "".(intval($_REQUEST["P"])*$N-$N); + else + $OFFSET = "0"; + + if (isset($_REQUEST["T"])) + { + /* single tag or part of tag */ + $tags = $_REQUEST["T"]; + $tags = explode(",",$tags); + $nrtags = count($tags); -foreach ($result as $res){ + foreach ($tags as $key => $value) + $tags[$key]=sqlite_escape_string(trim($value)); + $tags = "'".implode("','",$tags)."'"; - $row[$i] = $res; - - $i++; - - } + /* individual tags are seperated by ',' */ + /* use and AND query between tags as a default + a good explanation on different ways of doing this can be found at: + http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html + */ -echo json_encode($row); + if (isset($_REQUEST["C"])) + { + $DB->query("CREATE TEMP TABLE TEMPPICS AS SELECT p.id as id FROM photo_tags pt, photos p, tags t". + " WHERE pt.tag_id = t.id". + " AND (t.name COLLATE NOCASE IN ($tags))". + " AND p.id = pt.photo_id ". + " GROUP BY p.id HAVING COUNT( p.id )=$nrtags"); + + if (isset($_REQUEST["ID"])) + { + $ID = $_REQUEST["ID"]; + $result = $DB->query("SELECT count(*) as total, (SELECT rowid from TEMPPICS WHERE id = $ID) as row from TEMPPICS"); + } + else + $result = $DB->query("SELECT count(*) as total, -1 as row from TEMPPICS"); + } + else + { + $result = $DB->query("SELECT base_uri, filename, p.id as id FROM photo_tags pt, photos p, tags t". + " WHERE pt.tag_id = t.id". + " AND (t.name COLLATE NOCASE IN ($tags))". + " AND p.id = pt.photo_id ". + " GROUP BY p.id HAVING COUNT( p.id )=$nrtags". + " LIMIT $OFFSET, $N"); + } + } + else + { + if (isset($_REQUEST["C"])) + { + $DB->query("CREATE TEMP TABLE TEMPPICS AS SELECT id from photos"); + + if (isset($_REQUEST["ID"])) + { + $ID = $_REQUEST["ID"]; + $result = $DB->query("SELECT count(*) as total, (SELECT rowid FROM TEMPPICS WHERE id=$ID) as row FROM TEMPPICS"); + } + else + $result = $DB->query("SELECT count(*) as total, -1 as row FROM TEMPPICS"); + } + else + $result = $DB->query("SELECT * FROM photos LIMIT $OFFSET, $N"); + } + } + +/* encode result as an array */ +$tmp=array(); +if(!$usePDO) + { + /* convert results into array */ + while($res = $result->fetchArray(SQLITE3_ASSOC)) + $tmp[]=$res; + } +else + { + foreach($result as $res) + $tmp[]=$res; + } +$result=$tmp; + +echo json_encode($result); /* close the database */ if($usePDO) $DB=null; else - sqlite_close($DB); + $DB->close(); + ?>