[SOLVED] Multiple Row Insert

[jgifford]

I've created a Database schema that will allow the admin to easily update thier form. To do this I am using the data from the DB to dynamically create the form. For example, if the admin wants to add a new input to the form, they can do that by giving it a name and a type, such as text or radio.

 

I've written code that displays the form based on the data. I'm stuck on getting the results from the form inserted back to the DB. Hopefully I can explain this well enough without having to post all the details.

 

I have one table that is the responses from the form. Each row contains 5 columns that represent the request, the question, the option chosen, any details from the option chosen (text from a text field) and a customer id.

 

I need to do an insert that will create a new row for the user's response to each question. I'm having some difficulty creating this mutiple row insert. Here is what I have so far. I'm hoping it's just a syntax issue...but it could be more. Thanks in advance.

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1"))  
{ 
foreach ($_POST as $var => $value)  
{  
    if ($value != NULL) 
    { 
  $insertSQL = sprintf("INSERT INTO response (request_ID, questions_ID, options_ID, details, customer_ID) VALUES (%s, %s, %s, %s, %s)",  
                        GetSQLValueString($_POST['rid'], "int"),  
                       GetSQLValueString($var, "int"),  
                       GetSQLValueString($value, "int"),  
                       GetSQLValueString($value, "text"),  
                       GetSQLValueString($_POST['cid'], "int")); 
                        
  mysql_select_db($database_connDBName, $connDB); 
  $Result1 = mysql_query($insertSQL, $connDB) or die(mysql_error()); 
     } 
} 
}  

[DeanWhitehouse]

What difficulty?

 

Any errors?

 

Add error_reporting(E_ALL); to the top of the script

[jgifford]

Sorry, forgot that, I get no results. Nothing displays on the page. I added error_reporting(E_ALL); to the top of the script and still got nothing.

[DeanWhitehouse]

The code you shown us wouldn't print anything out unless their was a problem with the query?

 

Or do you mean that nothing is inserted into the db

[jgifford]

Nothing is inserted into the database.

 

I have some other code that displays on the page, but when I add this insert, nothing displays.

[trq]

We need to see the form posting to this script. The values for options_ID and details are the same according to your logic, I'm sure thats not meant to be the case.

 

Echo $insertSQL and you might see what I meen.

[DeanWhitehouse]

Ok, well it could be something to do with sprint_f

(i have never used it like that)

 

This is how i would do it

 

  $insertSQL ="INSERT INTO response (request_ID, questions_ID, options_ID, details, customer_ID) VALUES ('".mysql_real_escape_string($_POST['rid'])."', '".$var."', '".$value."','".$value."','".mysql_real_escape_string($_POST['cid'])."')";

 

Also check the actual db to see if it has been inserted.

[jgifford]

We need to see the form posting to this script. The values for options_ID and details are the same according to your logic, I'm sure thats not meant to be the case.

 

Echo $insertSQL and you might see what I meen.

 

The form posting to the script is very dynamic, so I'm not sure how helpful it will be as most of the details are in the DB. If after looking at my new code a solution isn't apparent, I will do my best to post the form with comments that will be helpful.

 

You were right about options_ID and details having to be different values. I have made a change accordingly.

 

I also followed this advice:

 

Ok, well it could be something to do with sprint_f

(i have never used it like that)

 

This is how i would do it

 

  $insertSQL ="INSERT INTO response (request_ID, questions_ID, options_ID, details, customer_ID) VALUES ('".mysql_real_escape_string($_POST['rid'])."', '".$var."', '".$value."','".$value."','".mysql_real_escape_string($_POST['cid'])."')";

 

Also check the actual db to see if it has been inserted.

 

