Topic: Finding every Member *without* a specified custom_field

[Wastel]

Hey everyone!
I'm quite new to SMF, and this is my first post here... Hi!
What I'm trying to do:
Update a custom_field with information gathered from some functions outside SMF.
(But only if this custom field is *not* already set)

What I need:
I need a list of every member in my database who has *not* set a specified custom_field.

What I really looking for:
A SQL query for this.

What I did:
Checked every resource i was able to find, tried a lot of SQL Code (I'm not really good at it.)
Checked the Sourcecode of all the mods... all over again.

The problem is that in fact I need the information from 2 custom fields, 'joined' together with the smf_members.id_member and the smf_members.email_address but only if the smf_themes.variable ('cust_checkr') ist NOT present.

I hope someone can get me a hint what to do..

Thanks!

Sebastian

[Colin]

It looks like you have an idea of what you are supposed to code, but we can't help you further without specifics.

[Wastel]

What do you need?

the custom field:

INSERT INTO `smf_custom_fields` (`id_field`, `col_name`, `field_name`, `field_desc`, `field_type`, `field_length`, `field_options`, `mask`, `show_reg`, `show_display`, `show_profile`, `private`, `active`, `bbc`, `can_search`, `default_value`, `enclose`, `placement`) VALUES
(5, 'cust_checkr', 'check_result', 'SSH Result, 1 = True = Checked \n0 = False = not checked.', 'check', 255, '', 'nohtml', 0, 0, 'account', 3, 1, 0, 0, '', '', 0),

I need every row where this field is 0 or not present
Thanks!

[Suki]

Certainly this is possible with a query, unfortunately I'm not that accurate with queries.

I was thinking in a LEFT JOIN between the members and the themes table, then fetch all the info you want and add a WHERE cust_yourvalue = ''  or empty or null, I'm not so sure.

[Joker™]

Try something like this

Code: [Select]

SELECT m.id_member, m.email_address
FROM smf_members AS m
LEFT JOIN smf_themes AS th ON ( th.id_member = m.id_member )
WHERE m.id_member NOT
IN (
th.variable = 'cust_checkr'
)

[Wastel]

Hi!

thanks for your answers, i already tried this, and the problem is that the response contains all the rows with other custom variables set (but NOT cust_checkr).
Also the combination with a subquery like  WHERE m.id_member IS ANY(SELECT DISTINCT()....) doesn't work.

This is a screenshot of the response:
hxxp:www.sebastianehinger.de/upload//response-20120415-183626.jpg [nonactive]

Thanks again!

Sebastian

[Joker™]

Code: [Select]

SELECT DISTINCT m.id_member, m.email_address
FROM smf_members AS m
WHERE m.id_member NOT
IN (
SELECT th.id_member
FROM smf_themes AS th
WHERE th.id_member = m.id_member
AND th.variable = 'cust_checkr'
)

Note - Sub queries are injurious to system health

[Wastel]

THANK YOU SO MUCH!

This was exactly the answer I was looking for. I don't know why but I tried this and totally forgotten the possibillity of the negation.
The Script I wrote is just for one-time usage (checking all existing members) or maybe for maintenance once in a while, the newly registered users will be checked directly on registering, so I don't see a direct problem for the system, but thanks for the hint!

Greetings
Sebastian

[Joker™]

Your welcome. I'm happy as due to this topic only I got a chance to write a sql query after a long ago.