Jump to content

updating mysql table


Go to solution Solved by hamburgerlove413,

Recommended Posts

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>

Link to comment
https://forums.phpfreaks.com/topic/284143-updating-mysql-table/
Share on other sites

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 by Ch0cu3r

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.

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 by Ch0cu3r

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?

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.