Update SQL Record Using PHP

@themonkey40May 29.2009

So I have created a page called ‘edit.php’ that pulls a sql record from the previous page and inputs that various values in text boxes so that they can be edited. I need to create an update script, but not sure where to start. Here is the code for the edit.php page:

[code=php]<?php session_start(); if (@$_SESSION[‘auth’] !=”yes”) { header(“Location: login_form.php”); exit(); } ?> <?php $host=”localhost”; $username=”usernamer”; $password=”password”; $db_name=”db_name”; $tbl_name=”table”;

// Connect to server and select database. mysql_connect(“$host”, “$username”, “$password”)or die(“cannot connect”); mysql_select_db(“$db_name”)or die(“cannot select DB”);

$member_id = $_GET[‘id’]; $results = mysql_query(“select * from member where id = $member_id”); $row = mysql_fetch_assoc($results);

?> <html> <head> <link href=”style.css” type=”text/css” rel=”stylesheet”> </head> <body>

<div align=”center”> <table width=”80%” cellpadding=”0″ cellspacing=”0″ border=”0″> <tr> <td>Guest 1 <input type=”text” value=”<? echo $row[‘guest_1’]; ?>” name=”guest_1″></td> </tr>

</div> </body> </html>[/code]

Any help, pointers or suggestions would be greatly appreciated. Thanks guys.

to post a comment

PHP

11 Comments(s) _↴

@Blizzard84May 29.2009 — #Hi once again,

Now you need to create the form. You have all the information in the array $row now.

This would be a form that you could edit.

[code=php]<form action="edit_process.php" method="post">
 <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
 <input type="text" name="column1" value="<?php echo $row['column1']; ?>">
 ...
 ...
 <input type="submit" value="Update">
 </form>[/code]

When finished the person would press submit and this would go to edit_process.php. Make sure you use your own names for the columns.

In edit_process.php, you will grab the variables that were posted from edit.php

[code=php]
 header("Location: index.php"); // redirect back to main page 
 
 $host="localhost";
 $username="usernamer";
 $password="password";
 $db_name="db_name";
 $tbl_name="table";
 
 // Connect to server and select database.
 mysql_connect("$host", "$username", "$password")or die("cannot connect");
 mysql_select_db("$db_name")or die("cannot select DB"); 
 
 $id = $_POST['id'];
 $column1 = $_POST['column1'];
 
 mysql_query("UPDATE member SET column1 = $column1 where id = $id");[/code]

These are only small samples of what you need done. Once again make sure you change columns to your column names.

@Blizzard84May 29.2009 — #You could also post it back to the same file, and use if statements. It might save you a step, but this'll do.

Once you feel more comfortable with php, you can fool around with it.

@themonkey40authorMay 29.2009 — #Ok so I tried the code that you gave me, and it doesn't seem to be working. I think I did something wrong. Here is the code from my edit_process.php:

[code=php]<?php
 session_start();
 if (@$_SESSION['auth'] !="yes")
 {
 header("Location: login_form.php");
 exit();
 }
 
 $host="localhost"; 
 $username="username"; 
 $password="password"; 
 $db_name="db_name"; 
 $tbl_name="table";
 
 header("Location: rsvp_view.php"); // redirect back to main page 
 
 // Connect to server and select database.
 mysql_connect("$host", "$username", "$password")or die("cannot connect");
 mysql_select_db("$db_name")or die("cannot select DB"); 
 
 $id = $_POST['id'];
 $column1 = $_POST['guest_1'];
 
 mysql_query("UPDATE guests SET guest_1 = $column1 where id = $id"); 
 
 ?>[/code]

And here is the code from my edit.php page. I have more text boxes than your example will edit. Just trying to get the 'guest_1' to update in my sql database, then will continue with the other fields.

