using custom user field with mysql..

[supreemball]

ok i added a custom user profile field with vb, and basically i'd like to know what the mysql query code is to show all users who have actually filled in the user profile field, as opposed to those who haven't. something like "SELECT (*) from user WHERE userinfo[field9] != NULL", although i know that's not right.. but hopefully you get the gist of it, heh.

[Mark Hensler]

I'm not sure how you would do this for vB, but the syntax for the SQL query your looking for is this:

SELECT * FROM user WHERE field9 IS NOT NULL

[Admin]

SELECT * FROM user LEFT JOIN userfield USING (userid) WHERE field9<>""
The fieldX fields are never NULL.

[supreemball]

Originally posted by FireFly
SELECT * FROM user LEFT JOIN userfield USING (userid) WHERE field9<>""
The fieldX fields are never NULL.

ok, assuming that query is correct, i'd like to go a step further.. basically i'm trying to create a photo album, and i've added a custom field9 where users input a url to their personal photo. i've added that code to global.php, then added a new section in member.php that goes like this, basically:

// ############################## start showphotos ############################
if ($action=="showphotos") {
include("./global.php");
eval("dooutput(\"".gettemplate("getinfo_showphotos")."\");");
while ($showpics=$DB_site->fetch_array($allphotos)) {
eval("dooutput(\"".gettemplate("getinfo_showphoto_bit")."\");");
} // end while
}


i've added the getinfo_showphotos template and a getinfo_showphoto_bit template, but when i view the member.php?action=showphotos url, it only shows one photo (mine cuz i'm userid 1) and nobody elses. i'm pretty sure i'm messing up on the while loop or something.. any help on this would be greatly appreciated.. if you want to figure this out, i'd let anyone release this as a hack, although i know it's kinda been done already, but not in this method.