Software Developer's Blog

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_nameADD 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_nameDROP 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_nameMODIFY 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_studentADD 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_studentALTER 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_studentDROP 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_nameVALUES (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.)

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:

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_nameWHERE 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.

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.

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:

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.

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.

/**
*
* @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.
*/

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.

//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.