Page 1 of 1

Cant add certain field but can update the field MySQL

Posted: Tue Nov 07, 2006 3:56 am
by JimiH
Hi

I have a form where a user can add a record & also update a record depending on which mode is selected.

There is one field which is giving me a problem "VALUEExworks"

When the mode is "Add" the contents of the field doesn't get posted, however when I "Update" the Value is posted?

I have checked the spelling, format etc in the "Add" mode section & the "Update" mode section and I cant see a difference.

Sorry for the long PHP section, I was going to cut the form down but thought it better to post in its entirity.

Code: Select all

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Branch</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<table width="775" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
  <tr>
    <td>
    <p align="center">
    <img border="0" src="images/companylogo.gif" width="668" height="92"></td>
  </tr>
  <tr>
    <td>      <table width="700" border="0" align="center" cellpadding="2" cellspacing="2">
        <tr>
          <td>&nbsp;</td>
        </tr>
        <tr>
          <td>
		  <?
		  $mode=$_GET["mode"];
		  if($mode=="add") {
		  ?>
          <form name="form1" method="post" action="formsubmit.php?mode=add">
            <table width="500" border="1" align="center" cellpadding="2" cellspacing="2">
              <tr>
                <td><strong>Add New VSI </strong></td>
                <td>&nbsp;</td>
              </tr>
              <tr>
                <td>VSI Number </td>
                <td>
                <input name="VSI_NUMBER" type="text" id="VSI_NUMBER" size="20"></td>
              </tr>
              <tr>
                <td>Engineer</td>
                <td><input name="ENGINEER" type="text" id="ENGINEER" size="20"></td>
              </tr>
              <tr>

         <td>Sales Man</td>
                <td><input name="Salesman" type="text" id="Salesman" size="20"></td>
              </tr>
              <tr>

			<td>Customer</td>
                <td><input name="CUSTOMER" type="text" id="CUSTOMER" size="20"></td>
              </tr>
              <tr>

			<td>Sales Group</td>
                <td>
                <input name="SALESGROUP" type="text" id="SALESGROUP" size="20"></td>
              </tr>
              <tr>

		<td>SIC Code</td>
                <td><input name="SICCODE" type="text" id="SICCODE" size="20"></td>
              </tr>
              <tr>

		<td>New/Existing</td>
                <td><select name="N_E"><option selected>New</option><option>Existing</option></select></td>
              </tr>
              <tr>

		<td>Year Qty</td>
                <td><input name="YEARQTY" type="text" id="YEARQTY" size="20"></td>
              </tr>
              <tr>

                <td>Check list ratio</td>
                <td>
                <input name="CHCKLRATIO" type="text" id="CHCKLRATIO" size="20"></td>
              </tr>
              <tr>

		<td>Value Ex works</td>
                <td>
                <input name="VALUEEworks" type="text" id="VALUEEworks" size="20"></td>
              </tr>
              <tr>

		<td>Date in (yyyy-mm-dd)</td>
                <td><input name="DATEIN" type="text" id="DATEIN" size="20"></td>
              </tr>
              <tr>

		<td>Order Recieved</td>
                <td><select name="orderrec"><option selected>No</option><option>Yes</option></select></td>
              </tr>
              <tr>

		<td>Comments</td>
                <td><input name="COMMENTS" type="text" id="COMMENTS" size="20"></td>
              </tr>
              <tr>

		<td>Action Date (yyyy-mm-dd)</td>
                <td>
                <input name="ACTIONDATE" type="text" id="ACTIONDATE" size="20"></td>
              </tr>
              <tr>

		<td>Status</td>
                <td><select name="Status"><option selected>Open</option><option>Closed</option></select></td>
              </tr>
              <tr>

		<td>Project Members</td>
                <td>
                <input name="Project_Members" type="text" id="Project_Members" size="20"></td>
              </tr>
              <tr>

		<td>@Gate</td>
                <td><select name="Gate"><option selected>1</option><option>2</option><option>3</option><option>4</option><option>5</option><option>6</option><option>7</option></</td>
              </tr>
              <tr>

		<td>Job Number</td>
                <td><input name="Job_No" type="text" id="Job_No" size="20"></td>
              </tr>
              <tr>


		<td>Catalogue Number</td>
                <td>
                <input name="Cata_Number" type="text" id="Cata_Number" size="20"></td>
              </tr>
              <tr>

		<td>Suspend</td>
                <td><select name="Suspend"><option selected>No</option><option>Yes</option></select></td>
              </tr>
              <tr>


                <td><input type="submit" name="Submit" value="Save Data"></td>
                <td>&nbsp;</td>
              </tr>
            </table>
          </form>
		  <?		
		  } else  {
		  	include("conn.php");
			$VSI_NUMBER=$_GET["VSI_NUMBER"];
			$sql="select * from $branch where VSI_NUMBER='$VSI_NUMBER'";
			
			$result=mysql_query($sql,$connection) or die(mysql_error());
			while($row=mysql_fetch_array($result)) {
				$VSI_NUMBER=$row['VSI_NUMBER'];
				$Product=$row['Product'];
				$ENGINEER=$row['ENGINEER'];
				$Salesman=$row['Salesman'];
				$CUSTOMER=$row['CUSTOMER'];
				$SALESGROUP=$row['SALESGROUP'];
				$SICCODE=$row['SICCODE'];
				$N_E=$row['N_E'];
				$YEARQTY=$row['YEARQTY'];
				$CHCKLRATIO=$row['CHCKLRATIO'];
				$VALUEExworks=$row['VALUEExworks'];
				$DATEIN=$row['DATEIN'];
				$orderrec=$row['orderrec'];
				$COMMENTS=$row['COMMENTS'];
				$ACTIONDATE=$row['ACTIONDATE'];
				$Status=$row['Status'];
				$Project_Members=$row['Project_Members'];
				$Gate=$row['Gate'];
				$Job_No=$row['Job_No'];
				$Cata_Number=$row['Cata_Number'];
				$Suspend=$row['Suspend'];


			}
		?>
		<form name="form1" method="post" action="formsubmit.php?mode=update">
            <table width="500" border="1" align="center" cellpadding="2" cellspacing="2">
              <tr>
                <td><strong>Update VSI </strong></td>
                <td><input type="hidden" name="VSI_NUMBER" value="<? echo $VSI_NUMBER; ?>">
                  &nbsp;</td>
              </tr>
              <tr>
                <td>VSI Number </td>
                <td>
                <input name="VSI_NUMBER" type="text" id="VSI_NUMBER" value="<? echo $VSI_NUMBER; ?>" size="20"></td>
              </tr>
              <tr>
                <td>Engineer</td>
                <td>
                <input name="ENGINEER" type="text" id="ENGINEER" value="<? echo $ENGINEER; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Sales Man</td>
                <td>
                <input name="Salesman" type="text" id="Salesman" value="<? echo $Salesman; ?>" size="20"></td>
              </tr>
              <tr>


                <td>Customer</td>
                <td>
                <input name="CUSTOMER" type="text" id="CUSTOMER" value="<? echo $CUSTOMER; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Sales Group</td>
                <td>
                <input name="SALESGROUP" type="text" id="SALESGROUP" value="<? echo $SALESGROUP; ?>" size="20"></td>
              </tr>
              <tr>

                <td>SIC Code</td>
                <td>
                <input name="SICCODE" type="text" id="SICCODE" value="<? echo $SICCODE; ?>" size="20"></td>
              </tr>
              <tr>

                <td>New/Existing</td>
                <td>
                <select name="N_E"><option selected><? echo $N_E; ?></option><option>New</option></option><option>Existing</option></select></td>
              </tr>
              <tr>

                <td>Year Qty</td>
                <td>
                <input name="YEARQTY" type="text" id="YEARQTY" value="<? echo $YEARQTY; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Check list ratio</td>
                <td>
                <input name="CHCKLRATIO" type="text" id="CHCKLRATIO" value="<? echo $CHCKLRATIO; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Value Ex Works</td>
                <td>
                <input name="VALUEExworks" type="text" id="VALUEExworks" value="<? echo $VALUEExworks; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Date in (yyyy-mm-dd)</td>
                <td>
                <input name="DATEIN" type="text" id="DATEIN" value="<? echo $DATEIN; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Order recieved</td>
                <td>
                <select name="orderrec"><option selected><? echo $orderrec; ?></option><option>Yes</option></option><option>No</option></select></td>

              </tr>
              <tr>

                <td>Comments</td>
                <td>
                <input name="COMMENTS" type="text" id="COMMENTS" value="<? echo $COMMENTS; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Action Date (yyyy-mm-dd)</td>
                <td>
                <input name="ACTIONDATE" type="text" id="ACTIONDATE" value="<? echo $ACTIONDATE; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Status</td>
                <td>
                <select name="Status"><option selected><? echo $Status; ?></option><option>Open</option></option><option>Closed</option></select></td>
              </tr>
              <tr>


                <td>Project Members</td>
                <td>
                <input name="Project_Members" type="text" id="Project_Members" value="<? echo $Project_Members; ?>" size="20"></td>
              </tr>
              <tr>

                <td>@Gate</td>
                <td>
                    <select name="Gate"><option selected><? echo $Gate; ?></option><option>1</option><option>2</option><option>3</option><option>4</option><option>5</option><option>6</option><option>7</select></td>
              </tr>
              <tr>


                <td>Job Number</td>
                <td>
                <input name="Job_No" type="text" id="Job_No" value="<? echo $Job_No; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Catalogue Number</td>
                <td>
                <input name="Cata_Number" type="text" id="Cata_Number" value="<? echo $Cata_Number; ?>" size="20"></td>
              </tr>
              <tr>

                <td>Suspend</td>
                <td>
                    <select name="Suspend"><option selected><? echo $Suspend; ?></option><option>Yes</option></option><option>No</option></select></td>

              </tr>
              <tr>


                <td><input type="submit" name="Submit" value="Update Data"></td>
                <td>&nbsp;</td>
              </tr>
            </table>
          </form>
		
		<?	
			
		  }
		  ?>
		 
		  </td>
        </tr>
        <tr>
          <td>&nbsp;</td>
        </tr>
    </table></td>
  </tr>
