setting up and printing out associative array values

[webguync]

Hi, I may be over complicating this. I want to set up associative values of an array being pulled from a MySQL DB field and then print out the results along with the associative value. The one value is an integer while the other value is a string so it may look like 1=>Section 1, slide 3 2=>Section 2 slide 5. The reason i need to do this is I am displaying an array of missed questions for an application 1,5,7,9,15 etc. and each question has a corresponding section, so I need to display that in order to visually indicate the area to review.

 

I thought I may need to set up like this.

$workon["Section 1, slide 3"] =1;
$workon["Section 2 slide 5"] = 2;

 

and then

echo "The area to work on is - $" . $workon["Section 1, slide 3"] . "<br />";

 

this will be cumbersome though as there may be 50 questions. I am pulling out the missed questions via  a SQL Query and then fetch_assoc())

$incorr[] = $row['incorrect_resp'];

 

 

[de.monkeyz]

You know how to grab the section from the database you can use

$workon[$section] = $response;

 

Although for what you want it for, I'd do

$workon[] = array($section, $response);

 

Since having an array key with spaces and things like comma's is a bad idea in my opinion.

[webguync]

well the thing is the section isn't captured in the database, so I might have to set that part up in the PHP manually and then associate with the responses. This is the part I am having trouble with.

[de.monkeyz]

If there's nothing dynamic linking to the sections, you can't automate the process. You could make a static array in php called $sections

$sections = array('Slide 1, Section 1', 'Slide 2, Section 3'); 

 

Or something like that.

 

But I don't really know what you're application is, so I can't offer anymore insight than that

[Psycho]

well the thing is the section isn't captured in the database, so I might have to set that part up in the PHP manually and then associate with the responses. This is the part I am having trouble with.

 

So, why not add the section information to the database. It would be very easy to do so.

 

1. Create a table for the section descriptions:

 

Table: Sections:

Fields: section_id, section_name

 

2. Create a table for the slide descriptions:

 

Table: Slides:

Fields: slide_id, slide_name, section_id (foreign key)

 

3. In the questions table add a new field (foreign key) to associate each question to a slide

 

[webguync]

that might work. The questions aren't actually in a table right now, but the incorrect responses are. The application is actually a flash app that pulls in the questions via XML. Could I set up a table with a field called questions and one called sections and then pull together the fields in an array if they are in different tables? So for example in my table results I have a table field called incorrect_responses. and in that field will be 25,39. I have in my other table questions_sections which will tell me what question and section 25 and 39 correspond to. Just need help as to how I would then display that info.

[Psycho]

that might work. The questions aren't actually in a table right now, but the incorrect responses are. The application is actually a flash app that pulls in the questions via XML. Could I set up a table with a field called questions and one called sections and then pull together the fields in an array if they are in different tables? So for example in my table results I have a table field called incorrect_responses. and in that field will be 25,39. I have in my other table questions_sections which will tell me what question and section 25 and 39 correspond to. Just need help as to how I would then display that info.

 

Yes, all of that is possible. I only provided possible solutions. Without knowing the final schema of the database this is all just conjecture. Here is a more complete example of what the DB could look like:

 

Table: questions

Description: this table copntains one entry for each question. It is not for storing results

Fields: id, question, slide_id (foreign key)

 

Table: answers

id, answer, question_id (foreign key), correct (boolean true/false)

 

Table: Sections:

Fields: id, section

 

Table: Slides:

Fields: id, slide, section_id (foreign key)

 

Table: results

Fields: user_id, question_id, answer_id

 

Assuming the results table has all the correct and incorrect responses, here is a query that would get the question text, slide and section for all the questions the user got incorrect

 

SELECT s.section, sl.slide, q.question
FROM results r
  JOIN questions q on q.id = r.question_id
  JOIN answers a ON a.question_id = q.id AND a.correct = 1
  JOIN slides sl ON sl.id = q.slide_id
  JOIN sections s ON s.id = sl.section_id
WHERE r.answer_id <> a.id

 

Note, none of this is tested. I may have missed something in the query, but it is possible.

[webguync]

actually I don't think I will need  all those tables, basicly just need to match up a field in these two.

