Software Developer's Blog

Introduction to the ALTER, INSERT INTO, UPDATE & DELETE Statements:

Today, we will try to see the:

ALTER, INSERT, UPDATE and DELETE SQL keywords and we will try to illustrate their use with example.

1. The ALTER keyword:

This key word is one of the DDL (Data Definition Languages). We can use the ALTER keyword to change the basic structure of a database table. When we use the ALTER keyword with a table, it can be used to add, delete, or modify columns of the selected table.

The syntax is:

ALTER TABLE table_name
ADD column_name datatype

If you want to delete/remove a column from an existing table, you can use the ALTER keyword as follows:

ALTER TABLE table_name
DROP COLUMN column_name

If you want to change/modify the data-type of a column of an existing table, you can use the ALTER key word as follows:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Now let us see the above syntax in use with an example. Assume you just created a table called ‘tbl_student’ using the following CREATE TABLE statement…

CREATE TABLE tbl_student(

id int auto_increment,

name varchar(50) not null,

id_number varchar(20) not null,

city varchar(30) not null,

primary key(id)

);

Then you realize that you forgot to add one column called ‘CGPA’ to the table. So to add a new column to the existing table ‘tbl_student’ you can use:

ALTER TABLE tbl_student
ADD cgpa int;

When you execute this statement, the table will have a new column ‘cgpa’ appended to it. But then you again saw one error. A student CGPA is a floating number. E.g student can have 3.45 as a cgpa. But the data type of cgpa in tbl_student is int. So to change the ‘int’ to a ‘float’ data type, you need to do the following:

ALTER TABLE tbl_student
ALTER COLUMN cgpa float

If you want to change and decide that you don’t want to store the cgpa column in tbl_student, you need to only drop/delete the ‘cgpa’ column with out affecting the rest of the columns in the table. To do this, you need to use the DROP key word with ALTER. See the example given below:

ALTER TABLE tbl_student
DROP COLUMN cgpa

Executing the above statement will eliminate the ‘cgpa’ column and leave the rest of the table intact.

2. INSERT INTO Keyword:

This key word is one of the DML (Data Manipulation Languages). It strictly deals with the content of a table and not with the underlying table structure. This key word is used to add/insert a new record to the table specified in the statement. The INSERT INTO SQL statement has two forms (syntax). These are given below:

Version One: (Here you don’t have to specify the order of the columns of the table. You just give the name of the table where the new record is supposed to be added followed by the actual data values to be inserted.)

INSERT INTO table_name
VALUES (value1,value2,value3,...);

Version Two: (In the second version, you need to specify the order of the columns followed by the actual data values you want to add to the table.)

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

Let us assume the example table we have created in the ALTER TABLE section. We will use the tbl_student schema (table structure) to insert data to it. In the beginning, the table contains no data, hence the table is empty.

First we will try to use the version one approach, and next we will use the version two approaches to insert new record to the table.

Version One:

INSERT INTO tbl_student

VALUES(0,’Alemu Teshome’,’STD/1234/02’,’Addis Ababa’);

Version Two:

INSERT INTO tbl_student (Name, Id_Number, City)

VALUES(‘Aster Degu’, ‘STD/2345/02’,’Jimma’);

As you can see from the above two examples, we are adding two new records to tbl_student but using two different versions of the INSERT INTO statement.

The first version always assumes to insert data to all columns of the table. Hence you need to pass a value to all columns of the table (full record value). You might have seen we have used 0 as the id value for the table. This column is defined as auto increment, so the value you pass will not be saved to the table. Rather, an automatically generated PK will be saved. All you need to do in such cases is to use a data value that has the same data type as the auto incremented value. In this case we are using int data type.

The second version of the INSERT INTO statement is used to specifically determine the columns you want to add data to. If you see the example again, we have left the id (auto incremented) value out and we have started from the ‘Name’ of the student table. Hence, data insertion will begin from the specified column name.

Therefore, you need to understand when to use the two different versions of the INSERT INTO statement. (N.B: You need to check if you have specified the columns as NOT NULL when creating them. In such cases, executing such an insert statement might generate an error.)

3. The UPDATE Statement.

