SQL Question (Not Really Sure Were to Put This)

[Troy Spiral]

I'm running IPB 4.2.6. 

My board is connected with several other online resources.  When users signup for my board is a typical reason is that they came from one of my other ventures (mostly e-commerce opt-in type things).  I'd like to integrate all of my other users together with my board users at least for my email updates and such, which I can't do.  (I use a thing called Klaviyo to manage newsletter updates and such which as far as I know doesn't integrate with IPB so I have most of my users in one place and then the rest in another and its a pain in the rear now after years of the user base gets larger and larger).

What table holds the Member info (really just need the name and the email address)?  What command would I use to pull that out in a useable format?  (text, CSV whatever)  I'm not sure if this is a hard question or an easy question (guessing its easy for someone that knows their stuff, which I do not.)  Regardless I'd really appreciate some help.  I've googled this and watched videos and I'm still not any closer to being able to do this myself, although I at least have some clue about SQL and how it works and very basics of how to use it, no idea how to do the above. 

Any help would be GREATLY appreciated.  Thanks in advance. 

 

[evandixon]

I don't know the table name off hand, but depending on what will be accessing the data, it may be easier to use the REST API to get member details in JSON. AFAIK it doesn't come with much searching/filtering, but it sounds like you'll want to iterate through most of them anyway.

[Numbered]

Primary answer for your question - table core_members. But better way for provide account data sync - create an application, which implement ProfileSync extension (may be with your cusom login handler).

[Troy Spiral]

I have MyPHP admin / MySQL as the language (as I understand it that's what they are called I'm a total noob here, but I have been reading/ watching videos to try and grasp this more)  

Core_Members seems to only have 600 entries, and the board has 55,000 members?  What might I be missing? But then even when I find this (something is wrong) table it seems to be way too small?

@evandixon

@Upgradeovec

THANK YOU 

for taking the time to respond. 

[Numbered]

Which source of that numbers 600 and 55k?

You can count number of records in table with query 'select count(*) from core_members'. I think pma not show all and use pager with 600 rows per page, is'nt it?

Anyway, i recommend just learn IPS architect - it will be positive for futher development. But with that knowing and without deep experience with the framework i recommend to didn't change anything directly in db from any source (hand, third party scripts, etc). For any your task better way to do anything with framework methods. For example - good way for create new member - call $member = \IPS\Member::load($id). If $member didn't have member_id - so it not exist on forum, else - present. After that change any params, like $member->email = 'some@example.com', change other params (i dream it's something sync updater) and after all call $member->save() - will save that new member (or change params on already exist member).

I already said which way you need to prefer for learning to create data sync with other databases. Get it  And of course ask anything if you get a trouble on some step. There are a lot of good and nice developers, which like to help (and make attenstion if you start doing something not good)

[Troy Spiral]

@Upgradeovec

I am really just trying to export the member name and member email to a .csv (or txt) file.   I'm not trying to change anything. I'm not trying to edit or update any member info, just see it and copy it to an export file. Which sounds simple to me, but I've been trying to do it for hours and can't seem to figure it out. 

I just noticed I was wrong, I don't see core_Members anywhere, the last folder is  ibf_core_hooks and nothing below that, so it stops at the letter h (hooks) and there is no entry with just "core_members" that I can find via the search tool. 

[Troy Spiral]

Well, I found it.  But I'm not sure what to do from here, how to export the member name and email?  

[Numbered]

I am sorry. My english understand (and write abilities too) is not good. So it's a misscommunication 

About PMA i can't help you clear. Not use it at all. Anyway somewhere it must has 'export' section, where you can choose a format (excel, csv, etc) to export. Not sure about specail column select param, but you can easy clear it after full export. Try to open that table on the left side. I think it shoud open some structure shown with a tabs, where may be exist some export buttons.

[Troy Spiral]

13 minutes ago, Upgradeovec said:

I am sorry. My english understand (and write abilities too) is not good. So it's a misscommunication 

About PMA i can't help you clear. Not use it at all. Anyway somewhere it must has 'export' section, where you can choose a format (excel, csv, etc) to export. Not sure about specail column select param, but you can easy clear it after full export. Try to open that table on the left side. I think it shoud open some structure shown with a tabs, where may be exist some export buttons.

The language is fine.  I have many relatives that live in  Croatia / Russia / Germany and I have to find out what they mean because of the english.  So I understand it better than most Americans would.   I'm still trying to figure out what to do.   *bangs head on keyboard*   

[Nathan Explosion]

Paste the following in to that big SQL Query box and click Submit Query

SELECT name,email FROM `core_members`

And then export the results.

[Troy Spiral]

@Nathan Explosion Thanks a ton! That's what I was looking for.  Really appreciated.  I think people, in general, don't appreciate the help they get from here. 

 

I have a problem though when I run that it returns this:

#1146 - Table 'detroitg_ipb.core_members' doesn't exist

I have a standard install, I've not customized it hardly at all and definitely not the database.  Did they change the name of it or some such?   I obviously do have users so they must be stored somewhere. 

I've been watching more / reading more, but it's not helping this situation. 

 

Any ideas? Why is it saying that doesn't exist? Its supposed to exist right? Is that list just "standard table names" or something?  Somehow mine is different? I went through every single item listed on the far left of that picture, and there is nothing about user data specifically in any of them as far as my poking around can tell. 

 

[Faqole]

It looks like you are using a prefix for your database table. You must include it in the query for it to work.

[Nathan Explosion]

Make this much easier...log in to the ACP, go to the Members list and click "Download member list"