Advanced MySQL Features

June 9th, 2011 | Posted under Software, Web Hosting Tips | No Comments »

Advanced MySQL Features

ADVANCED MySQL FEATURES

MySQL is a powerful RDMS platform. The basic functions are more than sufficient to carry out most of the regular, day to day requirements of developing a suitable query for any purpose. However, MySQL also has certain advanced features, which are not usually utilized in basic, ordinary queries, but find application in certain occasions when the basic functions fail to provide the required functionality. Certain special or advanced feature of MySQL have been mentioned below.

My SQL 3 introduced the concept of search on text elements in a text field, with the FULTEXT index. The FULLTEXT indexing allows us to compare a certain text field, specifically in a particular column, to any required string which is mentioned in the query.
For example, the following code for CREATE-ing a Document table will be like this

CREATE TABLE Document(
url           VARCHAR(150) NOT NULL PRIMARY KEY,
title        VARCHAR(100) NOT NULL,
FULLTEXT(title)
);

A simple query like the following:

SELECT title FROM Document
WHERE MATCH(title) AGAINST (‘MySQL’);

This will be enough to search the whole title field for possible matches. However, it is to be taken care of that the title and any other field mentioned in the FULLTEXT statement should be structured according to the index, and not against the columns. The search is case insensitive. The results with most relevance are displayed, while those with very remote relevance are discarded.

Table locking is locking down a group of tables so that only a single or limited number of clients can gain access to it. Even though the type of table does not from a limiting factor, any action, once taken, cannot be rolled back. Locking of a table has two advantages- firstly, a group of statements can be executed for a group of tables, as one unit of work, and secondly, multiple updates can occur faster under certain conditions.

There are three kinds of locks supported by MySQL: read, write and read local. Both the read locks lock down a table, so that the clients can only view it, but cannot alter or add anything. Read local lock differs in the property that it allows a local client to execute non-conflicting INSERT statements, as long as there is no change to the MySQL files, from any external agents, that is, from outside MySQL. Write locks the specified table against all access- either read or write. However, it is very important to unlock the locks, as long lived data locks are supposed to reduce database performance.

Even though date functions are the most commonly used ones in MySQL, we can do a lot more with a little bit of modification to the query. For example, MySQL can be used to calculate the time  between two dates by specifying them is this format:

SELECT TO_DAYS(NOW( )) – TO_DAYS(‘2010-11-10’);

This query will calculate the number of days in the millennium that have already passed. The TO_DAYS() returns the number of days since 1 B.C.

String functions like LENGTH() are among the most commonly used commands in SQL. However, there are certain advanced string functions, like the SOUNDEX, which are rarely applied. The SOUNDEX() function essentially converts a word into its soundex representation. This is advantageous as it helps to find out if any one of the input strings have been misspelt, by comparing the two input strings.

Get the best of MySQL hosting  | MySQL web hosting only on Bounceweb hosting!


Basic Functions of MySQL

June 2nd, 2011 | Posted under Internet Trends, Software, Web Hosting Tips | No Comments »

Basic Functions of MySQL

Basic Functions of mySQL

SQL or Structured Query Language is a standardized query language, which is used to access information from a database. The request for information which is written in SQL is called a query. It was initially designed by the IBM in 1975s, and later introduced in the commercial market by Oracle Corporation. SQL utilizes distributed databases, which means that the databases are spread over several machines. That is why it is gradually gaining popularity among LAN network users. MySQL is a Relational Database Management System which is used to provide access to multiple databases for multiple users, as the situation requires.

Some basic functions of MySQL are,

Connect function

This is one of the most easiest and fundamental functions of my SQL. Here, the function is added, and the user data is stored in constants, so no variable needs to be specified while calling the function. An example code of the Connect function is provided below

function connectDb() {
// connect and set the working db
if (mysql_connect( DBHOST, DBUSER, DBPWD ) && mysql_select_db( DBNAME ))
return true;
else
return false;
}

Repair Table and Optimize Table

This command is used to scan all the tables which are present in a database, and then perform repair and optimization in each table. An example code is provided below for the Repair and Optimize Table function.

