admin « CodeThatAint

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]."
";			
 }

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;

    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);

SELECT DATE_FORMAT(NOW(), ‘%d %b %Y’)

Output
11 Jul 2012

Other Formats

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>
[code lang="sql"]
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

Everyone knew that you could force a link to open in a new tab or window with the target=”_blank” attribute.

How about form

You can do the same thing for form by the following way

<form action="#" method="post" target="_blank"> 
.
.
.
</form>

A stored procedure, by definition, is a segment of declarative SQL code which is stored in the database catalog and can be invoked later by a program, a trigger or even a stored procedure.

Now let us see how to Create stored procedure in mysql
Code for Simple Procedure

DELIMITER //
CREATE PROCEDURE Sample()
BEGIN
  SELECT 'Hello World !';
END//

Calling Procedure

CALL Sample()

<strong>Deleting Procedure if Already exists</strong>
[code lang="sql"]
DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages()
BEGIN
  SELECT *
    FROM Pages;
END

Supplying a Argument to Procedure

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages(IN Var1 VARCHAR(30))
BEGIN
  SELECT *
    FROM Pages
   WHERE PageName = Var1;
END

Working with Supplied Argument

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages(IN Var1 INT)
BEGIN
   SELECT Var1 + 10;
END

Calling Procedure

CALL ShowPages(10)

O/P:
20

Getting output from Procedure

DELIMITER //
DROP PROCEDURE  IF EXISTS ShowPages;
CREATE PROCEDURE ShowPages(OUT Var1 VARCHAR(30))
BEGIN
  SET Var1 = 'Mugilvannan';
  SELECT Var1;
END//

Calling Procedure

CALL ShowPages(@parameter_var1);

O/P:
Mugilvannan

Returns The Last Inserted ID

DELIMITER //
DROP PROCEDURE  IF EXISTS  InsertSubjects;
CREATE PROCEDURE InsertSubjects(OUT Id INT, IN strMenuName VARCHAR(30),IN strPosition INT, IN strVisible TINYINT)
BEGIN
  INSERT INTO Subjects(MenuName, Position, Visible)
VALUES(strMenuName, strPosition, strVisible);

 SELECT LAST_INSERT_ID()
   FROM Subjects
  LIMIT 1;
END//

CALLING ONE PROCEDURE WITHIN ANOTHER PROCEDURE
Procedure1 mp_proc1 Will return value returned by procedure 2

//PROCEDURE 1
DROP PROCEDURE IF EXISTS mp_proc1;
CREATE PROCEDURE mp_proc1(OUT AmenityName1 VARCHAR(100))
BEGIN
 CALL mp_proc2(@amnName);
 SET AmenityName1 = (SELECT @amnName); 
END

//PROCEDURE 2
DROP PROCEDURE IF EXISTS mp_proc2;
CREATE PROCEDURE mp_proc2(OUT AmenityName VARCHAR(100))
BEGIN
 SET AmenityName =(SELECT amenity_name 
                     FROM mp_amenities
                    WHERE id = 2);
END

CodeThatAint

Author Archives: admin

Storing and Retrieving array in Cookie

Procedure Call from PHP

Smarty Functions

XML File Generation In PHP

Group By Status

Creating JSON File With PHP

Mysql Date Functions

Mysql Queries

How to Submit a form to New Page/Window?

MySQL Procedure Part1