forum.coppermine-gallery.net

Support => Looking for Freelancers / Paid help => Topic started by: hama on November 16, 2004, 09:57:39 pm

Title: "counter-code"
Post by: hama on November 16, 2004, 09:57:39 pm

With this code in the head.php:

Code: [Select]

// gallery_pics
$gallery_pics = implode('', $_gallery->fetch_first('SELECT COUNT(*) FROM `cpg130_pictures`'));
   
// gallery hits
$gallery_hits = implode('', $_gallery->fetch_first('SELECT SUM(`hits`) FROM `cpg130_pictures`'));
and this code in the left_login.tpl:

Code: [Select]

<li><a href="http://www.dorfschule.ch/copper/index.php">Gallery-Fotos</a> ($gallery_pics)</li>
<li><a href="http://www.dorfschule.ch/copper/index.php">Gallery-Views</a> ($gallery_hits)</li>
of my website http://www.orst.ch (login with test test) I can see on the startpage how many pictures are in the gallery and how many views that the gallery has.

The problem is that with this code "the counter" on the startpage counts the private albums/categories too. So the numbers on the startpage and the numbers in the gallery are not the same.

All private albums are in cat=9.

Has anybody a good idea? You will get a dvd for "the" solution.

hama

Title: Re: "counter-code"
Post by: kiig on November 16, 2004, 11:15:12 pm

not sure about "cat=9"... but looking at the different tables made me think that the field AID indicates the category, - or album...

so if select count(*) from cpg130_pictures gives you the total count, -
then try select count(*) from cpg130_pictures where aid != 9

and likewise with the number of hits :
select sum(hits) from cpg130_pictures where aid != 9

I'm guessing here, - but I don't think the database structure is more complex than that... but still, - a guess.

If it's wrong... :-) I'll look again :-)

Not very flexible, - and very hardcoded, - but if I'm right, - this will solve your problem.

Kim Igel.

Title: Re: "counter-code"
Post by: hama on November 16, 2004, 11:26:06 pm

Nothing changes by adding your idea.  :\'(

I still hope for a good solution.

Thanks

hama

Title: Re: "counter-code"
Post by: kiig on November 16, 2004, 11:43:39 pm

okok.. looked a bit more... seems like there is a table of categories, - and a table of albums... albums belong to a categori, - and all pictures belong to an album....

So selecting from pictures, - omitting the albums that are marked as part of the category that belongs to users...

I've tried this:
SELECT count(  *  )
FROM  `cpg11d_pictures`
WHERE aid NOT
IN (  'SELECT aid
FROM cpg11d_albums
WHERE category =14' )

My tables have other names, - and you probably need to change the category= part, - if cat=9 means the database value '9', - then put that in there....

This time I'm not saying I think it works.... :-)
(try fiddling with it in a mysql Myadmin if you have one.....)

I'm not used to Mysql, - and I might have missed out on a couple of quotes or something, - but the above works in my MySql admin.... try it...

Kim

Title: Re: "counter-code"
Post by: Nibbler on November 17, 2004, 12:22:12 pm

OK, that query will only work on mysql 4.1+ due to use of the subquery so likely will not work on most ppls webservers. To exclude cat 9 you change the query to:

Code: [Select]

SELECT COUNT(*) FROM cpg130_pictures AS p, cpg130_albums AS a WHERE p.aid = a.aid AND a.category <> 9
Similar thing for the second query.

Title: Re: "counter-code"
Post by: hama on November 17, 2004, 01:50:15 pm

@Nibbler: Yes, that's the solution. --> solved.

Send me a "personal message" with your address and the name of the dvd. I'll do the amazon-order in the next few days.

@Kim: Thanks for your help. If you send me your address too, I'll send you a CD.

Thanks to all for the professional help!

hama

Title: Re: "counter-code"
Post by: kiig on November 17, 2004, 02:30:51 pm

thanx for the insight Nibbler.... :-)

@Hama, - don't worry about the CD... :-) I came close, - but no cigar... :-) and as I said, - I'm not used to MySql.