There is 3 ways to implement multiple languages website.

1)put english, malay, chinese hyperlink on each pages profile-malay.php, profile-english.php, profile-chinese.php. Goodness is simple coding. Two weakness : Weakness 1 is the url is long and ugly. Weakness 2 is hard to maintaince, let say there is the word "age" in 100 pages files, and u want change the "age" to "how old", u need to open 100 pages to change it, very wasting time.

2)profile.php page body content can be changed by using jquery call profile-malay.php, profile-english.php, profile-chinese.php. Goodness is url is short and beautiful. Two weakness : Weakness 1 is lot of jquery coding in begining, for example, if profile-malay.php has the link of edit-malay.php, u need to modify the edit-malay.php to edit.php, then in edit.php jquery call edit-malay.php. What if edit-malay.php have another link save-malay.php? Lot of modifications. Weakness 2 is hard to maintaince, let say there is the label"age" in 100 pages files, and u want change the "age" to "how old", u need to open 100 pages to change it, very wasting time.

3)change all html label into php variable, save all different languages labels into database. Examples, php variable $age for malay language table in database is "umur". Goodness is easy to maintance, when need to change "age" to "how old", no need open all pages, just need to edit the record in database. Goodness 2 don't need to use jquery call another page. No weakness.

Steps :
1) Left Join 2 tables in database to retrieve hidden setting_name and stores all of them to an php array.
2) Create full setting_name php array.
3) (left setting name) = (full setting_name) - (hidden setting_name).
4) Join elements of array with needed syntax for sql select query.
5) Join elements of array with needed syntax for header of html table.
6) Join elements of array of sql in while loop with needed syntax for displaying records to body of html table.