This key word is one of the DML (Data Manipulation Languages). It strictly deals with the content of a table and not with the underlying table structure. This statement is used to manipulate the content of a table (data only). You should remember that UPDATE is only used to modify an existing record. If there is no record, update statement will NOT insert a new record. The syntax of the update statement is given below:

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

In the SET clause, you are expected to specify which column’s content you would like to modify in the WHERE clause, you are expected to specify the condition that will identify which records from the table to update. If you leave out the WHERE clause, all the records of the table will be updated regardless of any condition. Let us see an example here:

UPDATE tbl_student

SET Name = ‘Solomon Teka’

WHERE Id_Number = ‘STD/1234/02’

Here only a record with Id_Number = ‘STD/1234/02’ will be modified. That is, the name value will be replaced with the new data value ‘Solomon Teka’. Executing this statement will not have any effect to the rest of the records of tbl_student. But if you leave out the WHERE Id_Number = ‘STD/1234/02’ part, then all records of tbl_student will have same name. i.e, ‘Solomon Teka’.

4. The DELETE Statement.

This key word is one of the DML (Data Manipulation Languages). It strictly deals with the content of a table and not with the underlying table structure. The DELETE statement is used to delete rows in a table. The syntax of the delete statement is given below:

DELETE FROM table_name
WHERE some_column=some_value;

Just like the UPDATE statement, if you leave the WHERE clause, all the records of the specified table will be deleted. Let us see an example where we try to remove the record with Id_Number ‘STD/2345/02’ from tbl_student.

DELETE FROM tbl_student

WHERE Id_Number = ‘STD/2345/02’

Executing the above statement will remove the record with a student Id_Number ‘STD/2345/02’. Only this record is expected to be deleted. If there is no student record with the given Id_Number, then the delete will not affect the other records of the table. But if you omit the WHERE condition, all the records of the table will be deleted. So you need to always be extra careful when dealing with DELETE statements.

Software Developer's Blog: Learning SQL Everyday: Day 2: Introduction to the CREATE SQL statement: The CREATE statement is one of the DDL (Data Definition Language) in that it i...

Introduction to the CREATE SQL statement:

The CREATE statement is one of the DDL (Data Definition Language) in that it is used to define/create database objects like, database, table, user etc.

We will use the CREATE statement to do the following things:

• To create a database

• To create a table structure

• To create a database user

Today, We will see the how the CREATE statement is used to achieve above points in MySQL.

The CREATE DATABASE Statement:

Before creating a database, we need to understand what a database is. A database is a collection of related tables and serves as a repository. For instance you can think of a school as a database and the teachers, students, courses etc as a table. The interaction among the entity (objects) teacher, student and course etc. is what we call relationship among them. You know that a student takes a course and a course is tutored (given) by a teacher. So when you want to know who is teaching who? Who is taking what course etc, you have to trace the bits and pieces of information across each related table. Hence you will get full information when you combine the related records across each related tables. As you can see, the database contains a full information by keeping the data across related tables.

The CREATE DATABASE statement is used to create a database in a DBMS. As you we have discussed in Day 1, a DBMS can have a number of databases in it. So if you want to create a new database in your DBMS, you will have to use the CREATE DATABASE statement. The syntax is given as follows:

CREATE DATABASE dbname;

N.B: dbname is the name of your database. Remember that you need to end the statement with a semi-colon (;). This is important when you are writing statements in a MySQL console window.

Eg.

CREATE DATABASE db_school;

This statement will create a blank database (no tables in it) named db_school in your DBMS. Unless you select the newly created database and execute a CREATE TABLE statement, the database will contain no tables in it.

The CREATE TABLE Statement:

The CREATE TABLE Statement is used to create table in the selected database. This statement is also one of the DDL of SQL in that we use the CREATE TABLE statement to create a new database structure, in this case a TABLE.

A database table is a collection of rows/tuples. Each row represents a record/an entity we are persisting (saving) to the database. In turn, a row is a collection of fields/columns. Continuing from our first example, if you take the object/instance Student, you know that a student object should have name, id_number, sex, department etc.

Each attributes we have listed as name, id_number, sex etc. are known as fields/columns. When you actually merge this together, they will form a row/record. A record is one instance/object of the table Student in our example.

If you consider this example:

STUDENT

