Page 1 of 1

Arrays, Forms, and SQL Headache.

Posted: Mon Oct 27, 2003 6:58 am
by gjb79
Hey everyone. I stumbled across something I thought I knew how to do, only to find that what I have tried is not working.

:arrow: The challenege is to take a form, with a list element that allows multiple selection (selectfiles), and have the multiple selections added to an sql database. The table (filetopage) contains three columns, ID, pageid, fileid. There is an additional hidden form element that has the value of pageid as a 1, 2, 3, or 4.

Thus far I have tried the following:

Code: Select all

<?php
  foreach ( $HTTP_POST_VARS['selectfiles'] as $filevalue ) {
  $insertSQL = sprintf("INSERT INTO filetopage (page, file) VALUES (%s, %s)",
                       GetSQLValueString($HTTP_POST_VARS['pageid'], "int"),
                       GetSQLValueString($filevalue, "int"));
  }
?>
:idea: My logic says when the form is submitted, this script will execute, inserting a new entry for each item that was selected in the list form.

:?: Does anybody see the problem?
Thanks again!

Posted: Mon Oct 27, 2003 8:22 am
by volka
what are the symptoms?
Maybe the string does not contain what you expect. Did you print it for debugging?
When and how do you execute the query? Does this return any error (you might track)?

Symptoms

Posted: Mon Oct 27, 2003 8:36 am
by gjb79
Ok, when that script is executed, only one entry is inserted into the database. I have five items that should be on the form's list element. Each value is numbers 1 through 5.

When the form is submitted, whichever items are selected should become a new entry in the database. the form also has a hidden entry called "pageid" with a value of 1. The table would look like this if items 2 and 4 where selected from the form.

filestopage <--Table name
Page | Files <-- Column names
1 | 2
1 | 4

Insted I will get only one of the numbers from the list (the last set of numbers I believe).

Posted: Mon Oct 27, 2003 8:48 am
by volka
can it be the code for executing the insert-statement is at the wrong position?

Code: Select all

<?php
  foreach ( $HTTP_POST_VARS['selectfiles'] as $filevalue ) {
  $insertSQL = sprintf("INSERT INTO filetopage (page, file) VALUES (%s, %s)",
                       GetSQLValueString($HTTP_POST_VARS['pageid'], "int"),
                       GetSQLValueString($filevalue, "int"));
  // <- database insert code here ->
  }

  // <- not here, since it would be outside the loop => executed only once ->
?>

Uhmn.... Dunno what happened

Posted: Mon Oct 27, 2003 9:01 am
by gjb79
:? Ok so I tried that out, and I dont know what happened but now I dont get anything added to the database.

Code: Select all

<?php
 foreach ( $HTTP_POST_VARS['selectfiles'] as $filevalue ) { 
  $insertSQL = sprintf("INSERT INTO filetopage (page, file) VALUES (%s, %s)", 
                       GetSQLValueString($HTTP_POST_VARS['pageid'], "int"), 
                       GetSQLValueString($filevalue, "int")); 

  mysql_select_db($database_cms, $cms);
  $Result1 = mysql_query($insertSQL, $cms) or die(mysql_error());
 }
?>
Weird.

If there is an error with this, please let me know. Thanks

Posted: Mon Oct 27, 2003 9:21 am
by volka
uh? Maybe more debug output?

Code: Select all

<?php
mysql_select_db($database_cms, $cms) or die(mysql_error());
echo count($HTTP_POST_VARS['selectfiles']), ' entries in HTTP_POST_VARS[selectfiles]<br />';

foreach ( $HTTP_POST_VARS['selectfiles'] as $filevalue ) {
  $insertSQL = sprintf("INSERT INTO filetopage (page, file) VALUES (%s, %s)",
                       GetSQLValueString($HTTP_POST_VARS['pageid'], "int"),
                       GetSQLValueString($filevalue, "int"));

  // debug only
  echo '<div style="border: 1px solid black; display: block;">', htmlentities($insertSQL), '<br />';
  $Result1 = mysql_query($insertSQL, $cms);
  if ($Result1 === FALSE)
  	echo mysql_error();
  else
  	echo 'done. Affected rows: ', mysql_affected_rows($cms);
  echo '</div>';
}
?>
(script tested not even by compiler)

Complete info

Posted: Mon Oct 27, 2003 9:26 am
by gjb79
I thought I'd put all the info I had up so that if the problem is something I haven't posted, ya'll might catch it. Thanks!!!

Here is the form code (populated by a database inqury btw):

Code: Select all

<form action="<?php echo $editFormAction; ?>" method="POST" name="updatepage" id="updatepage">

<select name="selectfiles" size="5" multiple class="textlist" id="selectfiles">
   <?php do { ?>
<option value="<?php echo $row_filelist['ID']; ?>"><?php echo $row_filelist['filename']; ?></option>
   <?php } while ($row_filelist = mysql_fetch_assoc($filelist)); ?>
</select>