function fixTables($dbname) {
// search for all the tables of a db and run repair and optimize
// note: this can take a lot of time if you have big/many tables.
$result = mysql_list_tables($dbname) or die(mysql_error());
while ($row = mysql_fetch_row($result)) {
mysql_query(“REPAIR TABLE $row[0]”);
mysql_query(“OPTIMIZE TABLE $row[0]”);
}
}

Converting a Recordset to HTML Table

This code receives a recordset from a mysql_query output, and then prints it in the usual HTML format. Using css can improve the code functionality. A small example of this code is provided below.

function getHtmlTable($result)
{
// receive a record set and print it into an html table
$out = ‘<table>’;
for($i = 0; $i < mysql_num_fields($result); $i++){
$aux = mysql_field_name($result, $i);
$out .= “<th>”.$aux.”</th>”;
}
while ($linea = mysql_fetch_array($result, MYSQL_ASSOC)) {
$out .= “<tr>”;
foreach ($linea as $valor_col) $out .= ‘<td>’.$valor_col.'</td>’;
$out .= “</tr>”;
}
$out .= “</table>”;
return $out;
}

Returning the values of a row in a comma separated format

It helps the user to get all the values in the first row of the table, in a comma separated format. The code below is an example of the same:

function getCommaValues($sql) {
// execute a $sql query and return all the first value of the rows in a comma separated string
$out = “”;
$rs = mysql_query($sql) or die(mysql_error().$sql);
while($r=mysql_fetch_row($rs)) $out.=($out?”,”:””).$r[0];
return $out;
}

To get the first value after running a query

One of the most commonly used functions, this code, as explained by the topic header, helps to execute the code and then provides the first output of the executed code. We always need to extract a singular value or row, so this code often comes in handy.

function getScalar($sql,$def=””) {
// execute a $sql query and return the first value, or, if none, the $def value
$rs = mysql_query($sql) or die(mysql_error().$sql);
if (mysql_num_rows($rs)) {
$r = mysql_fetch_row($rs);
mysql_free_result($rs);
return $r[0];
}
return $def;
}

To get the first row after running the query

Once again, as the topic name explains, the ensuing code will help to get the first row after executing a code

function getRow($sql) {
// execute a $sql query and return the first row, or, if none, return an empty string
$rs = mysql_query($sql) or die(mysql_error().$sql);
if (mysql_num_rows($rs)) {
$r = mysql_fetch_array($rs);
mysql_free_result($rs);
return $r;
}
mysql_free_result($rs);
return “”;
}

To duplicate a specific row

This function is used to insert into any table, after duplicating any specified row, which is defined using the primaryID field and the values which have been specified in the parameters itself.

function duplicateRow($table,$primaryField,$primaryIDvalue) {
// duplicate one record in a table and return the id
$fields = getCommaFields($table,$primaryField);
$sql = “insert into $table ($fields) select $fields from $table where $primaryField='”.mysql_real_escape($primaryIDvalue).”‘ limit 0,1″;
mysql_query($sql) or die(mysql_error().$sql);
return mysql_insert_id();
}

These are some of the many fundamental functions provided by SQL for developing queries for databases.

Get the best of MySQL hosting  | MySQL web hosting only on Bounceweb hosting!


Importance of proper indexing for better searching in your database

March 25th, 2011 | Posted under Computer Advice, Web Design Tutorials | No Comments »

Importance of proper indexing for better searching in your database

Importance of proper indexing for better searching in your database

Indexing is a term used to describe a process involving the improvement of speed in data retrieval in a database table. Though indexing takes up a computer’s hardware resources, but it is advantageous when it comes to software performance, because any data retrieval is much faster.

Proper indexing has its benefits in the following applications.

Softwares using indexed information

Some sophisticated automatic softwares and algorithms use indexed database for different purposes like regularly updated websites, large public databases, research project databases etc. The advantage of these softwares is that they employ many intelligent methods to index for faster search response. The softwares also continuously make these methods efficient so that the indexing is done at a faster rate with more efficiency and consume less hardware resources.

