How to Convert an Multidimensional Array to One Dimensional Array
//First Method with No Argument

$this->RestructArray($arrTempCounts)

//Second Method with  Column Name as Argument
$this->RestructArray($arrTemp, 'UserNums');

function RestructArray($parrInput, $pArg1 = 0)
{
	$arrTemp = $parrInput;
	
	for($i=0;$i<count($parrInput);$i++)
	{
		$arrNames[$i] = $parrInput[$i][$pArg1];
	}
	return $arrNames;
}

How to Convert an One Dimensional Array to Multidimensional Array

$k = 0;
for($j=0;$j<$lngCols;$j++)
{
	for($i=0;$i<$lngRows-1;$i++)
	{		
		$arrTemp[$j][$i] = $arrIndividualCounts[$k];
		$k = $k+1;
	}
}
Posted in PHP.

How to Run Multiple Queries at Once in OOP
Note:The SQL Queries Shld be seperated by Semicolon(;)

$Conn = $this->dbConnect;
$strSQL = "SELECT name FROM tbSample1;";
$strSQL = $strSQL."SELECT age FROM tbSample1;";

if ($Conn->multi_query($strSQL)) 
{
	 do 
	 { 
		/* store first result set */
		if ($result = $Conn->store_result()) 
		{	
			while ($row = $result->fetch_row()) 
			{
				$item = array();
				$item = $row;
				$arrTempCounts[] = $item;
			}
			$result->free();
		}
		/* print divider */

		if ($Conn->more_results()) 
		{
		 print("-----------------n");
		}
	}while ($Conn->next_result());
}

Posted in PHP.

