updating mysql table

hamburgerlove413 · November 21, 2013

Hello,

I'm making a simple program that adds homework assignments to a database table and then displays them. I'm trying to add the ability to edit them, but I can't figure it out. Here's what I have right now, but after I go to the edit screen, change the value, and hit save, it doesn't update the table. Sorry for the amount of code, i didnt know what I needed to include or not.

<html>

	<head>

		<title>untitled</title>

		<link href="css/styles.css" rel="stylesheet" type="text/css">
	</head>


<body>

<form method='post'>
<?php


	function  showlist($mysqli,$last='') {
		if ($last)
    		$myquery="SELECT * FROM homeworklist WHERE course LIKE '$last%'";
		else
    		$myquery="SELECT * FROM homeworklist";
	
	    $result=$mysqli->query($myquery)
			or die ($mysqli->error); 
	
		$count=$result->num_rows;
	
		if($count > 0) {	
		
		$output="<table><tr><th>Task Nr:</th><th>assign-date</th><th>due-date</th><th>priority</th><th>course</th><th>prof</th><th>title</th><th>description</th></tr>";
		
			while ($row=$result->fetch_assoc()) {	
				$tasknr=$row['tasknr'];
				$assigndate=$row['assigndate'];
				$duedate=$row['duedate'];
				$priority=$row['priority'];
				$course=$row['course'];
				$prof=$row['prof'];
				$title=$row['title'];
				$description=$row['description'];
						
				$output.= "<tr><td align='center'>$tasknr</td><td>$assigndate</td><td>$duedate</td><td>$priority</td><td>$course</td><td>$prof</td><td>$title</td><td>$description</td></tr>";
			}
			$output.= "</table> <br />";
			
			
			
			print $output;
		} #count
		else print "There are no homework items in the list.<br />";
	} #showlist
	
	function drawinputs()
	{
		print "<dl>";
		print "<dt><label for='assigndate'>Assign Date:</label></dt><dd><input type='text' name='assigndate'></dd>
				<dt><label for='duedate'>Due Date:</label></dt><dd><input type='text' name='duedate'></dd>
				<dt><label for='priority'>Priority:</label></dt><dd><input type='text' name='priority'></dd>
				<dt><label for='course'>Course:</label></dt><dd><input type='text' name='course'></dd>
				<dt><label for='prof'>Professor:</label></dt><dd><input type='text' name='prof'></dd>
				<dt><label for='title'>Title:</label></dt><dd><input type='text' name='title'></dd>
				<dt><label for='description'>Description:</label></dt><dd><input type='text' name='description'></dd>

				<input type='submit' name='action' value='Add Task'>
				
				<dt><label for='matchlast'>Filter the displayed list. Only display courses with names that begin with this string:</label></dt>
				<dd><input type='text' name='matchlast'></dd>
			
				<input type='submit' name='action' value='Filter List'><br /><br />
				
				<dl>

				<dt><label for='delsel'>Item number to be deleted:</label></dt><dd><input type='text' name='delsel' size=2></dd>
				
				</dl>
				
				<input type='submit' name='action' value='Delete Task'><br />

				<dl>

				<dt><label for='editsel'>Item number to be edited:</label></dt><dd><input type='text' name='editsel' size=2></dd>
				
				</dl>
				
				<input type='submit' name='action' value='Edit Record'><br />

				";

		print "</dl>";
	} # drawinputs
	
	function addtask($mysqli)
	{
		$assigndateX=$_POST['assigndate'];
		$duedateX=$_POST['duedate'];
		$priorityX=$_POST['priority'];
		$courseX=$_POST['course'];
		$profX=$_POST['prof'];
		$titleX=$_POST['title'];
		$descriptionX=$_POST['description'];
		
		// ESSENTIAL cleaning to avoid SQL Injectin Attack
		$assigndate=$mysqli->real_escape_string($assigndateX);
		$duedate=$mysqli->real_escape_string($duedateX);
		$priority=$mysqli->real_escape_string($priorityX);
		$course=$mysqli->real_escape_string($courseX);
		$prof=$mysqli->real_escape_string($profX);
		$title=$mysqli->real_escape_string($titleX);
		$description=$mysqli->real_escape_string($descriptionX);
		
		// The 'null' in the VALUES list allows the auto-incrementing idnumber to work
		$query="INSERT INTO homeworklist VALUES (null,'$assigndate','$duedate','$priority','$course','$prof','$title','$description')";

		$result=$mysqli->query($query)
			or die ($mysqli->error); 
	} #addtask
	
	// STARTER item for deleterecord function
	function deleterecord($mysqli,$deletenumber)
	{		
		$query="DELETE FROM homeworklist WHERE tasknr=$deletenumber";
		$result=$mysqli->query($query)
			or die ($mysqli->error); 
	}