Information users

With increasingly more content being available to the users people are using the internet and other online resources more frequently. Many people are busy checking out new information on particular subjects. Companies are also generating large volumes of information every day and without proper indexing these data are only as good as trash. If the data is not properly indexed then the data is not available to the numerous employees of the company. If the data is indexed, there are less chances of the data being missing or being overlooked by common users. With commercial indexing softwares, users are given a high degree of freedom to a huge databank of information in a very short duration.

Indexing for portal or intranet architecture

Many growing industries are backed up by a strong internet backbone. This also facilitates huge data storage and processing of those on real time. But to do real time searches within a intranet network for data retrieval, indexing is a very important requirement for efficiently carrying out the job. This has also made the overall information system very efficient and responsive.

Indexing also has its own advantages over other data retrieval techniques.

Other data retrieval methods like tables in MySQL are not as efficient or fast as indexing of same data. Though the data, that is being indexed is indexed one way and needs to be indexed again once the data is changed. In case of tables the data can be changed very easily.

Indexing takes up the least amount of space compared to all data retrieval systems. It is fast and also gives quick and easy modification capabilities. This in turn gives an efficient system when considering for a commercial use.

Types of Indexing

There are many types of indexing available like bitmap indexing, dense indexing, sparse indexing, reverse indexing etc, it is a tool to mainly locate data efficiently in a huge pool of arranged or may be raw information. After the data is located, then other techniques retrieve the data fast. In covering index, which is a special case, the index also contains the data fields, which make the overall system performance very fast. But it may take up more memory space due to its special fields to which it owes its characteristics.

Proper Indexing and Bounceweb helps your web applications in becoming faster!


Rapid web-application development with Ruby On Rails and MySQL

March 2nd, 2011 | Posted under The Internet, Web Design Tutorials, Web Hosting, Web Hosting Tips | No Comments »

Rapid web-application development with Ruby On Rails and MySQL

Rapid web-application development with RUBY ON RAILS and MySql

“Ruby on Rails” generally shortened as RoR is one if the best web application development framework available for the Ruby programming language. It is intended for use with “Agile Development Technology” which helps in creating rapid web application development. It was first created by a person named David Heinemeier Hansson in July 2004 under an open source license. From then onwards about six versions of this toolkit has been released.

Technical overview

A lot of recent developments have added many new packages and up-gradations. Major developments include,

Efficient built-in engine

The built-in engines helps reuse the application codes along with view paths and models. Like many other available frameworks RoR uses MVC (Model View Controller) architecture pattern to develop applications. The commonly used tools are now preinstalled in the package. WEBrick, a simple ruby web server is also given with the distribution. The extensive use of JavaScript libraries like Script.aculo.us and Prototype for Ajax makes the web framework very noteworthy. The most recent version of RoR also use a technique called Unobtrusive JavaScript. This functionality actually separates the main logical part of the application from the structure and other related parts of the application.

Modular Nature

Ruby runs using the Matz interpreter as its default but it has been criticised by many for issued on scalability. Merb now comes integrated with RoR, and it has given a new structure to the whole system with modular coding interface. Earlier the Ruby developers had to use very specific sub-frameworks to be developed on applications but now this is changed forever. The choice between the sub-frameworks likes ORM, jQuery, DataMapper or JavaScript can be made without hassle.

Default templates

Large number of default templates is included out-of-the –box. It eases developing the basic structure of the applications with customisations and configurations. When developing an application today we look forward to fast code generation because of a very fast changing web world. The whole package of Ruby on Rails along with MySql makes sure we follow their principles “Convention over Configuration” and “Don’t repeat yourself”. The first principle states that we have to just create the code for the unconventional things we are using in the web application, the code generation of the conventional things will be taken care of through templates. The second principle states that we do not have to repeat every detail or code of a newly included object. The parameters linked to it will automatically be configured to make it work.

Easy interface

The new “Rack server interface” and “Metal” helps the developer to write optimised code fast and easy. These interfaces allow very highly configurable “middlewares” applications to be integrated with “Ruby on Rails”