Codes :
$setting_name = array(); $sql = "SELECT setting_2.setting_name FROM setting_2 LEFT JOIN setting_2_of_user on setting_2.setting_id = setting_2_of_user.setting_id WHERE user_id=?"; $q = $conn->prepare($sql); $result = $q->execute(array($user_id3)); if(!$result){ die("Error Select setting_name join setting_2 table query : ". implode(" : ", $q->errorInfo())); } $i=0; while($r = $q->fetch(PDO::FETCH_ASSOC)){ $setting_name[$i] = $r['setting_name']; $i++; } $setting_name_full = array("courier", "tracking_number", "posting_date", "payment_date", "price", "product", "customer_name", "phone_number", "address", "bank", "order_from", "remarks"); $setting_name_left=array_diff($setting_name_full,$setting_name); $setting_name_left_with_sign = join(', ', $setting_name_left); $setting_name_left_for_table = join('</td><td> ', $setting_name_left); $sql = "SELECT " . $setting_name_left_with_sign . " FROM sale WHERE seller_id=?"; $q = $conn->prepare($sql); $result = $q->execute(array($user_id3)); if(!$result){ die("Error Select user table query : ". implode(" : ", $q->errorInfo())); //if don't use implode, output of errorInfo() will be "Array". } echo "<table id=\"tblContent1\" class=\"table-with-border\" style=\"text-align:center;\"> <tr><td>No</td><td>" . $setting_name_left_for_table . "</td></tr>"; $count = $q->rowCount(); if ($count == 0){ echo "<script type=\"text/javascript\"> document.getElementById('no-record-msg').style.display = 'inline'; </script>"; echo "<script type=\"text/javascript\"> document.getElementById('tblContent1').style.display = 'none';</script>"; } else { $no_count=0; while($r = $q->fetch(PDO::FETCH_ASSOC)){ foreach ($r as $name => $value) { if ($name == "price"){ $r[$name] = "usd" . $value; } } $gg = join('</td><td>', $r); echo "<tr><td>" . $no_count . "</td><td>" . $gg . "</td></tr>"; $no_count = $no_count + 1; } echo "</table>"; }

Open connection to database :
<?php //Database Constants $DB_SERVER = "localhost"; $DB_NAME = "2aekdb"; $DB_USER = "root"; $DB_PASS = "bbacc"; try{ $conn = new PDO("mysql:host=$DB_SERVER;dbname=$DB_NAME",$DB_USER,$DB_PASS); $conn->setAttribute(PDO::ATTR_PERSISTENT,true); $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING ); } catch(PDOException $pe){ die('Connection error, because: ' .$pe->getMessage()); } ?>

An example of Select query and show error message if fail :
<?php $sql = "SELECT * FROM traceuser ORDER BY id DESC LIMIT 300"; $q = $conn->prepare($sql); $result = $q->execute(); if(!$result){ die("Error Select traceuser table query : ". implode(" : ", $q->errorInfo())); //if don't use implode, output of errorInfo() will be "Array". } while($r = $q->fetch(PDO::FETCH_ASSOC)){ $id_db = $r['id']; $username_db = $r['username']; $ibrowser_db = $r['ibrowser']; $operatingsystem_db = $r['operatingsystem']; $ipaddress_db = $r['ipaddress']; $datetime_db = $r['datetime']; } ?> If you wrote $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); on database open connection previously, then you don't need to write implode(" : ", $q->errorInfo())); anymore.

An example of Select query with WHERE Clause :
$sql = "SELECT * FROM traceuser WHERE username=? ORDER BY id DESC LIMIT 300"; $q = $conn->prepare($sql); $q->execute(array("wwwwwwwwww"));

An example of Select query with two inputs in WHERE Clause :
$title = 'PHP%'; $author = 'Bobi%'; $sql = "SELECT * FROM books WHERE title like ? AND author like ? "; $q = $conn->prepare($sql); $q->execute(array($title,$author)); The question marks on codes above are called as "Position Placeholder".

Below is an example of using "Named Placeholder". The placeholder is not depends on position of value, it depends on name. That's why it called "Named Placeholders" : $title = 'PHP%'; $author = 'Bobi%'; $sql = "SELECT * FROM books WHERE title like :title AND author like :author "; $q = $conn->prepare($sql); $q->execute(array(':author'=>$author, ':title'=>$title));

Below is an example of using "OR" at "where" condition in select query and show error message : $sql = "SELECT message FROM sale WHERE seller_id=? AND payment_date=? AND bank IN (?, ?, ?)"; $q = $conn->prepare($sql); $result = $q->execute(array($user_id, $paydate, "maybank", "public", "cimb")); if(!$result){ die("Error on Select moving_buttons_of_user table query : ". implode(" : ", $q->errorInfo())); }

If you have a dynamic number of banks $sql = "SELECT message FROM sale WHERE seller_id=? AND payment_date=? "; $values = array($user_id, $paydate); if ($banks) { $sql .= " AND bank IN (" . implode(",",array_fill(0,count($banks),"?")) . ")"; $values = array_merge($values, array_values($banks)); } $q = $conn->prepare($sql); $result = $q->execute($values); if(!$result){ die("Error on Select moving_buttons_of_user table query : ". implode(" : ", $q->errorInfo())); }

Count Result (must use count(*) and $q->fetchColumn()) :
$sql = "SELECT count(*) FROM traceuser WHERE username=? ORDER BY id DESC LIMIT 300"; $q = $conn->prepare($sql); $q->execute(array("ddd")); $number_of_rows = $q->fetchColumn(); echo $number_of_rows; //if above code got error, then use below code : $count = $q->rowCount(); if ($count == 0){ echo "No record is found"; }

Check if sql query successful or fail :
$result = $q->execute(array("ddd")); //add a php variable $result to pdo execute if($result){ echo "<br /><br />sql query successful. "; }else{ echo "sql query fail. "; print_r ($q->errorInfo()) ; //or implode(" : ", $q->errorInfo())); }

First example of Insert query with PDO Prepared :
$sql = "INSERT INTO users (username, password, email) VALUES (?, ?, ?)"; $q = $conn->prepare($sql); $q->execute(array($username, $password, $email));

Second example of Insert query with PDO Prepared :
$sql = "INSERT INTO books (title,author) VALUES (:title,:author)"; $q = $conn->prepare($sql); $q->execute(array(':author'=>$author,':title'=>$title));

An example of Update query with PDO Prepared :
$title = 'PHP Pattern'; $author = 'Imanda'; $id = 3; $sql = "UPDATE books SET title=?, author=? WHERE id=?"; $q = $conn->prepare($sql); $q->execute(array($title,$author,$id));

Persistent connections are known as way to improve performance. When a persistent connection is requested, PHP checks if there's already an identical persistent connection from earlier remained open. If it exists, it uses it. What meaning of remained open? Persistent connection are links that do not close when the execution of your script ends. An 'identical' connection is a connection that was opened to the same host, with the same username and the same password. We can use PDO::ATTR_PERSISTENT to do persistent connection :
// database connection $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass, array(PDO::ATTR_PERSISTENT => true)); // query $sql = "SELECT * FROM books"; $q = $conn->query($sql) or die("ERROR: " . implode(":", $conn->errorInfo())); $r = $q->fetch(PDO::FETCH_ASSOC); print_r($r); echo $conn->getAttribute(PDO::ATTR_PERSISTENT); //true if output is 1, false if output is empty

Check connection attribute. Get value from connection attributes. We can use getAttribute(). Look this example :
echo $conn->getAttribute(PDO::ATTR_DRIVER_NAME); echo "<br>"; echo $conn->getAttribute(PDO::ATTR_CLIENT_VERSION); echo "<br>"; echo $conn->getAttribute(PDO::ATTR_SERVER_VERSION);
Output will be :
mysql
5.0.51a
5.1.32-community-log

get last id after insert >> $id = $conn->lastInsertId();

More tutorial at http://www.phpeveryday.com/articles/PDO-Error-Mode-Attributes-P558.html.

---

The different between two prepared statements :
1) fake / emulate pdo prepared statement (client-side prepared statement).
2) real / native pdo prepared statement (server-side prepared statement).

This SQL statement is wrong - "SELECT ice FROM artica AND antarctica" because spelling mistake of "artica", the correct spelling is "arctic".

fake / emulate pdo prepared statement will treat the sentence is right because its database doesn't require to check the statement.

real / native pdo prepared statement will check it and will know the sentence has error, and will treat the sentence is wrong.

