SQL help

sbillard

I am implementing album publishing but I have a problem wiht a SQL query.

Currently I have a query to return the number of subalbums: `$sql = "SELECT COUNT(id) FROM ". prefix("albums") ." WHERE parentid <> "NULL"";`

With the implementation of publishing, the some albums are not displayed. They are marked with the field 'show' set to zero. I would like to change the above query to exclude those records. However I keep getting SQL errors when I use `$sql = "SELECT COUNT(id) FROM ". prefix("albums") ." WHERE parentid <> \"NULL\" AND show = 1";`

I'd really appreciate one of you query experts giving me the correct query.

trisweb

"show" is a reserved word in MySQL. (as in SHOW STATUS, or SHOW [query], which shows how MySQL would run a specified query without actually running it. Anyway...)

The correct (though untested) query is:
`$sql = "SELECT COUNT(id) FROM ". prefix("albums") ." WHERE parentid IS NOT NULL AND ``show`` = 1";`
(If you can't see it, "show" is enclosed in backticks, those characters you use for code on the forums)

However, this is not the best way to do this. Since in most cases you have to get a list of the subalbums anyway, it's preferable to not use a query at all, just call Album::getSubAlbums() (or $this->getSubAlbums(0) from within the Album class) and then count the array.

`$this->getSubAlbums();

$num_subalbums = count($this->subalbums);`

I'm assuming that you don't want hidden subalbums to be in that array, right (if the user is not an admin)? If all albums are in the array, then running the single count query and caching the result would be okay.

sbillard

Thanks. The above was to count all the subalbums in the gallery, not just the ones in an album.

Note: with the subalbum paging build you must use `getSubalbums(true);` to get the complete (less hidden) list of subalbums.

trisweb

Gotcha. Hope that worked alright.

sbillard

It did. Thanks again.