<input type="submit" name="Submit" value="Submit">
<input type="hidden" name="MM_insert" value="updatepage">

</form>
$row_filelist['ID']; - outputs a number. since there are five entries, the numbers go from 1-5 accordingly


Here is the code to process the form:

Code: Select all

<?php
if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "updatepage")) {
 foreach ( $HTTP_POST_VARS['selectfiles'] as $filevalue ) { 
  $insertSQL = sprintf("INSERT INTO filetopage (page, file) VALUES (%s, %s)", 
                       GetSQLValueString($HTTP_POST_VARS['pageid'], "int"), 
                       GetSQLValueString($filevalue, "int")); 

  mysql_select_db($database_cms, $cms);
  $Result1 = mysql_query($insertSQL, $cms) or die(mysql_error());
 }
}
?>
Thanks for your help!!! :)

Posted: Mon Oct 27, 2003 9:43 am
by volka
ah, right.
Doesn't explain why nothing's inserted by now but anyway...
<select name="selectfiles" size="5" multiple class="textlist" id="selectfiles">
try

Code: Select all

&lt;select name="selectfiles" size="5" multiple class="textlist" id="selectfiles&#1111;]"&gt;
php treats the post/get-params almost like php-code, i.e.

Code: Select all

$a = '1';
$a = '2';
$a = '3';
vs.

Code: Select all

$a[] = '1';
$a[] = '2';
$a[] = '3';

something small.

Posted: Mon Oct 27, 2003 10:00 am
by gjb79
Arrays definately arn't my strong suite.

When I change the id to selectfiles[], do I change the php code up above as well? Also should I set name = selectfiles[] as well in the input code?

Thank you sooo much

Works without the loop

Posted: Mon Oct 27, 2003 10:12 am
by gjb79
Hey, I discovered that without the loop it inserts to the sql database. Now as for why the loop blocks that I'm not sure.

Code: Select all

<?php
if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "updatepage")) {
  $insertSQL = sprintf("INSERT INTO filetopage (page, file) VALUES (%s, %s)",
                       GetSQLValueString($HTTP_POST_VARS['pageid'], "int"),
                       GetSQLValueString($HTTP_POST_VARS['selectfiles'], "int"));

  mysql_select_db($database_cms, $cms);
  $Result1 = mysql_query($insertSQL, $cms) or die(mysql_error());
}
?>
It seems to me that when we add the

Code: Select all

<?php
  foreach ( $HTTP_POST_VARS['selectfiles'] as $filevalue ) {
?>
code, the selectfiles array is not passed on to the $filevalue variable.

So when it trys to insert, there is nothing there for it to insert. Then again, it should atleast put the pageid up in the page column and leave the other blank. hmmm...... :?

Posted: Mon Oct 27, 2003 10:44 am
by volka
only the select element's name has to be changed.
Can you please try this code

Code: Select all

<form action="<?php echo $editFormAction; ?>" method="POST" name="updatepage" id="updatepage">
	<select name="selectfiles[]" size="5" multiple="multiple" class="textlist" id="selectfiles">
	<?php do { ?>
		<option value="<?php echo $row_filelist['ID']; ?>"><?php echo $row_filelist['filename']; ?></option>
  <?php } while ($row_filelist = mysql_fetch_assoc($filelist)); ?>
	</select>

	<input type="submit" name="Submit" value="Submit" />
	<input type="hidden" name="MM_insert" value="updatepage" />
</form>
and

Code: Select all

<?php
mysql_select_db($database_cms, $cms) or die(mysql_error());
if (!isset($HTTP_POST_VARS['selectfiles']))
	die('missing parameter: selectfiles');

if (!is_array($HTTP_POST_VARS['selectfiles']))
	die('unexpected type of selectfiles');
	
echo count($HTTP_POST_VARS['selectfiles']), ' entries in HTTP_POST_VARS[selectfiles]<br />';

foreach ( $HTTP_POST_VARS['selectfiles'] as $filevalue ) {
  $insertSQL = sprintf("INSERT INTO filetopage (page, file) VALUES (%s, %s)",
                       GetSQLValueString($HTTP_POST_VARS['pageid'], "int"),
                       GetSQLValueString($filevalue, "int"));

  $Result1 = mysql_query($insertSQL, $cms);
  // debug only
  echo '<div style="border: 1px solid black; display: block;">', htmlentities($insertSQL), '<br />';
  if ($Result1 === FALSE)
     echo mysql_error();
  else
     echo 'done. Affected rows: ', mysql_affected_rows($cms);
  echo '</div>';
}
?>
haven't tested it myself. Parse errors are possible ;)

Fantastic!

Posted: Mon Oct 27, 2003 11:44 am
by gjb79
8O Thank you sooo much Volka, the code you whipped up worked brilliantly. I'm studying what I had vs what you did to see if I can understand what was wrong. So far no such luck, though I'm sure I will figure it out shortly. :)

Btw, I noticed that you're from Germany, so does Volka mean anything?

Thanks again! :D