[code=php]<?php
 session_start();
 if (@$_SESSION['auth'] !="yes")
 {
 header("Location: login_form.php");
 exit();
 }
 ?>
 <?php 
 $host="localhost"; 
 $username="username"; 
 $password="password"; 
 $db_name="db_name"; 
 $tbl_name="table";
 
 // Connect to server and select database.
 mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
 mysql_select_db("$db_name")or die("cannot select DB");
 
 $member_id = $_GET['id']; 
 $results = mysql_query("select * from guests where id = $member_id"); 
 $row = mysql_fetch_assoc($results); 
 
 ?>
 <html>
 <head>
 <link href="style.css" type="text/css" rel="stylesheet">
 </head>
 <body>
 
 <div align="center">
 <form action="edit_process.php" method="post">
 <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
 <table width="70%" cellpadding="0" cellspacing="0" border="0">
 <tr>
 <td width="30%"><table width="100%" cellpadding="0" cellspacing="0" border="0">
 <tr>
 <td>Guest 1 <input type="text" value="<? echo $row['guest_1']; ?>" name="guest_1"></td>
 </tr>
 <tr>
 <td>Guest 2 <input type="text" value="<? echo $row['guest_2']; ?>" name="guest_2"></td>
 </tr>
 <tr>
 <td>Guest 3 <input type="text" value="<? echo $row['guest_3']; ?>" name="guest_3"></td>
 </tr>
 <tr>
 <td>Guest 4 <input type="text" value="<? echo $row['guest_4']; ?>" name="guest_4"></td>
 </tr>
 <tr>
 <td>Guest 5 <input type="text" value="<? echo $row['guest_5']; ?>" name="guest_5"></td>
 </tr>
 </table></td>
 
   <td width="70%"><table width="100%" cellpadding="0" cellspacing="0" border="0">
   <tr>
   <td>Will Be Attending <input type="text" value="<? echo $row['attending']; ?>" name="vegetarian"></td>
   </tr>
   <tr>
   <td>Number Vegetarian Meals <input type="text" value="<? echo $row['vegetarian']; ?>" name="vegetarian"></td>
   </tr>
   <tr>
   <td>How Many People Attending<input type="text" value="<? echo $row['total_guests']; ?>" name="vegetarian"></td>
   </tr>
   <tr>
   <td>Email Address <input type="text" value="<? echo $row['email']; ?>" name="vegetarian"></td>
   </tr>
   </table></td>
  </tr>
  <tr>
   <td><input type="submit" value="Update"></td>
  </tr>
 </table>
 </form>
 </div>
 </body>
 </html>[/code]

Thanks for helping me out, you have really been getting me out of this pinch.

@Blizzard84May 29.2009 — #I'm guessing guest_1 is a string so use this instead.

[code=php]mysql_query("UPDATE guests SET guest_1 = '$column1' where id = $id");[/code]

If that does work comment out

header("Location: rsvp_view.php"); and echo $column1 variable so u know it's passing correctly. Then we'll know where the error is.

@themonkey40authorMay 29.2009 — #How do I do the echo in the header. Not sure how to code that. I had the single quotes around column1, but still no update.

@themonkey40authorMay 29.2009 — #Also guest_1 is a column in my sql database. Thought I would clarify, not sure what a string is.

@Blizzard84May 29.2009 — #

[code=php]session_start();
 if (@$_SESSION['auth'] !="yes")
 {
 header("Location: login_form.php");
 exit();
 }
 
 $host="localhost";
 $username="username";
 $password="password";
 $db_name="db_name";
 $tbl_name="table";
 
 // header("Location: rsvp_view.php");
 
 // Connect to server and select database.
 mysql_connect("$host", "$username", "$password")or die("cannot connect");
 mysql_select_db("$db_name")or die("cannot select DB");
 
 $id = $_POST['id'];
 $column1 = $_POST['guest_1'];
 
 echo $column1; // check if value is correct
 
 mysql_query("UPDATE guests SET guest_1 = '$column1' where id = $id limit 1"); [/code]

Since you're not forwarding it rsvp_view.php because you commented it out, it should tell you if there are errors in the code.

Make sure 'guests' is the table you're updating

@themonkey40authorMay 29.2009 — #Ok so I put that in the edit_process.php code and still nothing is showing up in the URL that would show that it is updating the sql database. Have anymore thoughts or different ways that we could go about this?

@themonkey40authorMay 29.2009 — #Ok, so now after I click on update, it goes to a new blank screen and shows the change I made in the previous screen. It just isn't saving to the sql database. So we are half way there. Any thoughts?

@Blizzard84May 30.2009 — #Put the echo underneath your update query. Should output an error if any.

