Sorting VARCHAR data in MySql

Here’s a quick tip at sorting VARCHAR type data in mysql database with values in a column.

With the default sort, it would look something like below:

mysql> SELECT column FROM table_name ORDER BY column; 

column
======
100
1000
10000
200
2000
20000
...

Now with “… ORDER BY column+0”, I get it sorted right:

mysql> SELECT column FROM table_name ORDER BY column+0; 

column
======
100
200
1000
2000
10000
20000
...

This is a quick fix instead of sorting to CAST operator.

Reference: Sorting VARCHAR data in mysql

Practical PHP Programming

Free PHP Programming Books

Practical PHP Programming | TuxRadar Linux.

PHP,Free source code for the taking. Over five million lines of programs.

PHP,Free source code for the taking. Over five million lines of programs..

Saving PHP’s Session data to a database

Introduction

As you should be aware the HTTP protocol, as used for serving web pages, is completely stateless. This means that after the server has received a request, processed it and sent a response, the process which dealt with that request dies. Anything that the process had in its memory therefore dies with it, so when a subsequent request is received from the same client it is unable to refer to its memory about anything that happened previously.

Fortunately PHP provides a standard method of maintaining memory (state) between requests in the form of Session Handling functions. This allows the programmer to maintain a set of variables in the $_SESSION array which is automatically saved to persistent storage at the end of each script, and then automatically loaded back into memory when a subsequent request is received from a client which supplies the same session_id.

By default the medium used as persistent storage by the session handler will be a series of disk files, one per session, where the file name is the session_id. A file is created when a new session starts, and is deleted when the session terminates (or has expired). This is perfectly adequate for most circumstances, but it has the following drawbacks:

If you are using a shared server then other users of that server may be able to access your session files, thus compromising the security of your site.
Each server will have its own directory where these session files are maintained, so if you are employing load balancing across multiple servers there is no guarantee that a request for an existing session will be given to the server which is maintaining the state for that session.
It would be difficult for a site administrator to perform such queries as “how many sessions are currently active?” or “which users are currently logged in?”
The authors of PHP have provided the ability to store session data using a method other than disk files by means of the session_set_save_handler function. This document will show how I have used this function to store all my session data in my application database.

Define database table

This is how I have defined the database table which will hold all my session data:

CREATE TABLE `php_session` (
`session_id` varchar(32) NOT NULL default ”,
`user_id` varchar(16) default NULL,
`date_created` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`last_updated` datetime NOT NULL default ‘0000-00-00 00:00:00’,
`session_data` longtext,
PRIMARY KEY (`session_id`),
KEY `last_updated` (`last_updated`)
) ENGINE=MyISAM
Please note the following:

session_id This is the identity of the session, so it must be the primary key.
session_data This must be big enough to hold the largest $_SESSION array that your application is liable to produce.
date_created This is used to identify when the session was started.
last_updated This is used to identify when the last request was processed for the session. This is also used in garbage collection to remove those sessions which have been inactive for a period of time.
user_id This is used to identify the person to whom this session belongs. The value is provided by the application logon screen.
Define database class

Within my development infrastructure it is my practice to use a separate class to access each database table. Each table class is actually a subclass to a generic table class which contains all the functionality which is standard across all database tables. This section identifies the contents of the subclass. A copy of the superclass is contained within the source code for my sample application.

The table class exists in a file called .class.inc which is described in A Data Dictionary for PHP Applications.

dirname = dirname(__file__);

$this->dbname = ‘audit’;
$this->tablename = ‘php_session’;

$this->fieldspec = $this->getFieldSpec_original();

// there is absolutely NO logging of the audit database
$this->audit_logging = false;

} // php_Session
The member variable $session_open is for use in the close() method (see below).

The constructor forces the member variable $audit_logging to be false as updates to this database table are not to appear in the audit log.