</table>
</body>
</html>
The formsubmit.php

Code: Select all

<?
		  include("conn.php");
		  $mode=$_GET["mode"];
		  if($mode=="add") {

$VSI_NUMBER=$_POST["VSI_NUMBER"];
$Salesman=$_POST["Salesman"];
$ENGINEER=$_POST["ENGINEER"];
$CUSTOMER=$_POST["CUSTOMER"];
$SALESGROUP=$_POST["SALESGROUP"];
$SICCODE=$_POST["SICCODE"];
$N_E=$_POST["N_E"];
$YEARQTY=$_POST["YEARQTY"];
$CHCKLRATIO=$_POST["CHCKLRATIO"];
$VALUEExworks=$_POST["VALUEExworks"];
$DATEIN=$_POST["DATEIN"];
$COMMENTS=$_POST["COMMENTS"];
$ACTIONDATE=$_POST["ACTIONDATE"];
$Project_Members=$_POST["Project_Members"];
$Gate=$_POST["Gate"];
$Job_No=$_POST["Job_No"];
$Cata_Number=$_POST["Cata_Number"];


$sql="insert into $branch(VSI_NUMBER,Salesman,ENGINEER,CUSTOMER,SALESGROUP,
SICCODE,N_E,YEARQTY,CHCKLRATIO,VALUEExworks,DATEIN,COMMENTS,ACTIONDATE,
Project_Members,Gate,Job_No,Cata_Number) 