Easy integration with MySql

All important web applications have a database running behind the scenes, to manage all the huge data generated from the corporate servers. This is where MySql comes in handy. It is the best known database management application in the world running. From small to gigantic databases it can manage all of it with ease and also is licensed under open source software.

Each passing day more and more developers are shifting to this. It is one of the best available rapid program development environments for web designers.

Rapid web-application development with RUBY ON RAILS and MySql

Enjoy making ROR applications on Bounceweb hosting!


Scaffolding with Ruby on Rails

June 16th, 2010 | Posted under Software, Web Design Tutorials | No Comments »

Scaffolding with Ruby on Rails

Ruby on Rails (ROR) is famous with web developers for a million reasons – one of the biggest being the scaffolding feature, which lets you to create web applications based on the database within minutes! Here, we see how ROR and MySQL combine to create fast, clean and efficient web applications!

1. Create a ROR Application

An empty application has to be created first using the following command:

rails application_name

2. Create a database for the application

A database on which the scaffolding application will be based has to be created using MySQL basic commands:

create database db_name;

grant all priviledges on db_name.* to ‘user@localhost’ identified by ‘password’

3. Change the database settings of ROR

The database.yml file needs to be updated with the new database name so that the application can connect to the database.

4. Create the tables, model and controller

The table has to be created with the CREATE TABLE command of MySQL, and the ROR model and controller has to be also created by the following commands:

ruby script\generate model application_name

ruby script\generate controller application_name

5. Change the controller file to begin scaffolding

The application_name_controller.rb file could be located from the path app/controller and has to modified by adding the following line

class Application_nameController < ApplicationController

scaffold:application_name

end

This will lead to the automatic creation of the following interfaces: creating new entries, editing new entries, viewing existing entries and deleting existing entries!

BounceWeb provides great deals on ROR hosting!


What makes a good web hosting service?

June 9th, 2010 | Posted under Content Management Systems, Web Hosting, Web Hosting Tips | No Comments »

What makes a good web hosting service?

There are plenty of web hosting services available in the market in different packages. While some of them offer free service, others need you to pay fees for their web hosting service. However, one needs to zero-in on one of these service providers based on a few criteria. But, what criteria can decide a good hosting service from a bad one? Here are some of the points that a good host should fulfill

It should provide reliability and have maximum uptime

Uptime is the percentage of total hours the server is accessible in the Internet. It indicates the performance of the hosting server.

Compatibility with most web components

Scripting languages like PHP, Perl; databases like MySQL, mSQL and framework like Ruby-on-Rails must be supported by the hosting server.

Interface for managing the server and database

An easy-to-use interface that helps in accessing, modifying and viewing of server files and database, like cPanel should be provided.

Excellent customer-support

They should provide support to their customers 24 x 7, and be competent to handle all kinds of issues.

Support for content management systems (CMS)

Modern content management systems like Drupal and Joomla should be supported in order to facilitate users who are comfortable in using them.


BounceWeb provides web hosting service that meets all the above criteria.


Five reasons why you should go for Linux based web hosting!

June 6th, 2010 | Posted under Web Hosting, Web Hosting Tips | No Comments »

Five reasons why you should go for Linux based web hosting!

In most cases, the criteria for choosing a good web hosting service is based on the space it provides, the speed, uptime guarantee and number of mailboxes. However, what most of us miss is what operating system is used by the web hosting server. This decision generally differs from website to website, depending on the requirements. The most popular of them is however the Linux based web hosting, due to its reliability and flawless security!

Here, we give you five most irresistible reasons why you should opt for Linux based hosting

It’s a free and open source product

There is no licensing fees, or fears of copyright issues. You can download files, use them and modify them according to your requirements.

Linux is stable and secure

It is compact file system would never let you down and most Linux-based servers run without any server glitches.

It is compatible with most databases, scripting softwares

Web scripting languages like PHP, Python and Perl; and databases like MySQL, PostgreSQL and mSQL are supported by Linux.

Linux gives you unmatched flexibility and interoperability

