Posts

Showing posts from September, 2014

How to Install and Make First Laravel App on Windows

Image
I use Wamp on my Windows box for developing PHP based apps, I recently installed Laravel on my Windows box to test how to framework works and performs common tasks related to web development. I would keep posting good stuff I learned about Laravel in easy to follow steps for new developers of Laravel like me. The Steps I followed are following: Laravel used Composer for dependency management, so first I installed Composer for Windows. Like to download is: https://getcomposer.org/Composer-Setup.exe During the Composer installation, it would ask for the location of PHP.exe, so make sure you have installed PHP on your system before you install Composer. It would download a few things from internet, so wait with patience. It uses SSL, so make sure SSL module is enabled in your php.ini file, just remove the ";" before the like: "extension=php_openssl.dll". The php.ini file is located at your php's installation home folder. The installation would also set the composer

SQL Query To Append Data

If there exist some record already, and you need to append a string with an existing value of a field. You can use CONCAT function for it. Assume we have a table named students which has following record before we execute the query: mysql> select * from students; +----+----------------------+------------------+------------+ | id | name | email | phone | +----+----------------------+------------------+------------+ | 1 | Alice | alice@gmail.com | 5487845784 | | 2 | Bob Chris | bob@gmail.com | 879874578 | | 3 | Oracle | oracle@gmail.com | 548798453 | +----+----------------------+------------------+------------+ To concat a string with name Alice, record id 1, you can execute following query: update students set name = CONCAT(name, " Lewis") where id=1 After the query, the record would look like this: mysql> select * from students; +----+-------------+------------------+------------+ | id | na

SQL Query to Find Duplicates

You can find how many times a particular field is duplicated in whole table. Assume there exist a table  named students and we want to find how many times a name duplicates. There are following total number of records: mysql> select * from students; +----+-----------+---------------------+------------+ | id | name | email | phone | +----+-----------+---------------------+------------+ | 1 | Alice | alice@gmail.com | 5487845784 | | 2 | Bob Chris | bob@gmail.com | 879874578 | | 3 | Oracle | oracle@gmail.com | 548798453 | | 4 | Alice | alice2@example.com | 85698566 | | 5 | Alice | alice3@example.com | 987654789 | | 6 | Oracle | oracle2@example.com | 98746354 | +----+-----------+---------------------+------------+ 6 rows in set (0.00 sec) You can see, Alice and Oracle duplicates in name field, 3 and 2 times, respectively. You can use following SQL Query to fetch how many time any name duplicates: select name, count(*)

SQL Query To Change Value

If you need to change an existing field, first you need to identify the record to be updated. Usually using primary key is best way. For example if there is a table students with following columns: id, int(11), PRI, auto_increment phone, varchar(45) name, varchar(45) email, varchar(45) And assume there exist a few records: mysql> select * from students; +----+-----------+------------------+------------+ | id | name | email | phone | +----+-----------+------------------+------------+ | 1 | Alice | alice@gmail.com | 5487845784 | | 2 | Bob Chris | bob@gmail.com | 879874578 | | 3 | Oracle | oracle@gmail.com | 548798453 | +----+-----------+------------------+------------+ 3 rows in set (0.00 sec) Let, we want to update the email address of Bob, following query would be used to update the email address field: UPDATE students SET email="bob_2@gmail.com" WHERE id=2 where students is the table name. You can also update multiple field in a singl

SQL Query To Find the Second Highest Salary

Finding the maximum salary is very easy, you just need to select the field with max function: SELECT max(salary) FROM employees; Sometime you need to find the 2nd highest salary or 2nd highest number from a column. Assume following data exist in employees table: mysql> select * from employees; +----+--------+-------------------+--------+ | id | name | email | salary | +----+--------+-------------------+--------+ | 1 | Alice | alice@gmail.com | 10000 | | 2 | Bob | bob@gmail | 20000 | | 3 | Oracle | oracle@gmail.com | 30000 | | 4 | John | john@example.com | 15000 | | 5 | Chris | chris@example.com | 25000 | | 6 | Moris | moris@example.com | 35000 | +----+--------+-------------------+--------+ 6 rows in set (0.00 sec) If you need to select the 2nd highest salary, you can use the following query: SELECT max(salary) FROM employees WHERE salary < (SELECT max(salary) FROM employees);  Lets see the output, when query is executed against above

SQL Query To Update a Field

If you need to update an existing field, first you need to identify the record to be updated. Usually using primary key is best way. For example if there is a table students with following columns: id, int(11), PRI, auto_increment phone, varchar(45) name, varchar(45) email, varchar(45) And assume there exist a few records: id      name       email                              phone 1 Alice alice@gmail.com 548784578 2 Bob bob@gmail.com 879874578 3 Oracle oracle@gmail.com         548798453 Let, we want to update the email address of Bob, following query would be used to update the email address field: UPDATE students SET email="bob_2@gmail.com" WHERE id=2 where students is the table name. You can also update multiple field in a single query. For example to update name and email of Bob, you would use following query: UPDATE students SET email="bob@gmail.com", name="Bob Chris" WHERE id=2 In same way, you can update any number of field in a single q

SQL Query to CSV

