admin « CodeThatAint

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

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