Instructions for updating data to query from NHANES.
1. Download data from [NHANES](https://wwwn.cdc.gov/nchs/nhanes/default.aspx). These files are in XPT format (SAS files).
  a. Current datasets include:
      * Demographics (demo)
      * Standard Biochemistry Profile (biopro)
      * Body Measures (bmx)
      * Blood Pressure - Oscillometric Measurement (bp)
2. Convert the XPT files to MySQL database.
  a. Install MySQL from [here](https://dev.mysql.com/downloads/mysql/). Instructions are [here](https://dev.mysql.com/doc/refman/8.0/en/macos-installation-pkg.html)
  a. Install Python libraries pandas and sqlalchemy.
    `pip3 install pandas sqlalchemy`
  b. Run `create_nhanes_db.py` from root directory of nhanes project. This will create a database in the `usr/local/mysql` folder.
    `python3 create_nhanes_db.py`
3. Install Sequel Ace using homebrew. This is just a GUI for MySQL, but creates the sql file that is similar to the one Sequel Pro generated.
  `brew install --cask sequel-ace`
4. Run Sequel Ace and select the `nhanes` database from the dropdown at the top of the window, or from the menu Database > Go to Database
5. Export the tables.
  a. From the menu Files > Export. In the dialog box, check all the checkboxes for the tables you would like to export. Each dataset is a table in the database. You can specify where to export the file to. For this repo, export the file to `codap-data-interactives/data-science-worlds/sql data`
6. Copy the `decoder` table from the previous `.sql` file. This will have most of the variables you need from the tables. You may have edit, add, delete variables depending on what data is available for the year(s) you selected.
7. copy the `varlist` table from the previous `.sql` file. This will have most of the variables you need from the tables. You may have edit, add, delete variables depending on what data is available for the year(s) you selected.

**To test local MySQL database**
1. Download and install [MAMP](https://www.mamp.info/en/mamp/mac/)
2. Make a folder named `cred` in `/Applications/MAMP/`. Make a copy of the `nhanes` folder and put the copy in the `cred` folder. Make a copy of the `common` folder in the `codap-data-interactive/data-science-worlds` folder and put the copy in the `cred` folder.
3. Move the `nhanesCred.php` in the `nhanes` folder out to the `cred` folder.
4. Launch MAMP. You may have to change your document root to `Applications > MAMP > cred`. This is because of the existing paths require us to put the `nhanes` folder in the `cred` folder.
5. Click on the Start button in MAMP. This should start up a local server that has MySQL in `localhost:8888`.
6. In a browser page, navigate to `http://localhost:8888/phpMyAdmin5/index.php`. This should bring up a GUI for accessing your database in `php`.
7. In phpAdmin, click on Import button. This brings up a dialog that will allow you to import the sql file. In the dialog, select the sql file in the `File To Import` section. Click on Import button at the bottom of the page. Once imported, you should see the nhanes database list on the left side, with the tables listed under the database name.
8. Navigating to `http://localhost:8888/nhanes/nhanes.php?c=getCases&tables=biochem&atts=*&joins=SEQN&n=15`, you should see a page full data. Note that navigating to `http://localhost:8888/nhanes/nhanes.php`, you will see `"{ Unhandled command : }"`.

**Load plugin in CODAP**
1. Start a local instance of CODAP.
2. Navigate to `http://localhost:4020/dg?di=http://localhost:8888/nhanes/nhanes.html`. You should see the plugin in CODAP.

**Upload data to CODAP server**
To use the new data in the nhanes plugin, the existing data in the database in the CODAP server needs to be replaced with the new data.
1. Upload the new sql file into the CODAP server. Currently, this should be stored in `var/www/html/data-science-worlds/nhanes/sql data`.
2. Back up the old database.
3. Drop the old database.
4. Create a new database called `nhanesdb`, and populate it with the data from you new sql file.
