insert data only when user fills in field

ejwf

I am working on a user entry form that querys the mysql database, and creates the form based on the results of the query.
Then I want to only insert records into the database where the user has completed the row on the form and ignore the rows where there is no input.

The code I have so far creates the user entry form OK, but inserts all the rows into the database regardless of whether there is user entered data in there or not. For the rows where there is no user entry I get '0' records in the database (which causes me problems later down the line).

Can anyone suggest a way of cleaning this up so I only get a record inserted when the user puts data in the form - TIA

code I have so far;
to create the form

$query = "SELECT (Type) AS Type, (No) AS No, (Name) AS Location, (Id) AS Id, (List) AS List FROM `tbl1` WHERE Ref = '$Ref' "; 
//variable $Ref set earlier in the code

$result2 = mysql_query($query) 
or die(mysql_error());

if ($result2)
{		
echo' <form action="process.php" method="POST">
<table align="centre" cellspacing="0" cellpadding="0"> 
<tr>
<td align="left"></td>
<td align="left"></td>
<td align="left"><b>Ref</b></td>
<td align="left"><b>Number</b></td>
<td align="left"><b>Location</b></td>
<td align="right"><b>Code</b></td>
<td align="left"></td>
<td align="left"><b></b></td>
</tr>';

while ($row2 = mysql_fetch_array($result2,MYSQL_NUM)) 
{ 
echo "<td width=\"10\" align=\"left\"><input type='hidden' name='Ref' value='$Ref'></td>
<td width=\"10\" align=\"left\"><input type=\"hidden\" name=\"List\" value\"{$row2[4]}\"></td>
<td width=\"70\" align=\"left\"> {$row2[0]}</td> 
<td width=\"120\" align=\"left\"> {$row2[1]}</td> 
<td width=\"200\" align=\"left\"> {$row2[2]}</td> 
<td width=\"30\" align=\"right\"> {$row2[3]}</td>";

if ($row2[4] == 1)
{
echo "<td width=\"10\" align=\"left\"> {$row2[7]} <input type=\"text\" name=\"Id[]\" size=\"3\"></td>
<td width=\"200\" align=\"left\"> {$row2[5]} Size <input type=\"text\" name=\"Size[]\"></td>
<td width=\"300\" align=\"left\"><input type=\"hidden\" name=\"Colour[]\" value\"{NULL}\"></td>";
}
else 
{
echo "<td width=\"1\" align=\"left\"> {$row2[7]} <input type=\"text\" name=\"[]\" size=\"3\"></td>
<td width=\"200\" align=\"left\"> {$row2[5]} Size <input type=\"text\" name=\"Size[]\"> </td> 
<td width=\"300\" align=\"left\"> {$row2[6]} Colour <input type=\"text\" name=\"Colour[]\"></td>";
}
echo "</font></tr>\n"; 
}  // close while loop 
} //close main if

echo '</table>';
echo "<br><p align=\"center\"><input type=\"submit\" name=\"submit\" value=\"Submit\"> "; 
echo '</form>';

to insert the data

// check to see if id has been submitted
if(is_array($_POST['Id'])  && count($_POST['Id'])>0) 
{
 foreach($_POST['Id'] as $value) 
  { 
 if (empty($_POST['Id']) && !empty($_POST['Colour']))
	{ 
	$empty=false;
	}
elseif (empty($_POST['Id']) && empty($_POST['Colour']))
	{ 
	$empty=true;
	}
  }	// close foreach
} // close if

// if ref is empty give error message
if($empty) 
  { 
    echo"<br><br><br><br><font face=\"Arial, Helvetica, sans-serif\">Sorry, it appears that the <b>Code</b>
	is missing.<br><br>Please click on the back arrown of your browser to return to the 
     input form, and re-enter this code.  <br>Thank you</font>"; 
  } 