For insert query, the real / native pdo / Server prepared statement is slower 10% than fake / emulate pdo / Client ps.
Client PS = 13.5645 seconds.
Server PS = 15.5019 seconds.
But if you use bindParam(), Server PS is 10% faster than Client PS. Eg,
$stmt = $db->prepare('INSERT INTO test(id, label) VALUES (?, ?)'); $stmt->bindParam(1, $j); $stmt->bindParam(2, $label); for ($j = 0; $j > $num_rows; $j++) { $stmt->execute(); } For select query, both client ps and server ps is the same speed but server ps become more faster if select more LARGE or MANY records.

The default setting use fake / emulate prepared statement. If you want to use real / native pdo prepared statement, u can use the code : setAttribute(PDO::ATTR_EMULATE_PREPARES,false); More info at : http://blog.ulf-wendel.de/?p=187#pdo.

echo should be d only one u use 2 show content to the page.
print is slightly slower.
var_dump() and print_r() should only be used 2 debug.

---

For print_r() and var_dump(), they will echo by default, you can add a second argument that evaluates to true to get it to return instead, e.g. print_r($array, TRUE).

For echo, We can't put 2nd argument on it.

The difference between echoing and returning are:
echo: Will immediately print the value to the output.
returning: Will return the function's output as a string. Useful for logging, etc.

---

echo() outputs one or more strings.
print_r() displays information about a variable in a way that's readable by humans. If given a string, integer or float, the value itself will be printed. If given an array, values will be presented in a format that shows keys and elements.

---

echo is faster speed than print, but :
‎$result = mysql_query($sql) or print mysql_error(); // valid, works
$result = mysql_query($sql) or echo mysql_error(); // not valid, won't works

---

echo is a statement
while print is a function
we can't print whole array by using echo or print. For this, we have to use the print_r function

$a=array("ford","benz","bmw","maruthi");
echo ($a);//output Array

$a=array("ford","benz","bmw","maruthi");
print_r ($a);//output Array ( [0] => ford [1] => benz [2] =>bmw [3] => maruthi )

$a=array("ford","benz","bmw","maruthi");
print ($a);//output Array

---

echo is a language construct.
print is a function.
print_r used to display the contents and structure of objects such as array.

---

Warning: Cannot modify header information - headers already sent happen if there is any output appear before Header. Below are 3 common factors that will cause the warning :

Factor 1 : There is spacing before or after php tag, the spacing will appear before header, so the warning will appears. For example :
<?php $servertime = date("Y-m-d H:i:s",time());?> <head> </head>
Solution : Delete the spacing before <?php

---

Factor 2 : The included file is above header, and the included file has html output. For example :
<?php include("includes/connection.php"); ?> <head> </head>
If connection.php has html output, then the warning will appears.
Solution : Move the included file code, put it below </head>.

---

Factor 3 : You set $_SESSION['username'] = $username; before header('Location: http://localhost/plekz/home.php');
Solution : You can add <?php ob_start();?> on the most top of page to buffer the output. When output buffering is active, no output is sent from the script (other than headers), instead the output is stored in an internal buffer. You can add <?php ob_flush(); ?> at the most bottom of page to clear away the contents of buffer.

Problem : Resubmitting form when click "f5" to reload the page. There are 3 different solutions :
Solution 1 :
1) page1.php does 3 different tasks :
Task 1 is show a textbox and submit button in a form if $_Get['success']=="true".
Task 2 is show errors message such as "name is used by other" or "multiple inputs too fast" if $_Get['success']=="false".
Task 3 is show the records from database if $_Get['success']=="".

2) page2.php does the data processing. Process the data after the user click submit button on page1.php. Then :
Task 1 is redirect the user to page1.php?success=true by code header('Location: page1.php?success=true'); if data process successful.
Task 2 is redirect the user to page1.php?success=false by the code of header('Location: page1.php?success=false'); if data process fail.

How it works?
1) User submit data by clicking the "submit button on page1.php,
2) Post method <form name="frm_register" action="page2.php" method="post"> jump to page2.php to process the data.
3) After page2.php finish process the data, it redirect user to page1.php?successful=true by header('Location: page1.php?successful=true'); (If you use header to redirect, the page will not remember anything happened previously, it will not remember page1.php did submit the form before). That's why it will not resubmit form when click "f5" to refresh the page1.php.

4) User submit data fail (because the name is used by other or because multiple input too fast), redirect to page1.php?success=false, and error message pop up.

5) If the user reload the page, it will still pop up the error message. It will not resubmit the form, because of this two reasons :
Reason 1 : The page doesn't show the form when $_Get['success']=="false".
Reason 2 : Just now you redirect to page1.php by header(), so page1.php will not remember the form is sent before.

6) If the user click "back" button, the page url will change the url back to page1.php?success=true which shows records and doesn't show the form. If click "back" button again, it will change the url back to page1.php which shows the form, but it will not resubmit the form because just now page2.php had redirected to page1.php by using header() which make page1.php forget everything in the past.

Note that header('Location: http://localhost/plekz/page1.php'); must has spacing after the sign of : and must have full path of url which has http://.

