Output database tables in CSV

For lattest updates please visit, http://web2students.com/web2blog/
Code to output all tables with data in CSV (comma seperated version) without using phpmyadmin.
<?php
define("DB","test_db");
define("LB","\n\r");
define("SEPERATOR","\t");
function init(){
    mysql_connect("localhost","root","");
    mysql_select_db(DB);
}

function get_tables(){
    $query = mysql_query("show tables");
    $tables = array();
    $col_name = "Tables_in_".DB;
    while($result=mysql_fetch_assoc($query)){
        $tables[] = $result[$col_name];
    }
    return ($tables);
}

init();

function table_header($table){   
    $out = "";
    $result = mysql_query("SHOW COLUMNS FROM $table");
    while($header = mysql_fetch_assoc($result)){
        $out .= $header['Field'].SEPERATOR;
    }
    $out = substr($out,0,strlen($out)-1);//remove last comma
    return($out);
}

function table_content($table){
    $out = "";
    $query = mysql_query("select * from $table");
    while($result=mysql_fetch_assoc($query)){
        $row = "";
        foreach($result as $column){
            $row .= $column.SEPERATOR;
        }
        $row .= substr($out,0,strlen($row)-1);//remove last comma
        $out .= $row.LB;
    }
    return($out);
}

$out = "";
$tables = get_tables();
foreach($tables as $table){
    $out .= "Data in table $table".LB;
    $out .= table_header($table);
    $out .= LB;
    $out .= table_content($table);
    $out .= LB;
}
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=output.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo $out;
?>

Learn .htaccess (URL-re-write)

For lattest updates please visit, http://web2students.com/web2blog/
.htaccess (hypertext access) file has only extension no name. So,if you can't create on windows, just download from internet and edit it. As there is no file name, it's sometimes invisible. So, on server, first change settings to view hidden files (if you have permission and ftp/ cpanel has this feature). It's directory-level configuration file supported by several web servers. Directory-level configuration means, it will effect files that are in that directory or in sub-directory, it can't effect outside that directory.
What we can do with .htaccess?

