Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Editing sql Records with PHP

Posted on 2006-05-22
8
Medium Priority
?
319 Views
Last Modified: 2013-12-12
I am building a site which so far add's and delete's records into an sql db. I want to attempt editing, but am unsure of how to call the record to be edited. I think I am going to use a text field to input a product id and call the record. Or i could put an edit button next to the products which are called on a page which calls the products and displays them.

I am new to web development, and am unsure of what i should be doing, can anybody help me?
0
Comment
Question by:foster30
  • 6
  • 2
8 Comments
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16737324
Hi foster30,

Sure, well I will start with the basic theory and then some implementation.  To edit a record in a database you will use the UPDATE sql query.  In that case, to update an idividual record you need to match that individiual record or you will update the entire table, all the records (which I am sure you DO NOT want to do).  So, typcially each record has a primary key, most likely a unique id.

Now for the actual design.  The most simple will start with a 2 page process.  Page 1 you choose the record, Page 2 will hold all the data currently in that record and allow you to change that data.  It goes like this:

Page 1:  To ensure no error I would use a drop down menu which all of the records in the table.  Why a drop down and not a textbox?  A textbox has room for error, and for now its just easier to have a select box.  To populate the select box you will go to the table and get all of the records.  I will use a common example where the table has at least two fields, id and name.  The id will be the product id, most likely the primary key so that it can be linked directly to that product and that product only.  The name is just something that the user can read and understand.  A typical table might be this:

id | name | price | stock
--------------------------
1  | apple | 1.00 | 100
2  | glove | 4.00 | 10

Here is page 1 to populate the select box:
[page1.php]
<html>
<head><title>Page 1, Pick Record</title></head>
<body>
<form action="page2.php" method="GET">
<select name="pid">
<?php
// Connect to database
$conn = mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('database', $conn) or die(mysql_error());

// Create SQL and query
// This assumes two field names, "id" and "name"
$sql = "SELECT id, name FROM tableName";
$result = mysql_query($sql, $conn) or die(mysql_error());

// Cycle through the results and make them an option
// The value will be the id, what the user actually sees is the name
while ($record = mysql_fetch_aarry($result))
  print "<option value=$record[id]>$record[name]</option>";
?>
</select>
<br>
<input type="submit" value="Proceed">
</form>
</body>
</html>

Summary of page 1.  Fills the select box but there are a few other important parts.  I set the action to page2.php (which I will write next) and the method to GET.  You may be familier with POST and GET, but if you arent here is a very brief explanation of GET.  GET posts the information fromt he form into the query string.  You have seen this before, it looks like:
http://www.google.com/search?hl=en&q=glove&btnG=Google+Search
With everything after the ? being variables in the query string.
hl = 'en', q = 'glove', btnG = 'Google Search'
The result of somebody pushing the "Proceed" submit button on your form will do this:
www.yourdomain.com/page2.php?id=###
Where the number is the id of the item you selected from the drop down menu.

Page 2 coming very soon.

Joe P
0
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16737469
Page 2: This page does the majority of the work.  You need to get the information from the query string, remember it's:
www.yourdomain.com/page2.php?id=1   (the 1 means they picked apple from the drop down menu)

Then you need to populate a form with the values that the specific record currently has.  In the case of the table I showed up above, the form should have a textfield for: name, cost, and stock (NOT for id, that should not change).  For this simple case I will only have a textbox for you to change the name but you will see how easy it is to expand on this.

[page2.php]
<?php
// This page starts out with php code immediatly
// We get the id from the query string, very simple, just use $_GET
$id = 0;
// if isset() that means it is there, this is what we want
if (isset($_GET['id']))
  $id = $_GET['id'];
else
{
  // In this case there was no id in the query string, redirect immediatly to page1
  header('Location: page1.php');
  exit;
}

// By this point the id of the record we want to update is in $id
// Query the database for information on the record with that id
$conn = mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_connect('database', $conn) or die(mysql_error());

// Sql and Query
$sql = "SELECT name FROM database WHERE id = $id";
$result = mysql_query($sql, $conn) or die(mysql_error());

// This should have 1 result and only 1 result, if it has more then 1
// then there was an error in the database (or your query if it is different)
$onlyRecord = mysql_fetch($array);
$name = $onlyRecord['name'];

// Now the current name for that record is in $name
?>
<html>
<head><title>Page 2</title></head>
<body>
<form action="page3.php" method="POST">
<input type="hidden" value="<?php echo $id; ?>">
Record Name: <input type="text" name="NAME" value="<?php echo $name; ?>">
<br>
<input type="submit" value="Update">
</form>
</body>
</html>