Name	Id_Number	Sex	Department
Selam Alemu	SC/2345/00	Female	Math
Abebe Kebede	SC/1234/00	Male	Physics

As you can see from the above table each column represents fields of the object student.

If you view horizontally (records/tuples) it represents a single student object.

It might be note worthy to remember this relationship:

Table = Class = File

Tuple/Record = Object

Column = Field/attribute

These points will be used when we do mapping later on in our journey.

The CREATE TABLE Syntax:

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

The column name parameters specify the names of the columns of the table.

The data_type parameter specifies what type of data the column can hold (e.g. varchar, integer, decimal, date, etc.).

The size parameter specifies the maximum length of the column of the table.

Example:

CRATE DATABASE db_school;

USE db_school;

CREATE TABLE tbl_student(

Name varchar(50) not null,

Id_Number varchar(20) not null,

Sex varchar(10) not null,

Department varchar(50) not null,

PRIMARY KEY(Id_Number)

);

Assuming you created the database db_school, you need to select the specific database you wanted the new table to reside upon. Remember we have said a DBMS can contain many databases. For selecting which database to use, we use the ‘USE’ statement as listed in the example.

The above SQL code creates a table named ‘tbl_student’. The table has four columns with their data type and size specified. For eg. You can see that name is given only 50 characters. If you try to give a name value exceeding this size, the data value will be truncated. The ‘Not Null’ value in each column shows that empty value /null value for that filed/column is not allowed and will have to always contain a data value. Finally we have added the PRIMARY KEY information in the table.

It is not a must that a table shall have a primary key. But it is always a good idea to have a primary key defined for your tables because that is the only way you can create relationship b/n tables. Of course you can have flat table (a table with no relationship at all).

So how do you determine which column of a table should be a PK (Primary key)? The following are the criteria you should check:

· The field/attribute MUST NOT be NULL

· It must be UNIQUE for a single record in the table

If your attribute (Candidate key) happens to fulfill the above two points, then it can be taken as a Primary key, and hence you let your DBMS know that this particular column is a primary key. To do that you can use the PRIMARY KEY statement as used in the example.

The CREATE USER Statement:

Since we are using MySQL DBMS, we will see how to create and grant a privilege to a user. By default MySQL has a user called ‘root’. This user has all the privilege so you can consider this as a super user.

Using this account, we can create our own users with different level of privilege.

The syntax for creating a user is:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

N.B:

‘newuser’ is the username that you want it to be. It should be a valid variable name.

‘password’ is the password of the new user you are just creating.

Sadly, at this point newuser has no permissions to do anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell.

Therefore, the first thing to do is to provide the user with access to the information they will need.

GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables in the DBMS.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

FLUSH PRIVILEGES;

How To Grant Different User Permissions

Here is a short list of other common possible permissions

· ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)

CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables
INSERT- allows them to insert rows into tables
SELECT- allows them to use the Select command to read through databases
UPDATE- allow them to update table rows
GRANT OPTION- allows them to grant or remove other users' privileges

To provide a specific user with a permission, you can use this syntax:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

If you want to give them access to any database or to any table, make sure to put an asterisk (*) in the place of the database name or table name and each time you update or change a permission be sure to use the Flush Privileges command to make the changes.
If you need to revoke permission, the structure is almost identical to granting it:

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘localhost’;

Just as you can delete databases with DROP, you can use DROP to delete a user altogether:

DROP USER ‘newuser@‘localhost’;

Software Developer's Blog: Tagging Pictures! Complete Example Explained with ...: Recently I have been getting many questions from my readers and former students asking me to develop and show how a picture tagging works ...

Recently I have been getting many questions from my readers and former students asking me to develop and show how a picture tagging works using jQuery and php. I have been using this functionality in one of my projects. Hence I decided to grab the codes and develop a quick app showing exactly how to tag a picture using jquery and php.

In this post, I will try to show how we can use jQuery and php to store tag values from a picture in to a database and show the tagged values to the page when the user hovers the mouse pointer to the picture that was tagged.

For this case, I have use the following:

