Hello all,
I hope you can help me with this problem.
My task is:
Update a table in MySQL with the following.
I have a page that was developed partly in DreamweaverMX but I couldn't do everything I wanted to do in DMX so I had to hand code most of it, so forgive the hybrid code.
First I have a document that will have a link that will bring the person to my login page. Once they click on that link, it does a GET function allowing me to hide the value of the field in a varible called $n_program, so lets say it is 3456 so the varible is now:
$n_program=3456.
I get to the log in page and there are the standard fields, User Name and Password in a form. I still have a value for 3456 in a hidden input. Once the user has entered their USER name I store it in another varible $id_userid at this stage I ignore the password since I am using sessions.
Now they are at the main detail page. And it displays information that is part of the 3456 set. So, here is what I have:
$n_program (which is 3456)
$id_userid which is the user name they entered on the login page.
Now on this page they need to update only one field and that is a checkbox stating they approve the stage.
Now here is the problem:
I need to insert a Y in a field titled ind_approved, as well as an (int) value for user but $id_userid is a string of say "TOM" and this will all happen by the user selecting the checkbox and then clicking the update button.
My Database is as follows:
Table struture for n_program
*/
CREATE TABLE n_program (
name_program char(20) NOT NULL default '',
id_app int(11) default '0',
id_type_pgm int(11) default NULL,
PRIMARY KEY (name_program)
) TYPE=MyISAM;
/
Table struture for n_program_stg
/
CREATE TABLE n_program_stg (
name_program char(20) NOT NULL default '',
id_stage_pgm int(11) NOT NULL default '0',
dt_stage_pgm timestamp(14) NOT NULL,
id_user_stage int(11) NOT NULL default '0',
ind_approved char(1) NOT NULL default 'N',
dt_approve timestamp(14) NOT NULL,
id_user_approve int(11) NOT NULL default '0',
PRIMARY KEY (name_program,id_stage_pgm)
) TYPE=MyISAM;
/
Table struture for n_reporter
/
CREATE TABLE n_reporter (
id_reporter bigint(11) NOT NULL auto_increment,
name_reporter varchar(255) NOT NULL default '',
email varchar(150) NOT NULL default '',
id_userid varchar(15) NOT NULL default '',
id_password varchar(15) NOT NULL default '',
ts_created timestamp(14) NOT NULL,
id_created bigint(11) NOT NULL default '0',
ts_edited timestamp(14) NOT NULL,
id_edited bigint(11) NOT NULL default '0',
nbr_active int(11) NOT NULL default '1',
PRIMARY KEY (id_reporter),
UNIQUE KEY id_userid (id_userid),
KEY id_reporter (id_reporter),
KEY id_userid_2 (id_userid)
) TYPE=MyISAM;
/
Table struture for n_stage_pgm
/
CREATE TABLE n_stage_pgm (
id_stage_pgm int(11) NOT NULL default '0',
name_stage char(50) default NULL,
PRIMARY KEY (id_stage_pgm)
) TYPE=MyISAM;
/
Table struture for n_type_pgm
/
CREATE TABLE t_type_pgm (
id_type_pgm int(11) NOT NULL default '0',
name_type_pgm char(20) default NULL,
PRIMARY KEY (id_type_pgm)
) TYPE=MyISAM;
My Current PHP Code is:
<?php
//Connection statement
require_once('../Connections/connProgram.php');
//Aditional Functions
require_once('../includes/functions.inc.php');
// build the form action
$editFormAction = $HTTP_SERVER_VARS['PHP_SELF'] . (isset($HTTP_SERVER_VARS['QUERY_STRING']) ? "?" . $HTTP_SERVER_VARS['QUERY_STRING'] : "");
if ((isset($HTTP_POST_VARS["MM_update"])) && ($HTTP_POST_VARS["MM_update"] == "update")) {
$updateSQL = sprintf("UPDATE n_program_stage SET ind_approved=%s WHERE id_stage_pgm=%s",
GetSQLValueString(isset($HTTP_POST_VARS['ind_approve']) ? "true" : "", "defined","'Y'","'N'"),
GetSQLValueString($HTTP_POST_VARS['id_stage_pgm'], "int"));
$Result1 = $connProgram->Execute($updateSQL) or die($connProgram->ErrorMsg());
$updateGoTo = "program_1.php";
if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
$updateGoTo .= $HTTP_SERVER_VARS['QUERY_STRING'];
}
KT_redir($updateGoTo);
}
//Aditional Functions
// begin Recordset
$colnamersMain = '1';
if (isset($HTTP_POST_VARS['name_program'])) {
$colnamersMain = $HTTP_POST_VARS['name_program'];
}
$query_rsMain = sprintf("SELECT * FROM n_program, n_program_stg RIGHT OUTER JOIN n_stage_pgm ON n_program_stage.id_stage_pgm = n_stage_pgm.id_stage_pgm AND n_program.name_program = n_program_stage.name_program LEFT OUTER JOIN n_reporter ON n_reporter.id_reporter = n_program_stage.id_user_approve, n_type_pgm WHERE n_program.name_program = '%s' AND n_type_pgm.id_type_pgm = n_program.id_type_pgm ORDER BY n_program.name_program", $colname__rsMain);
$rsMain = $connProgram->SelectLimit($query_rsMain) or die($connProgram->ErrorMsg());
$totalRows_rsMain = $rsMain->RecordCount();
// end Recordset
//initialize the Alternate Color index
$AltColors1 = 0;
echo "My Program Name: <B>$name_program</B><BR>";
echo "My User Name Is: <B>$id_userid</B><BR>";
//PHP ADODB document - made with PHAkt 2.1.0?>
<html>
<head>
<title>Program Overview</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="demo.css" rel="stylesheet" type="text/css">
</head>
<body>
<form action="<?php echo $editFormAction; ?>" method="POST" name="update" id="update">
<table width="100%" border="0" cellpadding="1" cellspacing="0" id="Program_name">
<tr>
<td width="18%" class="p2">Program Name:</td>
<td width="34%" class="p2"><?php echo $rsMain->Fields('name_program'); ?></td>
<td width="25%"> </td>
<td width="23%"> </td>
</tr>
<tr>
<td class="p2">Program Type:</td>
<td class="p2"><font color="#FF0000"><?php echo $rsMain->Fields('name_type_pgm'); ?></font></td>
<td> </td>
<td> </td>
</tr>
<tr>
<td colspan="4"><hr></td>
</tr>
<tr class="p2">
<td bgcolor="#FFFFCC">Stage</td>
<td bgcolor="#FFFFCC">Approved</td>
<td bgcolor="#FFFFCC">Date Approved</td>
<td bgcolor="#FFFFCC">Approved By:</td>
</tr>
<?php
while (!$rsMain->EOF) {
?>
<tr bgcolor="<?php echo (((++$AltColors1) % 2) == 0) ? "#FFFFFF" : "#CCCCCC"; ?>" class="nor">
<td><?php echo $rsMain->Fields('name_stage'); ?></td>
<td>
<?php if ($rsMain->Fields('ind_approved')=="Y") {?>
<?php echo "Yes";?>
<?php }elseif ($rsMain->Fields('ind_approved')==NULL) {?>
<?php echo "";?>
<?php } else { echo "<input type=\"checkbox\" name=\"checkbox\" value=\"checkbox\">"; } ?>
</td>
<td><?php echo $rsMain->Fields('dt_approve'); ?></td>
<td><?php echo $rsMain->Fields('name_reporter'); ?></td>
</tr>
<?php
$rsMain->MoveNext();
}
?>
</table>
<table width="100%">
<tr><td><select name="id_stage_pgm" id="id_stage_pgm">
<option value="0">Assigned</option>
<option value="1">Layout Designed</option>
<option value="2">Initial Data Analysis</option>
<option value="3">Inquire Working</option>
<option value="4">Update Working</option>
<option value="5">Add/Delete Working</option>
<option value="6">Business Logic Designed</option>
<option value="7">Business Logic Working</option>
</select>
</td>
<td><input name="ind_approve" type="checkbox" id="ind_approve" value="checkbox">
</td>
<td><input type="submit" name="Submit" value="Submit">
</td>
</tr>
</table>
<input type="hidden" name="MM_update" value="update">
</form>
</body>
</html>
<?php
$rsMain->Close();
?>
What should happen is that the correct INT value for the user is entered into n_program_stg.id_user_approve, a time stamp is entered into n_program_stg.dt_approve and a Y is entered into n_program_stg.ind_approved. So only three fields are getting updated but one filed is the actual ID from n_reporter.
I am at a loss as to how to get this to work.
Thanks for any help you can give, or advice. I am totally new to PHP and MySQL and I understand that this isn't an easy problem. I think I need a for loop to generate the checkboxes so that they have a way to make them different, but I can't think of how to do that either.
The three item table at the bottom of the code is a test to see if I was updating the table and I am, but I need to do it with out all the choices, and by the user selecting one checkbox, and pressing the update button.
Thanks,
Mickey
