MySQL INSERT, UPDATE, DELETE and SELECT with PHP


First off, this tutorial assumes you have a database connection above all the SQL statements. The database connection should look something like this:

php:
<?php
//these variables are used for database connection
$host = "localhost";
$user = "db_user";
$pass = "db_pass";
$database = "db_name";

//connect to the database.
$db = mysql_connect($host, $user, $pass);
mysql_select_db ($database);
?>

INSERT Statement:
The first statement we will look at is the INSERT statement. This is how you actually put information intno a table in the database. In this example, we will pretend like we’re adding a person to a “people” table in the database. It will look like this:

php:
<?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 Statement:
An UPDATE statement is how you edit the data in an existing row in a table in the database. For example, if you have a website that has a user login, you can also let them edit their information. In our sample UPDATE statement, we will do just that:

php:
<?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.";
?>

As you can see, the UPDATE statement looks fairly similar to the INSERT statement. You tell it which columns to update, and then use the WHERE clause to tell it which record(s) to update.

DELETE Statement:
The DELETE statement is how you remove 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:
<?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.

SELECT Statement (Simple select):
This is what a simple select statement looks like in PHP. This will get all the data in the “people” table and output the name of every person:

php:
<?php
//load database connection
//...

//make the query to run.
$query = "SELECT * FROM `people`";
$result = mysql_query($query) OR die(mysql_error());

//now we turn the results into an array and loop through them.
while($row = mysql_fetch_array($result))
{
$firstName = $row['firstName'];
$lastName = $row['lastName'];

echo 'This is: ' . $firstName . ' ' . $lastName . "<br/>\n";
}
?>

SELECT Statement (With ordering):
If you want to sort your output based on one of the columns in the table, you can add on an “ORDER BY” clause to your select statement and tell it to sort either ascending (ASC) or descending (DESC). Here is what the previous example will look like if we’re sorting by last name:

php:
<?php
//load database connection
//...

//make the query to run.
//Sort the last name in an ascending order (A-Z)
$query = "SELECT * FROM `people` ORDER BY lastName ASC";
$result = mysql_query($query) OR die(mysql_error());

//now we turn the results into an array and loop through them.
while($row = mysql_fetch_array($result))
{
$firstName = $row['firstName'];
$lastName = $row['lastName'];

echo 'This is: ' . $firstName . ' ' . $lastName . "<br/>\n";
}
?>

SELECT Statement (Limiting results):
Lets say that you want to grab the last 5 entries into the table. What we can do is add on a “LIMIT” clause and tell it how many results we would like returned. Here is how this will work:

php:
<?php
//load database connection
//...

//make the query to run.
//order the date descending (most recent first) and get the last 5 entries.
$query = "SELECT * FROM `people` ORDER BY dateAdded DESC LIMIT 5";
$result = mysql_query($query) OR die(mysql_error());

//now we turn the results into an array and loop through them.
while($row = mysql_fetch_array($result))
{
$firstName = $row['firstName'];
$lastName = $row['lastName'];

echo 'This is: ' . $firstName . ' ' . $lastName . "<br/>\n";
}
?>

SELECT Statement (with WHERE clause):
If you’re only interested in the users that have a last name that start with a “W” you can add a WHERE clause to the SELECT statement to only get the users with a W as the first letter of their last name. You can use the WHERE clause for other things like checking to see if data exists in a specific column or if a set of data matches another.

php:
<?php
//load database connection
//...

//make the query to run.
//UPPER makes the lastName uppercase.  This way if the first character is lowercase,
//it will still find it.
$query = "SELECT * FROM `people` WHERE UPPER(lastName) LIKE 'W%'";
$result = mysql_query($query) OR die(mysql_error());

//now we turn the results into an array and loop through them.
while($row = mysql_fetch_array($result))
{
$firstName = $row['firstName'];
$lastName = $row['lastName'];

echo 'This is: ' . $firstName . ' ' . $lastName . "<br/>\n";
}
?>

SELECT Statement (Counting the results):
It is often required that you check how many results are returned before you tell the code what to do. For example, you can check to see if a username and password match the ones stored in a database. If they match, you welcome them. If they do not match, you tell them to please log in or try again. Here is what that would look like:

php:

<?php
//load database connection
//...

//get the username and password from the form.
$userName = $_POST['username'];
$password = $_POST['password'];

//make the query to run.
$query = "SELECT * FROM `people` WHERE username='$userName' AND password='$password'";
$result = mysql_query($query) OR die(mysql_error());
$count = mysql_num_rows($result);

//if count > 0, then it returned a result, meaning the info provided was valid.
//now we can welcome them.
if($count > 0) {
//since there can only be one person with that username and password,
//we don't have to loop
$row = mysql_fetch_array($result);

$firstName = $row['firstName'];

echo 'Welcome back, '.$firstName.'!<br/>';
}
else
{
echo 'The information you provided was not valid.';
}
?>

About these ads

12 thoughts on “MySQL INSERT, UPDATE, DELETE and SELECT with PHP

  1. saritha July 3, 2012 at 9:51 am

    sir plz send the update and delete in php with mysql

  2. news July 18, 2013 at 11:30 pm

    Excellent web site. Lots of helpful information here.
    I am sending it to a few friends ans additionally sharing in delicious.
    And certainly, thank you on your sweat!

  3. news July 18, 2013 at 11:39 pm

    Hi mates, nice piece of writing and nice urging commented
    at this place, I am truly enjoying by these.

  4. More about the author July 21, 2013 at 10:57 pm

    Hi, all the time i used to check website posts here in
    the early hours in the morning, for the reason that i love to
    find out more and more.

  5. maheshkanna July 24, 2013 at 5:16 am

    thanks for the comments

  6. maheshkanna July 24, 2013 at 5:20 am

    Thanks for comments

  7. https://ashllness.wiki.zoho.com/ July 26, 2013 at 10:26 pm

    Because the admin of this site is working, no question very shortly it will be famous,
    due to its feature contents.

  8. Damian August 2, 2013 at 7:39 am

    Aw, this was an exceptionally nice post. Taking the time and actual effort to generate a good article… but what can I say… I put things
    off a lot and never seem to get nearly anything done.

  9. http://pathfinderapp.org June 18, 2014 at 10:07 am

    Howdy this is kind of of off topic but I was wanting to know if blogs use WYSIWYG editors or if you have to manually code with HTML.
    I’m starting a blog soon but have no coding know-how so I wanted to get advice from someone with
    experience. Any help would be greatly appreciated!

  10. best mobile phone insurance uk June 28, 2014 at 7:32 pm

    Thanks for the auspicious writeup. It if truth be told was a entertainment account it.

    Look complex to far delivered agreeable from you! By the way, hoow can we communicate?

  11. wooden furniture July 4, 2014 at 8:45 am

    Wow, this paragraph is nice, my younger sister is analyzing these
    kinds of things, so I am going to convey her.

  12. Gerald July 4, 2014 at 4:36 pm

    I love reading through a post that can make people think.
    Also, thank you for permitting me to comment!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

www.impratima.in

Frontend Web Developer & Designer .......

Magento Tips and Fundamental Solutions

Magento Technical and Fundamental Tips

Magento-4U

Lets share magento e-commerce experience ...

ITechonology

Information Techonology

Don Charisma

because anything is possible with Charisma

PHP MySQL

Share Knowledge

Follow

Get every new post delivered to your Inbox.

Join 141 other followers

%d bloggers like this: