SQL Query Help - INSERT Statement

[raaboo]

Hi,

I am trying to generate an sql query to insert several pieces of data, most of which comes from another table, however I have one variable that comes from PHP variables as below:

insert into scores (AuditID, question_id, available_score, question, question_number, question_section) values ($auditvalue, (SELECT questions.quid, questions.question_value, questions.question, questions.question_number, questions.question_section FROM questions))

the following code works, however because I cannot figure this out, the AuditID column is being left blank and I need it to be populated - I cannot update after insert because the table will contain duplicate values:

insert into scores (question_id, available_score, question, question_number, question_section) SELECT questions.quid, questions.question_value, questions.question, questions.question_number, questions.question_section FROM questions

basically - the 'quid', 'question_value', 'question', 'question_number', 'question_section' from the 'questions' table needs to be copied in to the scores table, but whilst inserting, for each inserted row, i need to insert the '$auditvalue' variable from PHP in to the 'AuditID' column.

Can anyone point me in the correct direction for doing this as I know the above is wrong (I get 'column count does not match' error) - I am using a MySQL database for this. I jiust need to be able to get the AuditID inserted!

[rajandass65]

$query1="SELECT quid,question_value,question,question_number, question_section FROM questions";
$result=mysqli_query($dbc,$query1);
$row=$row=mysqli_fetch_array($result)
$quid=$row;
$question_value=$row;
$question=$row;
$question_number=$row;
$question_section=$row;

insert into scores (AuditID, question_id, available_score, question, question_number, question_section) values ($auditvalue,$quid, $question_value, $question, $question_number, $question_section );

Why do you need to duplicate this data? Surely you just need to have a relationship on one field (one the id fields)?

[raaboo]

the data needs to be duplicated because over time the question information may change as it is flexible data. Therefore i need to store the actual question and associated information in the database for historical reporting. I will try the first solution and see if this works!

[raaboo]

okay so this sort of works:

$query1="SELECT quid, question_value, question, question_number, question_section FROM questions";
$result=db_query($query1,$conn);
$row=db_fetch_array($result);
$quid=$row['quid'];
$question_value=$row['question_value'];
$question=$row['question'];
$question_number=$row['question_number'];
$question_section=$row['question_section'];
$strSQLInsert="insert into scores (AuditID, question_id, available_score, question_number, question_section, question) values ($values[ID], $quid, $question_value, ".'"'.$question_number.'"'.", $question_section, ".'"'.$question.'"'.")";
db_exec($strSQLInsert,$conn);

(had to concatenate a couple of fields and alter it owning to text values and commas etc causing breaks in code from returned values)

The problem with this is that it only inserts the first question - I need it to insert all returned results as there could be a couple of hundred! Anyone any ideas?

> the data needs to be duplicated because over time the question information may change as it is flexible data. Therefore i need to store the actual question and associated information in the database for historical reporting. I will try the first solution and see if this works!

In this case I'd have a table called 'bank'

bank_id | commence_date | description | title ...

Then a link table bank_data:

bq_id (AUTO/PK) | bank_id | question_id | sortorder (all ints)

You could do without the bq_id, but it's useful for the answer table.

Then your bank of questions

question_id | ...(other fields pertaining to the question itself)...

An answer table:

user_id | bq_id | answer

THis is a rough'n'ready solution off the top of my head, but it's important to keep the data normalized. Duplicating data as you're doing is wasteful and errors may creep in. I seriously encourage you to reconsider your DB schema.

[raaboo]

hi ardav,
what you have described is not far from what I have already done. Copying the data is the only way to ensure it is 100% historically accurate (although i do agree it will eat resources).

I have looked at linking and this was my first suggestion (again in agreement with you), however this has been ruled out by the powers that be above me, owing to needing to trace historically as some question text or available scores may change over time, and viewing historic records needs to be as accurate as viewing current records, so to meet this stipulation, the only option is to literally copy the full question set from one table to another, otherwise someone may get 100% today, but after updating questions may only get 90%, which cannot be allowed.

