Can't get UPDATE statement to work

MjM8082 · August 16, 2011

Sorry I'm still new to php. So I'm trying to get my update statement to work. I will post the statement I have for it, I will also post sql code from the database it is trying to update and I will also post my form page. Appreciate any help, thanks.

Here is my update statement...

<?php

	if (isset($_POST['update']))
{          
	foreach($_POST['update'] as $update_id)
	{             
		$query = "UPDATE FROM users WHERE course_id = $update_id";             
		mysqli_query($dbc, $query) or die ('can\'t update course');          

		$course_id 	= $_POST['course_id'];
		$course_name = $_POST['course_name'];
		$student_id = $_POST['student_id'];
		$sql = "INSERT INTO users (course_id, course_name, student_id) VALUES ('$course_id', '$course_name', '$student_id');"; 
		$db->exec($sql);

	}				
	echo 'course has been updated.<br />';   
}

?>

Here is the page that the update statement is on...

<html>
<head>
<title>Update User</title>
</head>
<body>
<form method="post" action="update_user2.php">
<?php
$dbc = mysqli_connect('localhost', 'se266_user', 'pwd', 'se266') 
	or die(mysql_error());
//delete users
echo '<b>Delete or Update User</b>.<br />';
if (isset($_POST['remove']))
{          
	foreach($_POST['delete'] as $delete_id)
	{             
		$query = "DELETE FROM users WHERE course_id = $delete_id";             
		mysqli_query($dbc, $query) or die ('can\'t delete course');          
	}        
	echo 'user has been deleted.<br />';   
}

		if (isset($_POST['update']))
{          
	foreach($_POST['update'] as $update_id)
	{             
		$query = "UPDATE FROM users WHERE course_id = $update_id";             
		mysqli_query($dbc, $query) or die ('can\'t update course');          

		$course_id 	= $_POST['course_id'];
		$course_name = $_POST['course_name'];
		$student_id = $_POST['student_id'];
		$sql = "INSERT INTO users (course_id, course_name, student_id) VALUES ('$course_id', '$course_name', '$student_id');"; 
		$db->exec($sql);

	}				
	echo 'course has been updated.<br />';   
}

//display users info with checkbox to delete  
$query = "SELECT * FROM users";  
$result = mysqli_query($dbc, $query);  
while($row = mysqli_fetch_array($result))
{      
	echo '<input type="checkbox" value="' .$row['course_id'] . '" name="delete[]" />'; 
	echo ' ' .$row['course_name'] .' '. $row['student_id'];
	echo '<br />';  
}
mysqli_close($dbc);

?>	
<input type="submit" name="remove" value="Remove" />
<input type="submit" name="update" value="Update" />
<br>
<br>
<form method="GET" action="update_user2.php">		
<label for="course_id">Course ID:</label> 
<input type="text" id="course_id" name="course_id" /><br />		
<label for="course_name">Course Name:</label>
<input type="text" id="course_name" name="course_name" /><br />

<label for="course_name">Student ID:</label>

<input type="text" id="student_id" name="student_id" /><br />		



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

Here is my sql database...

DROP DATABASE IF EXISTS se266;
CREATE DATABASE se266;


use se266;
CREATE TABLE users (
  course_id        INT(11)        NOT NULL   AUTO_INCREMENT,
  course_name       VARCHAR(50),
  student_id     VARCHAR(50)    NOT NULL,

  PRIMARY KEY (course_id)
);
-- create the users and grant priveleges to those users
GRANT SELECT, INSERT, DELETE, UPDATE
ON se266.*
TO se266_user@localhost
IDENTIFIED BY 'pwd';

INSERT INTO users (course_id, course_name, student_id)
VALUES ('se255', 'Web Design Using PHP', '43253256');
use se266;
SELECT * FROM users;

The Little Guy · August 16, 2011

don't use FROM while using update, also you need to use set, other wise it won't update anything

for example:

update members set first_name = 'billy' where member_id = 123;

MjM8082 · August 16, 2011

Thanks. What if I want the "set" to be anything that the user puts in the textbox? How would I code that?

The Little Guy · August 16, 2011

You would do something like this:

$first_name = mysql_real_escape_string($_POST['first_name']);