// if ref is not empty process form
 if(!$empty)
 { 

// sets date into correct format to match database
$today = date('YmdHis');

$Size = $_POST['Size'];
if ($Size IS NOT NULL)
{
$arr_size = count($_POST['Size']); 
for ($i=0;$i< $arr_size;$i++) 
{ 
    $Size = strip_tags(trim($_POST['Size'][$i])); 
    $Colour = strip_tags(trim($_POST['Colour'][$i])); 
    $Id = ($_POST['Id'][$i]); 


$query = "INSERT INTO tblResults (DateRef,Ref,SRef,CRef,Size,Colour,By)
		VALUES ('$DateRef','$Id','$SRef','$CRef','$Size','$Colour','$UserId')";
$result = mysql_query($query)
or die(mysql_error());
} //close for
} //close if

echo "<p></p><p></p><p></p>
<p><div align=\"center\"><font face=\"Arial, Helvetica, sans-serif\" size=\"3\"><b><font color=\"#FF0000\">
Thank you $username, <br> <br> your information has been added<br> and will be processed accordingly.
</font></b></font></div></p>";

tomhath

I think the problem is in the first "foreach" loop. You set variable $empty every time through the loop, but then you use it after the loop has finished. Whatevery it was set to on the last time through the loop is used in the rest of the page.

if(is_array($_POST['Id'])  && count($_POST['Id'])>0) 
{
 foreach($_POST['Id'] as $value) 
  { 
     if (empty($_POST['Id']) && !empty($_POST['Colour'])) { 
        $empty=false;
     }
     elseif (empty($_POST['Id']) && empty($_POST['Colour'])) { 
        $empty=true;
     }
  }    // close foreach
} // close if

// ######### $empty is set to ???? here
if($empty)

cheese

Hi ejwf,

This is the sort of thing I use sometimes, between pages for checking, but I am sure someone will offer a better way.

The whole lot would need changing to fit your requirements, but might give you an idea.

Here it is;

if (($_POST[field1] != '') AND ($_POST[field2] != '') AND ($_POST[field1] != ''))  // as many things as you want  '' is 2 single quotes
{
	header("Location: insertpage.php");
}
else
{
// you could pass back stuff here with gets if you wanted
	header("Location: formpage.php?thingto be passed=x&anotherthing=y");
}

die ("Error in log in 2");
?>

Please understand my code is not as slick as some, but it usually works.

Just one other comment, in you code you have;

Thank you $username, your information has been added and will be processed accordingly.

If you used a css file you could put the details of the font in to the css and then the code would be

<p class=something>Thank you $username, <br> <br> your information has been added<br> and will be processed accordingly. 
</p>

The entry in the css could be something like;

p.something {color : black ; font-family:Lucida Sans Unicode, Lucida Grande, Arial narrow, Lucida Sans Unicode, Lucida Grande, Arial ,helvetica,sans-serif; font-weight: bold; font-size: 8pt ;}

You can use the whenever you want in the site and just by changing the values in the css you would change every page with on it. Makes reworks really easy.

Cheers,

Neil.

ejwf

TOMHATH

I think the problem is in the first "foreach" loop. You set variable $empty every time through the loop, but then you use it after the loop has finished. Whatevery it was set to on the last time through the loop is used in the rest of the page.

I am not sure I understand, I thought that in closing both the if and foreach parts of the code I could move on to the next 'if'.
anyway I have re-run the code commenting out the whole section with this first foreach loop and it still doesn't do what I want (to only insert rows where user has entered data and ignore the others)

Cheese

if (($_POST[field1] != '') AND ($_POST[field2] != '') AND ($_POST[field1] != ''))  // as many things as you want  '' is 2 single quotes 
{ 
    header("Location: insertpage.php"); 
} 
else 
{ 
// you could pass back stuff here with gets if you wanted 
    header("Location: formpage.php?thingto be passed=x&anotherthing=y"); 
} 

die ("Error in log in 2");

I understand where you are pointing me with this code and I have had a play around but cannot get anything to work - this is driving me mad !!!
Taking your principle of if ($POST[field1] != '') and adding a bit to it because I am going to have an array of $POST[field1] I have come up with the following - but needless to say it doesn't work

// if user has not entered ref, run error message
if (is_array($_POST['Size'])  && count($_POST['Size'])>0) 
{
 foreach($_POST['Size'] as $value) 
  { 
 if (!empty($_POST['Size']) && empty($_POST['Id']))
	{ 
echo"<br><br><br><br><font face=\"Arial, Helvetica, sans-serif\">Sorry, it appears that the <b>Code</b>
	is missing.<br><br>Please click on the back arrow of your browser to return to the 
    input form, and re-enter this code.  <br>Thank you</font>"; 
    } // close 2nd if
  } // close foreach
} // close if

else if(is_array($_POST['Size'])  && count($_POST['Size'])>0) 
{
 foreach($_POST['Size'] as $value) 
  { 
 if (!empty($_POST['Size']) && !empty($_POST['Id']))
	{ 
// if user has entered data, run the code
$arr_size = count($_POST['Size']); 
for ($i=0;$i< $arr_size;$i++) 
       { 
    $Size = strip_tags(trim($_POST['Size'][$i])); 
    $Colour = strip_tags(trim($_POST['Colour'][$i])); 
    $Id = ($_POST['Id'][$i]); 

$query = "INSERT INTO tblResults (DateRef,Ref,SRef,CRef,Size,Colour,By) 
        VALUES  ('$DateRef','$Id','$SRef','$CRef','$Size','$Colour
','$UserId')"; 
$result = mysql_query($query) 
or die(mysql_error()); 
       } //close for

echo "<p></p><p></p><p></p> 
<p><div align=\"center\"><font face=\"Arial, Helvetica, sans-serif\" size=\"3\"><b><font color=\"#FF0000\"> 
Thank you $username, <br> <br> your information has been added<br> and will be processed accordingly. 
</font></b></font></div></p>";
     } //close 2nd if
   }// close foreach
} // close if

by the way Cheese - thanks for the css tip - will get round to doing that when I sort this problem out !

Can anyone see a way out of this - I am under MAJOR PRESSURE to get this working !!

tomhath

I thought that in closing both the if and foreach parts of the code I could move on to the next 'if'.

Yes, you can move on.

But look at your code. You check whether $empty is true or false. If it's true you insert all of the rows. If it's false you don't insert any. The value of $empty doesn't tell you if the row you are inserting is empty or not; it only tells you if the last row in the form is empty, because that's the value that was set when the for loop ended.

ejwf

Thanks for explaining that, but how do I get round it?

I need the code to look at each row in turn, check to see if it is empty or not, if it isn't empty run the code, then move on to the next row.

my limited php brain just doesn't stretch that far - thanks if you can help.

tomhath

In the first "for" loop where you are setting $empty to true or false, just do the INSERT or write out an error message when you find a blank field. No need for the second loop.

ejwf

I am still having problems with this code
I still cannot get it to go through the loop and only insert when the user enters data in a row.
Can you please tak a look at the code and see what you think - thanks

I'm not using $empty = true / false anymore becuase I thought that might be part of the problem so I am checking to see if the $POST[] is empty / !empty and trying to make it work that way, but I cannot think of the correct code to say if both fields are empty don't do anything, just move on ........

if (is_array($_POST['Size'])  && count($_POST['Size'])>0) 
{
 foreach($_POST['Size'] as $value) 
  { 
// if the user has put the size in but not the code then give an error message 
// By the way this doesn't work, it insert the record anyway without the code
 if (!empty($_POST['Size']) && empty($_POST['Id']))
	{ 
echo"<br><br><br><br><font face=\"Arial, Helvetica, sans-serif\">Sorry, it appears that the <b>Code</b>
	is missing.<br><br>Please click on the back arrow of your browser to return to the 
    input form, and re-enter this code.  <br>Thank you</font>"; 
    } // close if

// if the user has put the size and code in, insert the record
// this works (but only in that it inserts all the records
   else if (!empty($_POST['Size']) && !empty($_POST['Id']))	
    {
	$arr_size = count($_POST['Size']); 
    for ($i=0;$i< $arr_size;$i++) 
       { 
    $Size = strip_tags(trim($_POST['Size'][$i])); 
    $Colour = strip_tags(trim($_POST['Colour'][$i])); 
    $Id = ($_POST['Id'][$i]); 

$query = "INSERT INTO tblResults (DateRef,MRef,SRef,CRef,Size,Colour,LoadedBy)
		VALUES ('$DateRef','$MId','$SRef','$CRef','$Size','$Colour','$UserId')";
$result = mysql_query($query)
or die(mysql_error());
       } //close for
	} // close else if

else if (empty($_POST['Size']) && empty($_POST['Id'])
{
            // this is the bit I can't work out - how do you say, move on to the next record in the loop?
            } // close else if
  } // close foreach
} // close if

tomhath

Your loops still weren't right. I didn't try this, but it should be pretty close. It'll echo a warning for every row that's missing data, you'll probably want to add a row number or flag to keep it from printing the message on every error.

if (is_array($_POST['Size'])  && count($_POST['Size'])>0) 
{
  $arr_size = count($_POST['Size']); 
  for ($i=0; $i< $arr_size; $i++) 
  { 
    // if the user has put the size in but not the code  

    //
    if (!empty($_POST['Size'][$i]) && empty($_POST['Id'][$i]))
    { 
            echo "<br><br><br><br>" .
                 "<font face=\"Arial, Helvetica, sans-serif\">" .
                 "Sorry, it appears that the <b>Code</b> " .
                 "is missing.<br><br>Please click on the back" .
                 "arrow of your browser to return to the " .
                 "input form, and re-enter this code.  " .
                 "<br>Thank you" .
                 "</font>";
    }
    // if the user has put the code in but not the size  

    //
    else if (empty($_POST['Size'][$i]) && !empty($_POST['Id'][$i])
    {
          echo "<br><br><br><br><font face=\"Arial, Helvetica, sans-serif\">" .
               "Sorry, it appears that the <b>size</b> " .
               "is missing.<br><br>Please click on the back" .
               " arrow of your browser to return to the " .
               "input form, and re-enter this code.  <br>Thank you</font>"; 
    }
    // if the user has put neither the size in nor the code  

    //
    else if (empty($_POST['Size'][$i]) && empty($_POST['Id'][$i])
    {
          echo"<br><br><br><br><font face=\"Arial, Helvetica, sans-serif\">" .
              "Sorry, it appears that the <b>size</b> and <b>Code</b> " .
              "are missing.<br><br>Please click on the back" .
              " arrow of your browser to return to the " .
              "input form, and re-enter this code.  <br>Thank you</font>"; 
    }
    // the user has put the size and code in, insert the record
    else    

    {
      $Size   = strip_tags(trim($_POST['Size'][$i])); 
      $Colour = strip_tags(trim($_POST['Colour'][$i])); 
      $Id     = ($_POST['Id'][$i]); 

  $query = "INSERT INTO tblResults (DateRef,MRef,SRef,CRef,Size,Colour,LoadedBy) " .
           " VALUES  ('$DateRef','$MId','$SRef','$CRef','$Size','$Colour','$UserId')";

  $result = mysql_query($query) or die(mysql_error());
} 
  }  // close for i < $arr_size
} //close if count > 0

ejwf

thank you so much tomhath

first of all

you'll probably want to add a row number or flag to keep it from printing the message on every error.

can you tell me what this means and point me in the right direction - I definitely need this because there are going to be instances where there are 30+ rows and 30+ error messages is not a good look for a website !.

tried the code - so close, but still not working !

you have broken the code down into 4 clauses

a) if size if there but not code, echo this ....
b) else if code is there but not size, echo this ...
c) else if neither size nor code is there, echo this ...
d) else, insert the records

if I have all 4 clauses in the code it doesn't insert anything, it gives the error message for clause c) as if there is nothing there.

so if I comment out clause c) it inserts all the records inserting 0 values on the rows where there is no user input.

