MySql Store Procedure

Summary: in this tutorial, you will learn about MySQL stored procedures, their advantages and disadvantages.

Definition of stored procedures

A stored procedure is a segment of declarative SQL statements stored inside the database catalog. A stored procedure can be invoked by triggers, other stored procedures or applications such as Java, C#, PHP, etc.

A stored procedure that calls itself is known as a recursive stored procedure. Most database management system supports recursive stored procedures. However MySQL does not support it very well. You should check your version of MySQL database before implementing recursive stored procedures in MySQL.

Stored Procedures in MySQL

MySQL is known as the most popular open source RDBMS which is widely used by both community and enterprise. However, during the first decade of its existence, it did not support stored procedures, stored functions, triggers and events. Since MySQL version 5.0, those features were added to MySQL database engine to make it more flexible and powerful.

MySQL stored procedures advantages

• Typically stored procedures help increase the performance of the applications. Once created, stored procedures are compiled and stored in the database. However MySQL implements the stored procedures slightly different. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it to a cache. And MySQL maintains its own stored procedure cache for every single connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used, otherwise the stored procedure works like a query.
• Stored procedures helps reduce the traffic between application and database server because instead of sending multiple lengthy SQL statements, the application has to send only name and parameters of the stored procedure.
• Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so that developers don’t have to develop functions that are already supported in stored procedures.
• Stored procedures are secure. Database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permission on the underlying database tables.

Besides those advantages, stored procedures have their own disadvantages, which you should be aware of before using the store procedures.

MySQL stored procedures disadvantages

• If you use a lot of stored procedures, the memory usage of every connection that is using those stored procedures will increase substantially. In addition, if you overuse a large number of logical operations inside store procedures, the CPU usage will also increase because database server is not well-designed for logical operations.
• A constructs of stored procedures make it more difficult to develop stored procedures that have complicated business logic.
• It is difficult to debug stored procedures. Only few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.
• It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance phases.

MySQL stored procedures have their own advantages and disadvantages. When you develop applications, you should decide whether you should or should not use stored procedure based on the business requirements.

In the following tutorials, we will show you how to leverage MySQL stored procedures in your database programming tasks with many practical examples.

Abstract Classes and Interface in PHP

Abstract class and Interface in php play very important role in oop. In this section we will discuss following point

1. What is abstract classes.
2. What is interface
3. How to implement abstract classes in php
4. How to implement interface in php
5. Different between abstract classes and interface.

What is abstract Classes

As from name it seem like something that is hidden. Yes nature of the abstract classes are same. Abstract classes are those classes which can not be directly initialized. Or in other word we can say that you can not create object of abstract classes. Abstract classes always created for inheritance purpose. You can only inherit abstract class in your child class. Lots of people say that in abstract class at least your one method should be abstract. Abstract method are the method which is only defined but declared. This is not true definition as per my assumption. But your any class has at least one method abstract than your class is abstract class.

Usually abstract class are also known as base class. We call it base class because abstract class are not the class which is available directly for creating object. It can only act as parent class of any normal class. You can use abstract class in class hierarchy. Mean one abstract class can inherit another abstract class also.

Abstract classes in PHP

Abstract classes in php are simillar like other oop languages. You can create abstract classes in php using abstract keyword. Once you will make any class abstract in php you can not create object of that class.
abstract class abc
{
public function xyz()
{
return 1;
}
}
$a = new abc();//this will throw error in php
above code will throw error in php.

Abstract classes in php are only for inheriting in other class.
abstract class testParent
{
public function abc()
{
//body of your funciton
}
}
class testChild extends testParent
{
public function xyz()
{
//body of your function
}
}
$a = new testChild();
In above example you are creating of testChild Class. TestChild class is inheriting testParent abstract class. So your abstract class is only available for inheritance. Main motive of creating abstract classes in php is to apply restriction of direct initialization or object creation.

Implementation of abstract method