1. php
2. mysql
3. jquery (you can grab the latest version from http://jquery.com/download/)
4. netbeans IDE (you can use any compatible text editor)

I will try to provide different screen captures to show how the whole project is structured and developed.

Project Structure:

As you can see from the above picture, I have categorized the different files under the appropriate folders.

In this project we have two classes and the class files are stored under a folder named "Classes".

classes folder:

A. DBConnection.php: This class is used to establish a database connection and handles also writing and reading data to and from the table.

B. NameTag.php: This class encapsulate the attributes that we are interested in saving to the database. This class is used to create instance of the NameTag class and then we can save data to the database table or read data from the table. This class in turn will communicate with DBConnection.php class to get its instances saved to the database or to get all name tag records saved for a particular image.

css folder:

This folder is dedicated to containing any css related files. In our case the 'tag_style.css' which is an external css file is defined under this folder.

db_script folder:

This folder is dedicated to store any .SQL file which you may have used in creating database, table and stored-procedures or other database related activities.

images folder:

This folder should contain any image that the web app/site is using. In our case, I have grabbed a random picture from the internet for tagging purpose.

js folder:

If you have any external .js (javascript) files you need to store them in a folder dedicated to hold all javascript files under such a folder. In this example, our jQuery library is saved under this folder.

The .php filers (index.php and savetag.php) are stored under the root folder. so if you type www.tagphoto.com then you will be redirected to www.tagphoto.com/index.php. You can modify this project structure as you wish. But you should always remember to organize your files under a self descriptive folders so when you come back later to maintain the application you don't waste a lot of time where each file is under.

Now let us step by step see how the name tagging could be developed.

STEP 1: Database And Table Creation:
The following picture shows the script I have used in creating the database and the table:

N.B: I have made the person_name and image id columns to be unique for every record in table tbl_name_tag. The reason for this is, to avoid any identical name tagging in the same picture. For example if you develop this application and try to give the same name for more than one person in the example image, it will not be saved. If you want to have name duplicates in the same image, just simply ignore the line # 11.

STEP 2: Create the DBConnection File Under 'classes' folder:

The content of this file is given as follows:

<?php
/**
* Description of DBConnection
*
* @author Mahder
*/
class DBConnection {
private static $DATABASE_NAME = 'db_photo_tagging';
/**
* This method is used to establish a connection to the database
* @return db connection
*/
public static function connect()
{
$server = "localhost";
$username = "root";
$password="root";
$connection = mysql_pconnect($server, $username, $password);
return $connection;
}

/**
* This method is used to write data to the database (do action)
* A wrapper method around the build in mysql_query() function.
* @param type $query : SQL values passed from the caller methods
* @return type: $result,
*/
public static function writeToDatabase($query)
{
$dbConnection = DBConnection::connect();
mysql_select_db(DBConnection::$DATABASE_NAME);
$result = mysql_query($query);
return $result;
}

/**
*
* @param type $query - Basically SQL SELECT statements from the caller
* methods. This method in turn will call the writeToDatabase method. I
* know the name does not make sense but basically we are using mysql_query...
* @return type
*/
public static function readFromDatabase($query)
{
$result = DBConnection::writeToDatabase($query);
return $result;
}

}//end class
?>

I have tried to add comments under each method. The only modification you need to do if you want to use this DBConnection class is, to just change the $username and $password values under the connect() method.

STEP 3: Create the Entity Class NameTag.php

This is the representation of the underlying database table tbl_name_tag. Using this class we can save or read what ever values are stored in this table. The content of this file is given below:

<?php

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

/**
* Description of NameTag
*
* @author Mahder
*/
require_once 'DBConnection.php';
class NameTag {
private $id;
private $personName;
private $imageId;
private $posTop;
private $posLeft;

function __construct($personName,$imageId, $posTop, $posLeft) {
$this->personName = $personName;
$this->imageId = $imageId;
$this->posTop = $posTop;
$this->posLeft = $posLeft;
}

public function getId() {
return $this->id;
}

public function setId($id) {
$this->id = $id;
}

public function getPersonName() {
return $this->personName;
}

public function setPersonName($personName) {
$this->personName = $personName;
}

public function getPosTop() {
return $this->posTop;
}

public function setPosTop($posTop) {
$this->posTop = $posTop;
}

public function getPosLeft() {
return $this->posLeft;
}

public function setPosLeft($posLeft) {
$this->posLeft = $posLeft;
}

public function getImageId() {
return $this->imageId;
}

public function setImageId($imageId) {
$this->imageId = $imageId;
}

public function saveNameTag(){
try{
$query = "insert into tbl_name_tag values(0,'$this->personName',
'$this->imageId',$this->posTop,$this->posLeft)";
DBConnection::writeToDatabase($query);
}catch(Exception $e){
$e->__toString();
}
}

public static function getAllNameTagsForImage($imageId){
$nameTags = null;
try{
$query = "select * from tbl_name_tag where image_id = '$imageId'";
$result = DBConnection::readFromDatabase($query);
if($result != null){
$nameTags = $result;
}
}catch(Exception $e){
$e->__toString();
}
return $nameTags;
}

}//end class