CREATE TABLE `results_April2010` (
  `results_id` int(11) NOT NULL auto_increment,
  `user_id` int(11) default NULL,
  `log_id` int(11) default NULL,
  `section` int(11) default NULL,
  `sect_start` int(11) default NULL,
  `total_questions` int(11) default NULL,
  `responses` text,
  `incorrect_resp` text,
  `num_correct` int(11) default NULL,
  
  `date` int(11) default NULL,
  PRIMARY KEY  (`results_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

and

 

CREATE TABLE `Questions` (
  `Question` text NOT NULL,
  `Section` text NOT NULL,
  `Q_ID` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`Q_ID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

and then really I just need to match  the results in incorrect_resp (2,5,10 etc.) with Q_ID in order to determine which Question/Section the numbers in incorrect_resp correspond to in my Questions table.

 

Also I guess each table needs to have a field in common huh? Right now I don't see a way to be able to create a field that the two would be able to have in common.

[Psycho]

Youreally shouldn't be storing comma se;arated values - that is what a tagble is for. Are the number in "incorrect_resp" the same as "Q_ID"? If so, here is what you could do (the LONG way).

 

$query = "SELECT `incorrect_resp`
          FROM `results_April2010`
          WHERE `user_id` = '$user_id'";

$result = mysql_query($query);
$record = mysql_fetch_assoc($result);

$query = "SELECT *
          FROM `Questions`
          WHERE q.Q_ID in ({$record['incorrect_resp']})";

 

If you had the incorrect responses in an associative table, you could get all the information you need in a  single query.

[webguync]

Ill try that b/c yes in my incorrect_resp field I am going to set p the table for Questions so that the array for $incorr (1,4,7,9) will = tables q_id of 1,4,7,9. I will post my results.

[webguync]

I am probably a bit off on how to echo out the results with this. Here is what I have. I am not getting any SQL errors at least, but no results.

 

   <?php
$query1 ="SELECT `incorrect_resp`
          FROM `results_April2010`
          WHERE `user_id` = '$user_id'";

$result1 = mysql_query($query1) or trigger_error('Query failed: ' .mysql_error());
$record = mysql_fetch_assoc($result1);

$query2 = "SELECT *
          FROM `Questions`
          WHERE q.Q_ID in ({$record['incorrect_resp']})";
	  
$result2 = mysql_query($query2) or trigger_error('Query failed: ' .mysql_error());

echo $result1,$result2;
?>

[Psycho]

Well, first off, you are trying to echo $result1 and $result2 which would be resource identifiers from the query NOT the results. There is, however, an error in the second query. You are trying to use 'q' as a pointer for the 'Questions' table, but you didn't define it. But, since you are only querying one table you don't need it anyway.

 

Add, some debuggingto validate the data at each step.

<?php

//Query for the user's worng responses
$query ="SELECT `incorrect_resp`
          FROM `results_April2010`
          WHERE `user_id` = '$user_id'";
$result = mysql_query($query) or trigger_error('Query failed: ' .mysql_error());
$record = mysql_fetch_assoc($result1);

//For debugging
echo "Query: $query<br />\n";
echo "Records returned: " . mysql_num_rows($result) . "<br />\n";
echo "Incorrect_resp: $record['incorrect_resp']<br />\n";

//Query for the questions of wrong responses
$query = "SELECT *
          FROM `Questions`
          WHERE `Q_ID` in ({$record['incorrect_resp']})";
$result = mysql_query($query) or trigger_error('Query failed: ' .mysql_error());

//For debugging
echo "Query: $query<br />\n";
echo "Records returned: " . mysql_num_rows($result) . "<br />\n";
echo "Questions: <br />\n";
while($record = mysql_fetch_assoc($result))
{
    echo "{$record['Question']}<br />\n";
}

?>

[webguync]

thanks, I will give it a try. This line is throwing an error, must be syntax related, not sure what it is though.

 

echo "Incorrect_resp: $record['incorrect_resp']<br />\n";

[webguync]

Seems to be working, but I wanted to add another column to display for this line, but doing it this way must be wrong, since causes an error. Please advise.

{
    echo "{$record['Question']['Section']}<br />\n";
}

 

[Psycho]

Not sure what you are trying to do. But, according to your table structure...

 

This will echo the question

    echo "{$record['Question']}<br />\n";

 

This will echo the section

    echo "{$record['Section']}<br />\n";

 

Assuming, of course, you have done a successful DB query and assigned the extracted results to $record.

[webguync]

this appears to be working as intended now. Thanks, I will mark as solved.