I have thought of parsing the original results to an array, then setting the array to one query and inserting this way - how could this be achieved?

I disagree. You just create new questions as opposed to writing over the question. Your create a new BANK (i.e. test set) and add all the questions you want to include (just questions ids and the sortorder - the order in which you want them to appear, you could add question_number field to provide a definite question label as opposed to a default label [like the sortorder would give you]). Now the from_date ensures that anybody taking a test NOW gets the latest (or current) bank of questions. Questions from old tests will not appear.

The bq_id field uniquely identifies a BANK and a QUESTION. So you can trace back the original question details from the QUESTIONS table.

WRT parsing results to array: if you implode all question fields to form a list, you defeat the object of having a decent db structure as it won't be properly indexed OR properly searchable.

PS. I'm no expert, and I may be overcomplicating things slightly, BUT your powers that be need to wake up and get a grip. Do they even know how DBs work? It doesn't sound like it from what you mention. I won't be offended if you don't see things my way. Honest :)

[raaboo]

hi ardav,

let me explain what this is designed to achieve and then we can take it from there - i fully agree with what you say by the way!

There are several tables - clients, sites, audits, questions and scores.

Each client can have mutiple sites, each site can have multiple audits and each audit has a set of scores.

Basically, when the auditors go out, they assess a site and score it. If a criteria is not applicable to the site, they mark it as such and it is not included in the overall score. when they go out to a site, they need to see all the questions for the site, and log whether it passes, fails etc... the way they do this is by accessing and editing the scores table via a php page.

is there a way to get all the question ID's and copy them to the scores table with the current ID of the audit which is stored as a php variable ($auditnum)?

Ok nosebleed! I'll have to take some time to process that. I'll get back to you!

[raaboo]

okay - figured it eventually (even though it is not compliant with best practice really!)

$strSQLInsert = "insert into scores (AuditID, question_id, available_score, question_number, question_section, question) select '$auditnum' as auditid, quid, question_value, question_number, question_section, question FROM questions";

basically took the variable from php and treated it as a fresh value for a field! thanks to everyone for their help!

AUDITORS
auditor_id | auditor_detail1 | auditor_detail2 ...

CLIENTS
client_id | client_detail1 | client_detail2 ...

SITES
site_id | site_detail1 | site_detail2 ...

CLIENT_SITE (link table)
client_site_id | client_id | site_id (could do without client_site_id, but this just makes it easier for the audit table)

AUDITS
audit_id | auditor_id | date_audit | client_site_id | datestamped | totalscore (optional)

QUESTION_SECTIONS
question_section_id | section_name | sortorder

QUESTIONS
question_id | question_text | sortorder | question_section_id | active [tinyint - 0 = inactive, 1 = active - this decides which questions should be set for the audit at any given time]

SCORES
answer_id | audit_id | question_id | answer (1 or 0 or -1 the -1 means N/A [default])

The scores would be entered via form:

Question... 3 radio buttons (Pass, Fail, N/A [default checked])
The 'name' attribute of each radio should contain the question_id and values should be 1,0,-1.
This way, when the form is sent, ALL answers are added to the SCORES table via 'batch' INSERT.

That's my take anyway. The only downpoint of this setup is the number of INNER JOINS you have to place into the SQL - can get a bit tricky. However, you now have a normalized structure.

So,

SCORES pretty much hold the question data and results:

answer_id | audit_id | question_id | answer
   1            7          2            0
   2            7          3            1
   3            7          6            1
   4            7          7            -1

The results above show that 4 questions were in the audit - questions 1,4,5 are inactive as the no longer apply due to better questions in the case of 4,5 (use 6,7 instead).
answer will give score BUT you need to be careful when totalling - don't involve '-1'! You can do this by doing an 'WHERE answer > -1' clause.

That's how I'd do it. I'm getting trouble thinking of this in a non-normalized fashion. Sorry about that.

//EDIT - just saw your last post. Bugger.