Page 1 of 1

Inserting and Updating MySQL for Beginners How to insert and update records in your database Rate Topic: -----

#1 akozlik  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 90
  • View blog
  • Posts: 797
  • Joined: 25-February 08

Post icon  Posted 06 June 2008 - 12:00 PM

A request was made for a tutorial on how to insert and update MySQL tables, and I was more than happy to oblige. The following tutorial will teach you the very basic insert and update commands used in SQL. By the time you are finished reading you should be able to insert a new record into a table, and to update an existing record if one of its values should change. We will begin by building a form that a user will submit, and then the php code to take the information and insert it into the database.

This tutorial will assume you know how to create tables and connect to your database. We will only be going over the insertion and updating portions specifically.

NOTICE: There is a great tutorial on DIC in the PHP section on SQL Injection. READ IT before beginning to develop an application. Preferably immediately after reading this tutorial.

This code will assume that you have a table named 'people'. It will contain the fields 'fname', 'lname', and 'email'. Make sure you have this table in your database when you begin this tutorial.

Inserting a Record

Alright, first the code for our form.

<form method="POST" action="submitForm.php">
<table>
   <tr>
      <td>First Name: </td>
      <td><input type="text" name="fname"></td>
   </tr>
   <tr>
       <td>Last Name: </td>
       <td><input type="text" name="lname"></td>
   </tr>
   <tr>
       <td>E-mail: </td>
       <td><input type="text" name="mail"></td>
   </tr>
   <tr>
       <td></td>
       <td><input type="submit" value="Submit!"></td>
   </tr>
</table>
</form>



That's just a basic form with a first name, last name and e-mail field. Remember to always form validate and sanitize your data. That's the only time I'm going to mention it.

Notice that we are using the POST method, and the action causes the form to go to submitForm.php. Save that form file as form.html, or whatever you want it to be.

Next we're going to create the submitForm.php file.

<?php

// Step 1
$fname = $_POST['fname'];
$lname = $_POST['lname'];
$email = $_POST['email'];

// This is where you would sanitize your data.  It is unbelievably 
// important that you do so before inserting it into the database.  
// This is where reading that SQL injection tutorial will come in handy.

// Step 2
$sql = "insert into people (fname, lname, email) VALUES ('$fname', '$lname', '$email')";

// Step 3
$result = mysql_query($sql) or die ( mysql_error() );
?>



Step 1:
Grab the variables from the $_POST. If you used "GET" on your form you would use $_GET['fname'].

Step 2:
Here we're building our query string. We use the format:

"INSERT INTO tablename (fields) VALUES (variables)";

The insert into causes the data to be inserted into the table. The 'tablename' is the name of the table you want to insert the data into. Within the parenthesis you want to put the names of all the fields you are inserting into. This is followed by the word VALUES, and then all the variables that you want to insert into the database.

Step 3:
Runs the query string through mysql. If it's successful it returns the value to result. If it fails, it kills the program and displays the error generated by mysql.

That's inserting into a database in a nutshell. It's a very basic example, and you can take some time to explore other methods and additional parameters to put into your query strings.


Updating a Record

Lets say you have a form that allows you to change the last name of a record in the database. We'll also assume that you are going to find the record in the database to updated based on the user's e-mail address. In production, this isn't how you would do things, you would typically use a userid field in the database and check for that, but that starts to get complicated. For now, we'll just stick with this basic example.

We will need a field that takes in the new last name, and the user's e-mail address.

<form method="POST" action="updateLastName.php">
<table>
   <tr>
      <td>New Last Name:</td>
      <td><input type="text" name="lname"></td>
   </tr>
   <tr>
       <td>E-mail Address</td>
       <td><input type="text" name="email"></td>
   </tr>
   <tr>
       <td></td>
       <td><input type="submit" value="Submit!"></td>
</table>
</form>



Next we'll create our updateLastName.php file. Typically you would just use a function, but for the sake of simplicity I am just going to do this in a separate file. Trying to keep things as focused as possible on the task at hand.

The following code goes into our updateLastName.php file.

<?php

// Step 1
$lname = $_POST['lname'];
$email = $_POST['email'];

// Clean your data.  Nobody likes smelly data

// Step 2
$sql = "update people set lname='$lname' where email = '$email'";

// Step 3
$result = mysql_query($sql) or die ( mysql_error() );
?>



Step 1:
Get your variables from the $_POST.

Step 2:
Build your SQL string. It is in the format

"UPDATE table SET field = 'variable' WHERE field = 'variable'";

Use the keyword UPDATE, followed by the table name, followed by SET, followed by the field name is equal to the variable, followed by WHERE, followed by the condition you want the query to meet. In this sql string we're setting the lname field to the $lname variable where the e-mail address is the same as the email variable. This will change every instance that the e-mail is found.

You could also set other criteria. If we wanted to change every record where the email was NOT the given e-mail, we would have used

$sql = "update table set lname='$lname' WHERE email != '$email'";



As you can see, there are many different ways to limit what gets updated.

Hopefully that will get you started on inserting and updating records. If you are looking for more information I would definitely recommend the PHP tutorials on this forum, or check out Tizag's tutorials at www.tizag.com. That's the resource I used when I first started learning PHP.

As always, take care, and leave your comments and suggestions.

Is This A Good Question/Topic? 0
  • +

Replies To: Inserting and Updating MySQL for Beginners

#2 clickfanatic  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 11-January 09

Posted 11 January 2009 - 05:30 AM

Thanks this is a nice starting point for php and mysql newbies like me.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1