As we know that abstract functions are those functions of abstract class which is only defined. It will be declared in your child class. You can create any method abstract using keyword abstract. You can only create abstract method either in abstract class or interface. Following is example of the abstract method implementation:

abstract class abc
{
abstract protected function f1($a , $b);
}
class xyz extends abc
{
protected function f1($name , $address)
{
echo "$name , $address";
}
}
$a = new xyz();

In class abc we have defined an abstract function f1. Now when we have inherited class abc then declared function f1. If you have an abstract method in your abstract class then once you inherit your abstract class then it is necessary to declare your abstract method. If you will not declare your abstract method then PHP will throw error in that case.

You can declare your abstract method in child class with the same visibility or less restricted visibility.

abstract class parentTest
{
abstract protected function f1();
abstract public function f2();
//abstract private function f3(); //this will trhow error
}
class childTest
{
public function f1()
{
//body of your function
}
public function f2()
{
//body of your function
}
protected function f3()
{
//body of your function
}
}
$a = new childTest();

In above code you can see that you have declare 3 function in abstract class. But private declaration of the abstract method will always throw error. Because private method is availabe only in the same class context. But in case of f1. This is protected. Now in child class we have defined it as public because public is less restricted than protected. And for function f2 which is already public so we have defined it as public in our child class. We have defined it public because no any visibility is less restricted than public.

What is Interface ?

Interface in oop enforce definition of some set of method in the class. By implementing interface you are forcing any class to must declaring some specific set of methods in oop. For example if you are creating class to render HTML element then it is necessary to set id and name of your html tag. So in this case you will create interface for that class and define method like setID and setName. So whenever someone will create any class to render HTML tag and implemented your interface then he must need to define setId and setName method in their class. In other word you can say that by help of interface you can set some definition of your object. Interface is very useful if you are creating architecture of any oop base application. Inter

Interface in PHP

Interface in php can be implemented like other oop lanugage. You can create interface in php using keyword interface. By implementation of interface in php class you are specifying set of the method which classes must implement.

You can create interface in php using interface keyword. Rest of the things are typically identical to classes. Following is very small example of interface in php.
interface abc
{
public function xyz($b);
}
So in above code you are creating interface with name abc. Interface abc has function xyz. Whenever you will implement abc interface in your class then you have to create method with name xyz. If you will not create function xyz then it will throw error.

You can implement your interface in your class using implements keyword. Let us implement our interface abc in our class
class test implements abc
{
public function xyz($b)
{
//your function body
}
}

You can only define method in interface with public accessibility. If you will use other than public visibility in interface then it will throw error. Also while defining method in your interface do not use abstract keyword in your methods.

You can also extend interface like class. You can extend interface in php using extendskeyword.
interface template1
{
public function f1();
}
interface template2 extends template1
{
public function f2();
}
class abc implements template2
{
public function f1()
{
//Your function body
}
public function f2()
{
//your function body
}
}
So here template2 has all property of tempate2. So whenever you will implement template2 in your class, you have to create function of both interfaces.

You can also extend multiple interface in one interface in php.
interface template1
{
public function f1();
}
interface template2
{
public function f2();
}
interface template3 extends template1, template2
{
public function f3();
}
class test implements template3
{
public function f1()
{
//your function body
}
public function f2()
{
//your function body
}
public function f3()
{
//your function body
}
}

You can also implement more than one interface in php class.
interface template1
{
public function f1();
}
interface template2
{
public function f2();
}
class test implments template1, template2
{
public function f1()
{
//your function body
}
public function f2()
{
//your function body
}
}
You can not implement 2 interfaces if both share function with same name. It will throw error.

Your function parameter in class must be identical to the parameter in the interface signature. Following is example some example
interface template1
{
public function f1($a)
}
class test implements template1
{
public function f1($a)
{
echo $a;
}
}
Above will work. But following example will not work:
interface template1
{
public function f1($a)
}
class test implements template1
{
public function f1()
{
echo $a;
}
}
But it is not necessary to use the same name of the variable. Like $a. You can also use any name. For example:
interface template1
{
public function f1($a)
}
class test implements template1
{
public function f1($name)
{
echo $name;
}
}
If you are using default argument then you can change your value of the argument. For example
interface template1
{
public function f1($a = 20)
}
class test implements template1
{
public function f1($name  = "ankur")
{
echo $name;
}
}

