MjM8082 Posted August 16, 2011 Share Posted 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; Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/ Share on other sites More sharing options...
The Little Guy Posted August 16, 2011 Share Posted 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; Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258222 Share on other sites More sharing options...
MjM8082 Posted August 16, 2011 Author Share Posted 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? Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258224 Share on other sites More sharing options...
The Little Guy Posted August 16, 2011 Share Posted 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;"); Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258227 Share on other sites More sharing options...
MjM8082 Posted August 16, 2011 Author Share Posted 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 />'; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258229 Share on other sites More sharing options...
The Little Guy Posted August 16, 2011 Share Posted 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. Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258230 Share on other sites More sharing options...
MjM8082 Posted August 16, 2011 Author Share Posted 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 />'; } Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258234 Share on other sites More sharing options...
The Little Guy Posted August 16, 2011 Share Posted August 16, 2011 i used mysql and your using mysqli, you might want to change my function. Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258235 Share on other sites More sharing options...
MjM8082 Posted August 16, 2011 Author Share Posted August 16, 2011 hmm.. still not working, I can't find what it is. Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258236 Share on other sites More sharing options...
MjM8082 Posted August 16, 2011 Author Share Posted 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> Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258257 Share on other sites More sharing options...
voip03 Posted August 16, 2011 Share Posted August 16, 2011 http://www.phpfreaks.com/forums/index.php?topic=341666.0 Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1258265 Share on other sites More sharing options...
Jim R Posted September 7, 2011 Share Posted September 7, 2011 Ignore...screwed up. Quote Link to comment https://forums.phpfreaks.com/topic/244944-cant-get-update-statement-to-work/#findComment-1266439 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.