mysql_query("update members set first_name = '$first_name' where member_id = 123;");

MjM8082 · August 16, 2011

Still not working, this is what my update statement looks like now...

<?php

	if (isset($_POST['update']))
{          
	foreach($_POST['update'] as $update_id)
	{             
		$course_name = mysql_real_escape_string($_POST['course_name']);

		mysql_query("update users set course_name = '$course_name' where course_id = course_id;");             
		mysqli_query($dbc, $query) or die ('can\'t update course');          

		$course_id 	= $_POST['course_id'];
		$course_name = $_POST['course_name'];
		$student_id = $_POST['student_id'];
		$sql = "INSERT INTO users (course_id, course_name, student_id) VALUES ('$course_id', '$course_name', '$student_id');"; 
		$db->exec($sql);

	}				
	echo 'course has been updated.<br />';   
}

?>

The Little Guy · August 16, 2011

your update needs to be below

$course_id = $_POST['course_id'];

and the "course_id" needs to have a $ in front of in the update query.

MjM8082 · August 16, 2011

Still not updating.. here is my updated code...

			if (isset($_POST['update']))
{          
	foreach($_POST['update'] as $update_id)
	{             
		$course_id 	= $_POST['course_id'];
		$course_name = $_POST['course_name'];
		$student_id = $_POST['student_id'];

		mysql_query("update users set course_name = '$course_name' where course_id = $course_id;");             
		mysqli_query($dbc, $query) or die ('can\'t update course');     

		$sql = "INSERT INTO users (course_id, course_name, student_id) VALUES ('$course_id', '$course_name', '$student_id');"; 
		$db->exec($sql);

	}				
	echo 'course has been updated.<br />';   
}

The Little Guy · August 16, 2011

i used mysql and your using mysqli, you might want to change my function.

MjM8082 · August 16, 2011

hmm.. still not working, I can't find what it is.

MjM8082 · August 16, 2011

I feel like I'm getting closer... Here is my updated code...

<html>
<head>
<title>Update User</title>
</head>
<body>
<form method="post" action="update_user2.php">
<?php
$dbc = mysqli_connect('localhost', 'se266_user', 'pwd', 'se266') 
	or die(mysql_error());
//delete users
echo '<b>Delete or Update User</b>.<br />';
if (isset($_POST['remove']))
{          
	foreach($_POST['delete'] as $delete_id)
	{             
		$query = "DELETE FROM users WHERE course_id = $delete_id";             
		mysqli_query($dbc, $query) or die ('can\'t delete course');          
	}        
	echo 'user has been deleted.<br />';   
}

		if (isset($_POST['update']))
{          
	foreach($_POST['update'] as $update_id)
	{             
		$course_id 	= $_POST['course_id'];
		$course_name = $_POST['course_name'];
		$student_id = $_POST['student_id'];

		$query = "UPDATE users SET course_name = $course_name WHERE course_id = $course_id";             
		mysqli_query($dbc, $query) or die ('can\'t update course');     

		$update_count = $db->exec($query);

	}				
	echo 'course has been updated.<br />';   
}


//display users info with checkbox to delete  
$query = "SELECT * FROM users";  
$result = mysqli_query($dbc, $query);  
while($row = mysqli_fetch_array($result))
{      
	echo '<input type="checkbox" value="' .$row['course_id'] . '" name="delete[]" />'; 
	echo ' ' .$row['course_name'] .' '. $row['student_id'];
	echo '<br />';  
}
mysqli_close($dbc);

?>	
<input type="submit" name="remove" value="Remove" />
<input type="submit" name="update" value="Update" />
<br>
<br>
<form method="POST" action="update_user2.php">		
<label for="course_id">Course ID:</label> 
<input type="text" id="course_id" name="course_id" /><br />		
<label for="course_name">Course Name:</label>
<input type="text" id="course_name" name="course_name" /><br />

<label for="course_name">Student ID:</label>

<input type="text" id="student_id" name="student_id" /><br />		



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

voip03 · August 16, 2011

http://www.phpfreaks.com/forums/index.php?topic=341666.0

Jim R · September 7, 2011

Ignore...screwed up.

Sign In

Can't get UPDATE statement to work

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information