In above section we have discussed interfaces and abstract classes in php. Both are almost doing same things but has some difference.

Differences between abstract class and interface in PHP

Following are some main difference between abstract classes and interface in php

1. In abstract classes this is not necessary that every method should be abstract. But in interface every method is abstract.
2. Multiple and multilevel both type of inheritance is possible in interface. But single and multilevel inheritance is possible in abstract classes.
3. Method of php interface must be public only. Method in abstract class in php could be public or protected both.
4. In abstract class you can define as well as declare methods. But in interface you can only defined your methods.

jQuery counter to count up to a target number

<span class=”timer”></span>
<hr/>
<span id=”help”>From: 50 – To: 2500 / Over 5000 Milli-Seconds</span>

(function($) {
$.fn.countTo = function(options) {
// merge the default plugin settings with the custom options
options = $.extend({}, $.fn.countTo.defaults, options || {});

// how many times to update the value, and how much to increment the value on each update
var loops = Math.ceil(options.speed / options.refreshInterval),
increment = (options.to – options.from) / loops;

return $(this).each(function() {
var _this = this,
loopCount = 0,
value = options.from,
interval = setInterval(updateTimer, options.refreshInterval);

function updateTimer() {
value += increment;
loopCount++;
$(_this).html(value.toFixed(options.decimals));

if (typeof(options.onUpdate) == ‘function’) {
options.onUpdate.call(_this, value);
}

if (loopCount >= loops) {
clearInterval(interval);
value = options.to;

if (typeof(options.onComplete) == ‘function’) {
options.onComplete.call(_this, value);
}
}
}
});
};

$.fn.countTo.defaults = {
from: 0,  // the number the element should start at
to: 100,  // the number the element should end at
speed: 1000,  // how long it should take to count between the target numbers
refreshInterval: 100,  // how often the element should be updated
decimals: 0,  // the number of decimal places to show
onUpdate: null,  // callback method for every time the element is updated,
onComplete: null,  // callback method for when the element finishes updating
};
})(jQuery);

jQuery(function($) {
$(‘.timer’).countTo({
from: 50,
to: 2500,
speed: 5000,
refreshInterval: 50,
onComplete: function(value) {
console.debug(this);
}
});
});

Performs a smooth page scroll to an anchor on the same page.