values('$VSI_NUMBER','$Salesman','$ENGINEER','$CUSTOMER','$SALESGROUP','$SICCODE'
,'$N_E','$YEARQTY','$CHCKLRATIO','$VALUEExworks','$DATEIN','$COMMENTS','$ACTIONDATE',
'$Project_Members','$Gate','$Job_No','$Cata_Number')";

			$result=mysql_query($sql,$connection) or die(mysql_error());
			header("location: index.htm");
			
		  } elseif($mode=="update") {

$VSI_NUMBER=$_POST["VSI_NUMBER"];
$Salesman=$_POST["Salesman"];
$ENGINEER=$_POST["ENGINEER"];
$CUSTOMER=$_POST["CUSTOMER"];
$SALESGROUP=$_POST["SALESGROUP"];
$SICCODE=$_POST["SICCODE"];
$N_E=$_POST["N_E"];
$YEARQTY=$_POST["YEARQTY"];
$CHCKLRATIO=$_POST["CHCKLRATIO"];
$VALUEExworks=$_POST["VALUEExworks"];
$DATEIN=$_POST["DATEIN"];
$COMMENTS=$_POST["COMMENTS"];
$ACTIONDATE=$_POST["ACTIONDATE"];
$Project_Members=$_POST["Project_Members"];
$Gate=$_POST["Gate"];
$Job_No=$_POST["Job_No"];
$Cata_Number=$_POST["Cata_Number"];
$orderrec =$_POST["orderrec"];
$Status =$_POST["Status"];
$Suspend =$_POST["Suspend"];



			$sql="update $branch set VSI_NUMBER='$VSI_NUMBER',Salesman='$Salesman',ENGINEER ='$ENGINEER'
			,CUSTOMER ='$CUSTOMER',SALESGROUP ='$SALESGROUP',SICCODE ='$SICCODE',N_E ='$N_E'
			,YEARQTY ='$YEARQTY',CHCKLRATIO ='$CHCKLRATIO',VALUEExworks ='$VALUEExworks',DATEIN ='$DATEIN'
			,COMMENTS ='$COMMENTS',ACTIONDATE ='$ACTIONDATE',Project_Members ='$Project_Members'
			,Gate ='$Gate',Job_No ='$Job_No',Cata_Number ='$Cata_Number',orderrec ='$orderrec'
			,Status ='$Status',Suspend ='$Suspend' where VSI_NUMBER='$VSI_NUMBER'";
			//echo $sql;
			$result=mysql_query($sql,$connection) or die(mysql_error());
			header("location: index.htm");
		  }
		  ?>