[code=php]
 
 mysql_query("UPDATE guests SET guest_1 = '$column1' where id = $id limit 1"); 
 
 echo mysql_error();
 
 [/code]

If you still can't debug, send all the code for the edit process.

@themonkey40authorJun 01.2009 — #Here is the edit.php code. I am only trying the edit guest_1. After we have figured this out, I will update the code to include all the fields.

[code=php]<?php
 session_start();
 if (@$_SESSION['auth'] !="yes")
 {
 header("Location: login_form.php");
 exit();
 }
 ?>
 <?php 
 $host="localhost"; 
 $username="username"; 
 $password="password"; 
 $db_name="db_name"; 
 
 // Connect to server and select database.
 mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
 mysql_select_db("$db_name")or die("cannot select DB");
 
 $id = $_GET['id']; 
 $results = mysql_query("SELECT * FROM guests WHERE id = $id"); 
 $rows = mysql_fetch_assoc($results); 
 
 ?>
 <html>
 <head>
 <link href="style.css" type="text/css" rel="stylesheet">
 </head>
 <body>
 
 <div align="center">
 <form action="edit_process.php" method="post"  onSubmit="return confirm('Are you sure you want to update this record?');">
 <input type="hidden" name="id" value="<?php echo $row['id']; ?>">
 <table width="70%" cellpadding="0" cellspacing="0" border="0">
 <tr>
 <td width="30%"><table width="100%" cellpadding="0" cellspacing="0" border="0">
 <tr>
 <td>Guest 1 <input type="text" value="<? echo $rows['guest_1']; ?>" name="guest_1"></td>
 </tr>
 <tr>
 <td>Guest 2 <input type="text" value="<? echo $rows['guest_2']; ?>" name="guest_2"></td>
 </tr>
 <tr>
 <td>Guest 3 <input type="text" value="<? echo $rows['guest_3']; ?>" name="guest_3"></td>
 </tr>
 <tr>
 <td>Guest 4 <input type="text" value="<? echo $rows['guest_4']; ?>" name="guest_4"></td>
 </tr>
 <tr>
 <td>Guest 5 <input type="text" value="<? echo $rows['guest_5']; ?>" name="guest_5"></td>
 </tr>
 </table></td>
 
   <td width="70%"><table width="100%" cellpadding="0" cellspacing="0" border="0">
   <tr>
   <td>Will Be Attending <input type="text" value="<? echo $rows['attending']; ?>" name="vegetarian"></td>
   </tr>
   <tr>
   <td>Number Vegetarian Meals <input type="text" value="<? echo $rows['vegetarian']; ?>" name="vegetarian"></td>
   </tr>
   <tr>
   <td>How Many People Attending<input type="text" value="<? echo $rows['total_guests']; ?>" name="vegetarian"></td>
   </tr>
   <tr>
   <td>Email Address <input type="text" value="<? echo $rows['email']; ?>" name="vegetarian"></td>
   </tr>
   </table></td>
  </tr>
  <tr>
   <td><input type="submit" value="Update"></td>
  </tr>
 </table>
 </form>
 </div>
 </body>
 </html>[/code]

Here is the edit_process.php code:

[code=php]<?php
 
 $host="localhost"; 
 $username="username"; 
 $password="password"; 
 $db_name="db_name"; 
 $tbl_name="tbl_name";
 
 header("Location: rsvp_view.php");
 
 // Connect to server and select database.
 mysql_connect("$host", "$username", "$password")or die("cannot connect");
 mysql_select_db("$db_name")or die("cannot select DB");
 
 $id = $_POST['id'];
 $column1 = $_POST['guest_1'];
 
 //echo $column1; // check if value is correct
 
 mysql_query("UPDATE guests SET guest_1 = '$column1' where id = $id limit 1"); 
 echo mysql_error();
 ?>[/code]

When I echo column_1, It goes to a blank page and shows the change I made in the text box. The problem seems to be that it never saves it to the sql database. Any ideas on why?

Also in #PHP _↴

PHP Troubles on server PHP File Characters save php created image

Success!

Help @themonkey40 spread the word by sharing this article on Twitter...

Tweet This

Update SQL Record Using PHP

11 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Update SQL Record Using PHP

11 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

11 Comments(s) _↴

Also in #PHP _↴