Insert, update and delete values

INSERT - It puts information intno a table in the database. In this example, we're adding a person to a "people" table in the database. It will look like this:

php:
  //load database connection
  //...

  //we will pretend we're getting the info from a form.
  $firstName = $_POST['firstName'];
  $lastName = $_POST['lastName'];
  $emailAddress = $_POST['email'];
  $zipCode = $_POST['zip'];

  //do your error checking on the input.
  //...

  //Make the query to insert into the table.
  $query = "INSERT INTO `people` (`firstName`,`lastName`,`email`,`zip`, `dateAdded`)
                 VALUES ('$firstName','$lastName','$emailAddress','$zipCode', NOW());";

  //run the query to insert the person.
  $result = mysql_query($query) OR die(mysql_error());

  //let them know the person has been added.
  echo "You have successfully added " . $firstName . " " . $lastName . " to the database.";
?>


Inserting is a fairly straightforward thing to do. You specify the column names you're inserting data to, and then you pass it the variables you wish to add. One thing I did here was add a "dateAdded" column. To add the current date and time, you can use the NOW() MySQL function. It is a fast and easy way to add the date without having to rely on PHP.

UPDATE: An UPDATE statement edits the data in an existing row in a table in the database. For example, if a website has a user login, website can also let them edit their information. In our sample UPDATE statement, we will do just that:
php:
  //load database connection
  //...

  //we will pretend we're getting the info from a form.
  $firstName = $_POST['firstName'];
  $lastName = $_POST['lastName'];
  $emailAddress = $_POST['email'];
  $zipCode = $_POST['zip'];

  //we want to know who we are supposed to update.
  //It would be safer to grab the userid from a cookie or session
  //and check with the database to make sure it's a valid user
  //and that the person is trying to update their own info and not
  //someone elses info.
  $userId = $_POST['userId'];

  //do your error checking on the input.
  //...

  //Make the query to update the table.
  $query = "UPDATE `people` SET firstName='$firstName', lastName='$lastName',
                 email='$emailAddress', zip='$zipcode' WHERE userid='$userId';";

  //run the query to update the person.
  $result = mysql_query($query) OR die(mysql_error());

  //let them know the person has been added.
  echo "You have successfully updated " . $firstName . " " . $lastName . " in the database.";
?>


The UPDATE statement looks fairly similar to the INSERT statement as script needs to tell it which columns to update, and then use the WHERE clause to tell it which record(s) to update.



DELETE : The DELETE statement removes a row from a table in the database. In this example, we will be removing a user from the database based on their userId. Here is how the DELETE statement works:
php:
  //load database connection
  //...

  //get the userid of the person we're deleting.
  $userId = $_POST['userId'];

  //write the query to delete the person.
  $query = "DELETE FROM `people` WHERE userid='$userId'";

  //run the query to delete the person.
  $result = mysql_query($query) OR die(mysql_error());

  echo 'You successfully deleted the user.';
?>

The important thing to remember when deleting rows from a table is to make sure your WHERE clause is correct. If you forget to put the WHERE clause in the statement, it will delete all the rows in your table.

mysql_query() function - It executes a query on a MySQL database. This function returns the query handle for SELECT queries, TRUE/FALSE for other queries, or FALSE on failure.

Syntax
mysql_query(query,connection)

Parameter     Description
query     Required. Specifies the SQL query to send (should not end with a semicolon)
connection     Optional. Specifies the MySQL connection. If not specified, the last connection opened by mysql_connect() or mysql_pconnect() is used.