On this page the action mostly happens before any html is rendered.  The php gets the id fromt he query string, and queries the database looking for information on that record (in this case just the name), narrowing the sql using the WHERE optimizer in the sql.  Then while building the <form> in the html code a few things happen.  There is a hidden field that will continue to pass the id which we need to select that specific record and it sets the textbox <input type="text"...> value to the current name.  When the page loads, if the person selected "apple" on the previous page then "apple" would be in the textbox right now.  Finally there is a submit button which will go to page 3, which will finally do the updating.  Notice this time I am using method="POST" because I want the information to be more secure.  There is a small reason that I used GET on the first page which I will tell you later.  For now lets concentrate on the UPDATE code that will be in page 3.

Page 3 coming soon!

Joe P
0
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16737479
Correction: The hidden input needed a name, lets give it name="id"
<input type="hidden" name="id" value="<?php echo $id; ?>">

Joe P
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Accepted Solution

by:
BogoJoker earned 2000 total points
ID: 16737611
Page 3: All of the information has just been given to this script to update the name of the chosen record!  All of it is coming from page 2 in the POST.  Just like $_GET gets information from method="GET", $_POST gets information from method="POST" (POST is the default just so that you know).  Two variables will/should be in $_POST right now, the id and the new name they just put and submitted int he textbox on page 2.  Then you will update the database, print out a confirmation message and provide a link back to page 1 for fun.  Here we go:

[page3.php]
<?php
// Again, because this page depends on information in $_POST
// we will start with php code before the html code even starts

// Variables
$id = 0;
$name = "";
// This time we check if they are not set, if so --> redirect to page1
if (!isset($_POST['name'] || !isset($_POST['id']))
{
  header('Location: page1.php');
  exit;
}
// They are sure to be set so update $id and $name
$id = $_POST['id'];
$name = $_POST['name'];

// Connect to the database
$conn = mysql_connect('localhost', 'user', 'pass') or die(mysql_error());
mysql_select_db('database', $conn) or die(mysql_error());

// UPDATE SQL and QUERY
$sql = "UPDATE table SET name='$name', id=$id";
$result = mysql_query($sql, $conn) or die(mysql_error());

// Update worked if it got this far, print out success message
?>
<html>
<head><title>Page 3</title></head>
<body>
<h3>Successfully Updated Database</h3>
New Name: <?php echo $name; ?>
<br><br>
<a href="page1.php">Link</a> to Page 1<br>
<a href="page2.php?id=<?php echo $id; ?>">Link</a> To Change the name for THIS record again!
</body>
</html>

This page wraps up the update.  It also lets me point out a few things.
1) Notice that each page required the same database connection code.  And you had to select the database.  You can put all of that into a small php file, and include that file in each of your scripts.  That makes it a little easier for you, and potentially a little safer because it is not on every single page, the code is only on one page and you can control access to that single page.  If you have any questions on this let me know!
2) Notice at the bottom of page 3 I provided two links.  One is just back to page1.php, nothing intresting there but what about that second link?  That was the reason I used method="GET".  page2.php checks the query string even if you just type in your browser www.yourdomain.com/page2.php?myFunnyNameIs=BogoJoker&id=1  That would work just fine.  This allows for a lot of flexability, you can bookmark that page even!!!!  But when there is critical data, for instance actually updating the database, I made sure you need to click a submit button to do that.

I hope this was helpful.  To make any of these scripts work you need to do a few things.  Change the mysql_connect() and mysql_select_db() information to the ones that will work on your server.  And also change all of the sql (therefore changing all the $record['index'] indexs as well.  It often pretty hard to give an example to someone when you know nothing of their table structure and what not so I used very generic, easy to recognize and understand names.  If you have any problems let me know because this was not tested at all, just off the top of my head.

Enjoy,
Joe P
0
 

Author Comment

by:foster30
ID: 16737648
Thanks so much for the detailed answer, I'll get straight to work, and I think i will be okay with all that you have said, you have most certainly earned the points.

:-)
0
 

Author Comment

by:foster30
ID: 16740635
I am struggleing with this....

I understand the logic and the concept behind this, but when trying to manipulate your code into mine, the drop down box does not poppulate it appears as blank.
0
 
LVL 17

Expert Comment

by:BogoJoker
ID: 16744635
Sure, Show me what your table looks like so I can edit the code closer to what you need, somethign like:
Table Name: (name)
Fields: field1, field2, field3,....

That way I can make the sql statements are perfect for you.
Again make sure you are putting the correct msyql_connect() information in =)

If you ever get any error messages, because I put a lot of die(mysql_error()); for good debugging, then tell me them and I can point you in the right direction.
0
 
LVL 17

Expert Comment

by:BogoJoker
ID: 17170807
Glad you got it working!

Joe P
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
This article discusses how to implement server side field validation and display customized error messages to the client.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question