Hope someone can help.

Thanks

Geoff

Posted: Tue Nov 07, 2006 5:10 am
by volka
try

Code: Select all

} elseif($mode=="update") {

	$VSI_NUMBER=$_POST["VSI_NUMBER"];
	$Salesman=$_POST["Salesman"];
	$ENGINEER=$_POST["ENGINEER"];
	$CUSTOMER=$_POST["CUSTOMER"];
	$SALESGROUP=$_POST["SALESGROUP"];
	$SICCODE=$_POST["SICCODE"];
	$N_E=$_POST["N_E"];
	$YEARQTY=$_POST["YEARQTY"];
	$CHCKLRATIO=$_POST["CHCKLRATIO"];
	$VALUEExworks=$_POST["VALUEExworks"];
	$DATEIN=$_POST["DATEIN"];
	$COMMENTS=$_POST["COMMENTS"];
	$ACTIONDATE=$_POST["ACTIONDATE"];
	$Project_Members=$_POST["Project_Members"];
	$Gate=$_POST["Gate"];
	$Job_No=$_POST["Job_No"];
	$Cata_Number=$_POST["Cata_Number"];
	$orderrec =$_POST["orderrec"];
	$Status =$_POST["Status"];
	$Suspend =$_POST["Suspend"];
	
	$sql="UPDATE $branch SET VSI_NUMBER='$VSI_NUMBER',Salesman='$Salesman',ENGINEER ='$ENGINEER'
	,CUSTOMER ='$CUSTOMER',SALESGROUP ='$SALESGROUP',SICCODE ='$SICCODE',N_E ='$N_E'
	,YEARQTY ='$YEARQTY',CHCKLRATIO ='$CHCKLRATIO',VALUEExworks ='$VALUEExworks',DATEIN ='$DATEIN'
	,COMMENTS ='$COMMENTS',ACTIONDATE ='$ACTIONDATE',Project_Members ='$Project_Members'
	,Gate ='$Gate',Job_No ='$Job_No',Cata_Number ='$Cata_Number',orderrec ='$orderrec'
	,Status ='$Status',Suspend ='$Suspend' where VSI_NUMBER='$VSI_NUMBER'";
	
	
	echo '<pre>POST: '; print_r($_POST); echo "\n</pre>\n";
	echo '<pre>sql: '; print_r($sql); echo "\n</pre>\n";
	
	
	$result=mysql_query($sql,$connection) or die(mysql_error());
	header("location: index.htm");
}
?>
Does it print what you expect?