Weakness of solution 1 : If display records must depend of php if else statement, then you need to wrap the html codes of displaying records by php echo. It will be a lot of works to wrap all html in php echo.

---

Solution 2 :
1) page1.php does 2 different tasks :
Task 1 is show a textbox and submit button in a form if $_SESSION['error123']=="".
Task 2 is show error message if $_SESSION['error123']="samename".
Task 3 is process the data after the user click submit button :
- If process data successful, redirect user to page2.php to view records by code header('Location: page2.php');
- If process data fail because of same name or multiple input too fast, redirect use to page1.php?error123=samename by code header('Location: page1.php?error123=samename');

2) page2.php displays the records from database.

How it works?
1) User submit data by clicking the "submit button on page1.php,
2) Post method <form name="frm_register" action="page1.php" method="post"> jump to page1.php (same page) to process the data.
3) After finish process the data, it will use header() to redirect user to page2.php if no error, it will redirect user to page1.php if there is error.
If the user press "f5" button to reload the page or click "back" button, it will not resubmit the form because just now page1.php use header() to redirect this page, so this page won't remember everything in the past. So It don't remember the form is submitted before, so it will not resubmit the form when the user press "f5" button and "back" button.

Example of codes :
if ($_SESSION['error123'] == "toofast"){ $_SESSION['error123'] = ""; //clear the value of session echo "<script type='text/javascript'>alert('Your info FAIL to be saved for this time, because each visitor can save info for 1 time per minute.'); </script>"; }elseif ($_SESSION['error123'] == "samename"){ $_SESSION['error123'] = ""; echo "<script type='text/javascript'>alert('Your info FAIL to be saved for this time, because your name is the same as previous name.');</script>"; } if (strtotime($_SESSION['servertime']) < time()-60){ //10800 = 3 hours or 3600 = 1 hour or 60 = 1 minute if (($username != "") AND ($username != $_SESSION['username'])){ $_SESSION['username'] = $username; $query = "INSERT INTO traceuser (username) VALUES ('{$username}')"; $result = mysql_query($query20, $connection); $_SESSION['error123'] = ""; header('Location: http://localhost/plekz/page2.php'); exit; } else { $_SESSION['error123'] = "samename"; header('Location: http://localhost/plekz/page1.php'); exit; } }else{ $_SESSION['error123'] = "toofast"; header('Location: http://localhost/plekz/page1.php'); exit; } } }
If you want to redirect user to page2.php after user click "OK" button on javascript alert box, u can use this codes : echo "<script type='text/javascript'>alert('Your info FAIL to be saved for this time, because each visitor can save info for 1 time per minute.'); document.write('Loading....'); window.location='page2.php'; </script>";

Note that header('Location: http://localhost/plekz/page1.php'); must has spacing after the sign of : and must have full path of ur which has http://

---

Solution 3 : You can use onclick to call ajax function to submit the form. So it will not recall the funtion to resubmit the form until u click submit button again. Example of codes :
<form id="thisForm"> <textbox> <input type="button" onclick="return submitForm('thisForm')"/> </form> function submitForm(formId) { $.ajax( { type: "post", url: 'page2.php', data: $('#' + formId + ' input').serialize(), ... any other Ajax parameters..., success: function(data) { } }); return false; } Reference at http://stackoverflow.com/questions/7747569/prevent-resubmit-form-after-click-back-button/7747700

I have did a lot of research on making decision about using Varchar or Text. Here I list out all the importance points when choosing Varchar or Text :

- Varchar size can grow when records are updated frequently. Reference at here.

- Comparison can be done if using Varchar, eg :
select your_column from your_table
where your_column like '%dogs%'
- Comparison cannot be done if using TEXT unless the storage engine is MYISAM.

- Performance / speed of Varchar is faster than Text. Reference at here.

---

- Text column should be separated out to other table. When a table has TEXT or BLOB columns, the table can't be stored in memory. This means every query (which doesn't hit cache) has to access the file system - which is orders of magnitude slower than the memory.

Therefore you should store this TEXT column in a seperate table which is only accessed when you actually need it. This way the original table can be stored in memory and will be much faster.

Think of it as separating the data into one "memory table" and one "file table". The reason for doing this is to avoid accessing of the filesystem except when neccessary (i.e. only when you need the text).

You don't earn anything by storing the text in multiple tables. You still have to access the file system.

Note : some other people said better don't move out TEXT column to other table if u need to do many queries per second. Reference at here.

---

- 1 byte per character in latin1 encoding, but up to 3 in UTF8.
- Every table has a maximum row size of 65,535 bytes. utf8 characters require 3 or 4 bytes per character, so for a CHAR(255) CHARACTER SET utf8 column, the server must allocate 255 × 3 = 765 bytes per value. A table cannot contain more than 65,535 / 765 = 85 such columns. A VARCHAR(255) CHARACTER SET utf8 column takes 2 bytes to store the length of the value, so each value can take up to 767 bytes. NULL columns on MyISAM database require additional space in the row to record whether their values are NULL. Each NULL column takes one bit extra. Storage size of NULL and NOT NULL columns is the same size on InnoDB database. Reference at : here.

