Web Technology Experts Notes

Wednesday, 22 January 2014

Manage Cron Job with PHP - SSH2 Connection

Today, Cron play very vital role in our website. It reduce the lots of manual work like update the record, Caching the pages, Remove not-required data etc.

Following are use of Cron Job

Update Database records
Copy/Move data from one server to another server
Caching the data for enhance the website performance
Remove the Redundancy data
Remove the un-necessary data.

If there are too-many cron jobs in our web application that its hard to manage the cron jobs through crontabs. Also sometimes Compmay Owner/Manger wants to mange the cron jobs through the Admin Panel.

Here is sample code with use of which you can mange the cron jobs from PHP. Now you can add/update/delete the cron job from PHP.

        /** check ssh2 connection**/
        if (!function_exists("ssh2_connect")){
            die('ssh2 is not installed');
        }
        /** check ssh2 connection**/
        
        if (!($con = ssh2_connect("www.example.com", 22))) {
            echo "Domain Not exist";
        } else {
            // try to authenticate with username root, password secretpassword
            if (!ssh2_auth_password($con, "username", "password")) {
                echo "credentials is In-Correct";
            } else {
                // execute a command
                if (!($stream = ssh2_exec($con, "ls -al"))) {
                    echo "Execution Failed";
                } else {
                    /** Read output */
                    stream_set_blocking($stream, true);
                    $data = "";
                    while ($buf = fread($stream, 4096)) {
                        $data .= $buf;
                    }
                    fclose($stream);
                    
                    echo $data;
                     /** Read output */
                }
            }
        }

Friday, 17 January 2014

MySQL Interview Questions and Answers

Question: Can you give me an MySQL Self Join Example?

SELECT l1.city, COUNT(l1.city) AS cityview,  l2.link_count FROM LOGS AS l1 LEFT JOIN (
    SELECT city,COUNT(city) AS link_count FROM LOGS WHERE TYPE="city_blurb_click" GROUP BY city 
    ) AS l2 ON l1.city=l2.city WHERE AND l1.TYPE="low_rate" GROUP BY city ORDER BY cityview DESc

Question: What is DDL, DML and DCL ?
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 and Update etc.
Data Control Language includes commands such as GRANT, and mostly concerns with rights, permissions and other controls of the database system.

Question: 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.

Question: 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;

Question: 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.

Question: 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.

Question: 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)

Question: How would you select all the users, whose phone number is null?
SELECT user_name FROM users WHERE ISNULL(phonenumber);

Question: 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

Question: 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.

Question: 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.

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

Question: How can you see all indexes defined for a table?
SHOW INDEX FROM questions;

Question: How would you change a column from VARCHAR(10) to VARCHAR(50)?
ALTER TABLE questions CHANGE content CONTENT VARCHAR(50).

Question: How would you delete a column?

ALTER TABLE answers DROP answer_user_id.

Question: How do I get the current time zone of MySQL?

SELECT @@global.time_zone, @@session.time_zone

Monday, 13 January 2014

MySQL Functions - MySQL tutorial for beginners

Following are functions which is most command used in mysql

Least: Return Least value amont all

SELECT LEAST(4,3,8,-1,5);

Greatest: Return greatest value amont all

SELECT GREATEST(4,3,8,-1,5);

Interval(): takes a comparison value as its first argument. The remaining arguments should be a set of values in sorted order. INTERVAL() compares the first argument to the others and returns a value to indicate how many of them are less than or equal to it.

SELECT INTERVAL(2,1,2,3,4);

BETWEEN: The BETWEEN operator takes the two endpoint values of the range and returns true if a comparison value lies between them and The comparison is inclusive.

SELECT * FROM `products` WHERE id between 1 and 10

ISNULL(0): return true if value is NULL

select ISNULL(1)

Group By: The MySQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result. IN: This is a clause, which can be used along with any MySQL query to specify a condition.

BETWEEN: This is a clause, which can be used along with any MySQL query to specify a condition.

UNION: Use a UNION operation to combine multiple result sets into one.

COUNT: COUNT aggregate function is used to count the number of rows in a database table.

MAX: The MAX aggregate function allows us to select the highest (maximum) value for a certain column.

MIN: The MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

AVG: The AVG aggregate function selects the average value for certain table column.

SUM: The SUM aggregate function allows selecting the total for a numeric column.

SQRT: This is used to generate a square root of a given number.

RAND: This is used to generate a random number using MySQL command.

CONCAT: This is used to concatenate any string inside any MySQL command.

Switch Case in Mysql

CASE  case_expression
   WHEN when_expression_1 THEN execute_statement1
   WHEN when_expression_2 THEN execute_statement1 
   ELSE execute_statement
END CASE;

Example 1

set @variable='10';
select (case when (@variable = '10') then "Equal to 10" else "Not Equal to 10" end)

Example 2

set @variable=10;
select (case when (@variable <=10) then "Less Than Equal to 10" else "Greater Than 10" end)

Example 3

set @variable=10;
select (case
            when (@variable <10) then "Less Than Equal to 10"
            when (@variable =10) then "Equal to 10"
        else "Greater Than 10" end)

mysqlsubqueries examples Scalar Subqueries Row Subqueries Column Subqueries Table Subqueries

It is query very similar to Normal queries in databases like
It may return one column
it may return more column
It may return single rows
It may return multiple rows

OR
any of above combination.

SubQuries are those queries which are used within Normal Queries.

For Example

select * from users where user_id =(select user_id from profile where type='user');

Here queries used within bracket is known as sub queris

There are four types of sub-queries.
a. Scalar Subqueries: return single columan of single row

select * from users where user_id =(select user_id from profile where type='user' limit 1);

b. Row Subqueries: Return single row

select u1.* from users As u1 left join (select user_id,country from profile limit 1) as u2 on u2.user_id=u1.user_id and u2.country=u1.country

c. Column Subqueries: Return one column of one or more rows

select * from users where user_id in(select user_id from profile where type='user');

d. Table Subqueries: return one more col of one/more rows

select u1.* from users As u1 left join (select user_id,country from profile) as u2 on u2.user_id=u1.user_id and u2.country=u1.country

Thursday, 2 January 2014

Domain Name Server

DNS is simple it just changes IP Address to human readable.

How to get the Ip address from the website Name
http://whois.net/domain-name-ip-address/

array dns_get_record ( string $hostname [, int $type = DNS_ANY [, array &$authns [, array &$addtl ]]] )

Fetch DNS Resource Records associated with a hostname

Fetch DNS Resource Records associated with the given hostname.


<?php
$result = dns_get_record("php.net");print_r($result);?>

host The record in the DNS namespace to which the rest of the associated data refers. class dns_get_record() only returns Internet class records and as such this parameter will always return IN. type String containing the record type. Additional attributes will also be contained in the resulting array dependant on the value of type. See table below. ttl "Time To Live" remaining for this record. This will not equal the record's original ttl, but will rather equal the original ttl minus whatever length of time has passed since the authoritative name server was queried.