Create install/setup script for database #7

New Issue

Open

opened 2015-11-01 10:15:04 -06:00 by mattbk · 13 comments

mattbk commented 2015-11-01 10:15:04 -06:00 (Migrated from github.com)

Copy link

Copy source

From @mattbk on October 29, 2015 3:10

Missing from the project from which this was forked. Needs to create a table and some fields to hold results.

• How are individual test-takers identified?
  • by IP

Copied from original issue: mattbk/changeblindnesstest#4

_From @mattbk on October 29, 2015 3:10_ Missing from the project from which this was forked. Needs to create a table and some fields to hold results. - [x] How are individual test-takers identified? - by IP _Copied from original issue: mattbk/changeblindnesstest#4_

mattbk commented 2015-11-01 10:15:05 -06:00 (Migrated from github.com)

Copy link

Copy source

Current db results table is named vcd_results (index.php line 79).

Based on this section, it looks like each respondent is recorded as a single row (ID by IP address REMOTE_ADDR), with three columns per phase. These three columns record the xcoordinate, ycoordinate, and response time of the respondent.

Columns referenced now:

• result_host
• result_date

Option 1:

Continue with this schema, and create a table in the database for each study (group of phases/scenes) with fields based on settings.json. This format is probably easier for RAs to analyze unless it is rewritten before download. This also means that the number of columns has to be hardcoded into index.php unless this section is changed.

Option 2:

Rebuild the schema as EVA so that a study can be modified by changing settings.json and without needing to run a db install script.

Current db results table is named `vcd_results` ([`index.php` line 79](https://github.com/mattbk/changeblindnesstest/blob/master/index.php#L79)). Based on this section, it looks like each respondent is recorded as a single row (ID by IP address `REMOTE_ADDR`), with three columns per phase. These three columns record the xcoordinate, ycoordinate, and response time of the respondent. Columns referenced now: - `result_host` - `result_date` #### Option 1: Continue with this schema, and create a table in the database for each study (group of phases/scenes) with fields based on `settings.json`. This format is probably easier for RAs to analyze unless it is rewritten before download. This also means that the number of columns has to be hardcoded into `index.php` unless [this section](https://github.com/mattbk/changeblindnesstest/blob/master/index.php#L79) is changed. #### Option 2: Rebuild the schema as [EVA](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model#Structure_of_an_EAV_table) so that a study can be modified by changing `settings.json` and without needing to run a db install script.

mattbk commented 2015-11-01 10:15:05 -06:00 (Migrated from github.com)

Copy link

Copy source

Fatal error: Call to a member function bind_param() on a non-object in /home/mattbkco/public_html/_projects/change/index.php on line 80

http://php.net/manual/en/mysqli-stmt.bind-param.php

I think this is because the table and columns don't exist.

> Fatal error: Call to a member function bind_param() on a non-object in /home/mattbkco/public_html/_projects/change/index.php on line 80 http://php.net/manual/en/mysqli-stmt.bind-param.php I think this is because the table and columns don't exist.

mattbk commented 2015-11-01 10:15:06 -06:00 (Migrated from github.com)

Copy link

Copy source


mattbk commented 2015-11-01 10:15:06 -06:00 (Migrated from github.com)

Copy link

Copy source

That's better, now we get Could not execute query.

That's better, now we get `Could not execute query`.

mattbk commented 2015-11-01 10:15:06 -06:00 (Migrated from github.com)

Copy link

Copy source

Got it to work in https://github.com/mattbk/changeblindnesstest/commit/839e7fe17f1d6b706a7b8b0015b3e95a55409257 (added the mysql error to die()), but phase names are still hardcoded.

Got it to work in https://github.com/mattbk/changeblindnesstest/commit/839e7fe17f1d6b706a7b8b0015b3e95a55409257 (added the mysql error to `die()`), but phase names are still hardcoded.

mattbk commented 2015-11-01 10:15:06 -06:00 (Migrated from github.com)

Copy link

Copy source

First column of the database must be a generated UID.

First column of the database must be a generated UID.

mattbk commented 2015-11-01 10:15:07 -06:00 (Migrated from github.com)

Copy link

Copy source

First column of the database must be a generated UID.

That's exactly what it is (or, it works when you make it as such).

> First column of the database must be a generated UID. That's exactly what it is (or, it works when you make it as such). 

mattbk commented 2015-11-01 10:15:07 -06:00 (Migrated from github.com)

Copy link

Copy source

I know more. The way it is set up, the phase name needs to be in the field name as well.

• _host
• _date
• _xcoordinate
• _ycoordinate
• _responsetime

are used in the code to pass to Twig.

I know more. The way it is set up, the phase name needs to be in the field name as well. - `_host` - `_date` - `_xcoordinate` - `_ycoordinate` - `_responsetime` are used in the code to pass to Twig. 

mattbk commented 2015-11-01 10:15:07 -06:00 (Migrated from github.com)

Copy link

Copy source

Adding all of this to an install script would probably be easier than going an EVA route right now.

Then if you run another study in the future, you specify a different table name and can use the same database. Would be best to install in a different folder with a different settings.json, though.

Should work for now, except for all the hardcoded phase names!

Adding all of this to an install script _would_ probably be easier than going an EVA route right now. Then if you run another study in the future, you specify a different table name and can use the same database. Would be best to install in a different folder with a different `settings.json`, though. Should work for now, _except for all the hardcoded phase names!_

mattbk commented 2015-11-01 10:15:07 -06:00 (Migrated from github.com)

Copy link

Copy source

Hardcoded phase/scene names are only a problem in the part in index.php where they are written to db.

Since $_SESSION['results'] is a 2d array, can I use a nested foreach to build the insert query?

Hardcoded phase/scene names are only a problem in the part in index.php where they are written to db. Since $_SESSION['results'] is a 2d array, can I use a nested foreach to build the insert query?

mattbk commented 2015-11-01 10:15:07 -06:00 (Migrated from github.com)

Copy link

Copy source

$_SESSION['results'['scene1'] is not built right. Should be $_SESSION['results'['phase1']. Scene1 is a value, not a key. Hmm.

$_SESSION['results'['scene1'] is not built right. Should be $_SESSION['results'['phase1']. Scene1 is a value, not a key. Hmm.

mattbk commented 2015-11-01 10:15:08 -06:00 (Migrated from github.com)

Copy link

Copy source

I had result_host and result_date swapped in table. Fixed now.

I had result_host and result_date swapped in table. Fixed now. 

mattbk commented 2015-11-01 10:29:33 -06:00 (Migrated from github.com)

Copy link

Copy source

Still needs to be done; does NOT need settings.json to be completed first.

Still need to do this first: https://github.com/mattbk/changeblindness/issues/2

Still needs to be done; does NOT need settings.json to be completed first. Still need to do this first: https://github.com/mattbk/changeblindness/issues/2

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

master

No results found.

Labels

Clear labels

bug

duplicate

enhancement

help wanted

invalid

question

wontfix

★

★★

★★★

No labels

Milestone

No items

No Milestone

Projects

Clear projects

No projects

Assignees

Clear assignees

matt (Matt Burton-Kelly)

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: matt/changeblindness#7