The post assume the MySQL database, syntax may vary for other databases. Sometime we need to send the actual data of the table but in a format that is easy to read for the received. If we sent the database backup, the received must create a database to read the data. So for method to export that with technology or database neutral format is to export it as Command Separate Version aka CSV format. The default command produced the .txt file with <tab> as filed values splitter. Here it command: SELECT * from students INTO OUTFILE 'd://students_list.txt' But if you want to produce the file with CSV, you just need to pass a few parameters to instructor the software how to create the CSV file, lets first see the query: SELECT * from students INTO OUTFILE 'd://students_list2.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' See we have terminated fields with comma (,) an have enclosed the fields with doubel quotes (") so that if there exist a field

SQL Query to Backup a Database in MySQL

If often need to migrate our database from one system to anther. Or may need to backup a database so that later, in case of some failure, we can restore the backup copy. So we must know a quick command to backup a database. SQL query is not used to backup the databases. But there comes a utility name mysqldump , it is located the bin/ folder of your MySQL installation directory. If you have added the bin/ path in system's path variable, you can directly use the mysqldump command to make a backup of database. Here is the command, assume we want to take backup of university_db database. Open your command prompt and type following: mysqldump -uroot -p university_db > university_db_backup_file.sql The detail of the above command is as follows: mysqldump -> a utility ships with MySQL that is used to back databases -uroot  -> -u means "user", root means, the "root" user, so want to connect with database using root user. Only authorized users can take backup

SQL Query To Allow Nulls

Sometime, when we create the tables in database we do not allow null for a field value, but after some data is inserted into table, we realize the null should be be allowed to be inserted in a that field. In such case, we do not need to delete the table completely. We can alter the table to allow null values for a particular field. Assume there exist a table with id(int) and name(varchar) attribtues where name do not receive NULL values. Such table was created using SQL query: CREATE TABLE `university_db`.`students` (   `id` INT NOT NULL,   `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`)); Where NOT NULL indicates, name's value should not be null for any record. We can use below query to alter the table design such that it allows NULL to be inserted into the name field: ALTER TABLE `university_db`.`students`  CHANGE COLUMN `name` `name` VARCHAR(45) NULL DEFAULT NULL ; "NULL DEFAULT NULL" and end of the query means: NULL is allowed for field name and default value is also

SQL Query To Alter Column Size

Let a table exist named students that was created using following command: CREATE TABLE `students`(   `id` int(11) NOT NULL,   `name` varchar(45) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; After the table is created and some record are inserted, we realized the column size for name is small and we want to extend to 150 characters. Here is the query that you would use to alter column size: ALTER TABLE `university_db`.`students`  CHANGE COLUMN `name` `name` VARCHAR(150) NULL DEFAULT NULL; The null default null means, null is allowed and default value should also be null when no value is inserted while adding a record in the table.

SQL Query To Get Column Names in MySQL

When you install a new MySQL Database Server, some schemas are created by default. These databases or schemas are used to store meta data about other databases and the database server users. The meta data describes: Which database users can connect with database server Which database users can access, update or delete which schemas Which user can connect as an admin Who has the read access to databases What is the structure of different databases created on the database server, etc.  Even you can configure table and row level security for different users. So to query list of columns of a particular database's table, you need to query information_schema database which already exist in MySQL, the query is following: SELECT column_name   FROM  information_schema.columns  WHERE  table_schema = 'university_db' AND  table_name  =  'students'; Here UNIVERSITY_DB is the database name and STUDENTS is the table whose columns we want to fetch.  Make sure you are connected with

SQL Query to Print Table Structure

While developing different applications, sometime we need to get the detailed information about a table. For example, list of columns, their data types, whether the columns allows null value or not, is the column is primary key or not and what is the default value of each column. We can use describe keyword of SQL to fetch the structure of details of a table. The exact query is as follows: >describe students; where students is the name of your table of the database you are currently connected with.

SQL Query To List All Tables

Sometime we need to view all the tables in the database we are connected with. May be from a SQL Client or our PHP or Java progam. Following Query Can be Used to List All tables: >show tables; It would return the list of all the tables in the database you are connected with.

How a Single Computer Can Host and Serve Multiple Domains

Image
When I first learned about hosting, I was taught there is a unique IP address against each domain we purchase and this IP address points to the hosting server. For example, if we register example1.com, it would be configured such that its bound with, say 127.1.2.3 IP address. And a user can access the website with IP address too, for example using http://127.1.2.3/. As the IP addresses are difficult to remember, that is why we use domain names, which are common language words and easy to remember for humans. About server, we are taught that, a single server can host multiple domain. And we know that each domain is mapped to an IP address, and on internet, an IP address is unique. When we type the domain name into address bar of internet browser, the browser first fetch the hosting server IP address and then send the request to the computer identified by the IP address. Assume, if we are fetching the home page, it seems only send request to server home page contents is enough, assuming

What are Different Types of Hosting Available

There are three types of hosting available, I would explain very brief without getting too much technical. Shared Hosting In shared hosting, the server where you put your web application contents is shared between multiple users by the hosting provider. So this is restricted type of hosting, you are not allowed to up-grade or install different softwares on server (as it would effects other users’ applications). You hosting provider manage such things. They issue some credential to you (e.g. login name and a password to login) for putting website contents and to managing your own database, etc. It is cheapest type of hosting. So you should start from shared hosting and move to others as need be. Virtual Server Hosting You get full access to an operating system. You can start/shutdown it. Your operating system is not shared among others users. You can install/uninstall any programs e.g. MySQL, PHP, etc. But it is all virtual. In fact, a virtualization software is running on server that a

What is Hosting

The website you visit are first developed on a computer by a software engineer. If the web site, also called web application, is large, it is developed by a team of software engineers and then each team member contribution is packaged into one software and run on local computer/s inside the company. On local computer/s, it is well tested by the software engineers or quality assurance engineers. At this stage the website can not be accessed on internet because that local computer is not uniquely identified on the internet but only at the local network inside the company. So to make a website accessible by all users on internet, it must be put in some computer which is always ON and connected to the internet.  Hosting means placing web application on a server that is always connected to internet so that it can respond users’ requests. It includes putting everything that is required to run the website e.g. static contents (images, styles, scripts), dynamic contents (PHP files, for example