any ideas?

thanks

tomhath

I don't see anything wrong in the second page. If it's not working there must be something wrong with the first page. This line looks wrong to me, but there could be others. Try dumping out the Size and Id arrays to see what's being posted:

echo "<td width=\"1\" align=\"left\"> {$row2[7]} <input type=\"text\" name=\"[]\" size=\"3\"></td>

ejwf

I have done print_r on the second page for 3 of the $_POST[] s from the first page and I get what I would expect.
I put data in the 3 out of 5 rows and got this
Array ( [0] => 205 [1] => 204 [2] => 201 [3] => [4] => )
Array ( [0] => 222 [1] => 444 [2] => 666 [3] => [4] => )
Array ( [0] => 111 [1] => 333 [2] => 555 [3] => [4] => )

so it it creating the arrays as I would expect ....

tomhath

When I fixed the missing "Id" tag mentioned earlier it all seems to work okay. Put these lines at the top of the second page and see what you get:

echo '<br/>Size is array: ' . is_array($_POST['Size']);
echo '<br/>Id is array: ' . is_array($_POST['Id']);
echo '<br/>Colour is array: ' . is_array($_POST['Colour']);

echo "<p/>Size:<br/>";
$arr_size = count($_POST['Size']); 
for ($i=0; $i< $arr_size; $i++) 
{ 
  echo "<br/>Size[$i] " . $_POST['Size'][$i];
}