- utf8 characters require 3 or 4 bytes, So 3000-character varchar column can take up to 9000 bytes(can support 7 columns only) or 12000 bytes(can support 5 columns only).

- INDEXes are limited to 768 or 1000 bytes. INDEXes cannot have TEXT, but can have VARCHAR.
- max size of row is different between InnoDB and MyISAM tables.
- InnoDB stores at least 768 bytes of each BLOB/TEXT/VARCHAR column locally into the row itself. This means that you can in practice have only at most 10 long columns in the row.
- If max size of row is not enough to have too many columns on the table, u can split the columns into multiple tables. Reference at : here.

CHAR( )	A fixed section from 0 to 255 characters long.
VARCHAR( )	A variable section from 0 to 255 characters long. (depend on mysql version. 5.1 version varchar support up to 65500 characters long, reference at here.)
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	A string with a maximum length of 65535 characters. (TEXT occupies a number actual length of your data + 2 bytes. Text store external file and store pointer value to database.).
BLOB	A string with a maximum length of 65535 characters.
MEDIUMTEXT	A string with a maximum length of 16777215 characters.
MEDIUMBLOB	A string with a maximum length of 16777215 characters.
LONGTEXT	A string with a maximum length of 4294967295 characters.
LONGBLOB	A string with a maximum length of 4294967295 characters.

TINYINT( )	-128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT( )	-32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT( )	-8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT( )	-2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT( )	-9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
FLOAT	A small number with a floating decimal point.
DOUBLE( , )	A large number with a floating decimal point.
DECIMAL( , )	A DOUBLE stored as a string , allowing for a fixed decimal point.

DATE	-YYYY-MM-DD.
DATETIME	-YYYY-MM-DD HH:MM:SS.
TIMESTAMP	-YYYYMMDDHHMMSS.
TIME	-HH:MM:SS.

Reference at : here and here.

conclusion : If you are using mysql version 5.0 or above, and if you can ensure max size of row (65,535 bytes) is enough for all columns, then you can use varchar. For example, utf8 characters require 4 bytes, So 3000-character varchar column which need 12000 bytes per column. So the table can support 5 of such columns only.

Comparison between Char vs Varchar at here.

Convert datetime format by using php :
$originalDate = "2010-03-21";
$newDate = date("d-m-Y", strtotime($originalDate));

$servertime = date("Y-m-d H:i:sa",time());
if(strtotime($servertime) < time()-10800)
the if statement will fail because variable $servertime contain value of "am/pm", need to delete the "a" from "Y-m-d H:i:sa" will fix it.

ADDTIME ('zzz'); and TIMEDIFF ('zzz') fail because there is a space before (

To create a Foreign Key :
1) Convert all MySQL tables from MyISAM to InnoDB.
2) Set Index onto column which u want it to be foreign key.
3) Click Relation view > Choose parent column (primary key for other table) > Choose CASCADE on "ON DELETE" drop down list.

If you can't apply Foreign Key to a particular field, because of error #1216 or #1452 - Cannot add or update a child row: a foreign key constraint fails. Why? Because parent's table doesn't has the records that child's table has, so can't apply foreign key on the child's table. Fortunately, I've found some handy SQL queries using LEFT OUTER JOIN to clean child tables and easily delete unmatched records. As always, don't forget to BACK EVERYTHING UP before attempting these queries!! First, to find "wayward" records with no matching id in a corresponding parent table:
SELECT * FROM `agenda` LEFT OUTER JOIN meetings on
agenda.meeting_id=meetings.meeting_id WHERE
meetings.meeting_id is NULL;

DELETE agenda.* FROM `agenda` LEFT
OUTER JOIN `meetings` ON agenda.meeting_id =
meetings.meeting_id WHERE meetings.meeting_id IS NULL;

Reference at : http://www.mytechmusings.com/2008/04/using-foreign-keys-in-mysql.html and http://lists.mysql.com/mysql/212302

---

To add a Composite Primary Key :
You can use SQL ALTER TABLE myTable
ADD CONSTRAINT pk_myConstraint PRIMARY KEY (Column1,Column2)

Or use the interface, after the table is created, you can add an index (type 2 or 3 in textbox of columns). Then select columns to be the composite primary key, then choose "Primary Key" from Index Type drop down list.

Reference at : http://www.sitepoint.com/forums/mysql-182/composite-key-through-phpmyadmin-290795.html and http://stackoverflow.com/questions/1545571/how-do-i-make-a-composite-key-with-sql-server-management-studio

---

To reset auto increment id on table :
You don't need to waste time to create a new table, you can just delete all records in existing table then run SQL : ALTER TABLE `table_name` AUTO_INCREMENT =0
Reference at : http://www.knowledgesutra.com/discuss/ttlist-reset-auto-increment-phpmyadmin.om

---

To find where is the foreign keys of particular primary key (getting error when trying to rename the field of primary key because there is foreign key on other table linked by the primary key) :
There are 2 different solutions :
1) SQL to list out all tables that has foreign keys of a particular primary key :
select ku.*
from INFORMATION_SCHEMA.table_constraints tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ku ON
(tc.CONSTRAINT_NAME = ku.CONSTRAINT_NAME and tc.CONSTRAINT_SCHEMA = ku.CONSTRAINT_SCHEMA )
where constraint_type='FOREIGN KEY' and ku.REFERENCED_TABLE_NAME = 'your_table_name'