There is always a possibility of communicating with servers that do not operate on Linux. Even in those cases, Linux throws up no issues.

It is extremely cost-effective

It is available free of cost, so are most of the application programs required for server maintenance. Therefore, the total cost of running a web hosting service is severely reduced.

BounceWeb provides extremely reliable Linux based Web hosting.


Simple steps to create a login form

June 6th, 2010 | Posted under Software, The Internet, Web Design Tutorials | No Comments »

Simple steps to create a login form

Most websites require visitors to register themselves and login time and again to access user-specific content. This is a great tool way to interact with the visitors and provide meaningful service to the customers. However, the login system is extremely easy to implement if it is created by using PHP and MySQL. Here are some easy steps through which you can create the login system

Connect to the database

The first step is to connect to the database that has the login information such as the username, password, etc. The following codes help in connecting to a database

$connection = mysql_connect (hostname, username, password)

mysql_select_db(database name);

Store the user provided data

The data provided by the user must be stored in the form of variables so that it can be checked with the database

$username = $_POST[‘uname’];

$password = $_POST[‘password’];

Check the data with the database

These stored variables would be now checked with the existing database. If there is a single match, then the user exists, and would be redirected to his specific page

$result = mysql_query(“SELECT * FROM table name WHERE username==’$username’ AND password==’$password’”);

if(mysql_num_rows($result)==1

{

//redirect to user page

}

else

{

echo (“Invalid login credentials!”);

}

Thus, one can easily let valid users in their specific website area and refuse access to the unknown ones!

BounceWeb provides fast, and reliable PHP-MySQL hosting.


Five Basic but Useful MySQL commands

May 27th, 2010 | Posted under Web Hosting, Web Hosting Tips | No Comments »

Five Basic but Useful MySQL commands

MySQL is the most popularly used database query and maintenance software. It has an excellent API and language bindings with most major languages – PHP, Python, C++, Ruby. Below are listed five very basic but useful SQL commands –

1. SELECT

This command is used in almost all interactive queries from a MySQL database, to request for tuples (rows). It is the most easy to use and basic command in MYSQL.

2. CREATE

This command creates a table with the desired name and attributes. If necessary, it can borrow fields from other tables.

3. INSERT

This command is used to insert new tuples (rows) in a table. It has 3 variations – INSERT … VALUES, INSERT … SETS, INSERT … SELECT. The first two statements insert explicitly mentioned values as tuples (rows), whereas the third is used to insert values from one table to another based on certain queries.

4. ALTER TABLE

This command is used to alter the structure of any table after it is created, for example, create, destroy or rename fields (columns), or change column attributes.

5. UPDATE

This SQL command is very commonly used, to update the tuples (rows) to some changed value based on certain queries.

BounceWeb provides great offers on MySQL hosting!


Five coding tips for PHP-MySQL hosting

May 18th, 2010 | Posted under Software, Web Hosting, Web Hosting Tips | No Comments »

Five coding tips for PHP-MySQL hosting

PHP and MySQL happen to be the favorite scripting-database combination for most websites. The popularity is due to the simplicity and effectiveness of PHP and the functionalities of MySQL. However, there are some tips and tricks which can help you perform better with your PHP hosting, and here we list the top five Do’s for you:

Use the quotes effectively

String variables should be quoted with single quotes, while there should not be any quotes around numeric data. This is effective while using them inside echo, which itself should be quoted with double quotes.

Enable error reporting

The php.ini file should be configured such that all errors, except for notices should be reported. This helps in proper debugging of the PHP script.

Use HTML and PHP alternatively, when required

Instead of using echo to write large sections of HTML, PHP codes can be temporarily closed to accommodate HTML and then PHP can be restarted.

Include include files for better effect

The include files are extremely useful, in case a fixed element, such as header, footer needs to be included in all pages. This saves the pain of editing all pages if any modification is required on the fixed part.

Use MySQL datatypes according to the need

Apart from INT, there are other similar datatypes like TINYINT, SMALLINT, which can server the purpose and takes less space than INT.

BounceWeb provides extremely fast and reliable PHP-MySQL hosting.


Previous Entries