echo "<p/>Id<br/>";
$arr_size = count($_POST['Id']); 
for ($i=0; $i< $arr_size; $i++) 
{ 
  echo "<br/>Id[$i] " . $_POST['Id'][$i];
}

echo "<p/>Colour<br/>";
$arr_size = count($_POST['Colour']); 
for ($i=0; $i< $arr_size; $i++) 
{ 
  echo "<br/>Colour[$i] " . $_POST['Colour'][$i];
}

if (is_array($_POST['Size'])  && count($_POST['Size'])>0) 
... rest of your page

ejwf

thanks
if I do the same and enter 3 out 5 rows of data, I get ...

Array ( [0] => 205 [1] => 204 [2] => 201 [3] => [4] => )
Array ( [0] => 222 [1] => 444 [2] => 666 [3] => [4] => )
Array ( [0] => 111 [1] => 333 [2] => 555 [3] => [4] => )

Size is array: 1
Id is array: 1
Colour is array: 1

Size:

Size[0] 111
Size[1] 333
Size[2] 555
Size[3]
Size[4]

Id[0] 205
Id[1] 204
Id[2] 201
Id[3]
Id[4]

Colour

Colour[0] 222
Colour[1] 444
Colour[2] 666
Colour[3]
Colour[4]

tomhath

Is it inserting the rows correctly now? If not you'll have to echo the POST variables inside it "if" block where it ends up. It looks to me like it has to work now.

ejwf

tomhath - Thank you so much, got it working now - drinks are on me !!!!

but ... can I ask one more favour before you go ....
If a user only enters 3 out of 5 rows, it is echoing the error message twice, once for each of the missing rows - you mentioned something in an earlier post about adding a 'row number or flag' to stop it doing this, but I don't know what you mean - can you please expand on this a bit (and I'll make your a double on the rocks !)

I can't tell you how relieved I am to get this far ! Monday morning is going to be SO much easier when I can tell them I (sorry you) got it working !.