I am creating a simple reporting system and I want the menues and pages to be generated from the database.
I saw this video on YouTube and managed to create a menu with the following code.
I have a database table called Reports and columns called rep_id, rep_date, rep_ledit_date, rep_by, department, position, report, and rep_to. And another table called users with columns called id, username, password, first_name, last_name, department, postion, and passphrase.
I managed to select the an added record for the reports table, however, I have the following problems.
1. The rep_to doesn't preselect the already chosen option
2. The record cannot be updated with php Notice: Undefined index: rep_id in C:\wamp\www\cme\edit-this-report.php on line 232 and no update on the database. This line is where report table is selected
Please see the php code below.
<?php
if(isset($_SESSION['users'])) {
$uname = $_SESSION['users'];
$fname = $_SESSION['firstname'];
$lname = $_SESSION['lastname'];
$dep = $_SESSION['depart'];
$pos = $_SESSION['position'];
$query = mysqli_query($con, "SELECT * FROM users WHERE username = $uname");
while($row = mysqli_fetch_assoc($query)) {
$id=$row['id'];
$fname=$row['first_name'];
$lname=$row['last_name'];
$dep = $row['department'];
$pos = $row['position'];
$repby = $row['first_name'] . " " . $row['last_name'];
$repdep = $row['department'];
$reppos = $row['position'];
}
}
mysqli_select_db($con, $db_name);
$edit= "SELECT * FROM reports WHERE rep_id = '{$_GET['rep_id']}'";
$result = mysqli_query($con, $edit) or die(mysqli_error($con));
$row2 = mysqli_fetch_array($result);
if(isset($_POST['update'])) {
$_GET['rep_id']=$row2['rep_id'];
$reptype = $_POST['reporttype'];
$report = $_POST['report'];
$repto = $_POST['reportedto'];
$update=(mysqli_select_db($con, $db_name));
if(!$update) {
die('Could not connect: ' . mysql_error($con));
}
else {
$sql = "UPDATE reports SET rep_type='$reptype', report='$report', rep_to='$repto',
rep_ledit_date=NOW() WHERE rep_id='{$_GET['rep_id']}'";
$retval = mysqli_query($con, $sql);
if(!$retval ) {
$errorMessage='Could not update data: ' . mysqli_error($con);
}
else {
$success="Updated data successfully\n";
header("location:edit-this-report.php");
mysqli_close($con);
}
}
}
?>
And the form code:
<form name="editor" action="edit-this-report.php" method="post" >
<p class="inline">
<span>
<label for="mem">Reported by</label>
<input type="text" name="reportedby" maxlength="20" disabled value="<?php print $fname . " " . $lname; ?>" />
</span>
</p>
<p class="inline">
<span>
<label for="mem">Department Name</label><input type="text" name="repdepart" disabled size="100" maxlength="100" value="<?php print $dep; ?>">
</span>
</p>
<p class="inline">
<span>
<label for="mem">Position</label><input disabled type="text" name="repposition" size="100" value="<?php print $pos; ?>">
</span>
</p>
<p>
<span>
<label for="mem">Report Type</label>
<select name="reporttype">
<option value=""<?php if ($row2['rep_type'] === 'Daily Report') echo ' selected="selected"'; ?>>Daily Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Weekly Report') echo ' selected="selected"'; ?>>Weekly Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Monthly Report') echo ' selected="selected"'; ?>>Monthly Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Quarterly Report') echo ' selected="selected"'; ?>>Quarterly Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Annual Report') echo ' selected="selected"'; ?>>Annual Report</option>
<option value=""<?php if ($row2['rep_type'] === 'Terminal Report') echo ' selected="selected"'; ?>>Terminal Report</option>
</select>
<span>
</p>
<p>
<span>
<label for="mem">Report</label>
<textarea name="report" id="report" rows="23" cols="auto" ><?php echo $row2['report'];?></textarea>
<span>
</p>
<p>
<span>
<label for="mem">Reported to</label>
<select name="reportedto">
<?php
require ("includes/db.php");
$q2= "SELECT * FROM users WHERE department like '%$repdep%'";
$result3=mysqli_query($con, $q2) or die(mysqli_error($con));
while ($getuser=mysqli_fetch_array($result3)){
$repto=$getuser['first_name'] . " " . $getuser['last_name'];
?>
<option value="<?php echo $repto; ?>"><?php echo $repto; ?></option>;
<?php
}
?>
</select>
</span>
</p>
<span>
<input name="update" type="submit" class="btn btn-large btn-primary" id="report_button" value="Submit Report" >
<input name="cancel" type="reset" class="btn btn-large btn-secondary" id="report_button" value="Cancel All Changes" >
</span>
</p>
</form>
Please help me on this.
Thanks!
I don't see a rep_id variable set in your form, so you're not getting back that value when submitting the form. You're relying on $_GET['rep_id'] in the previous query to provide the rep_id for your UPDATE, but I see no $_GET vars provided in your form. (Maybe not best practice to mix POST and GET, better yet add a hidden form var and set it to rep_id, and capture that as a POST var.)
None the less, the easiest way I can think of to make your code work is to append the rep_id to the form action attribute:
<form name="editor" action="edit-this-report.php?rep_id=<?php echo $_GET['rep_id']; ?>" method="post" >
Run that and report back if you get any more errors.
Another problem, though maybe not a show-stopper, before that in the SELECT * FROM users query, the $uname var is not quoted. Bigger, future problem though is using insecure, user-provided or hacker-manipulable variables in your SQL statements leaves your database open to sql-injection attacks.
UPDATE:
Consider something like this:
<?php
session_start();
require ('includes/db.php'); // provides $con, select database
// get logon info
if ( !isset($_SESSION['username']) ) { header('Location: logon.php'); } // logon and set session vars
else {
list($uname, $repby, $dep, $pos) =
array($_SESSION['username'], $_SESSION['repby'], $_SESSION['department'], $_SESSION['position']);
}
// get report id if GET'd
if ( isset($_GET['rep_id']) ) {
$rep_id = $_GET['rep_id'];
}
// update report if POST'd
else if ( isset($_POST['update'] ) ) {
$rep_id = $_POST['rep_id'];
$reptype = $_POST['reporttype'];
$report = $_POST['report'];
$repto = $_POST['reportedto'];
if ( mysqli_stmt_prepare($stmt, 'UPDATE reports SET rep_type= ?, report= ?, rep_to= ?, rep_ledit_date= ? WHERE rep_id= ?') ) {
mysqli_stmt_bind_param($stmt, 'sssi', $reptype, $report, $repto, NOW(), $rep_id);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
}
else { $errorMessage='Could not update report data: ' . mysqli_error($con); }
}
else { die('no report id'); }
// get/verify report info (can be moved to get'd if to save a db call when post'd)
list($rep_type, $report) = array('', '');
$stmt = mysqli_stmt_init($con);
if ( mysqli_stmt_prepare($stmt, 'SELECT rep_id, rep_type, report FROM Reports WHERE rep_id = ?') ) {
mysqli_stmt_bind_param($stmt, 'i', $rep_id);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $rep_id, $rep_type, $report);
mysqli_stmt_fetch($stmt);
mysqli_stmt_close($stmt);
}
else { $errorMessage='Could not select report data: ' . mysqli_error($con); }
?>
<form name="editor" action="edit-this-report.php" method="post" >
<input type="hidden" name="rep_id" value="<?=$rep_id?>">
<p class="inline">
<span>
<label for="mem">Reported by</label>
<input type="text" name="reportedby" maxlength="20" disabled value="<?=$repby?>" />
</span>
</p>
<p class="inline">
<span>
<label for="mem">Department Name</label><input type="text" name="repdepart" disabled size="100" maxlength="100" value="<?=$dep?>">
</span>
</p>
<p class="inline">
<span>
<label for="mem">Position</label><input disabled type="text" name="repposition" size="100" value="<?=$pos?>">
</span>
</p>
<p>
<span>
<label for="mem">Report Type</label>
<select name="reporttype">
<?php
list($rep_type_da, $rep_type_we, $rep_type_mo, $rep_type_qu, $rep_type_an, $rep_type_te) = array('', '', '', '', '', '');
switch ( $rep_type ) {
case 'Daily Report': $rep_type_da = ' selected'; break;
case 'Daily Report': $rep_type_we = ' selected'; break;
case 'Daily Report': $rep_type_mo = ' selected'; break;
case 'Daily Report': $rep_type_qu = ' selected'; break;
case 'Daily Report': $rep_type_an = ' selected'; break;
case 'Daily Report': $rep_type_te = ' selected'; break;
}
?>
<option value="Daily Report" <?=$rep_type_da?>>Daily Report</option>
<option value="Weekly Report" <?=$rep_type_we?>>Weekly Report</option>
<option value="Monthly Report" <?=$rep_type_mo?>>Monthly Report</option>
<option value="Quarterly Report"<?=$rep_type_qu?>>Quarterly Report</option>
<option value="Annual Report" <?=$rep_type_an?>>Annual Report</option>
<option value="Terminal Report" <?=$rep_type_te?>>Terminal Report</option>
</select>
<span>
</p>
<p>
<span>
<label for="mem">Report</label>
<textarea name="report" id="report" rows="23" cols="auto" ><?=$report?></textarea>
<span>
</p>
<p>
<span>
<label for="mem">Reported to</label>
<select name="reportedto">
<option value=""></option>
<?php
if ( mysqli_stmt_prepare($stmt, 'SELECT CONCAT(first_name, last_name) AS repto FROM users WHERE department LIKE ?') ) {
mysqli_stmt_bind_param($stmt, 's', "%$dep%");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $repto);
while ( mysqli_stmt_fetch($stmt) ) {
echo '<option value="' . $repto . '">' . $repto . "</option>\n";
}
mysqli_stmt_close($stmt);
}
else { $errorMessage='Could not select dep user data: ' . mysqli_error($con); }
?>
</select>
</span>
</p>
<span>
<input name="update" type="submit" class="btn btn-large btn-primary" id="report_button" value="Submit Report" >
<input name="cancel" type="reset" class="btn btn-large btn-secondary" id="report_button" value="Cancel All Changes" >
</span>
</p>
</form>
I've not set up the database so I've not tested it. If you run this and get errors, post them in the comments.
You would have to identify exactly which line above is line 232, but an "Undefined Index" error in PHP means that you have an array ($_GET and $_POST are both arrays) and you are trying to access a value (In this case, the one: $_GET['rep_id'] = 1;) but it can't find the 'index' in the array (in this case 'rep_id').
Somewhere you are accessing an array element via index that has not yet been defined on the page.
EDIT:
Probably here:
$edit= "SELECT * FROM reports WHERE rep_id = '{$_GET['rep_id']}'";
$_GET is referencing a url variable called rep_id, but aren't you using post to send the rep_id? in this case try changing
$_GET['rep_id']
to
$_POST['rep_id']
In addition to the solution given by bloodyKnuckles, I also got a help from a friend.
There is also a problem in giving initial values to the post values below if(isset($_POST['update'])).
<?php
if(isset($_SESSION['users'])) {
$uname = $_SESSION['users'];
$fname = $_SESSION['firstname'];
$lname = $_SESSION['lastname'];
$dep = $_SESSION['depart'];
$pos = $_SESSION['position'];
$query = mysqli_query($con, "SELECT * FROM users WHERE username = $uname");
while($row = mysqli_fetch_assoc($query)) {
$id=$row['id'];
$fname=$row['first_name'];
$lname=$row['last_name'];
$dep = $row['department'];
$pos = $row['position'];
$repby = $row['first_name'] . " " . $row['last_name'];
$repdep = $row['department'];
$reppos = $row['position'];
}
}
mysqli_select_db($con, $db_name);
$edit= "SELECT * FROM reports WHERE rep_id = '{$_GET['rep_id']}'";
$result = mysqli_query($con, $edit) or die(mysqli_error($con));
$row2 = mysqli_fetch_array($result);
if(isset($_POST['update'])) {
$repid = $_POST['repid'];
$reporttype = $_POST['reporttype'];
$report = $_POST['report'];
$repto = $_POST['reportedto'];
$sql = "UPDATE reports SET rep_type='$reporttype', report='$report', rep_to='$repto', rep_ledit_date=NOW() WHERE rep_id='$repid'";
$retval = mysqli_query($con, $sql);
mysqli_close($con);
$success="You have successfully edited your report.";
}
?>
Then on the form, the values for reptype were not set. I also added a hidden field which retrieves the rep_id.
<form name="editor" action="edit-this-report.php?rep_id=<?php echo $_GET['rep_id']; ?>" method="post" >
<p class="inline">
<span>
<label for="mem">Reported by</label>
<input type="text" name="reportedby" maxlength="20" disabled value="<?php print $fname . " " . $lname; ?>" />
</span>
</p>
<p class="inline">
<span>
<label for="mem">Department Name</label><input type="text" name="repdepart" disabled size="100" maxlength="100" value="<?php print $dep; ?>">
</span>
</p>
<p class="inline">
<span>
<label for="mem">Position</label><input disabled type="text" name="repposition" size="100" value="<?php print $pos; ?>">
</span>
</p>
<input name="repid" type="hidden" id="repid" value="<?php echo $row2['rep_id']; ?>">
<p>
<span>
<select name="reporttype">
<option value="Daily Report"<?php if ($row2['rep_type'] === 'Daily Report') echo ' selected="selected"'; ?>>Daily Report</option>
<option value="Weekly Report"<?php if ($row2['rep_type'] === 'Weekly Report') echo ' selected="selected"'; ?>>Weekly Report</option>
<option value="Monthly Report"<?php if ($row2['rep_type'] === 'Monthly Report') echo ' selected="selected"'; ?>>Monthly Report</option>
<option value="Quarterly Report"<?php if ($row2['rep_type'] === 'Quarterly Report') echo ' selected="selected"'; ?>>Quarterly Report</option>
<option value="Annual Report"<?php if ($row2['rep_type'] === 'Annual Report') echo ' selected="selected"'; ?>>Annual Report</option>
<option value="Terminal Report"<?php if ($row2['rep_type'] === 'Terminal Report') echo ' selected="selected"'; ?>>Terminal Report</option>
</select>
<span>
</p>
<p>
<span>
<label for="mem">Report</label>
<textarea name="report" id="report" rows="23" cols="auto"><?php echo $row2['report'];?></textarea>
<span>
</p>
<p>
<span>
<label for="mem">Reported to</label>
<select name="reportedto">
<?php
require ("includes/db.php");
$q2= "SELECT * FROM users WHERE department like '%$repdep%'";
$result3=mysqli_query($con, $q2) or die(mysqli_error($con));
while ($getuser=mysqli_fetch_array($result3)){
$repto=$getuser['first_name'] . " " . $getuser['last_name'];
?>
<option value="<?php echo $repto; ?>"><?php echo $repto; ?></option>;
<?php
}
?>
</select>
</span>
</p>
<span>
<input name="update" type="submit" class="btn btn-large btn-primary" id="report_button" value="Submit Report" >
<input name="cancel" type="reset" class="btn btn-large btn-secondary" id="report_button" value="Cancel All Changes" >
</span>
</form>
</p>
I hope this helps other visitors. Thank you bloodyKnuckles and Mark for the help!
