Practical Web Programming

Sunday, January 25, 2009

Database Insert, Update and Delete Using PHP and MySQL

One of the best uses of PHP to the MySQL database is data manipulation. PHP's native support to MySQL is a big advantage to developers who uses the two. Once you got PHP and MySQL running in your server, you're good to go toward dynamic web application.

In the following examples, I'll be focusing on how to do data INSERT, UPDATE, and DELETE to the database using the web's two powerful combination, PHP and MySQL.

Here's the first one, doing data INSERT to the database. In these examples, I use the table from this post which I created in the test database in MySQL.

<?php

//CREATE A DATABASE CONNECTION
$db_conn = mysql_connect("localhost", "root", "root1")
or die("Cannot connect to the database. ".mysql_error());
//SELECT THE DATABASE TO USE
mysql_select_db("test");

//COMPOSE THE SQL
$sql = "INSERT INTO employees (
first_name,
last_name,
email,
phone_number)
VALUES (
'Joel',
'Badinas',
'kabalweg@gmail.com',
'123.456.7890')";

//EXECUTE SQL
$result = mysql_query($sql, $db_conn);

//CHECK IF QUERY IS SUCCESSFUL
if(mysql_affected_rows($db_conn) > 0)
{
print "Save successful";
}
else
{
print "Error: ".mysql_error();
}

?>


And here's how to do the data Update.

<?php

//CREATE A DATABASE CONNECTION
$db_conn = mysql_connect("localhost", "root", "root1")
or die("Cannot connect to the database. ".mysql_error());
//SELECT THE DATABASE TO USE
mysql_select_db("test");

//COMPOSE THE SQL
$sql = "UPDATE employees SET
first_name = 'Kabalweg'
WHERE first_name = 'Joel'
AND last_name = 'Badinas'";

//EXECUTE SQL
$result = mysql_query($sql, $db_conn);

//CHECK IF QUERY IS SUCCESSFUL
if(mysql_affected_rows($db_conn) > 0)
{
print "Update successful";
}
else
{
print "Error: ".mysql_error();
}

?>


And the last is the DELETE.

<?php

//CREATE A DATABASE CONNECTION
$db_conn = mysql_connect("localhost", "root", "root1")
or die("Cannot connect to the database. ".mysql_error());
//SELECT THE DATABASE TO USE
mysql_select_db("test");

//COMPOSE THE SQL
$sql = "DELETE FROM employees
WHERE first_name = 'Kabalweg'
AND last_name = 'Badinas'";

//EXECUTE SQL
$result = mysql_query($sql, $db_conn);

//CHECK IF QUERY IS SUCCESSFUL
if(mysql_affected_rows($db_conn) > 0)
{
print "Delete successful";
}
else
{
print "Error: ".mysql_error();
}

?>


There you have it. The source codes are well commented so you should be able to follow. If not, post a question in the comment.

0 comments:

Recent Post