function edittask($mysqli, $editnumber) {

	$myquery="SELECT * FROM homeworklist WHERE tasknr=$editnumber";

	$result=$mysqli->query($myquery)
		or die ($mysqli->error); 


	while ($row=$result->fetch_assoc()) {	
		$tasknr=$row['tasknr'];
		$assigndate=$row['assigndate'];
		$duedate=$row['duedate'];
		$priority=$row['priority'];
		$course=$row['course'];
		$prof=$row['prof'];
		$title=$row['title'];
		$description=$row['description'];
			
	}
	print "<h1>Edit " . $title . "</h1>";
	print "<input type='text' name='editassigndate' value='$assigndate' />";
	print "<input type='text' name='editduedate' value='$duedate' />";
	print "<input type='text' name='editpriority' value='$priority' />";
	print "<input type='text' name='editcourse' value='$course' />";
	print "<input type='text' name='editprof' value='$prof' />";
	print "<input type='text' name='edittitle' value='$edittitle' />";
	print "<input type='text' name='editdescription' value='$description' />";
	print "<input type='submit' name='action' value='cancel' />";
	print "<input type='submit' name='saveTable' value='save' />";
	

		$action = $_POST['saveTable'];
		
		if ($action == "save") {
			
		$assigndateX=$_POST['editassigndate'];
		$duedateX=$_POST['editduedate'];
		$priorityX=$_POST['editpriority'];
		$courseX=$_POST['editcourse'];
		$profX=$_POST['editprof'];
		$titleX=$_POST['edittitle'];
		$descriptionX=$_POST['editdescription'];
		
		// ESSENTIAL cleaning to avoid SQL Injectin Attack
		$editassigndate=$mysqli->real_escape_string($assigndateX);
		$editduedate=$mysqli->real_escape_string($duedateX);
		$editpriority=$mysqli->real_escape_string($priorityX);
		$editcourse=$mysqli->real_escape_string($courseX);
		$editprof=$mysqli->real_escape_string($profX);
		$edittitle=$mysqli->real_escape_string($titleX);
		$editdescription=$mysqli->real_escape_string($descriptionX);
		
		$query = "UPDATE homeworklist SET assigndate=$editassigndate, duedate=$editduedate, priority=$editpriority, course=$editcourse, prof=$editprof, title=$edittitle, description=$editdescription
WHERE tasknr=$editnumber";

		$result=$mysqli->query($query)
		or die ($mysqli->error); 

		}

}
	
    // MAIN PROGRAM
    	
// Create the mysqli object

	$mysqli = new mysqli('localhost', 'nick', 
			'n1ck8arna', 'homework');
            
// Check for any errors. 

	$errnum=mysqli_connect_errno();
	if ($errnum) 
	{
     	$errmsg=mysqli_connect_error();
		print "Connect failed. error number=$errnum<br />
        		error message=$errmsg";    
		exit();
	}
	
// Now interact with the user


print "<h2>Project 4</h2>";

$act=$_POST['action'];
	
if ($act == 'cancel') {
	unset($act);
}
	
if ($act=='Add Task') {
	
	drawinputs();
	addtask($mysqli);	
	showlist($mysqli);

} else if ($act=='Filter List') {
	
	drawinputs();
	$matchlastX=$_POST['matchlast'];
	$matchlast=$mysqli->real_escape_string($matchlastX);
	showlist($mysqli,$matchlast);

} else if ($act=='Delete Task') {
	
	$deletenumberX=$_POST['delsel'];
	$deletenumber=$mysqli->real_escape_string($deletenumberX);
	
	if (!$deletenumber) {
		
		print "You need to enter a task number!";
		
	} else {
		
	deleterecord($mysqli,$deletenumber);

	
	}
	
	showlist($mysqli);
	drawinputs();

} else if ($act == 'Edit Record') {
	


	$editnumberX = $_POST['editsel'];
	$editnumber = $mysqli->real_escape_string($editnumberX);
	
	if (!$editnumber) {
		
		print "You need to enter a task number!";
		
		showlist($mysqli);
		drawinputs();
		
	} else {
		
		edittask($mysqli,$editnumber);
	
	}

} else {
	
	showlist($mysqli);
	drawinputs();
}
	

	
?>
</form>
</body>
</html>

Barand · November 21, 2013

String values need to be in quotes in your update query. You have quoted them in the insert query and the same applies.

Did you not get an error?

Ch0cu3r · November 21, 2013

In your Edit Record function you need to carry over the existing values of $_POST['editsel'] (record id) and $_POST['action'] (Edit Record). If you don't carry these values over in the edit record form the your code wont know what action to perform and what record needs updating when your submit the form for editing the record.

So change the Edit Record form fields to

    print "<h1>Edit " . $title . "</h1>";
    print "<input type='text' name='editassigndate' value='$assigndate' />";
    print "<input type='text' name='editduedate' value='$duedate' />";
    print "<input type='text' name='editpriority' value='$priority' />";
    print "<input type='text' name='editcourse' value='$course' />";
    print "<input type='text' name='editprof' value='$prof' />";
    print "<input type='text' name='edittitle' value='$edittitle' />";
    print "<input type='text' name='editdescription' value='$description' />";
    print "<input type='hidden' name='action' value='Edit Record' />"; // <--- carry over the action (Edit Record)
    print "<input type='hidden' name='editsel' value='$editnumber' />"; // <-- what record needs updating
    print "<input type='submit' name='action' value='cancel' />";
    print "<input type='submit' name='saveTable' value='save' />";

Edited November 21, 2013 by Ch0cu3r

hamburgerlove413 · November 21, 2013

thank you! That worked, but now there's a new problem. When I click save, it's supposed to go back to the main screen which shows all the records, but instead it just stays on the edit screen. I thought because the form is submitted would mean $action's value would be lost, and therefore go back to the main screen but it doesnt.

Ch0cu3r · November 21, 2013

The problem the action is required for updating the record. This action is being reinforced when the Edit Record form is submitted. This is why the edit form is displayed.

What you need to do is perform a header redirect when the record has been updated

header('Location: pagename.php');

Edited November 21, 2013 by Ch0cu3r

hamburgerlove413 · November 21, 2013

just out of curiousity, i tried to add unset($act) at the end of the if $action='save' statement, but this doesn't seem to work, why doesn't it? when i click save, it should unset $act, and then it would run back through and end up at the part of the main if statement that's for when $act has no value, right?

Ch0cu3r · November 21, 2013

No this is not the case. PHP does not go back on itself.

The solution is to perform a header redirect as soon as the record has been updated. This will clear all $_POST data and thus should then display the record listings.

hamburgerlove413 · November 21, 2013

Ok, it's working now, thank you.

Sign In

updating mysql table

Recommended Posts

hamburgerlove413

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

hamburgerlove413

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

hamburgerlove413

Link to comment

Share on other sites

Ch0cu3r

Link to comment

Share on other sites

hamburgerlove413

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information