// ****************************************************************************
function open ($save_path, $session_name)
// open the session.
{
// do nothing
return TRUE;

} // open
The open() function does not have to do anything as the database is not actually opened until control is passed to my DML class.

// ****************************************************************************
function close ()
// close the session.
{
if (!empty($this->fieldarray)) {
// perform garbage collection
$result = $this->gc(ini_get(‘session.gc_maxlifetime’));
return $result;
} // if

return FALSE;

} // close
The close() function is responsible for calling the gc() function to perform garbage collection. Notice that it obtains its value from the session.gc_maxlifetime parameter in the php.ini file.

// ****************************************************************************
function read ($session_id)
// read any data for this session.
{
$fieldarray = $this->_dml_getData(“session_id='” .addslashes($session_id) .”‘”);

if (isset($fieldarray[0][‘session_data’])) {
$this->fieldarray = $fieldarray[0];
$this->fieldarray[‘session_data’] = ”;
return $fieldarray[0][‘session_data’];
} else {
return ”; // return an empty string
} // if

} // read
The read() function is responsible for retrieving the data for the specified session. Note that if there is no data it must return an empty string, not the value NULL.

// ****************************************************************************
function write ($session_id, $session_data)
// write session data to the database.
{
if (!empty($this->fieldarray)) {
if ($this->fieldarray[‘session_id’] != $session_id) {
// user is starting a new session with previous data
$this->fieldarray = array();
} // if
} // if

if (empty($this->fieldarray)) {
// create new record
$array[‘session_id’] = $session_id;
$array[‘date_created’] = getTimeStamp();
$array[‘last_updated’] = getTimeStamp();
$array[‘session_data’] = addslashes($session_data);
$this->_dml_insertRecord($array);
} else {
// update existing record
if (isset($_SESSION[‘logon_user_id’])) {
$array[‘user_id’] = $_SESSION[‘logon_user_id’];
} // if
$array[‘last_updated’] = getTimeStamp();
$array[‘session_data’] = addslashes($session_data);
$this->_dml_updateRecord($array, $this->fieldarray);
} // if

return TRUE;

} // write
The write() function is responsible for creating or updating the database with the session data which is passed to it.

// ****************************************************************************
function destroy ($session_id)
// destroy the specified session.
{
$fieldarray[‘session_id’] = $session_id;
$this->_dml_deleteRecord($fieldarray);

return TRUE;

} // destroy
If the session_destroy() function is issued in the code then this will be responsible for deleting the session data from the database.

// ****************************************************************************
function gc ($max_lifetime)
// perform garbage collection.
{
$real_now = date(‘Y-m-d H:i:s’);
$dt1 = strtotime(“$real_now -$max_lifetime seconds”);
$dt2 = date(‘Y-m-d H:i:s’, $dt1);

$count = $this->_dml_deleteSelection(“last_updated < ‘$dt2′”); return TRUE; } // gc This is the garbage collection or “clean-up” function. The value passed in $max_lifetime is a number of seconds. Any session record which has not been modified within this time limit will be eligible for deletion. Note that this does not mean that the session record will be deleted as soon as the time limit has expired as it depends on when the garbage collection routine is next run. With the method described in this article the garbage collection is only performed when a session closes, which means that a session can never delete its own data. If another session does not terminate then an ‘eligible for deletion’ session may continue to exist. If this is a problem then the gc() method must be called at the start of the read() method so that any expired data can be deleted before it is read. // **************************************************************************** function __destruct () // ensure session data is written out before classes are destroyed // (see http://bugs.php.net/bug.php?id=33772 for details) { @session_write_close(); } // __destruct // **************************************************************************** } // end class // **************************************************************************** ?>
This destructor method ensures that the session data is written to the database before the DAO (Data Access Object) disappears. See http://bugs.php.net/bug.php?id=33772 for details

Define session handler

This is as described in the manual for the session_set_save_handler() function. These lines must come before the session_start() function.

require_once ‘classes/php_session.class.inc’;
$session_class = new php_Session;
session_set_save_handler(array(&$session_class, ‘open’),
array(&$session_class, ‘close’),
array(&$session_class, ‘read’),
array(&$session_class, ‘write’),
array(&$session_class, ‘destroy’),
array(&$session_class, ‘gc’));
Notice that the arguments describe methods in a class and not stand-alone functions.

Conclusion

As you should be able to see it is a relatively straightforward process to switch the recording of session data from ordinary disk files to a database table. This overcomes the drawbacks inherent with ordinary disk files:

The session data is more secure as a potential hacker must be able to log into the database before he can access anything.
The use of multiple servers would not create a problem as all session data now resides in a single central place and is accessible by all servers.
It is much easier to query the database should the site administrator require information about current sessions or current users.

Source:

Saving PHP’s Session data to a database.

Creating a website in your local language

PHP & MySQL: Creating a website in your local language smoothly

Hello Developer,

You know English is the international language and accepted as international communication. So, most of the websites have been developed in English. But in many other times, a developer needs to work with local languages where they are developing a website in PHP. If you develop in local language, you need to know a small trick and that will show your content properly in all browsers smoothly.

So, what is the technique?

Well. Let me explain step by step.

Step One: An observation

I will show you the source code of two Bengali newspaper. In this newspaper: http://www.prothom-alo.com, the fonts and text comes properly in all browsers. But in this newspaper: http://www.ittefaq.com, it can show text and fonts properly only in Internet Explorer.

Now the matter is we must make sure our content will be displayed properly in any browser. Right?

OK. So what is the difference between the two websites I just gave example?

If you take a look at the source code of the first website’s content, you will find it is like this:&#2488;&#2691;&#2474;&#2494;&#2470;&#2453;

This is another website that had been developed as smooth site in the same technique: http://www.bdnews24.com/bangla. If you check its code, you will see that it uses same type of code for its text.

So, what are these &#2474;&#2494;&#2470;&#2453;&#2496;&#2527;things? It is very interesting that these are universal representation of local language in HTML entities. For every character of any local language, there is a unique and fixed symbol defined such as &#2474; in HTML entities. When you bring this kind of text in your browser source code, the site content looks smooth without any break and fonts displays properly.

Now if you open the source code of http://www.ittefaq.com, you will see something like AvR beg RvZxq msm` wbe©vP‡b jovB n‡e †RvU-gnv‡Rv‡Ui g‡a¨. The matter is they are also showing Bengali news content, but in a different way that is not useful in cross-broswer platform. This is often totally recognized by only Internet Explorer and often partially recognized by other browsers.

Step Two: Storing your local language content in the database

You see I have some content in my local language (Bangla) in the database.

Step Three: Converting your local language text in Universal code

Well. This is extremely easy.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
<?php

	class UnicodeHandler
	{
		var $dbLink;
		var $sqlQuery;
		var $dbResult;
		var $dbRow;
		var $salary;
		var $bonus;

		/* Use this constructor in case your PHP version is 4. */
/*
		function UnicodeHandler()
		{
			$this->dbLink = '';
			$this->sqlQuery = '';
			$this->dbResult = '';
			$this->dbRow = '';
			$this->mySalary = 0;
			$this->myBonus = 0;

			$this->dbLink = mysql_connect('localhost', 'root', '');
			mysql_query("SET character_set_results=utf8", $this->dbLink);
			mb_language('uni');
			mb_internal_encoding('UTF-8');

			mysql_select_db('test', $this->dbLink);
			mysql_query("set names 'utf8'",$this->dbLink);
		}
*/

		/* Use this constructor in case your PHP version is 5 or 5+. */
		/* I assume you are using PHP 5 or 5+. */
		function __construct()
		{
			$this->dbLink = '';
			$this->sqlQuery = '';
			$this->dbResult = '';
			$this->dbRow = '';
			$this->mySalary = 0;
			$this->myBonus = 0;

			$this->dbLink = mysql_connect('localhost', 'root', '');
			mysql_query("SET character_set_results=utf8", $this->dbLink);
			mb_language('uni');
			mb_internal_encoding('UTF-8');

			mysql_select_db('test', $this->dbLink);
			mysql_query("set names 'utf8'",$this->dbLink);
		}

		function displayLocalContent()
		{
			mysql_query("SET character_set_results=utf8", $this->dbLink);
			$this->sqlQuery = "SELECT * FROM unitext ";
			$this->dbResult = mysql_query($this->sqlQuery, $this->dbLink);
			while($this->dbRow = mysql_fetch_object($this->dbResult))
			{
				echo 'News: ' . $this->convertToLocalHtml($this->dbRow->news) . '<br />';
			}

		}

		function convertToLocalHtml($localHtmlEquivalent)
		{

			$localHtmlEquivalent = mb_convert_encoding($localHtmlEquivalent,"HTML-ENTITIES","UTF-8");
			return $localHtmlEquivalent;
		}
	}

?>

The displayLocalContent() function is going to display the news andconvertToLocalHtml() function converts my utf-8 content to HTML entities.

And here in index.php I am executing my displayLocalContent() function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Displaying Your Local Language</title>
</head>

<body>
<?php

	include_once 'class.unicode.php';
	$unicodeObject = new UnicodeHandler();
	$unicodeObject->displayLocalContent();

?>
</body>
</html>

As a result, the output is now as ঢাকা, ডিসে etc.

Step Four: You need to check my other posting for SELECT, INSERT & UPDATE your local language.

Here goes my other article so that you can perform all required operations for storing and displaying information in browser smoothly:

http://www.tanzilo.com/2008/10/13/php-mysql-unicode-solution-to-chinese-russian-or-any-language/

An alternative way

I have an alternative way in my mind and that is when you store the data in the database, you can first convert them to universal code such as&#2453;&#2496;&#2527;and then directly show your text.

This article will cover any language such as Afrikaans, Albanian, Amharic, Arabic, Armenian, Assamese, Aymara, Azeri, Belarusian, Bengali, Bislama, Bosnian, Bulgarian, Burmese, Catalan, Chinese, Mandarin,Croatian, Czech, Danish, Dari, Dhivehi, Dutch, Dzongkha, English, Esperanto, Estonian, Fijian, Filipino, Finnish, French, Frisian,  Gagauz, Georgian, German, Greek, Guaraní, Gujarati, Haitian Creole, Hebrew, Hindi, Hiri Motu, Hungarian, Icelandic, Indonesian, Italian, Japanese, Kannada, Kashmiri, Kazakh, Khmer, Korean, Kurdish, Kyrgyz, Lao, Latvian, Lithuanian, Luxembourgish, Macedonian, Malagasy, Malay, Malayalam, Maltese, Māori, Marathi, Mayan, Moldovan, Mongolian, Montenegrin,Náhuatl, Ndebele, Nepali, New Zealand Sign Language, Northern Sotho, Norwegian, Oriya, Papiamento, Pashto, Persian, Polish, Portuguese, Punjabi, Quechua, Romanian, Rhaeto-Romansh, Russian, Sanskrit, Serbian, Shona, Sindhi, Sinhala, Slovak, Slovene, Somali, Sotho, Spanish, Swahili, Swati, Swedish, Tagalog, Tajik, Tamil, Telugu, Tetum, Thai, Tok Pisin, Tsonga, Tswana, Turkish, Turkmen, Ukrainian, Urdu, Uzbek, Venda, Vietnamese, Welsh, Xhosa, Yiddish, Zulu etc.

You can also download this small piece of code from here:

http://www.tanzilo.com/demo/code/convert_to_unicode/convert_to_unicode.zip

PHP-Mysql Interview Questions

 Q:1 How can we submit a form without a submit button?

A:1  The main idea behind this is to use Java script submit() function in order to submit the form without explicitly clicking any submit button. You can attach the document.formname.submit() method to onclick, onchange events of different inputs and perform the form submission. you
can even built a timer function where you can automatically submit the form after xx seconds once the loading is done (can be seen in online test sites).

Q:2  In how many ways we can retrieve the data in the result set of MySQL using PHP?

A:2
You can do it by 4 Ways
1. mysql_fetch_row.

2. mysql_fetch_array

3. mysql_fetch_object

4. mysql_fetch_assoc

Q:3 What is the difference between mysql_fetch_object and mysql_fetch_array?

A:3
mysql_fetch_object() is similar tomysql_fetch_array(), with one difference – an object is returned, instead of an array. Indirectly, that means that you can only access the data by the field names, and not by their offsets (numbers are illegal property names).

Q:4 What is the difference between $message and $$message?
A:4
It is a classic example of PHP’s variable variables. take the following example.$message = “Mizan”;$$message = “is a moderator of PHPXperts.”;$message is a simple PHP variable that we are used to. But the $$message is not a very familiar face. It creates a variable name $mizan
with the value “is a moderator of PHPXperts.” assigned. break it like this${$message} => $mizanSometimes it is convenient to be able to have variable variable names. That is, a variable name which can be set and used dynamically.

Q:5 How can we extract string ‘abc.com ‘ from a string ‘http://info@abc.com’
using regular expression of PHP?

A:5 preg_match(”/^http:\/\/.+@(.+)$/”,’http://info@abc.com’,$found);

echo $found[1];

Q:6 How can we create a database using PHP and MySQL?

A:6 We can create MySQL database with the use of

mysql_create_db(“Database Name”)

Q:7 What are the differences between require and include, include_once and require_once?

A:7   The include() statement includes and evaluates the specified file.The documentation below also applies to require(). The two constructs are identical in every way except how they handlefailure. include() produces a Warning while require() results in a Fatal Error. In other words, use require() if you want a missingfile to halt processing of the page.

include() does not behave this way, the script will continue regardless.

The include_once() statement includes and evaluates the specified file during the execution of the script. This is a behavior similar to the include() statement, with the only differencebeing that if the code from a file has already been included, it will not be included again. As the name suggests, it will be included just once.include_once() should be used in cases where the same file might be included and evaluated more than once during a particularexecution of a script, and you want to be sure that it is included exactly once to avoid problems with function redefinitions, variable value reassignments, etc.
require_once()
should be used in cases where the same file might be included and evaluated more than once during a particular execution of a script, and you want to be sure that it is included exactly once to avoid problems with function redefinitions, variable value reassignments, etc.

Q:8 Can we use include (”abc.PHP”) two times in a PHP page “makeit.PHP”?
A:8 Yes we can use include() more than one time in any page though it is not a very good practice.

Q:9 What are the different tables present in MySQL, which type of table is generated when we are creating a table in the following syntax:
create table employee (eno int(2),ename varchar(10)) ?

A:9 Total 5 types of tables we can create
1. MyISAM
2. Heap
3. Merge
4. INNO DB
5. ISAM

MyISAM is the default storage engine as of MySQL 3.23 and as a result if we do not specify the table name explicitly it will be assigned to the default engine.

Q:10 How can we encrypt the username and password using PHP?
A:10 The functions in this section perform encryption and decryption, and compression and uncompression:
encryption decryption
AES_ENCRYT() AES_DECRYPT()

ENCODE() DECODE()

DES_ENCRYPT()   DES_DECRYPT()

ENCRYPT() Not available

MD5() Not available

OLD_PASSWORD() Not available

PASSWORD() Not available

SHA() or SHA1() Not available

Not available UNCOMPRESSED_LENGTH() 
 
 
Source: http://mycodings.blogspot.in/2008/07/php-mysql-interview-questions.html 

PHP-Mysql Interview Questions

Q:1 How can we submit a form without a submit button?

A:1 The main idea behind this is to use Java script submit() function in order to submit the form without explicitly clicking any submit button. You can attach the document.formname.submit() method to onclick, onchange events of different inputs and perform the form submission. you

can even built a timer function where you can automatically submit the form after xx seconds once the loading is done (can be seen in online test sites).

Q:2 In how many ways we can retrieve the data in the result set of MySQL using PHP?

A:2

You can do it by 4 Ways

1. mysql_fetch_row.

2. mysql_fetch_array

3. mysql_fetch_object

4. mysql_fetch_assoc

Q:3 What is the difference between mysql_fetch_object and mysql_fetch_array?

A:3

mysql_fetch_object() is similar tomysql_fetch_array(), with one difference – an object is returned, instead of an array. Indirectly, that means that you can only access the data by the field names, and not by their offsets (numbers are illegal property names).

Q:4 What is the difference between $message and $$message?

A:4

It is a classic example of PHP’s variable variables. take the following example.$message = “Mizan”;$$message = “is a moderator of PHPXperts.”;$message is a simple PHP variable that we are used to. But the $$message is not a very familiar face. It creates a variable name $mizan

with the value “is a moderator of PHPXperts.” assigned. break it like this${$message} => $mizanSometimes it is convenient to be able to have variable variable names. That is, a variable name which can be set and used dynamically.

Q:5 How can we extract string ‘abc.com ‘ from a string ‘http://info@abc.com’
using regular expression of PHP?

A:5 preg_match(”/^http:\/\/.+@(.+)$/”,’http://info@abc.com’,$found);

echo $found[1];

Q:6 How can we create a database using PHP and MySQL?

A:6 We can create MySQL database with the use of

mysql_create_db(“Database Name”)

Q:7 What are the differences between require and include, include_once and require_once?

A:7 The include() statement includes and evaluates the specified file.The documentation below also applies to require(). The two constructs are identical in every way except how they handlefailure. include() produces a Warning while require() results in a Fatal Error. In other words, use require() if you want a missingfile to halt processing of the page.

include() does not behave this way, the script will continue regardless.

The include_once() statement includes and evaluates the specified file during the execution of the script. This is a behavior similar to the include() statement, with the only differencebeing that if the code from a file has already been included, it will not be included again. As the name suggests, it will be included just once.include_once() should be used in cases where the same file might be included and evaluated more than once during a particularexecution of a script, and you want to be sure that it is included exactly once to avoid problems with function redefinitions, variable value reassignments, etc.
require_once() should be used in cases where the same file might be included and evaluated more than once during a particular execution of a script, and you want to be sure that it is included exactly once to avoid problems with function redefinitions, variable value reassignments, etc.

Q:8 Can we use include (”abc.PHP”) two times in a PHP page “makeit.PHP”?

A:8 Yes we can use include() more than one time in any page though it is not a very good practice.

Q:9 What are the different tables present in MySQL, which type of table is generated when we are creating a table in the following syntax:
create table employee (eno int(2),ename varchar(10)) ?

A:9 Total 5 types of tables we can create

1. MyISAM

2. Heap

3. Merge

4. INNO DB

5. ISAM

MyISAM is the default storage engine as of MySQL 3.23 and as a result if we do not specify the table name explicitly it will be assigned to the default engine.

Q:10 How can we encrypt the username and password using PHP?

A:10 The functions in this section perform encryption and decryption, and compression and uncompression:

encryption decryption

AES_ENCRYT() AES_DECRYPT()

ENCODE() DECODE()

DES_ENCRYPT() DES_DECRYPT()

ENCRYPT() Not available

MD5() Not available

OLD_PASSWORD() Not available

PASSWORD() Not available

SHA() or SHA1() Not available

Not available UNCOMPRESSED_LENGTH()

Source: