mySQL Query Creation with a for Loop

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lmg
Forum Commoner
Posts: 34
Joined: Tue May 26, 2009 10:11 am

mySQL Query Creation with a for Loop

Post by lmg »

The user fills out a form with radio buttons and text boxes for each question (there are 20 questions) and then the answers and comments will be put into a database. The way I have it set up now, I grab the answers with a for loop and use the . operator to append it to the end of the current query like so:

Code: Select all

while($i <= 20){
        if($_POST['answer$i'] < 1 || $_POST['answer$i'] > 5)
            $addHeader = error($i, $addHeader);
        $partialQuery .= "'$_POST[answer$i]', ";
        $i++;
    }
I had it working fine when I was testing it with inputting 5 answers, but now it is giving me the following error:

Parse error: syntax error, unexpected T_VARIABLE, expecting ']' in /home/a5317204/public_html/test/selfAudit1_writeToDatabase.php on line 60

(Line 60 is $partialQuery .= "'$_POST[answer$i]', ";)

I expect I will get the same error in the comment loop as well.

Here is the code for the two pages I am currently working on:

Form:

Code: Select all

<!--There are 20 questions in this section-->
    
<?php 
/*session_start();
 
if (!isset($_SESSION['username'])){
    header("Location: login.php");
}
else{*/
    $title="Self Audit";
    include("header.php");
    
    //connect to the database to get all of the questions for this section. 
    $username="user";
    $password="pass";
    $database="db";
 
    //connect and select the database
    @mysql_connect(localhost,$username,$password) or die("Cannot connect to database");
    @mysql_select_db($database) or die( "Unable to select database");
 
    $query="SELECT questions FROM question WHERE question_id BETWEEN 0 AND 20";
    $result=mysql_query($query);
//}
?>
 
</html>
 
<style type="text/css">
    DIV.alignment {text-align: justify}
    DIV.boldText {font-weight: bold}
</style>
 
<body style="filter:progid:DXImageTransform.Microsoft.Gradient(endColorstr='#9B8259', startColorstr='#F4EEE0', gradientType='0');">
<form method="post" action="selfAudit1_writeToDatabase.php">
 
<div class='borders'>
 
<a href="session.php"><font face="Arial">Return to Main Menu</font></a><br><br>
<br>
<br>
<font face="Arial" size=5 color="#5C4112"><div class=boldText>Section 1</div></font>
<br>
<br>
<br>
 
<table width=100% border=0 cellspacing=0 cellpadding=20>
 
<!-- The following code should be used to create the page, and the code which follows should be removed (remember to keep the </table> tag!) -->
 
<?php
//put all other info on page
for($i=0; $i < mysql_numrows($result); $i++)
    echo "<tr>
    <td width=60%><font face='Arial' color='#5C4112'><div class=alignment>".mysql_result($result, $i)."
        <br><br><div class=boldText>
        <INPUT TYPE='radio' NAME='group$i.' VALUE='1'><span title='Non-Compliant. Quality requirement not addressed.'>NC</span>
        &nbsp; &nbsp;
        <INPUT TYPE='radio' NAME='group$i.' VALUE='2'><span title='Minimally Compliant. Quality requirement addressed.'>MC</span>
        &nbsp; &nbsp;
        <INPUT TYPE='radio' NAME='group$i.' VALUE='3'><span title='Partially Compliant. Quality requirement addressed and implemented.'>PC</span>
        &nbsp; &nbsp;
        <INPUT TYPE='radio' NAME='group$i.' VALUE='4'><span title='Substantially Compliant. Quality requirement addressed and implemented. 
            Documentation reflects actual practice.'>SC</span>
        &nbsp; &nbsp;
        <INPUT TYPE='radio' NAME='group$i.' VALUE='5'><span title='Fully Complaint. Quality requirement addressed, implemented, and audited 
            annually for compliance and continuous improvement. Documentation reflects actual practice.'>FC</span>
        &nbsp; &nbsp;
        <INPUT TYPE='radio' NAME='group$i.' VALUE='6'><span title='Not Applicable. Does not apply.'>NA</span><br><br>
        
        <td>
        <font face='Arial' color='#5C4112'><div class=boldText>Comments:</div><br>
            <TEXTAREA COLS='75' name='comment$j' style='background:#EEE9DF'></TEXTAREA><br><br>
        </td>
        
    </tr>";