?>

STEP 4: Add the Appropriate Files Under Their Respective Folders.

As described under the project structure section, you are supposed to put the different files the project uses in the folder created for holding them. Please download the latest jquery library from the internet. You can use any picture you can get from the internet that can be used for tagging. e.g group of people, animals, objects etc.

The following picture shows the content of the CSS file I have used for this project.

STEP 5: Create the index.php and savetag.php Files

The two files are important and I will try to show the contents of each file here with.

<html>

<head>

</head>

<body>

<?php

require_once 'classes/NameTag.php';

//you can have more than one image in a page, so bring all the tagas

//for a particular image only.

$nameTags = NameTag::getAllNameTagsForImage('imgtag');

while($nameTagsRow = mysql_fetch_object($nameTags)){

$personName = $nameTagsRow->person_name;

$posTop = $nameTagsRow->pos_top;

$posLeft = $nameTagsRow->pos_left;

<?php

echo $personName;

</div>

<?php

}//end while...loop

<div>

</div>

</body>

</html>

$(document).ready(function(){

$('#imgtag').click(function(e){

//get the x and y coordinates of the click on the image

var posTop = e.clientY;

var posLeft = e.clientX;

//get the id value of the clicked image

var imageId = $(this).attr('id');

//store the imageId in the hidden text field component

$('#hiddenimageid').val(imageId);

//adjust the location (css attribute) of text field via jquery

$('#txtname').css('top',posTop-10).css('left',posLeft-20);

//after adjusting show and put the cursor inside the text field

$('#txtname').show().focus();

});//end imgtag click

$('#txtname').keyup(function(e){

//check if the pressed and release key is 'Enter' key

if(e.keyCode === 13){

//get the values of txtname content, top left point x and y coordinate

var personName = $(this).val();

var posTop = $(this).position().top;

var posLeft = $(this).position().left;

//contains the hidden image id of the image you are tagging

var imageId = $('#hiddenimageid').val();

var dataString = "personName="+personName+"&posTop="+

posTop+"&posLeft="+posLeft+"&imageId="+imageId;

//when enter key is released save data to database...

$.ajax({

type:'POST',

data:dataString,

url:'savetag.php',

success:function(data) {

//after saving refresh the page

location.reload();

}

});

}//end if

});//end keyup function

});//end document.ready

</script>

The content of savetag.php is shown below in the figure:

As you can see from the picture, this file gets the values sent from the jQuery.ajax call and stores them in a variable. ($personName,$posTop,$posLeft and $imageId).

Then using these variables, it creates an object from NameTag class and calls the saveNameTag() method to save the values to the database.

If you combine the above files in the order described in this post, you should be able to see the following out put window.

1. When you run the project, you will see the index page showing the sample picture

2. Try clicking on one person:

As you can see I have clicked on the first person (left extreme) and the text box is showing ready to take the name of the person. Try typing name value and press the enter key. You should get the next picture when you hover the mouse on the exact location you have tagged the person.

3. Types a Name on the Picture and After saving Moved mouse pointer to tag location:

This is how you can simple use jQuery and PHP to create your own tagging app. Feel free to customize the code in whatever way you would like to use. You might also want to modify this code if

1. You have more than one taggable image in a single page or

2. If the user uploads the picture and wants to tag the uploaded picture.

The basic is very similar and I am sure if you have made it this far, you can do your own imagination and creativity to this code.

I hope this will be of importance to some one out there. If you have any question or comment about this post, plz add your comments below or use my email mahderalem@gmail.com.

Thanks,

Mahder