My new code is giving the same result. The data is not inserted in the DB and I still get a blank screen.

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) 
{
foreach ($_POST as $var => $value) 
{ 
if ($value != NULL)
{

	$combinedID = explode("-",$var)); 
// $var is the input name, for text inputs the name is questions_ID followed by a dash followed by options_ID so we must split them apart to get the values. Example $var is 10-101 for text inputs
	if ($combinedID[1] != "" && $combinedID[1] != NULL)
	{
		 $insertSQL = "INSERT INTO response (request_ID, questions_ID, options_ID, details, customer_ID) VALUES ('".mysql_real_escape_string($_POST['rid'])."', '".(int)$combinedID[0]."', '".(int)$combinedID[1]."','".$value."','".mysql_real_escape_string($_POST['cid'])."')";
	}
	else 
// $var is the input name, $value is the input value, for radio inputs the name is the questions_ID and the value is the options_ID, radio inputs have no details. Example $var is 10, $value is 101 for radio inputs
	{
	  $insertSQL = "INSERT INTO response (request_ID, questions_ID, options_ID, details, customer_ID) VALUES ('".mysql_real_escape_string($_POST['rid'])."', '".(int)$var."', '".(int)$value."','".NULL."','".mysql_real_escape_string($_POST['cid'])."')";
	} 

	mysql_select_db($database_connDBName, $connDB); 
  $Result1 = mysql_query($insertSQL, $connDB) or die(mysql_error()); 
	}
}
} 

[trq]

Can you echo $insertSQL?

[jgifford]

Can you echo $insertSQL?

 

I've tried. I just get a blank page as before.

[trq]

Then it would appear that this...

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1"))

 

condition is not being met.

 

Can we see the html output of your form?

[jgifford]

Then it would appear that this...

 

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1"))

 

condition is not being met.

 

Can we see the html output of your form?

 

Thanks for your assistance.

 

The condition is not being met because the page isn't being loaded. The only way for that condition to be met is by clicking the submit button:

 

<input type="hidden" name="MM_insert" value="form1" />

 

There is no html output because something in the insert is causing the page not to load.

 

If I remove the insert, then I get output.

[trq]

Sorry but you've lost me. Your wondering why your insert isn't working yet...

 

The condition is not being met because the page isn't being loaded. The only way for that condition to be met is by clicking the submit button:

 

?

[DeanWhitehouse]

Without seeing the whole code we can't really help

[jgifford]

Sorry but you've lost me. Your wondering why your insert isn't working yet...

 

The condition is not being met because the page isn't being loaded. The only way for that condition to be met is by clicking the submit button:

 

?

 

If I take the insert out the page loads the form and the rest of the page. If I add the insert nothing loads. Doesn't that mean there is something wrong with the insert?

[jgifford]

Without seeing the whole code we can't really help

 

Here is the form, let me know if I need to post more. Thanks again for your help.

 

    <form id="form1" name="form1" method="post" action="testSectionsResponses.php">
    <?php 
