CODEWITHSUNDEEP
phpsymfony
clifford.duke
clifford.duke

Reputation: 4020

Persisting objects to a database using a loop

I have a form that returns multiple values and each value I would like to store in a database,

I created a test form for this purpose

<form method="post" action="{{ path('submit_exercise', {'page': 1}) }}">
    <input type="hidden" name="answers[]" value="Answer 1" "/>
    <input type="hidden" name="answers[]" value="Answer 2" "/>
    <input type="hidden" name="answers[]" value="Answer 3" "/>
    <input type="hidden" name="answers[]" value="Answer 4" "/>
    <input type="hidden" name="answers[]" value="Answer 5" "/>
    <input type="hidden" name="answers[]" value="Answer 6" "/>
    <input type="hidden" name="answers[]" value="Answer 7" "/>
    <input type="submit" name="submit" />
</form>
</body>
</html>

My submit answers Action is currently written like so.

public function submitAnswersAction($page)
{
    //get submitted data
    $data = $this->get('request')->request->all();
    $answers = $data['answers'];

    //get student ID
    $user = $this->get('security.context')->getToken()->getUser();
    $studentID = $user->getId();

    //Get Current time
    $currentTime = new \DateTime(date("Y-m-d H:m:s"));


    //var_dump($answers);
    //var_dump($studentID);
    //var_dump($currentTime);

    for($index = 0; $index < count($answers); $index++)
    {
        /*echo "Question ". ($index + 1) ."<br />";
        echo "Student ID: ". $studentID."<br />";
        echo "Page Number: $page <br />";
        echo "Answer: $answers[$index]"."<br />";
        echo "<br />";*/

        $studentAnswer = new StudentAnswer();
        $studentAnswer->setStudentID($studentID);
        $studentAnswer->setPageID($page);
        $studentAnswer->setQuestionID($index+1);
        $studentAnswer->setAnswer($answers[$index]);
        $studentAnswer->setDateCreated($currentTime);
        $studentAnswer->setReadStatus(0);

        $database = $this->getDoctrine()->getManager();
        $database->persist($studentAnswer);
        $database->flush();

    }

    return new Response('Answers saved for Student: '.$user->getFullName().' for page: '.$page);

When I do a var_dump everything seems to be associated correctly, meaning that the answers array is populated with the right data and so is every other variable, my problem is actually persisting it to the database. when run it returns with this error which seems to me like it doesn't know what variables to put into the row.

An exception occurred while executing 'INSERT INTO Student_Answer (student_id, page_id, question_id, answer, read, date_created) VALUES (?, ?, ?, ?, ?, ?)' with params {"1":2,"2":"1","3":1,"4":"Answer 1","5":0,"6":"2012-12-11 12:12:20"}:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read, date_created) VALUES (2, 1, 1, 'Answer 1', 0, '2012-12-11 15:12:51')' at line 1

Any help would be greatly appreciated as this is a personal project to help me try and understand web development a bit more.

Upvotes: 0

Views: 124

Answers (2)

clifford.duke
clifford.duke

Reputation: 4020

Wow okay, So the issue had nothing to do with Symfony or Doctrine at all,

The issue was basically the read field, I believe it's a keyword in SQL and so it read it as a keyword and not a column field name. I had to rename it to is_read

Upvotes: 1

Fran&#231;ois Constant
Fran&#231;ois Constant

Reputation: 5496

Not sure what the issue is. A few things:

That goes before the loop:

$database = $this->getDoctrine()->getManager();

and it should be $em or $entityManager (because that's how it's called...)

That should be called after the loop:

$database->flush();

You don't need to deals with IDs saved in the database with Doctrine2. So don't do this:

$studentAnswer->setStudentID($studentID);

Do this instead:

$studentAnswer->setStudent($student);

Upvotes: 4

Related Questions