2) You go rename the field to get error, then type SQL :
SHOW ENGINE INNODB STATUS
Then look at the "LATEST FOREIGN KEY ERROR" section. Reference at http://stackoverflow.com/questions/7463221/sql-lists-out-all-foreign-keys-of-a-primary-key

---

SERIAL is the best datatype for auto increament id field, because SERIAL is BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. It can accept integers from 0 up to 18446744073709551615 (max is 20 digits only), if I assign length 50, it will still accept 20 digits only, it won't accept 50 digits. 18446744073709551615 is the largest integer accepted. You cannot use 2 Serial on two fields in a table because 1 table can has 1 AUTO_INCREMENT field only. The word UNSIGNED is attribute. Reference at here.

I cannot find the different between BIGINT Signed and Unsigned, however the different between CHAR Signed and Unsigned :
-Signed char, which gives you at least the -128 to 127 range.
-Unsigned char, which gives you at least the 0 to 255 range.
-Signed number uses one bit to determine whether the number is positive or negative, an unsigned number is positive.
Reference at here.

---

Different between datatype Char and Varchar :
Char :
- Use Char if all of records values have the same length. Eg, if u set Char to 5 characters, each records will be 5 bytes.
- Don't use Char if all of records values have difference length. Eg, u store "abc123" on Char(10), it will be "0000abc123" or "abc1230000", u need extra work to delete all 0000. This feature could lead to many irritating bugs during development and makes testing harder). Info at here.
- Char can be faster and more space efficient.
- Use Char if you need to update the records frequently, because it won't grow the size everytime update the records.

Varchar :
- Use Varchar if all of records values have different length. Eg, if you save record "love1234", the size will be 8 bytes (to save the value) + 1 byte (to save the length of value). Varchar need extra 1 or 2 bytes to save the length of value. Info of datatype at here. The example of size of data / records are at here.
- Don't use Varchar if records need to be updated frequently because their size will grow, which can cause extra work. if a row grows and no longer fits in its original location, the behavior is storage engine-dependent. Info at here.

Example :
store the word "FooBar" :
CHAR(6) = 6 bytes (no overhead)
CHAR(10) = 10 bytes (4 bytes of overhead)
VARCHAR(10) = 7 bytes (1 byte of overhead)

Conclusion : Use Char only if you have fixed length of records values. Don't use Char if the length of records values are different, even 1 character different also must NOT use Char.

---

Comparison between Varchar vs Text at here.

---

Normalization :
If some of the fields are missing (null) for a bunch of the rows, they make a good candidate for splitting off -- the extra table would have fewer rows. You would use LEFT JOIN to put things back together when needed. Eg :
FROM t1 JOIN t2 on t1.ID = t2.ID

JOIN / INNER JOIN show the records on both tables. LEFT JOIN / LEFT OUTER JOIN show the records on 1st table. RIGHT JOIN / RIGHT OUTER JOIN show the records on 2nd table.

$query32 = "SELECT usersbusinessprofile.id FROM usersbusinessprofile RIGHT JOIN users on usersbusinessprofile.username = users.username where sponsor = '{$username2}' && accountstatus='on'"; $result32 = mysql_query($query32,$connection) or die (mysql_error()); confirm_query($result32); $number_count32 = mysql_num_rows($result32);

---

There are 2 different ways to select and display 15 friends' picture randomly. 1 is using mysql ORDER BY RAND(). Another is select and save all usernames into a php array then select 15 value randomly from the array.

1 - Using mysql ORDER BY RAND(). I saw many people said don't use ORDER BY RAND() from google search. The problem of ORDER BY RAND() is that as sql explain tells you the "Using temporary" and the "Using filesort". For each request a temporary table is created and sorted. Thats a pretty heavy operation. It will probably not matter when your database is not under heavy load but it will cost a lot of performance. I have discuss about it on Why don't use mysql ORDER BY RAND()?. Anyway, if you still want to use mysql ORDER BY RAND(), here is the codes :
    $friendsArray = array();
    $sql_output = array();
    $frenusernameArray = array();
    $picmediumArray = array();
    $query120 = "SELECT frenusername FROM friendship WHERE username='{$username2}' UNION SELECT username FROM friendship WHERE frenusername='{$username2}' ORDER BY RAND() LIMIT 15";
    $result120 = mysql_query($query120,$connection) or die (mysql_error());
    confirm_query($result120);
    while($userinfo120 = mysql_fetch_array($result120)){
        $friendsArray[] = $userinfo120['frenusername'];
    }
   
    foreach($friendsArray as $friend) {
        $sql_output[] = "'".mysql_real_escape_string($friend)."'";
    }
    $sql_output = join(', ',$sql_output);
   
    $query120 = "SELECT username, picturemedium FROM users WHERE username IN ($sql_output)";
    $result120 = mysql_query($query120,$connection) or die (mysql_error());
    confirm_query($result120);
    while($userinfo120 = mysql_fetch_array($result120)){
        $frenusernameArray[] = $userinfo120['username'];   
        $picmediumArray[] = $userinfo120['picturemedium'];   
    }
   
    for ($i = 0; $i < count($frenusernameArray); $i++) {   
        echo "<a $frenusernamearray[$i]="" .="" \="" href="http://www.blogger.com/%5C%22user-profile.php?username=%22" target="\"_blank\""><img $picmediumarray[$i]="" .="" \="" class="\"frenpic\"" src="%5C%22images/users/%22" /></a>";
    }