$(function() {
$(‘a[href*=#]:not([href=#])’).click(function() {
if (location.pathname.replace(/^\//,”) == this.pathname.replace(/^\//,”) && location.hostname == this.hostname) {
var target = $(this.hash);
target = target.length ? target : $(‘[name=’ + this.hash.slice(1) +’]’);
if (target.length) {
$(‘html,body’).animate({
scrollTop: target.offset().top
}, 1000);
return false;
}
}
});

});

Object Oriented Programming vs. Procedural Programming

Programming Paradigms

Programming is a creative process carried out by programmers to instruct a computer on how to do a task. A program is a set of instructions that tells a computer what to do in order to come up with a solution to a particular problem. There are a number of alternative approaches to the programming process, referred to asprogramming paradigms. Different paradigms represent fundamentally different approaches to building solutions to specific types of problems using programming. Most programming languages fall under one paradigm, but some languages have elements of multiple paradigms. Two of the most important programming paradigms are the procedural paradigm and the object-oriented paradigm. Let’s look at each of these in a bit more detail.

Procedural Programming

Procedural programming uses a list of instructions to tell the computer what to do step-by-step. Procedural programming relies on – you guessed it – procedures, also known as routines or subroutines. A procedure contains a series of computational steps to be carried out. Procedural programming is also referred to as imperative programming. Procedural programming languages are also known as top-down languages.

Procedural programming is intuitive in the sense that it is very similar to how you would expect a program to work. If you want a computer to do something, you should provide step-by-step instructions on how to do it. It is, therefore, no surprise that most of the early programming languages are all procedural. Examples of procedural languages include Fortran, COBOL and C, which have been around since the 1960s and 70s.

Object-Oriented Programming

Object-oriented programming, or OOP, is an approach to problem-solving where all computations are carried out using objects. An object is a component of a program that knows how to perform certain actions and how to interact with other elements of the program. Objects are the basic units of object-oriented programming. A simple example of an object would be a person. Logically, you would expect a person to have a name. This would be considered a property of the person. You would also expect a person to be able to do something, such as walking. This would be considered a method of the person.

A method in object-oriented programming is like a procedure in procedural programming. The key difference here is that the method is part of an object. In object-oriented programming, you organize your code by creating objects, and then you can give those objects properties and you can make them do certain things.

A key aspect of object-oriented programming is the use of classes. A class is a blueprint of an object. You can think of a class as a concept, and the object as the embodiment of that concept. So let’s say you want to use a person in your program. You want to be able to describe the person and have the person do something. A class called ‘person’ would provide a blueprint for what a person looks like and what a person can do. Examples of object-oriented languages include C#, Java, Perl and Python.

Key Differences

One of the most important characteristics of procedural programming is that it relies on procedures that operate on data – these are two separate concepts. In object-oriented programming, these two concepts are bundled into objects. This makes it possible to create more complicated behavior with less code. The use of objects also makes it possible to reuse code. Once you have created an object with more complex behavior, you can use it anywhere in your code.

REST Vs SOAP, The Difference Between Soap And Rest

Someone asked me a question today “Why would anyone choose SOAP (Simple Object Access Protocol) instead of REST (Representational State Transfer)?” My response: “The general rule of thumb I’ve always heard is ‘Unless you have a definitive reason to use SOAP use REST’”. He asked “what’s one reason?” I thought about it for a minute and honestly answered that I haven’t ever come across a reason. My background is building great internet companies.

While he seemed satisfied, I wasn’t very happy with that answer, I did some homework and here’s my summary on REST versus SOAP, the difference between SOAP and REST and why anyone would choose SOAP. As usual, with competing technologies both have value, the challenge is to know when to use each one (spoiler: luckily the answer is almost always REST).

I’m clearly boiling down a somewhat so please don’t flame me for simplifying things, but feel free to provide any corrections you feel are necessary.

Definitions

REST

RESTs sweet spot is when you are exposing a public API over the internet to handle CRUD operations on data. REST is focused on accessing named resources through a single consistent interface.

SOAP

SOAP brings it’s own protocol and focuses on exposing pieces of application logic (not data) as services. SOAP exposes operations. SOAP is focused on accessing named operations, each implement some business logic through different interfaces.

Though SOAP is commonly referred to as “web services” this is a misnomer. SOAP has very little if anything to do with the Web. REST provides true “Web services” based on URIs and HTTP.

By way of illustration here are few calls and their appropriate home with commentary.

getUser(User);

This is a rest operation as you are accessing a resource (data).

switchCategory(User, OldCategory, NewCategory)

This is a SOAP operation as you are performing an operation.

Yes, either could be done in either SOAP or REST. The purpose is to illustrate the conceptual difference.

Why REST?

Here are a few reasons why REST is almost always the right answer.

Since REST uses standard HTTP it is much simpler in just about ever way. Creating clients, developing APIs, the documentation is much easier to understand and there aren’t very many things that REST doesn’t do easier/better than SOAP.

REST permits many different data formats where as SOAP only permits XML. While this may seem like it adds complexity to REST because you need to handle multiple formats, in my experience it has actually been quite beneficial. JSON usually is a better fit for data and parses much faster. REST allows better support for browser clients due to it’s support for JSON.

REST has better performance and scalability. REST reads can be cached, SOAP based reads cannot be cached.

It’s a bad argument (by authority), but it’s worth mentioning that Yahoo uses REST for all their services including Flickr and del.ici.ous. Amazon and Ebay provide both though Amazon’s internal usage is nearly all REST source. Google used to provide only SOAP for all their services, but in 2006 they deprecated in favor of REST source. It’s interesting how there has been an internal battle between rest vs soap at amazon. For the most part REST dominates their architecture for web services.

Why SOAP?

Here are a few reasons you may want to use SOAP.

WS-Security

While SOAP supports SSL (just like REST) it also supports WS-Security which adds some enterprise security features. Supports identity through intermediaries, not just point to point (SSL). It also provides a standard implementation of data integrity and data privacy. Calling it “Enterprise” isn’t to say it’s more secure, it simply supports some security tools that typical internet services have no need for, in fact they are really only needed in a few “enterprise” scenarios.

WS-AtomicTransaction

Need ACID Transactions over a service, you’re going to need SOAP. While REST supports transactions, it isn’t as comprehensive and isn’t ACID compliant. Fortunately ACID transactions almost never make sense over the internet. REST is limited by HTTP itself which can’t provide two-phase commit across distributed transactional resources, but SOAP can. Internet apps generally don’t need this level of transactional reliability, enterprise apps sometimes do.

WS-ReliableMessaging

Rest doesn’t have a standard messaging system and expects clients to deal with communication failures by retrying. SOAP has successful/retry logic built in and provides end-to-end reliability even through SOAP intermediaries.

Summary

In Summary, SOAP is clearly useful, and important. For instance, if I was writing an iPhone application to interface with my bank I would definitely need to use SOAP. All three features above are required for banking transactions. For example, if I was transferring money from one account to the other, I would need to be certain that it completed. Retrying it could be catastrophic if it succeed the first time, but the response failed.

Get number of fields in mysql table

SELECT COUNT( * ) totalColumns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘tablename’
AND TABLE_SCHEMA = ‘databasename’

Main difference between InnoDB and MyISAM

The main differences between InnoDB and MyISAM (“with respect to designing a table or database” you asked about) are support for “referential integrity” and “transactions”.

If you need the database to enforce foreign key constraints, or you need the database to support transactions (i.e. changes made by two or more DML operations handled as single unit of work, with all of the changes either applied, or all the changes reverted) then you would choose the InnoDB engine, since these features are absent from the MyISAM engine.

Those are the two biggest differences. Another big difference is concurrency. With MyISAM, a DML statement will obtain an exclusive lock on the table, and while that lock is held, no other session can perform a SELECT or a DML operation on the table.

Those two specific engines you asked about (InnoDB and MyISAM) have different design goals. MySQL also has other storage engines, with their own design goals.

So, in choosing between InnoDB and MyISAM, the first step is in determining if you need the features provided by InnoDB. If not, then MyISAM is up for consideration.

A more detailed discussion of differences is rather impractical absent a more detailed discussion of the problem space… how the application will use the database, how many tables, size of the tables, the transaction load, volumes of select, insert, updates, concurrency requirements, replication features, etc.

cakephp 2.0 how to update auth data

I tried the following line. Its works well form me After modify the user data i written the following line

 $this->Session->write('Auth', $this->User->read(null, $this->Auth->User('id')));

How to paginate multiple results in CakePHP?

Here is my solution. In your controller :

function index(){
    // Your default model
    $this->set('model1', $this->paginate());
    // Pagination for model2
    $this->set('model2', $this->paginate('Model2'));
}

In your view :

// Display your model1 data, and then for prev and next 
echo $paginator->prev($options = array('model' => 'Model1'));
echo $paginator->next($options = array('model' => 'Model1'));
The point is input your model name to Controller's paginate method and to Paginator's link method (sort, prev, next).

// Display your model2 data, and then for prev and next 
echo $paginator->prev($options = array('model' => 'Model2'));
echo $paginator->next($options = array('model' => 'Model2'));