Posted: Tue Nov 07, 2006 6:33 am
by JimiH
Thanks for the reply

I notice you've added some code to the "Update" section, this section works fine, the problem is with the "Add" section.

I will paste the code and see what it does anyway

Geoff

Posted: Tue Nov 07, 2006 6:52 am
by volka
Same procedure for the "add" section

Code: Select all

$Cata_Number=$_POST["Cata_Number"];
	
	
	$sql="insert into $branch(VSI_NUMBER,Salesman,ENGINEER,CUSTOMER,SALESGROUP,
		SICCODE,N_E,YEARQTY,CHCKLRATIO,VALUEExworks,DATEIN,COMMENTS,ACTIONDATE,
		Project_Members,Gate,Job_No,Cata_Number)
		values('$VSI_NUMBER','$Salesman','$ENGINEER','$CUSTOMER','$SALESGROUP','$SICCODE'
		,'$N_E','$YEARQTY','$CHCKLRATIO','$VALUEExworks','$DATEIN','$COMMENTS','$ACTIONDATE',
		'$Project_Members','$Gate','$Job_No','$Cata_Number')";
	
	echo '<pre>POST: '; print_r($_POST); echo "\n</pre>\n";
	echo '<pre>sql: '; print_r($sql); echo "\n</pre>\n"; 
	
	$result=mysql_query($sql,$connection) or die(mysql_error());
	header("location: index.htm");
	                     
} elseif($mode=="update") {

Posted: Tue Nov 07, 2006 6:58 am
by JimiH
Thanks

This will be helpful for the future.

A friend had a quick look and discovered I had spelt VALUEExworks wrong in the "Add" section, I had looked
at it a thousand times and never seen it :oops:

Thanks for you help

Geoff

Posted: Tue Nov 07, 2006 7:33 am
by volka
Might be easier if you write the fieldnames only once

Code: Select all

$fields = array(
		'VSI_NUMBER','Salesman','ENGINEER','CUSTOMER',
		'SALESGROUP','SICCODE','N_E','YEARQTY',
		'CHCKLRATIO','VALUEExworks','DATEIN','COMMENTS',
		'ACTIONDATE','Project_Members','Gate','Job_No',
		'Cata_Number','orderrec','Status','Suspend'
	);
and then build the form and the querries from those values.
Or maybe even pull the names from the table itself, see http://de2.php.net/mysql_field_name

Posted: Fri Nov 10, 2006 1:07 pm
by JimiH
Thanks for the info, will take a look

Geoff

Posted: Fri Nov 10, 2006 2:36 pm
by RobertGonzalez
If a field was misspelled, you should have gotten something back from mysql_error().

Posted: Sat Nov 11, 2006 5:28 am
by JimiH
Thanks Everah

it was the input name that was incorrect

Code: Select all

<td>Value Ex works</td>
                <td>
                <input name="VALUEEworks" type="text" id="VALUEEworks" size="20"></td>
              </tr>
              <tr>
Should of been

Code: Select all

<td>Value Ex works</td>
                <td>
                <input name="VALUEExworks" type="text" id="VALUEExworks" size="20"></td>
              </tr>
              <tr>
With an "x"

Thanks

Geoff