Storing Array In Cookie and Retrieving it

  $arrNames= array('Name'=>'Mugil',
               	   'Email'=>'Mugil@cse.com', 
	           'ContactNo'=>'9962945097');
  
  $strDetails = implode(',', $arrNames);

  setcookie('Details', $strDetails, time()+ 3600*24);

  if(isset($_COOKIE['Details']))
  {
    $arrDetails = explode(',', $_COOKIE['Details']);
	
    for($i=0;$i<count($arrDetails);$i++)
     print $arrDetails[$i]."
"; }
Posted in PHP.

Mysqli Method
Procedure

  DROP PROCEDURE IF EXISTS prSample1;
  CREATE PROCEDURE prSample1(OUT Name VARCHAR(255))
  BEGIN
    SET Name = 'Mugil';
    SELECT Name;
  END;

Call to Procedure From PHP

  $Conn = new mysqli('localhost', 'root', '', 'metroplots');				

  $strSQL = "CALL prSample1(@Name)";
  $stmt   = $Conn->prepare($strSQL);

  $stmt->execute();
  $stmt->bind_result($Name);	

  while($stmt->fetch())
   $strName = $Name;

Upper Case First Letter in  Word

{$city|capitalize}

Input

{assign var="name" value="mugil"}

Output

Mugil


How to Check for Variable in Array

 {if in_array($current_item,$selected_array}

How to Find Absolute Value

  {$launchDate|abs ne 0}

How to find substring in String

  {$launchDate|substr:0:4}

Using Foreach Loop

  {assign var="Count" value=1}
  {foreach from=$arrTestimonials item=foo}
   .
   .
   .
   .
   {assign var="Count" value=$Count+1}
   {/foreach}    

Creating Simple XML File In PHP

 <?php    
    header("Content-Type: text/plain");

    //create the xml document
    $xmlDoc = new DOMDocument();

    //create the root element
    $root = $xmlDoc->appendChild(
              $xmlDoc->createElement("Students"));

    //make the output pretty
    $xmlDoc->formatOutput = true;

    echo $xmlDoc->saveXML();
?>

Output

<?xml version="1.0"?>
<Students/>

Adding Nodes To XML Document

//create a tutorial element
  $tutTag = $root->appendChild(
              $xmlDoc->createElement("Student"));

  //create the author attribute
  $tutTag->appendChild(
    $xmlDoc->createAttribute("id"))->appendChild(
      $xmlDoc->createTextNode('105'));

  //create the title element
  $tutTag->appendChild(
    $xmlDoc->createElement("FirstName", 'Mugil'));

  //create the title element
  $tutTag->appendChild(
    $xmlDoc->createElement("LastName", 'Vannan'));

  //create the date element
  $tutTag->appendChild(
    $xmlDoc->createElement("Age", '24'));

Whole Code

 <?php    
    header("Content-Type: text/plain");

    //create the xml document
    $xmlDoc = new DOMDocument();

    //create the root element
    $root = $xmlDoc->appendChild(
              $xmlDoc->createElement("Students"));

    //create a tutorial element
  $tutTag = $root->appendChild(
              $xmlDoc->createElement("Student"));

  //create the author attribute
  $tutTag->appendChild(
    $xmlDoc->createAttribute("id"))->appendChild(
      $xmlDoc->createTextNode('105'));

  //create the title element
  $tutTag->appendChild(
    $xmlDoc->createElement("FirstName", 'Mugil'));

  //create the title element
  $tutTag->appendChild(
    $xmlDoc->createElement("LastName", 'Vannan'));

  //create the date element
  $tutTag->appendChild(
    $xmlDoc->createElement("Age", '24'));              

    //make the output pretty
    $xmlDoc->formatOutput = true;

    echo $xmlDoc->saveXML();
?>

Output

<?xml version="1.0"?>
<Students>
  <Student id="105">
    <FirstName>Mugil</FirstName>
    <LastName>Vannan</LastName>
    <Age>24</Age>
  </Student>
</Students>

I have Table with Columns Name, EmailId, Process, Status Like Below. Now what i would like to have is the emailId and Status of people whoes Status are closed for all the Process – Process1, process2, process 3

Input
 

Output

Query1

  SELECT  fruit_email_id
    FROM  Fruits AS t
   GROUP BY fruit_email_id
  HAVING COUNT( CASE WHEN Status  'Closed' 
                     THEN 1                       
                      END) = 0;

Query2

   SELECT  t.fruit_email_id
     FROM  (SELECT DISTINCT fruit_email_id
              FROM Fruits) AS t
              LEFT JOIN Fruits AS tt ON  tt.fruit_email_id = t.fruit_email_id AND 
                   tt.Status  'Closed'
    WHERE tt.fruit_email_id IS NULL;
Posted in SQL.
    mysql_connect('localhost', 'root', '');
    mysql_select_db('sampleDB');
    
    $strSQL = 'SELECT * 
                 FROM tbAmenities 
                LIMIT 10';
                         
    $Result = mysql_query($strSQL); 

    $Response = array();
    $Posts    = array();
    
    while($row=mysql_fetch_array($Result)) 
    { 
        $id             =    $row['id']; 
        $amenityname    = $row['amenityname']; 
        
        $amenity[] = array('id'=> $id, 'amenity'=> $amenityname);    
    } 

    $Response['posts'] = $amenity;

    $fp = fopen('results.json', 'w');
    fwrite($fp, json_encode($Response));
    fclose($fp);

1.How To Create an SNo In MySql – Similar to row_num in MSSQL

SELECT @rownum:= @rownum+1 AS Sno
  FROM Personal_details p,(SELECT @rownum:=0) r;

How to Subtract 7 Day from Now

SELECT DATE_SUB(NOW(),INTERVAL 7 DAY)</pre>

How to View Active Connections & Process

SHOW PROCESSLIST;

How to Get Balance
Input Table

Query 1

    SELECT date,
          SUM(CASE
               WHEN TYPE = 'cash_in' THEN amount
               END) AS cash_in,
          SUM(CASE
                 WHEN TYPE = 'cash_out' THEN amount
               END) AS cash_out,
          SUM(CASE
                WHEN TYPE = 'cash_in' THEN amount
                ELSE -amount
               END) AS balance
    FROM tbSample4
   WHERE type IN ('cash_in', 'cash_out') 
   GROUP BY date(date)

Query 2

 SELECT a.currency_id, cash_in, cash_out, (cash_in - cash_out) balance
   FROM (SELECT currency_id, SUM(AMOUNT) cash_in
           FROM tbSample4
          WHERE type = 'cash_in'
          GROUP BY currency_id) a,
        (SELECT currency_id, SUM(AMOUNT) cash_out
           FROM tbSample4
          WHERE type = 'cash_out'
          GROUP BY currency_id) b
  WHERE a.currency_id = b.currency_id

Output Table

2.How to Make Selected Names at the Top of List

 SELECT Names
   FROM tbNames</pre>

<strong>Input Table</strong>
<img src="http://codeatelier.files.wordpress.com/2012/07/6.jpg" alt="" width="70" height="235" />

<strong>Output</strong>

<strong>Query1</strong>

SELECT Names
  FROM tbNames
 ORDER BY CASE WHEN names IN ('Chandiran', 'Suryan') 
               THEN 0
               ELSE 1
               END</pre>

Query2

 SELECT * 
   FROM tbNames
  ORDER BY  Names IN ('Heman','superman') DESC;</pre>
<img src="http://codeatelier.files.wordpress.com/2012/07/7.jpg" alt="" width="75" height="241" />

3.How To Return Row in Column as Comma Separated Value CSV

 
   SELECT GROUP_CONCAT(Name)
     FROM tblNames
    WHERE Id IN(15,16,17,18);

4.How Add OR Condition in Mysql case

CREATE TABLE tblConfirmationStatus (Confirm_Status TINYINT);

INSERT INTO tblConfirmationStatus 
Confirm_Status
VALUES
(1),
(0),
(1),
(null),
(0),
(1),
(null);

Required Output
ConfirmStatus

   Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed
   Not Confirmed
   Confirmed
   Not Confirmed

Query 1

 SELECT CASE 
        WHEN Confirm_Status IS NULL OR Confirm_Status = 0 THEN 'Not Confirmed' 
        ELSE  'Confirmed' 
       END AS ConfirmStatus
  FROM tblConfirmationStatus;

Query 2

 SELECT CASE 
        WHEN Confirm_Status &gt; 0 THEN 'Confirmed' 
        ELSE  'Not Confirmed' 
       END AS ConfirmStatus
  FROM tblConfirmationStatus;

MySQL Query To Skip Days - Sunday as Below

CREATE TABLE Patients_Appointment_List(Patent_Id INT  UNSIGNED PRIMARY KEY AUTO_INCREMENT,Patient_Name VARCHAR(255),appointment_Date DATE);

INSERT INTO Patients_Appointment_List
 (Patient_Name, appointment_Date)
  VALUES
 ('Mugil', 20120730),
 ('Vinu',  20120730),
 ('Madhavan',  20120729),
 ('Gopal',  20120730),
 ('Shivaji',  20120731);

SELECT Patient_Name
  FROM Patients_Appointment_List
 WHERE CASE WHEN WEEKDAY(CURDATE() + INTERVAL 1 DAY) = 6 THEN 
       appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 2 DAY 
            ELSE appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
       END

Output as on 20120728
Patient_Name

Mugil
Vinu
Madhavan
Gopal