2 - Using shuffle values in php array. Although this way avoid using mysql ORDER BY RAND() to prevent database heavy load problem, but it still have problem with php array memory heavy load problem. I have discuss it on confusing foreach loop show values of arrays. Here is the codes :
    $friendsArray = array();
    $sql_output = array();
    $frenusernameArray = array();
    $picmediumArray = array();
    $query120 = "SELECT frenusername FROM friendship WHERE username='{$username2}' UNION SELECT username FROM friendship WHERE frenusername='{$username2}' LIMIT 30";
    $result120 = mysql_query($query120,$connection) or die (mysql_error());
    confirm_query($result120);
    while($userinfo120 = mysql_fetch_array($result120)){
        $friendsArray[] = $userinfo120['frenusername'];
    }
   
    shuffle($friendsArray);
    $rand = array_slice($friendsArray, 0, 15);

Both ways also have its own problem. To reduce the problem, we can use precompute 15 fixed sets, each set has 15 random friends, then save 15 fixed sets into database. So next time we just need to random select from the 15 fixed sets, don't need to select from 5000 records anymore. Here is the codes :
$query120 = "SELECT COUNT(*) FROM friendship WHERE username='{$username2}' OR frenusername='{$username2}'";
$result120 = mysql_query($query120,$connection) or die (mysql_error());
confirm_query($result120);
while($userinfo120 = mysql_fetch_array($result120)){
    $friendshipTotalRows = $userinfo120['COUNT(*)'];
}
if ($friendshipTotalRows < "50" AND $fixedSetLabel != "50"){
    //precompute 15 fixed sets, each set has 15 ramdom friends, save the 15 fixed sets to database, insert value "50" to label field too.
}elseif ($friendshipTotalRows > "100" AND $friendshipTotalRows < "200" AND $fixedSetLabel != "100"){
     //precompute again
}elseif.....

$query120 = "(SELECT username FROM friendship WHERE frenusername='{$username2}') UNION ALL(SELECT frenusername FROM friendship WHERE username='{$username2}')";
$result120 = mysql_query($query120,$connection) or die (mysql_error());
confirm_query($result120);
while($userinfo120 = mysql_fetch_array($result120)){
$frenusername2= $userinfo120['username'];
echo $frenusername2; // this will display both username and frenusername.
}

//if u want to separate 2 sets of datas (username and frenusername), u can modify the abit the code above to something like this :
$query120 = "(SELECT frenusername, NULL as username FROM friendship WHERE username='{$username2}' UNION ALL SELECT NULL, username FROM friendship WHERE frenusername='{$username2}'";
$result120 = mysql_query($query120,$connection) or die (mysql_error());
confirm_query($result120);
while($userinfo120 = mysql_fetch_array($result120)){
$frenusername2= $userinfo120['username'];
$frenusername1= $userinfo120['frenusername'];
echo $frenusername2;
echo $frenusername1;
}
//Anyway, UNION is not necessarily the way to go when you want to get two separate data sets, because the purpose of UNION is to combine 2 sets of datas.

//if you want to replace UNION with OR, it is not easy because you need to select two fields with OR condition, look at my table records below :
username | frenusername
---------------------------------
zac1987 | qq
zac1987 | bb
uu | zac1987
oo | zac1987
As you can see, both fields username and frienusername has "zac1987". For example, if $username2 = "zac1987", "SELECT frenusername, username FROM friendship WHERE frenusername='{$username2}' ";, it will show "zac1987" for the output because you SELECT frenusername, so how to hide zac1987? U can write something like this :
$query120 = "SELECT frenusername, username FROM friendship WHERE username='{$username2}' OR frenusername='{$username2}' ";
$result = mysql_query($query120) or trigger_error(mysql_error());
if(mysql_num_rows($result) > 0) {
while($row = mysql_fetch_assoc($result)) {
if($row['frenusername'] == $username2) {
$friends[] = $row['username'];
}
elseif($row['username'] == $username2) {
$friends[] = $row['frenusername'];
}
}
}

echo $username2 . ' has ' . count($friends) . ' friends. They are ' . implode(', ',$friends);

Let's say you have 5000 records on database, it will take more than 15 seconds to select a record from 5000 records if you didn't optimize the query, because it loop through all 5000 records to search for a particular record for you. Actually you can optimize / faster the query to select the record within a second, perhaps 0.03second. There are many issues that causing slow query and there are many solutions to optimize / faster the query. I will list out all the issues and solutions here :
1 - Set index to the field that in WHERE clause
Example query : SELECT id FROM `messageslive` WHERE username='zac1985';
You need to set index to the field of username, please refer to the picture below :

