How to Install Apache, PHP, MySQL and PHPMyAdmin in Ubuntu 11.04

If you are a PHP based web developer, you need all the software running and configured properly. Here I am talking about installing them One by One in your Ubuntu Desktop. We are installing all the applications from terminal.

How to Open Terminal:
So, to fire up the terminal follow any of these steps:

1. If you are running Unity Desktop, click on the Ubuntu Logo at top left corner and type Terminal in the search application bar. Then click on the terminal icon.
2. If you are running GNome Desktop, click on Applications->Accessories->Terminal
3. For shortcut, you can also press Ctrl+Alt+T at once, to open the terminal.

How to install Apache:

1. Make sure you have the internet connection. To install apache execute the following command in the terminal:

1	sudo apt-get install apache2

It takes some time to download and install apache. After the setup completes, type http://localhost/ in your browser window to make sure apache is installed and running properly. If you see the page with It Works!, the setup of apache2 completes successfully.

How to Install PHP:

1. To install PHP 5, type following commands in the terminal one by one:

1 2	sudo apt-get install php5 sudo apt-get install libapache2-mod-php5

The first line installs PHP5 in the computer. The second one provides the PHP5 module for the Apache 2 webserver. If second one is not installed, then Apache cannot parse PHP codes in a web page.

2. After installing PHP5 and PHP module for apache, restart the apache with following code:

1	sudo /etc/init.d/apache2 restart

3. While restarting the apache server, if you see a warning as “Could not reliably determine the server’s fully qualified domain name, using 127.0.1.1 for ServerName”, then you can fix this by creating a file with the Server name. To do this type the following command in the terminal:

1	sudo gedit /etc/apache2/conf.d/fqdn

When the text editor opens, type “ServerName localhost” inside the file and click Save. Then close it. Now restart again with the above code and you will see that the warning message has gone.

4. Now, we have successfully installed php and apache web server. However, still we don’t know if PHP is successfully installed. To check this, create a file inside /var/www/ folder named test.php as:

1	sudo gedit /var/www/test.php

and write following code in it

1	<?php   phpinfo();  ?>

Save the file and type this in browser: http://localhost/test.php

If you see the various information about PHP and installed modules there, then we can confirm that Apache is parsing PHP codes. Hence the installation is successful up to this point.

How to Install MySQL:

1. To install MySQL Server in ubuntu, type following code in terminal window:

1	sudo apt-get install mysql-server libapache2-mod-auth-mysql php5-mysql

This will install latest mysql server and other necessary PHP modules for recognizing mysql functions from PHP code. While installing MySQL server, you may require to enter the password for MySQL root user.

How to Install PHPMyAdmin:

1. To Install PHPMyAdmin, type the following codes in the terminal:

1	sudo apt-get install phpmyadmin

While installing PHPMyAdmin, you may require to select the web server. In such case, tick the Apache2 Server and proceed with the installation. You may also require to input MySQL root user password during installation.

Once the installation completes, type this in your browser window to confirm the successful installation of PHPMyAdmin: http://localhost/phpmyadmin/index.php.

Now, you are finished. Your environment is setup and you can enjoy using all these applications. Next, you can install other applications that may be necessary such as Eclipse, GIMP etc.

Normalization Basics – 1NF 2NF and 3NF

Description of Normalization

Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.

What is an “inconsistent dependency”? While it is intuitive for a user to look in the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. The employee’s salary is related to, or dependent on, the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access because the path to find the data may be missing or broken.

There are a few rules for database normalization. Each rule is called a “normal form.” If the first rule is observed, the database is said to be in “first normal form.” If the first three rules are observed, the database is considered to be in “third normal form.” Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.

As with many formal rules and specifications, real world scenarios do not always allow for perfect compliance. In general, normalization requires additional tables and some customers find this cumbersome. If you decide to violate one of the first three rules of normalization, make sure that your application anticipates any problems that could occur, such as redundant data and inconsistent dependencies.

The following descriptions include examples.

First Normal Form

•	Eliminate repeating groups in individual tables.
•	Create a separate table for each set of related data.
•	Identify each set of related data with a primary key.

Do not use multiple fields in a single table to store similar data. For example, to track an inventory item that may come from two possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2.

What happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate table called Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key.

Second Normal Form

•	Create separate tables for sets of values that apply to multiple records.
•	Relate these tables with a foreign key.

Records should not depend on anything other than a table’s primary key (a compound key, if necessary). For example, consider a customer’s address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer’s address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.

Third Normal Form

•	Eliminate fields that do not depend on the key.

Values in a record that are not part of that record’s key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.

For example, in an Employee Recruitment table, a candidate’s university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key.

EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate all possible interfield dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records. In theory, normalization is worth pursing. However, many small tables may degrade performance or exceed open file and memory capacities.

It may be more feasible to apply third normal form only to data that changes frequently. If some dependent fields remain, design your application to require the user to verify all related fields when any one is changed.

Normalizing an Example Table

loadTOCNode(2, ‘moreinformation’); These steps demonstrate the process of normalizing a fictitious student table.

1.	Unnormalized table: Student# Advisor Adv-Room Class1 Class2 Class3 1022 Jones 412 101-07 143-01 159-02 4123 Smith 216 201-01 211-02 214-01
2.	First Normal Form: No Repeating Groups Tables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble. Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem is with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below: Student# Advisor Adv-Room Class# 1022 Jones 412 101-07 1022 Jones 412 143-01 1022 Jones 412 159-02 4123 Smith 216 201-01 4123 Smith 216 211-02 4123 Smith 216 214-01
3.	Second Normal Form: Eliminate Redundant Data Note the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form. The following two tables demonstrate second normal form: Students: Student# Advisor Adv-Room 1022 Jones 412 4123 Smith 216 Registration: Student# Class# 1022 101-07 1022 143-01 1022 159-02 4123 201-01 4123 211-02 4123 214-01
4.	In the last example, Adv-Room (the advisor’s office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below: Students: Student# Advisor 1022 Jones 4123 Smith Faculty: Name Room Dept Jones 412 42 Smith 216 42

Difference between mysql_connect() and mysql_pconnect()

mysql_connect() and mysql_pconnect() both are working for database connection but with little difference. In mysql_pconnect(), ‘p’ stands for persistance connection.

When we are using mysql_connect() function, every time it is opening and closing the database connection, depending on the request .

But in case of mysql_pconnect() function,
First, when connecting, the function would try to find a (persistent) connection that’s already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.
Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the connection will remain open for future use (mysql_close() will not close connection established by mysql_pconnect()).

mysql_pconncet() is useful when you have a lot of traffice on your site. At that time for every request it will not open a connection but will take it from the pool. This will increase the efficiency of your site. But for general use mysql_connect() is best.

I think this is a very imp concept in case of Database Connectivity.

MySQL interview questions

1. What is DDL, DML and DCL? – If you look at the large variety of SQL commands, they can be divided into three large subgroups. Data Definition Language deals with database schemas and descriptions of how the data should reside in the database, therefore language statements like CREATE TABLE or ALTER TABLE belong to DDL. DML deals with data manipulation, and therefore includes most common SQL statements such SELECT, INSERT, etc. Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

2. How do you get the number of rows affected by query? – SELECT COUNT (user_id) FROM users would only return the number of user_id’s.

3. If the value in the column is repeatable, how do you find out the unique values? – Use DISTINCT in the query, such as SELECT DISTINCT user_firstname FROM users; You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT user_firstname) FROM users;

4. How do you return the a hundred books starting from 25th? – SELECT book_title FROM books LIMIT 25, 100. The first number in LIMIT is the offset, the second is the number.

5. You wrote a search engine that should retrieve 10 results at a time, but at the same time you’d like to know how many rows there’re total. How do you display that to the user? – SELECT SQL_CALC_FOUND_ROWS page_title FROM web_pages LIMIT 1,10; SELECT FOUND_ROWS(); The second query (not that COUNT() is never used) will tell you how many results there’re total, so you can display a phrase “Found 13,450,600 results, displaying 1-10”. Note that FOUND_ROWS does not pay attention to the LIMITs you specified and always returns the total number of rows affected by query.

6. How would you write a query to select all teams that won either 2, 4, 6 or 8 games? – SELECT team_name FROM teams WHERE team_won IN (2, 4, 6, 8 )

7. How would you select all the users, whose phone number is null? – SELECT user_name FROM users WHERE ISNULL(user_phonenumber);

8. What does this query mean: SELECT user_name, user_isp FROM users LEFT JOIN isps USING (user_id) – It’s equivalent to saying SELECT user_name, user_isp FROM users LEFT JOIN isps WHERE users.user_id=isps.user_id

9. How do you find out which auto increment was assigned on the last insert? – SELECT LAST_INSERT_ID() will return the last value assigned by the auto_increment function. Note that you don’t have to specify the table name.

10. What does –i-am-a-dummy flag to do when starting MySQL? – Makes the MySQL engine refuse UPDATE and DELETE commands where the WHERE clause is not present.

11. On executing the DELETE statement I keep getting the error about foreign key constraint failing. What do I do? – What it means is that so of the data that you’re trying to delete is still alive in another table. Like if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still contains people enrolled at that university. Proper way to do it would be to delete the offending data first, and then delete the university in question. Quick way would involve running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done. If your foreign key was formulated with ON DELETE CASCADE, the data in dependent tables will be removed automatically.

12. When would you use ORDER BY in DELETE statement? – When you’re not deleting by row ID. Such as in DELETE FROM techinterviews_com_questions ORDER BY timestamp LIMIT 1. This will delete the most recently posted question in the table techinterviews_com_questions.

13. How can you see all indexes defined for a table? – SHOW INDEX FROM techinterviews_questions;

14. How would you change a column from VARCHAR(10) to VARCHAR(50)? – ALTER TABLE techinterviews_questions CHANGE techinterviews_content techinterviews_CONTENT VARCHAR(50).

15. How would you delete a column? – ALTER TABLE techinterviews_answers DROP answer_user_id.

16. How would you change a table to InnoDB? – ALTER TABLE techinterviews_questions ENGINE innodb;

17. When you create a table, and then run SHOW CREATE TABLE on it, you occasionally get different results than what you typed in. What does MySQL modify in your newly created tables? –
1. VARCHARs with length less than 4 become CHARs
2. CHARs with length more than 3 become VARCHARs.
3. NOT NULL gets added to the columns declared as PRIMARY KEYs
4. Default values such as NULL are specified for each column

18. How do I find out all databases starting with ‘tech’ to which I have access to? – SHOW DATABASES LIKE ‘tech%’;

19. How do you concatenate strings in MySQL? – CONCAT (string1, string2, string3)

20. How do you get a portion of a string? – SELECT SUBSTR(title, 1, 10) from techinterviews_questions;

21. What’s the difference between CHAR_LENGTH and LENGTH? – The first is, naturally, the character count. The second is byte count. For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

22. How do you convert a string to UTF-8? – SELECT (techinterviews_question USING utf8);

23. What do % and _ mean inside LIKE statement? – % corresponds to 0 or more characters, _ is exactly one character.

24. What does + mean in REGEXP? – At least one character. Appendix G. Regular Expressions from MySQL manual is worth perusing before the interview.

25. How do you get the month from a timestamp? – SELECT MONTH(techinterviews_timestamp) from techinterviews_questions;

26. How do you offload the time/date handling to MySQL? – SELECT DATE_FORMAT(techinterviews_timestamp, ‘%Y-%m-%d’) from techinterviews_questions; A similar TIME_FORMAT function deals with time.

27. How do you add three minutes to a date? – ADDDATE(techinterviews_publication_date, INTERVAL 3 MINUTE)

28. What’s the difference between Unix timestamps and MySQL timestamps? – Internally Unix timestamps are stored as 32-bit integers, while MySQL timestamps are stored in a similar manner, but represented in readable YYYY-MM-DD HH:MM:SS format.

29. How do you convert between Unix timestamps and MySQL timestamps? – UNIX_TIMESTAMP converts from MySQL timestamp to Unix timestamp, FROM_UNIXTIME converts from Unix timestamp to MySQL timestamp.

30. What are ENUMs used for in MySQL? – You can limit the possible values that go into the table. CREATE TABLE months (month ENUM ‘January’, ‘February’, ‘March’,…); INSERT months VALUES (’April’);

31. How are ENUMs and SETs represented internally? – As unique integers representing the powers of two, due to storage optimizations.

What’s the difference between LEFT, RIGHT, INNER, OUTER, JOIN?

What’s the difference between LEFT, RIGHT, INNER, OUTER, JOIN?

The difference is in the way tables are joined if there are no common records.
JOIN is same as INNER JOIN and means to only show records common to both tables. Whether the records are common is determined by the fields in join clause. For example:

FROM t1
JOIN t2 on t1.ID = t2.ID

means show only records where the same ID value exists in both tables.
LEFT JOIN is same as LEFT OUTER JOIN and means to show all records from left table (i.e. the one that precedes in SQL statement) regardless of the existance of matching records in the right table.
RIGHT JOIN is same as RIGHT OUTER JOIN and means opposite of LEFT JOIN, i.e. shows all records from the second (right) table and only matching records from first (left) table.

MySQL RIGHT JOIN

MySql Right Join

Here you find information about writing RIGHT JOINs (also referred to as RIGHT OUTER JOINs). This introduction into right joins includes a detailed description, syntax information and right outer join example statements. The Venn diagram on the left represents a result set that a statement with a right join produces. refer to the syntax examples below for an example.

Right Join syntax
First of all, some syntax examples for the impatient:

— right join with USING-clause
SELECT *
FROM RIGHT JOIN
USING(id)

— right join with ON-clause
SELECT *
FROM a RIGHT JOIN b
ON a.name = b.authorName

As you can see, a join condition can be written with the keyword ON or the keyword USING. The difference is that the ON keyword is used when each relationship column has a different name and USING when a column with the same name exists in both tables.
Reference table, left and right table?

When we join two tables, there is always a left and a right table (take a look at our syntax examples):

• The left table is listed on the left side of the OUTER JOIN keywords
• The right table is listed on the right side of the OUTER JOIN keywords

The outer join which is used decides which table is treated as the reference table. A left join treats the left- and a right join the right table as the reference table. Do you recognize the reference table in the syntax examples? Alright, fasten your seat belts. We’re ready to take off.
Right Outer Joins vs Inner joins

A right outer join is a specialized outer join. Like an inner join, an outer join combines (joins) matching rows that are stored in two different tables. In addition, an outer join also adds unmatched rows from a reference table to the result set. In case of a right outer join this means that when there is a row in the right table which can’t be combined with any row in the left table (according to the join condition), MySQL…

• takes all selected values from the right table
• combines them with the column names from the left table.
• sets the value of every column from the left table to NULL

This is the important difference, because an inner join is not able to select records from a reference table that have no related data in another.

MySQL LEFT JOIN

MySql Left Join

Here you find information about writing LEFT JOINs (also referred to as LEFT OUTER JOINs). This introduction into left joins includes a description, syntax information and example statements that use left outer joins. The Venn diagram on the left represents a result set that a statement with a left join produces. Please refer to the syntax examples below for an example. Links to additional information resources can be found at the end of this article.

Left Join syntax

First of all, some syntax examples for the impatient:

— left join with USING-clause
SELECT *
FROM LEFT JOIN
USING(id)

— left join with ON-clause
SELECT *
FROM a LEFT JOIN b
ON a.name = b.authorName

As you can see, a join condition can be written with the keyword ON or the keyword USING. The difference is that the ON keyword is used when each relationship column has a different name and USING when a column with the same name exists in both tables.

Reference table, left and right table?
When we join two tables, there is always a left and a right table (take a look at syntax examples):

• The left table is listed on the left side of the OUTER JOIN keywords
• The right table is listed on the right side of the OUTER JOIN keywords

The outer join which is used decides which table is treated as the reference table. A left join treats the left- and a right join the right table as the reference table. Do you recognize the reference table in the syntax examples?

Left Outer Joins vs Inner joins

A left outer join is a specialized outer join. Like an inner join, an outer join combines (joins) matching rows that are stored in two different tables. In addition, an outer join also adds unmatched rows from a reference table to the result set. In case of a left outer join this means that when there is a row in the left table which can’t be combined with any row in the right table (according to the join condition), MySQL…

• takes all selected values from the left table
• combines them with the column names from the right table.
• sets the value of every column from the right table to NULL

This is the important difference, because an inner join is not able to select records from a reference table that have no related data in another.

MySQL INNER JOIN With comma operator

Here you find information about writing inner joins with the comma operator. It’s the most basic way to combine (join) two tables. There is an alternative syntax that can be used, because in MySQL you can write inner joins in two different ways. Another popular way is it to use the INNER JOIN command or synonymous keywords like CROSS JOIN and JOIN.

Syntax

The following examples are equivalent to the INNER JOIN examples, to make it easy to compare them. The first example builds the Cartesian product of two tables: Every row in the left table is combined with every row in the right table. In ANSI SQL, this is called a cross join. MySQL however doesn’t distinguish between inner joins and cross joins.

— inner join without a condition: a cross join
SELECT *
FROM a, b

– inner join with WHERE-clause
SELECT *
FROM a, b
WHERE a. = b.

When you write inner joins using the comma operator, there is only one way to specify the join condition: with a WHERE-clause.

Basics

An inner join combines all matching rows from two related tables. Two rows match if they are related, for example because they share a common column. When you write an inner join with the comma operator, the join condition which reflects a relationship between tables is added as a WHERE clause. You can use inner joins to SELECT, UPDATE or DELETE data that is stored within MySQL tables. Here are two more examples which are equivalent to the INNER JOIN examples:

SELECT *
FROM tableA a, tableB b
WHERE a.someColumn = b.otherColumn

Further information
You have seen how to write inner joins with the comma operator. From My point of view, it’s “OK” to use this feature. However, using the alternative syntax makes it easier to read your joins. It’s considered as a good behavior to write statements where you can directly see if it’s an INNER JOIN or a CROSS JOIN (the Cartesian product of two tables).

MySQL INNER JOIN

My SQL Inner Join Example

This Article shows you how to write inner joins with the INNER JOIN keywords.

Please note: In MySQL the join keywords JOIN and CROSS JOIN are synonymous with INNER JOIN which means: All example statements found in this article work fine when you use JOIN or CROSS JOIN instead of INNER JOIN.

Syntax
Here are syntax examples for the impatient. A join condition can be specified in two different ways. Take a look at the following join examples:

— inner join with USING clause

SELECT *
FROM a INNER JOIN b
USING()

— inner join with ON clause

SELECT *
FROM a INNER JOIN b
ON a. = b.

Now we should take a closer look at inner joins and what they really do.

Basics

The most common join operation MySQL supports is an inner join. It identifies and combines only matching rows which are stored in two related tables. A join condition which indicates how the tables are related, is added with the keywords ON or USING :

ON is used when the relationship column has a different name
USING is used when the relationship column has the same name in both tables
Take a look at the examples:

— INNER JOIN with ON clause
SELECT *
FROM tableA a
INNER JOIN tableB b
ON  a.someColumn = b.otherColumn

— INNER JOIN with USING clause
SELECT *
FROM tableA a
INNER JOIN tableB b
USING(columnName)

Inner Join vs Cross Join

In MySQL, the keywords CROSS JOIN and INNER JOIN are synonymous. ANSI SQL defines a CROSS JOIN as a join without a condition which builds the Cartesian product of two tables. In that case, MySQL combines every row in the left table with every row in the right table and returns the result set.

— inner join without a condition: cross join
SELECT *
FROM CROSS JOIN

When you have to build the Cartesian product of two tables, use the CROSS JOIN keywords to indicate your intensions. It makes it easy to read your statement and of course, keeps your code more portable.

Inner Join vs Outer Join
The major dhifference between outer joins and inner joins is: an outer join is able to identify rows that were not matched by any row in the joined table.
More than one join
It’s also not uncommon to add more than one join to a single statement. There is no additional syntax required. You only have to write a second (a third, and so on) join:

— more than one inner join
SELECT *
FROM tableA a
INNER JOIN tableB b
ON a.someColumn = b.otherColumn
INNER JOIN tableC c
ON b.anotherColumn = c.nextColumn

How do you connect to multiple MySQL databases on a single webpage?

You can make multiple calls to mysql_connect(), but if the parameters are the same you need to pass true for the ‘$new_link’ (fourth) parameter, otherwise the same connection is reused.

so then you have

$dbh1 = mysql_connect($hostname, $username, $password);
$dbh2 = mysql_connect($hostname, $username, $password, true);

mysql_select_db(‘database1’, $dbh1);
mysql_select_db(‘database2’, $dbh2);

Then to query database 1, do

mysql_query(‘select * from tablename’, $dbh1);

and for database 2

mysql_query(‘select * from tablename’, $dbh2);

Alternatively, if the mysql user has access to both databases and they are on the same host (i.e. both DBs are accessible from the same MySQL connection) you could:

Example :

$migglecon = mysql_connect(“localhost”,”root”,”123″);
mysql_select_db(“thefarmyard”,$migglecon)or die(mysql_error());

$drupalcon= mysql_connect($localhost,$root,$123,true);
mysql_select_db(“miggle”,$drupalcon)or die(mysql_error());