I have three states as an option;
1: Null (default value of new record)
2: Not selected
3: Selected
When a new record is created, a ? would be desplayed indicating that the user has not indicated whether or not they are interesting in the item or not.
When they go to make a selection and save the settings, the recordset is then updated with either a 1 or 2.
Having problems getting this figured out.
Update record set:
PHP Code:
if(isset($_POST["esod"])){
if ($_POST["esod"]==2) $sql.='esod=2, ';
elseif($_POST["esod"]==1) $sql.='esod=1, ';
}
Create form:
PHP Code:
if(empty($row['esod'])) $str_edit.='<td><input type="checkbox" name="esod" value="2" tabindex="'.$tabindex++.'"></td>';
else $str_edit.='<td><input type="checkbox" name="esod" value="1" tabindex="'.$tabindex++.'" checked ></td>';
Desplay:
PHP Code:
if (IS_NULL($row['esod'])) $str_row.='<td>?</td>';
elseif(($row['esod'])==1) $str_row.='<td><img src="images/ok.gif"></td>';
else $str_row.='<td></td>';
Entire code:
PHP Code:
<?php
// set up global variables
@include 'menu.php';
if(isset($_POST["doc_desc"])){ //Document is being modified or added
if(isset($_POST["id"])){ //Document is being modified
$sql='UPDATE tbl_sod '.
'SET ';
if(empty($_POST["doc"])) $sql.='doc=NULL, ';
else $sql.='doc="'.strtoupper(mysql_real_escape_string($_POST["doc"])).'", ';
if(empty($_POST["doc_desc"])) $sql.='doc_desc=NULL, ';
else $sql.='doc_desc="'.strtoupper(mysql_real_escape_string($_POST["doc_desc"])).'", ';
if(empty($_POST["required_status"]))$sql.='required_status=NULL, ';
else $sql.='required_status="'.strtoupper(mysql_real_escape_string($_POST["required_status"])).'", ';
if(empty($_POST["rtpp"])) $sql.='rtpp=NULL, ';
else $sql.='rtpp='.strtoupper(mysql_real_escape_string($_POST["rtpp"])).', ';
if(isset($_POST["esod"])){
if ($_POST["esod"]==2) $sql.='esod=2, ';
elseif($_POST["esod"]==1) $sql.='esod=1, ';
}
if(empty($_POST["isod"])){
if ($_POST["isod"]==2) $sql.='isod=2, ';
elseif($_POST["isod"]==1) $sql.='isod=1, ';
}
if(empty($_POST["owner"])) $sql.='owner=NULL, ';
else $sql.='owner="'.$_POST["owner"].'", ';
$sql=substr($sql,0,-2);
$sql.=' WHERE sod_id='.$_POST['id'];
$result=query($sql);
$sql='SELECT * FROM tbl_sod_status WHERE sod_id='.$_POST['id'].' AND rev="'.strtoupper(mysql_real_escape_string($_POST['rev'])).'"';
if (mysql_num_rows(query($sql))){//Revision found
$sql='UPDATE tbl_sod_status '.
'SET ';
if(empty($_POST["pages"])) $sql.='pages=NULL, ';
else $sql.='pages="'.strtoupper(mysql_real_escape_string($_POST["pages"])).'", ';
if(empty($_POST["status"])) $sql.='status=NULL, ';
else $sql.='status="'.strtoupper(mysql_real_escape_string($_POST["status"])).'", ';
if(empty($_POST["eng_error"])) $sql.='eng_error=NULL, ';
else $sql.='eng_error='.strtoupper(mysql_real_escape_string($_POST["eng_error"])).', ';
if(empty($_POST["draft_error"])) $sql.='draft_error=NULL, ';
else $sql.='draft_error='.strtoupper(mysql_real_escape_string($_POST["draft_error"])).', ';
if(empty($_POST["vendor_error"])) $sql.='vendor_error=NULL, ';
else $sql.='vendor_error='.strtoupper(mysql_real_escape_string($_POST["vendor_error"])).', ';
if(empty($_POST["sourcing_error"])) $sql.='sourcing_error=NULL, ';
else $sql.='sourcing_error='.strtoupper(mysql_real_escape_string($_POST["sourcing_error"])).', ';
if(empty($_POST["value_eng"])) $sql.='value_eng=NULL, ';
else $sql.='value_eng='.strtoupper(mysql_real_escape_string($_POST["value_eng"])).', ';
if(empty($_POST["requested_change"])) $sql.='requested_change=NULL, ';
else $sql.='requested_change='.strtoupper(mysql_real_escape_string($_POST["requested_change"])).', ';
if(empty($_POST["spec_conflict"])) $sql.='spec_conflict=NULL, ';
else $sql.='spec_conflict='.strtoupper(mysql_real_escape_string($_POST["spec_conflict"])).', ';
if(empty($_POST["direction_deficient"]))$sql.='direction_deficient=NULL, ';
else $sql.='direction_deficient='.strtoupper(mysql_real_escape_string($_POST["direction_deficient"])).', ';
if(empty($_POST["schedule_issue"])) $sql.='schedule_issue=NULL, ';
else $sql.='schedule_issue='.strtoupper(mysql_real_escape_string($_POST["schedule_issue"])).', ';
if(empty($_POST["mfg_error"])) $sql.='mfg_error=NULL, ';
else $sql.='mfg_error='.strtoupper(mysql_real_escape_string($_POST["mfg_error"])).', ';
if(empty($_POST["rtpp"])) $sql.='rtpp=NULL, ';
else $sql.='rtpp='.strtoupper(mysql_real_escape_string($_POST["rtpp"])).', ';
if(empty($_POST["comments"])) $sql.='comments=NULL, ';
else $sql.='comments='.strtoupper(mysql_real_escape_string($_POST["comments"])).', ';
$sql=substr($sql,0,-2);
$sql.=' WHERE sod_id='.$_POST['id'].' AND rev="'.strtoupper(mysql_real_escape_string($_POST['rev'])).'"';
}
$result=query($sql);
}
else{ //Document is being added
$sql_begin='INSERT INTO tbl_sod (doc_desc, doc';
$sql_end='VALUES ("'.mysql_real_escape_string($_POST["doc_desc"]).'","'.strtolower(mysql_real_escape_string($_POST["doc"])).'"';
if(!empty($_POST["pages"])){
$sql_begin.=', pages';
$sql_end.=',"'.strtoupper(mysql_real_escape_string($_POST["pages"])).'"';
}
if(!empty($_POST["status"])){
$sql_begin.=', required_status';
$sql_end.=', "'.$_POST["status"].'"';
}
if(!empty($_POST["rtpp"])){
$sql_begin.=', rtpp';
$sql_end.=', '.$_POST["rtpp"];
}
if(!empty($_POST["esod"])){
$sql_begin.=', esod';
$sql_end.=', '.$_POST["esod"];
}
if(!empty($_POST["isod"])){
$sql_begin.=', isod';
$sql_end.=', '.$_POST["isod"];
}
$sql=$sql_begin.') '.$sql_end.')';
}
$result=query($sql);
}
//User setting: records the columns to be displayed
if(isset($_POST["console"])) my_console($_POST["console"],"interface_sod");
if(isset($_GET["sort"])) $sort=$_GET["sort"];
if(isset($_GET["r"])) $r=$_GET["r"];
if(empty($sort))$sort="1";
if(empty($r)) $r=0;
//Creates list of projects
//Displays list MyDocuments only
if (strlen(strstr($_SESSION['interface_sod'],"2"))>0){
$sql='SELECT * FROM tbl_sod INNER JOIN tbl_sod_status ON tbl_sod.sod_id=tbl_sod_status.sod_id';
}
//Displays all project related documents
else $sql='SELECT *, tbl_sod.sod_id FROM tbl_sod LEFT JOIN ('.
'SELECT *, MAX( tbl_sod_status.rev ) AS max_rev '.
'FROM tbl_sod_status '.
'GROUP BY tbl_sod_status.sod_id '.
') AS qry_max ON tbl_sod.sod_id = qry_max.sod_id '.
'WHERE project_id='.$_SESSION['project_id'].' ';
if ($sort=="1") $sql.="ORDER BY doc";
elseif ($sort=="2") $sql.="ORDER BY doc_desc";
elseif ($sort=="3") $sql.="ORDER BY required_status";
elseif ($sort=="4") $sql.="ORDER BY esod";
elseif ($sort=="5") $sql.="ORDER BY isod";
elseif ($sort=="6") $sql.="ORDER BY owner";
elseif ($sort=="7") $sql.="ORDER BY rtpp";
if ($r==1) $sql.=" DESC ";
else $sql.=" ASC ";
$result = query($sql);
//Determines if user wants to view or modify console
for ($x=0;$x<strlen($_SESSION['interface_sod']);$x++){
if(isset($_GET["modify"])) $col[$x]=1;
elseif(isset($_GET["edit"])) $col[$x]=1;
elseif(isset($_GET["new"])) $col[$x]=1;
else $col[$x]=substr($_SESSION['interface_sod'],$x,1);
}
echo '<body><table class="datagrid"><tr>';
if(isset($_GET["edit"])){ //edit the values in a given recordset
$tabindex=0;
$sql='SELECT *, tbl_sod.sod_id FROM tbl_sod LEFT JOIN ('.
'SELECT *, MAX( tbl_sod_status.rev ) AS max_rev '.
'FROM tbl_sod_status '.
'GROUP BY tbl_sod_status.sod_id '.
') AS qry_max ON tbl_sod.sod_id = qry_max.sod_id '.
'WHERE tbl_sod.sod_id='.$_GET['edit'];
$row = mysql_fetch_assoc(query($sql));
$str_edit='<form action="display_sod.php" method="post">'.
'<table class="datagrid"><tr>'.
'<td><input type="submit" value="Update" />'.
'<input type="hidden" name="id" value="'.$row['sod_id'].'"></td>'.
'<td><input maxlength="30" size="30" type="text" name="doc" value="'.$row['doc'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="3" size="1" type="text" name="rev" value="'.$row['max_rev'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="70" size="60" type="text" name="doc_desc" value="'.$row['doc_desc'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="pages" value="'.$row['pages'].'" tabindex="'.$tabindex++.'"></td>';
$sql_status='SELECT * FROM tbl_list_status WHERE final=1';
$result_dd_status=query($sql_status);
$str_edit.='<td><select name="required_status" tabindex="'.$tabindex++.'">';
while ($row1 = mysql_fetch_assoc($result_dd_status)) {
if ($row['required_status']==$row1['status_id'])$str_edit.='<option selected>'.$row1['s'].'</option>';
else $str_edit.='<option>'.$row1['s'].'</option>';
}
$str_edit.='</select></td>';
$str_edit.='<td><input maxlength="10" size="10" type="text" name="rtpp" value="'.$row['rtpp'].'" tabindex="'.$tabindex++.'"></td>';
if(empty($row['esod'])) $str_edit.='<td><input type="checkbox" name="esod" value="2" tabindex="'.$tabindex++.'"></td>';
else $str_edit.='<td><input type="checkbox" name="esod" value="1" tabindex="'.$tabindex++.'" checked ></td>';
if(empty($row['isod'])) $str_edit.='<td><input type="checkbox" name="isod" value="2" tabindex="'.$tabindex++.'"></td>';
else $str_edit.='<td><input type="checkbox" name="isod" value="1" tabindex="'.$tabindex++.'" checked ></td>';
$sql_staff='SELECT tbl_staff.staff_id, first_name, alias, surname '.
'FROM (tbl_staff INNER JOIN ('.
'tbl_project INNER JOIN tbl_team ON tbl_project.project_id = tbl_team.project_id) ON tbl_staff.staff_id = tbl_team.staff_id)'.
'INNER JOIN tbl_company ON tbl_staff.company_id = tbl_company.company_id '.
'WHERE tbl_project.project_id='.$_SESSION['project_id'].' '.
'ORDER BY first_name ASC';
$result_dd_owner=query($sql_staff);
$str_edit.='<td><select name="owner" tabindex="'.$tabindex++.'">';
while ($row1 = mysql_fetch_assoc($result_dd_owner)) {
if ($row['owner']==$row1['staff_id']) $str_edit.='<option selected>'.concat_name($row1['alias'],$row1['first_name'],$row1['surname']).'</option>';
else $str_edit.='<option>'.concat_name($row1['alias'],$row1['first_name'],$row1['surname']).'</option>';
}
$str_edit.='</select></td>'.
'<td><input maxlength="2" size="1" type="text" name="eng_error" value="'.$row['eng_error'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="vendor_error" value="'.$row['draft_error'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="vendor_error" value="'.$row['vendor_error'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="sourcing_error" value="'.$row['sourcing_error'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="value_eng" value="'.$row['value_eng'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="requested_change" value="'.$row['requested_change'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="spec_conflict" value="'.$row['spec_conflict'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="direction_deficient" value="'.$row['direction_deficient'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="schedule_issue" value="'.$row['schedule_issue'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="mfg_error" value="'.$row['mfg_error'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="230" size="50" type="text" name="comments" value="'.$row['comments'].'" tabindex="'.$tabindex++.'"></td>'.
'<td><input type="submit" value="Update" /></td></tr>';
echo $str_edit;
}
if(isset($_GET["new"])){ //edit the values in a given recordset
$tabindex=0;
$str_new='<form action="display_sod.php" method="post">'.
'<table class="datagrid"><tr>'.
'<td><input type="submit" value="Add" />'.
'<td><input maxlength="30" size="30" type="text" name="doc" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="3" size="1" type="text" name="rev" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="70" size="60" type="text" name="doc_desc" tabindex="'.$tabindex++.'"></td>'.
'<td><input maxlength="2" size="1" type="text" name="pages" tabindex="'.$tabindex++.'"></td>';
$sql_status='SELECT * FROM tbl_list_status WHERE final=1';
$result_dd_status=query($sql_status);
$str_new.='<td><select name="status" tabindex="'.$tabindex++.'">';
while ($row1 = mysql_fetch_assoc($result_dd_status)) {
if ($row['required_status']==$row1['status_id'])$str_new.='<option selected>'.$row1['s'].'</option>';
else $str_new.='<option>'.$row1['s'].'</option>';
}
$str_new.='</select></td>';
$str_new.='<td><input maxlength="10" size="10" type="text" name="rtpp" tabindex="'.$tabindex++.'"></td>'.
'<td><input type="checkbox" name="esod" value="1" tabindex="'.$tabindex++.'" checked ></td>'.
'<td><input type="checkbox" name="isod" value="1" tabindex="'.$tabindex++.'" checked ></td>'.
'<td><input type="submit" value="Add" /></td></tr>';
echo $str_new;
}
//Create datagrid header
if(isset($_GET["modify"])){
$str_modify='<form action="display_sod.php" method="post">'.
'<tr>'.
'<td><input type="submit" value="Update" />      </td>'.
'<td>Console: '.
'<select name="console[]">';
if ($col[0]==1) $str_modify.='<option value="1" selected="selected">All Documents</option>';
else $str_modify.='<option value="1" >All Documents</option>';
if ($col[0]==2) $str_modify.='<option value="2" selected="selected">My SOD</option>';
else $str_modify.='<option value="2" >My SOD</option>';
$str_modify.='</select></td>';
for ($x=1;$x<strlen($_SESSION['interface_sod']);$x++){
$str_modify.='<td><input type="checkbox" name="console[]" value="'.$x.'" checked></td>';
}
$str_modify.='</tr></form>';
echo $str_modify;
}
$x=1;
//Creates header for datagrid
$str_header='<th><a href="display_sod.php?new=1"><img src="images/bluecross.gif" alt="New..."></a></th>'.
'<th><a href="display_sod.php?sort=1&r='.!$r.'"><b>Document</b></a></th>';
if (!empty($col[$x++])) $str_header.='<th><b>Rev</b></th>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=2&r='.!$r.'"><b>Description</b></a></th>';
if (!empty($col[$x++])) $str_header.='<th><b>Pages</b></th>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=3&r='.!$r.'"><b>Required Status</b></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=7&r='.!$r.'"><b>RTPP</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=4&r='.!$r.'"><b>E-SOD</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>I-SOD</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Owner</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Eng<br />Error</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Graphics<br />Error</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Vendor<br />Error</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Sourcing<br />Error</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Value<br />Eng</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Change<br />Order</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Spec<br />Conflict</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Direction<br />Deficient</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Schedule<br />Issue</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><a href="display_sod.php?sort=5&r='.!$r.'"><b>Factory<br />Error</b></a></td>';
if (!empty($col[$x++])) $str_header.='<th><b>Comments</b></td>';
$str_header.='<th><a href="display_sod.php?new=1"><img src="images/bluecross.gif" alt="New..."></a></th>';
$str_header.='</tr>';
//Create datagrid
{ $x=1;
$i=0;
while ($row = mysql_fetch_assoc($result)) {
if($_SESSION['header_repeat']==0 && $i==0) echo $str_header; //Repeats header based on user setting
elseif(!empty($_SESSION['header_repeat'])){
$remainder=$i % $_SESSION['header_repeat'];
if(empty($remainder)) echo $str_header; //Repeats header based on user setting
}
$i++;
$str_row='<tr class=d'.($i & 1).'>'.
'<td><a href="display_sod.php?edit='.$row['sod_id'].'"><img src="images/pencil.gif" alt="Edit"></a></td>'.
'<td>'.$row['doc'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['max_rev'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['doc_desc'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['pages'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['required_status'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['rtpp'].'</td>';
if (!empty($col[$x++])){
if (IS_NULL($row['esod'])) $str_row.='<td>?</td>';
elseif(($row['esod'])==1) $str_row.='<td><img src="images/ok.gif"></td>';
else $str_row.='<td></td>';
}
if (!empty($col[$x++])){
if (IS_NULL($row['isod'])) $str_row.='<td>?</td>';
elseif(($row['isod'])==1) $str_row.='<td><img src="images/ok.gif"></td>';
else $str_row.='<td></td>';
}
if (!empty($col[$x++])) $str_row.='<td>'.$row['owner'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['eng_error'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['draft_error'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['vendor_error'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['sourcing_error'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['value_eng'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['requested_change'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['spec_conflict'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['direction_deficient'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['schedule_issue'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['mfg_error'].'</td>';
if (!empty($col[$x++])) $str_row.='<td>'.$row['comments'].'</td>';
$str_row.='<td><a href="display_sod.php?edit='.$row['sod_id'].'"><img src="images/pencil.gif" alt="Edit"></a></td>';
$str_row.='</tr>';
$x=1;
echo $str_row;
}
echo '</table>';
}
echo '</body></html>';
?>