Arrays, Forms, and SQL Headache.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Arrays, Forms, and SQL Headache.

Post 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!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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)?
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Symptoms

Post 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).
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 ->
?>
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Uhmn.... Dunno what happened

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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)
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Complete info

Post 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!!! :)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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';
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

something small.

Post 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
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Works without the loop

Post 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...... :?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 ;)
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Fantastic!

Post 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
Post Reply