1. URL- rewrite - (like user visits, http://web2students.blogspot.in/file1.html  but really access, public_html/file2.html ). It is used for SEO purpose.
2. Error document (404 page) - we can re-direct to customized page when user visits, non existing page.
3. Blocking user by IP address.
4. Many more things.

We can write 1 command in 1 line in htaccess. We can write comment by placing # before any line. Like,
# this is comment

404 error- Following file will be loaded when 404 error will occur,

ErrorDocument 404 /error_handler.html

We can also handle other errors, ( 400, 401, 500, etc).

Blocking IP address, 

order allow,deny
deny from 11.11.1.1
deny from 22.22.2.2
allow from all

Explanation -  

allow and deny are directives. Directives means one line command. First we write that following section defines allowing or denying access to server by using line, order allow,deny then we write deny from ip address (it's used to deny access to that ip address). Last line used to allow all other.    Redirecting to other page,  
redirect /folder/oldfile.html /newpage.html [L,R=301]
Above line says to redirect from /folder/oldfile.html to /newpage.html . [L,R=301] , This means, if this match is found, it's last command. Server don't try to match rest of .htaccess file. And R=301 means to re-direct and sending 301 headers. There are 2 types of headers for re-direct. 301 and 302. 301 is used for,Moved Permanently response and 302 is used for Moved temprory response. Why we care about these headers?It's used by search engine. If document is moved permanent , search engine will update the url in it's database. Else, it will not update.

You need to read all with example to understand below,
RewriteCond %{REQUEST_FILENAME} !-f
Explanation - Above line has 2 parts. First, RewriteCond (re-write condition)- this is re-write continue directives. It tells if condition is met, then continue re-writing. Condition is %{REQUEST_FILENAME} !-f. it says, if requested file is not found !-f means if file is not found then apply re-write rule (that rule will be written below that)
RewriteCond %{REQUEST_FILENAME} !-d
Above tells, if directory is not found then continue re-writing.
Now, if you want to re-write like, http://shoppingsite/product.php?product_id=1 to http://shoppingsite/products/productname
You will get productname from database. There is 1 to 1 relation of product_id and productname. Else it will not run. Why change to such URL?First it's good to have url having name of product not id. Second, good for SEO. Third, easy to remember. More coming soon. Also read this, http://www.addedbytes.com/articles/for-beginners/url-rewriting-for-beginners/

Introduction to HTML5

Features of HTML5,

• There are many new elements like <article> , <canvas> , <footer> , <header> , <section> , <hgroup> , <nav> , <audio> , <video> , <datalist> , etc
• Removed elements,<acronym> , <applet> , <basefont> , <big> , <center>, <frame>, etc

• Added New Attributes even we can add custom attributes. Custom attributes starts with data- string  like ,
  

  <div data-customfield="value" data-language="html">  .... </div>

• Full CSS3 Support
• 2D/3D Graphics
• Local Storage
• Local SQL Database (we can run sql query in browser but that is no longer active.
• HTML5 is simplified.HTML5 is much more Semantic than html4.
  
• Many more...

HTML5 is different than HTML4. It is not XML (XML rules doesn't apply in HTML5 like, every element should have closing element). It's XHTML5 that is valid XML.HTML5 is in progress and final spec may be different. All browsers don't support HTML5 properly (specially old IE browsers). Final draft is expected in year 2022.
Lots talk about theory, now time to getting started.
Doctype,
<!DOCTYPE html>
Enconding (required for security reasons),
<meta charset="utf-8"> (charset=utf-8 , charset='utf-8' , charset="utf-8" all are allowed, follow whatever you prefer, you can write like older long way).
Sample HTML5 page,
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Web2 Students blog</title>
</head>
<body>
<p>Introduction to html5</p>
</body>
</html>
Adding external CSS and JS,
<link rel="stylesheet" href="styles.css" />
<script src="scripts.js"></script>

<article> - It is self-contained content. It can be like div but with special semantic. Like articles can be list of posts in blog, news items, forum posts, comments on posts, etc. So, individual article is like single entry of list of items. It can contain <h1> , <p>, (like div )etc.
<header> - it's in header of html page. Normally, it contains, title, logo, link to home page, and other links, etc. It can be in the article containing heading of the article.
<hgroup> - It used to group heading (h1-h6) elements. It is used inside header.
<nav> - It's used for navigation links.
<footer> - As the name suggest, it's used as footer. Like header, it can be used in multiple times (in section). While multiple times, header and footer will be header and footer part of that section (like, in article). Example,

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Web2 Students blog</title>
</head>
<body>

<nav>
<ul>
<li><a href="index.html">Home</a></li>
<li><a href="/about/">About</a></li>
<li><a href="/blog/">Blog</a></li>
</ul>
</nav> 

<article>
  <header> 
    <hgroup>
      <h1>Introduction to HTML5</h1>
      <h2>Example of HTML5</h2>
    </hgroup>
  </header>  
 <footer>
  <p>Published: <time datetime="2013-04-12T00:00:00-00:00" pubdate>April 12,2013 0:00 am EDT</time></p>
 </footer>

 </article>

</body>
</html>
More coming soon. 

Database Indexes

Mysql database has  InnoDB and MyISAM storage engines. Both of them have different features. Differences between are,

• MyISAM tables use table-level locking where as InnoDB implements standard row-level locking , which means InnoDB is better. A lock that prevents any other transaction from accessing a table. So, during select or insert query in MyISAM table, all others transaction on same table will not be executed. So, wastage of resources. But in InnoDB, only that row will be locked.
•  InnoDB have Clustered index and MyISAM has no clustered index. There are two types of Indexes (Clustered and non-clustered index.) A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Example, in book, there is index which tells page number direct to the content.Second way is to write page number of content as well as number of next index, and so on. So, MyISAM is not so efficient in way of time of selection but it saves space and time in insertion in database. As in InnoDB, all indexes are shuffled and re- arranged according to order. It helps in speed up searching. Down-side of InnoDBis, it required more space. 
• MyISAM supports full text search but InnoDB don't.
• InnoDB supports Foreign Key and Transaction Model. MyISAM doesn't support transation and doesn't support foreign key natively (what natively means?I will read and tell you later). More next time.

HTML5 exciting things

New exciting things about HTML5 and new technologies are,

• WebSocket is a web technology providing full-duplex communications channels over a single TCP connection. HTML is stateless. Really?Read websocket. Prior it was, server will communicate browser, browser will reply, and so on. One at a time. But websocket is two-way communication.
• You can run sql in Web-browser. But it's no longer active, see, http://www.w3.org/TR/webdatabase/
• WebRTC for real time communication (voice, video chat, etc)
• Features like high performance 2D and 3D graphics, offline asset storage, rich audio APIs, and socket-based networking let you create compelling modern games with a reach like never before.
• So on...

 WebSocket API
The WebSocket protocol was standardized by the IETF as RFC 6455 in 2011, and the WebSocket API for in Web IDL is being standardized by the W3C
Example,
We need to tell whether, connection is over ws or wss (like HTTP or HTTPS). Creating websocket object,
var ws = new WebSocket("ws://www.example.com");
more coming soon.

Code is for posting data to multiple sites using multi curl in php

 This code is for posting data to multiple sites using multi curl in php. Useful for looping and posting to multiple site.
<?php
class curl_post{
    function __construct(){
        $this->curl_urls = array();
        $this->ch = array();
        $this->num_url = 0;
        $this->output = array();
        $this->multi_curl = curl_multi_init();
        $this->basic_options = array(
                 //CURLOPT_HEADER => true,
                 CURLOPT_RETURNTRANSFER => 1,
                 //CURLOPT_VERBOSE => 1,
                 CURLOPT_USERAGENT => $_SERVER['HTTP_USER_AGENT'],
                 CURLOPT_SSL_VERIFYPEER => false
                );
    }
   
    function add_url($options){
        ++$this->num_url;
        $this->ch[$this->num_url] = curl_init();
        foreach($this->basic_options as $k => $v){
            $options[$k] = $v;
        }
        curl_setopt_array($this->ch[$this->num_url], $options);
        curl_multi_add_handle($this->multi_curl,$this->ch[$this->num_url]);
    }
   
    function exe_curl(){
        $active = null;
        do {
            curl_multi_exec($this->multi_curl,$active);
        } while($active > 0);
        foreach($this->ch as $ch){
            $this->output[] = curl_multi_getcontent($ch);
        }
    }
   
    function __destruct(){
        foreach($this->ch as $ch){
            curl_multi_remove_handle($this->multi_curl, $ch);
            curl_close($ch);
        }
        curl_multi_close($this->multi_curl);
    }
   
}

Example
/*$curl_obj = new curl_post();

$data = "var1=value1&postvar2=value2&postvar3=value3";

$options = array(CURLOPT_URL => 'url-location1', CURLOPT_POSTFIELDS => $data);

$curl_obj->add_url($options);

$options = array(CURLOPT_URL => 'url-location2', CURLOPT_POSTFIELDS => "testing=value&test2=value2");

$curl_obj->add_url($options);

$options = array(CURLOPT_URL => '');

$curl_obj->add_url($options);
$curl_obj->exe_curl();

print_r($curl_obj->output);*/

XUL Mozilla firefox programming

Mozilla Firefox is a free and open source web browser developed for Windows, OS X, Linux, and Android by Mozilla Foundation and its subsidiary, the Mozilla Corporation. Firefox uses the Gecko layout engine to render web pages, which implements current and anticipated web standards. Copied from wikipedia

 Have you worked with FireBug and other application that works over firefox. XUL (XML User Interface Language) is Mozilla's XML-based language for building user interfaces. Official site can be found at, https://developer.mozilla.org/en/docs/XUL

See this, http://getfirebug.com/ Firebug and other firefox's extension are developed in XUL language.
Firefox extensions are applications that add new functionality to Mozilla applications such as Firefox and Thunderbird. It is also worth noting that there are differences between the definition of extension and add-on. All extensions are add-ons, but add-ons can also be themes, plugins, or language packs. XUL uses, mixture of XML, HTML, JS, CSS, Resource Description Framework (RDF),  etc. So, if you are web-designer/developer you will feel at home. More coming soon.

XUL (pronounced "zool") is one of many technologies used for creating Mozilla-based products and extensions. It is only one part of the development landscape, but given that it's practically exclusive to Mozilla, it tends to be used to identify all Mozilla-related development.  Copied from https://developer.mozilla.org/en-US/docs/XUL/School_tutorial/Introduction (also official site of firefox for learning XUL). More coming soon.

Connecting to multiple databases without disconnecting to prior connected databases

Below is code to connect with multiple databases without disconnecting to prior connected databases. It needed some improvement, but I will do some other day.
class db_class{
    private static $db_link = array();
    private function __construct(){
        //creating object is not allowed
    }
    static function getConnect($type,$db_detail_array=""){
        if(empty(self::$db_link[$type])){
            self::$db_link[$type] = mysql_connect($db_detail_array['host_name'],$db_detail_array['user'],$db_detail_array['pass']);
            mysql_select_db($db_detail_array['db']);
        }
    }
       
    static function run_query($query,$type){
        self::getConnect($type);
        $db_query = mysql_query($query,self::$db_link[$type]);
        return($db_query) ;
    }
   
    static function fetch_array($query,$type){
        $db_query = self::run_query($query,$type);
        $results = array();
        while($fetch_arr=mysql_fetch_assoc($db_query)){
            $results[] = $fetch_arr;
        }
        return($results);
    }   
    static function num_rows($query,$type){
        $db_query = self::run_query($query,$type);
        $num_rows = mysql_num_rows($db_query);
        return($num_rows);
    }   
    static function disconnect($type){
        mysql_close(self::$db_link[$type]);
    }
}

Usage-
$db_detail_array['host_name'] = "host1";
$db_detail_array['user'] = "user1";
$db_detail_array['pass'] = "pass1";
$db_detail_array['db'] = "database1";
$i = 1;
$type1 = "connection".$i;//connection1
db_class::getConnect($type,$db_detail_array);
$query = "SELECT * FROM `table1`";
$db_arr1 = db_class::fetch_array($query,$type);
print_r($db_arr1);
$db_detail_array['host_name'] = "host2";
$db_detail_array['user'] = "user2";
$db_detail_array['pass'] = "pass2";
$db_detail_array['db'] = "database2";
$i = 2;
$type2 = "connection".$i;//connection2
db_class::getConnect($type,$db_detail_array);
$query = "SELECT * FROM `table2`";
$db_arr2 = db_class::fetch_array($query,$type);
print_r($db_arr2);

//back to first database,
$type = "connection1";//connection1 is same as above so no need to connect db_class::getConnect
$query = "select something from table";
$db_arr2 = db_class::fetch_array($query,$type);//directly run query.
You can connect to any database, switch back to previous database with connecting again and so on. And at end of script end all connections to save memory by,

db_class::disconnect($type1);
db_class::disconnect($type2);
That's all for now.  More coming soon.

MySql_* extension is deprecated.

 Learn  MySQLi or PDO_MySQL

1. Why learn?

• MySql_* extension is deprecated as of PHP 5.5.0, and will be removed in the future. 
• MySQLi or PDO_MySQL are better than MySql. You will agree with me, when you learn anyone of these. 
• If you like OO (object-oriented), then you will like these more.
• Full form of MySqLi is MySqLi (improved) .MySql Improved Extension

First I am covering MySqLi then I will cover PDO.

MySqLi has dual interface. Procedural and object-oriented interface. You can choose whatever you like. Exactly same functionality is provided in both interfaces but in different ways. 

 Procedural way example-

<?php

//New way

$mysqli = mysqli_connect($host, $user, $pass, $database); 
$res = mysqli_query($mysqli, "SELECT 'something' FROM table"); 
$row = mysqli_fetch_assoc($res);
echo $row['something'];

//Old way 
$mysql = mysql_connect($host, $user, $pass); 
mysql_select_db($database);
$res = mysql_query("SELECT 'something' FROM table", $mysql); 
$row = mysql_fetch_assoc($res);
echo $row['something']; 
?>




It is very similar to MySql except mysqli_connect in which we have to write database name also. So, we don't need mysql_select_db($database); statement line.

Object oriented way,

$mysqli = new mysqli($host,$user,$pass,$database);
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
} 
$res = $mysqli->query("SELECT 'something' FROM table"); 
$row = $res->fetch_assoc();
echo $row['something'];

We can even mix both but it's bad way so I am not going to tell that way.

Full list of MySqli function/class can be found at,

http://www.php.net/manual/en/mysqli.summary.php

Prepared Statements

  A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency. 

This is two step process,

First step,

<?php
$mysqli = new mysqli(......);
/* Prepared statement, stage 1: prepare */ 
if (!($stmt = $mysqli->prepare("INSERT INTO table(column) VALUES (?)"))) {
    //Prepare failed
}//use ?,? for multiple 
?>

Second Step, binding and execution, (? is replaced with real values) 

Example #2 Second stage: bind and execute

<?php 
/* Prepared statement, stage 2: bind and execute */
if (!$stmt->bind_param("column", 'value')) {
    //error happens
}

if (!$stmt->execute()) {
    //execution error.
}?>

Official docs for prepare can be found at, 

http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

More coming soon.