?>
 
</table>
 
<!--End of survey-->
 
<br>
<input type="submit" name="continue" value="Continue">
<br>
<br>
<br>
 
</form>
</div>
</body>
</html>
 
<?php
    include("footer.html");
?>
Write to db:

Code: Select all

 
 
<!--selfAudit1_writeToDatabase.php
    Creates new entry in the auditData table (uses $_SESSION['username'] as username, determines the correct audit number, takes the date from the
        system, automatically sets question id to 1, and autogenerates answer id). After creating new entry, collects all of the answers
        from the form and inputs them into the answer table. -->
 
<?php
 
/*session_start();
 
if (!isset($_SESSION['username'])){
    header("Location: login.php");
}
 
else{*/
 
    //set the username, password, and database name
    $username="user";
    $password="pass";
    $database="db";
 
    //connect and select the database
    @mysql_connect(localhost,$username,$password) or die("Cannot connect to database");
    @mysql_select_db($database) or die( "Unable to select database");
 
    //query the database for all audits for the user. If none are found, audit number is 1. Otherwise, audit
        //number is (largest audit number) + 1.
    $prequery = "SELECT MAX(auditNumber) FROM auditData WHERE username=$_SESSION[username]";
    $result=mysql_query($prequery);
 
    //setting the audit number
    $auditNumber = $result+1;
    
    //get the date
    $date = date("F j, Y");
 
    //the question id will always be 1 until a new set of questions needs to be made. The answer id will be autoincremented. 
        //Update auditData table.
    $prequery2 = "INSERT INTO auditData VALUES('$_SESSION[username]', '$auditNumber', '$date', '', '')";
    mysql_query($prequery2);
 
    //Now, we fill in the answer/comment fields in the answer table.
 
    //get the answer id from our table
    $prequery3 = "SELECT answerID from auditData WHERE username=$_SESSION[username] AND auditNumber=$auditNumber";
    $anwerID = mysql_query($prequery3);
 
    //create part of the query
    $addHeader=true;
    $i=1;
    $partialQuery="('$answerID', ";
    //add answers to the query. If any are empty, an error message will be output requesting the user to go back and answer the empty
        //question.
    while($i <= 20){
        if($_POST['answer$i'] < 1 || $_POST['answer$i'] > 20)
            $addHeader = error($i, $addHeader);
        $partialQuery .= "'$_POST[answer$i]', ";
        $i++;
    }
    if($addHeader==false)
        include("footer.html");
    //add empty spaces for future sections
    /*while($i <= 79){
        $partialQuery .= "'', ";
        $i++;
    }*/
    //add comments to the query
    $j=0;
    while($j <= 20){
        $partialQuery .= "'$_POST[comment$j]', ";
        $j++;
    }
    //add empty spaces for future comments
    /*while($j < 79){
        $partialQuery .= "'', ";
        $j++;
    }*/
    $partialQuery .= "'')";
 
    $query = "INSERT INTO answer VALUES ".$partialQuery;
    //query the database
    mysql_query($query);
 
    mysql_close();
 
    header('Location: get_aPie.php');
//}
 
function error($qNum, $header){
    if($header==true){
        $title="Error!";
        include("header.php");
        echo "<FONT FACE='arial'>You left question number ".$qNum." blank. Please go back and fix your error.<br>";
        $header=false;
    }
    else
        echo "<FONT FACE='arial'>You left question number ".$qNum." blank. Please go back and fix your error.<br>";
    return $header;
}
 
?>
I hope I am making sense...

Thanks in advance for your help!
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: mySQL Query Creation with a for Loop

Post by pickle »

First, it's very dangerous to put something right into the database from $_POST. It pretty much opens the door for SQL injection. Second, I think the problem lies in how you're trying to access your $_POST variables.

I would change your line 60 to:

Code: Select all

$partialQuery .= "'".mysql_real_escape_string($_POST['answer'.$i])."',";
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
lmg
Forum Commoner
Posts: 34
Joined: Tue May 26, 2009 10:11 am

Re: mySQL Query Creation with a for Loop

Post by lmg »

Thanks for the help! I think it is working now.
Post Reply