$newSection = TRUE;
$countSection = 0;
    ?>
    <?php if ($requestID == NULL) 
{ 
    $requestID += $row_rsResponses[request_ID']; 
}  
     ?>
    <?php do { ?>
    <?php $questionDescription = $row_rsQuestions['questions_description']; // $questionDescription is set here so we can use it later for questions that have multiple options. ?>
    <div id="section<?php echo $row_rsSections['sections_ID']; ?>">
    <h2><?php echo $row_rsSections['sections_description']; ?></h2>
      <?php do { ?>
       <?php if ($row_rsQuestions['section_ID'] != $row_rsSections['sections_ID']) 
   {
   // If we've reached a new section of questions we need to break out and start a new section
        $newSection = TRUE;
        break;
                } 
        ?>   
      <?php if ($row_rsQuestions['questions_description'] != $questionDescription || $newSection) { 
// Here is where we test for questions with multiple options, we also check to see if we are in a new section, if so we don't output the question data yet.
      ?>
      <p><?php echo $row_rsQuestions['questions_description']; ?></p>
      <?php // We need to change the input name/value if it is a text field as opposed to a radio button. If it is a text field, then it won't have a value as the user will input the value. ?>
              <?php if ($row_rsQuestions['options_Type'] == "text") { ?>
              <?php echo $row_rsQuestions['options_Description']; ?> <input name="<?php echo $row_rsQuestions['questions_ID']; ?>-<?php echo $row_rsQuestions['options_ID']; ?>" type="<?php echo $row_rsQuestions['options_Type']; ?>"/>
              <?php } else { ?>
              <?php echo $row_rsQuestions['options_Description']; ?> <input name="<?php echo $row_rsQuestions['questions_ID']; ?>" type="<?php echo $row_rsQuestions['options_Type']; ?>" value="<?php echo $row_rsQuestions['options_ID']; ?>" />
              <?php } ?>
      <?php } else { ?>
      <?php if ($row_rsQuestions['options_Type'] == "text") { ?>
        <?php echo $row_rsQuestions['options_Description']; ?> <input name="<?php echo $row_rsQuestions['questions_ID']; ?>-<?php echo $row_rsQuestions['options_ID']; ?>" type="<?php echo $row_rsQuestions['options_Type']; ?>" />
        <?php } else { ?>
        <?php echo $row_rsQuestions['options_Description']; ?> <input name="<?php echo $row_rsQuestions['questions_ID']; ?>" type="<?php echo $row_rsQuestions['options_Type']; ?>" value="<?php echo $row_rsQuestions['options_ID']; ?>" />
        <?php } ?>
        <?php } ?>
        <?php $questionDescription = $row_rsQuestions['questions_Description']; $newSection = FALSE; ?>
        <?php } while ($row_rsQuestions = mysql_fetch_assoc($rsQuestions)); ?>
     </div> 
     <?php $countSection++; ?>
     <?php } while (($row_rsSections = mysql_fetch_assoc($rsSections))); ?> 
     <br />
      <input name="back" type="button" id="back" value="Back" disabled="disabled" onClick="goBack(<?php echo $countSection; ?>);"/>
      <?php for ($i=1; $i<=$countSection; $i++) { ?>
      <input name="<?php echo 'goto' . $i; ?>" type="button" value="<?php echo 'Step ' . $i; ?>" onClick="goToSection(<?php echo $i . "," , $countSection; ?>);"/>
      <?php } ?>
      <input name="next" type="button" id="next" value="Next" onClick="goNext(<?php echo $countSection; ?>);"/>
      <input type="text" name="rid" value="<?php echo $requestID ?>" />
      <input type="text" name="cid" value="1" />
      <input name="submit" type="submit" value="submit" />
      <input type="hidden" name="MM_insert" value="form1" />
    </form>

[phparray]

If I take the insert out the page loads the form and the rest of the page. If I add the insert nothing loads. Doesn't that mean there is something wrong with the insert?

 

Yes if the page loads without some code this is a good indication of a syntax error within that code.

 

What if you separate the insert action and put it on its own page.  Change your from action to submit to the new file. This way the form will load and you can troubleshoot the insert problem on another file.

[jgifford]

If I take the insert out the page loads the form and the rest of the page. If I add the insert nothing loads. Doesn't that mean there is something wrong with the insert?

 

Yes if the page loads without some code this is a good indication of a syntax error within that code.

 

What if you separate the insert action and put it on its own page.  Change your from action to submit to the new file. This way the form will load and you can troubleshoot the insert problem on another file.

 

Good suggestion. Thanks. I've done this, but am not sure what else to try to troubleshoot this. Any suggestions?

[phparray]

in both the if and the else statement you are setting $insertSQL Do you get anything by using echo $insertSQL; 

 

Also add echo 'something'; after each line of code to figure out where it is failing.

 

This should help you to narrow it all the way down to the line. 

 

If you get output from echo $insertSQL; post it here so we can see it.

[jgifford]

I don't get anything by using echo $insertSQL.

 

I added echo 'something'; after each line, including the first line of the page and still nothing.

 

[phparray]

The only thing left is to look at the server logs.  If you have access to the server check the apache error log.

 

if you're on linux and have ssh access run tail -f /path/to/file

 

Watch the output as you run the script.  If you don't have access to the error log work with your provider.

 

Also make sure your php.ini file has display_errors turn on for testing.

 

On windows check the php log path in the ini file.  Run your script and check out the bottom of that file.

[jgifford]

Thanks phparray. I actually set up a local site and turned display_errors on and was able to do the troubleshooting I needed.

 

I had an extra ")" in one spot and a missing "}" in a spot that I didn't post here. Working as expected now.

 

Thanks again for everyone's help.