For testing purpose, please follow the steps :
a) Click SQL on top menu.
b) Type in the mysql query, eg SELECT id FROM `messageslive` WHERE username='zac1985';
c) Click Explain SQL.
d) check the value of "Row" field. (Let's say you have 5000 rows of messages, but there are only 100 messages are posted by zac1987. If you didn't set index to the field that in WHERE clause, value of "Row" should be 5000, it mean query loop through all 5000 records. If you have set index to WHERE clause field, value of "Row" should be 100 only.

2 - Use subquery for RAND() * Max()
Example query : SELECT CEIL(RAND() * MAX(id)) FROM messageslive;
Although you have set index to the id field, RAND() * MAX(id) will generate an id which is not indexed, so causing lost optimization. We can use the solution of subquery like : SELECT RAND() * (SELECT MAX(id) FROM random);

3 - Replace WHERE clause with JOIN clause if there is a subquery in WHERE clause
Example query : SELECT name FROM random WHERE id = (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random)));
We can use the solution of replacing WHERE clause with JOIN clause. JOIN clause consist of "AS" and "USING", eg : SELECT name FROM random JOIN (SELECT CEIL(RAND() * (SELECT MAX(id) FROM random)) AS id) AS r2 USING (id);

More at Top 10 SQL Performance Tips and 10 Tips for Optimizing MySQL Queries (That don’t suck).

Let's say there are 10 messages, I have posted 3 responses onto 7 messages. There are 3 messages that I never responded. The system need to check if the 7 messages receive a new response from other users, the system should not check the 3 other messages.

Question 1 : SELECT msg-id FROM responsesLive WHERE username='{$myUsername}'; //This will output many duplicated msg-id if I sent multiple responses onto a particular message. How to prevent the duplicated msg-id?
Answer : SELECT DISTINCT msg-id FROM responsesLive WHERE username='{$myUsername}';

Question 2 : After I got the msg-id, I need to get the rsp-id of last response in each of the messages. How to do that?
Answer :
$query3 = "SELECT msg-id, response, max(rep-id) FROM responseslive WHERE msg-id in (SELECT DISTINCT msgid FROM responsesLive WHERE username='{$myUsername}') GROUP BY msg-id";
$result3 = mysql_query($query3,$connection) or die (mysql_error());
confirm_query($result3);
while($rspinfo = mysql_fetch_array($result3)){
     $latestrsp-id= $rspinfo['max(rsp-id)'];
     echo $latestrsp-id . " | ";
}

Question 3 : Isn't I need to loop through all the posts that user responded before? Let's say the user responded on 99999 posts before, i need to check all the 99999 posts to see whether any of them receive a new response? Sure it will slow down the entire page. May I know how did facebook.com or plurk.com do that? I saw plurk.com can tell the user when a very very very old post received a new response. Maybe my steps are wrong? I guess what they do are something like this :

Let's say there are 10 messages, Peter posted comments onto 7 messages. Next time when a user post a new comment onto any 3 of the 7 messages, the website will show the count button "3 new comments". When peter click on the button, the website will show the 3 messages which has the new comments.
Step 1 : Select the message id when a user post a new comment onto the message.
Step 2 : Select all username of users that have posted comment onto the message before.
Step 3 : Add username and message id onto table "new_response_count" in database.
Step 4 : Compare logged in username to the username in table of "tblNew_response_count" in database :
If ($username-login == $username-in-new-rsp-count) {
    $query3 = "SELECT msgid FROM tblNew_response_count WHERE username='{$username-login}'";
    $result3 = mysql_query($query3,$connection) or die (mysql_error());
    confirm_query($result3);
    $numCount = mysql_num_rows($result3);
}

Step 5 : <a href="javascript:;" id="newrspbtn"><</a>

Step 6 : Pass msgid from php variable to javascript variable then jquery bind click like :
$('#newrspbtn').bind('click',function(){
    jQuery.get("showRSPtest.php?lastmsgID=" + love2, function(newitems){
        $('#div123').append(newitems);
    });
});

Step 7 : Every 10 seconds will auto reload the function of Step 4.

Note that when the user click the button, two tasks will be carried out : 1) all messages that has new response will be displayed, 2) delete the records (username and msgid) on tblNew_response_count in database.

If you are writing any server script such as php or asp to send email from your server / hosting to people's mail box, the email will go into their junk / spam box, how to make the email go to inbox instead of junk / spam box? To do that, just follow the steps below :

Html
<!-- comment here -->

---

Css
/*comment here*/

---

Javascript
<!-- comment 1 here
javascript code here
// comment 2 here -->
Without the <!-- and -->, the browser will display "comment here 1" as normal text. So if it is html document, you need to put <!-- and -->, if it is js document, you don't need to put <!-- and -->.

The two slash // tell the browser's JavaScript interpreter that the rest of the line is not valid JavaScript code.

---

Ajax
// comment here

---

Php
1) // comment here
2) # comment here
3) /* comment here */

---

Mysql
/* comment here */

---

Asp
'comment here