Exploring Asp.net, Silverlight,c#,SQL Server,PHP

Friday 23 August 2013

Insert, Edit and Delete operations in PHP

In this article we will learn how to insert, edit, update and delete records from the database using PHP. Here we have to create five pages such as config.php (to provide the connection), view.php (to display the records from the database), insert.php (to insert records into the database), edit.php (to edit records), and delete.php (to delete records).

Table Structure

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `Sample`
--

-- --------------------------------------------------------

--
-- Table structure for table `employee`
--

CREATE TABLE `employee` (
`id` int(12) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`id`, `name`, `address`, `city`) VALUES
(1, 'Raj', '10 street dane', 'Pune'),
(2, 'Ravi', '12907A 53 St NW', 'Mumbai'),
(3, 'Rahul', '3rd Floor, 888 Fort Street', 'Noida'),
(4, 'Harry', 'Sir Frederick W Haultain Building 9811 109 ST NW', 'London'),
(5, 'Ian', 'Suite 303, 13220 St. Albert Trail', 'Sydney'),
(6, 'Shaun', '9700 Jasper Avenue', 'Perth');

Code part

Config.php

<?php

/* Database Connection */

$sDbHost = 'localhost';
$sDbName = 'Sample';
$sDbUser = 'root';
$sDbPwd = '';

$dbConn = mysql_connect ($sDbHost, $sDbUser, $sDbPwd) or die ('MySQL connect failed. ' . mysql_error());
mysql_select_db($sDbName,$dbConn) or die('Cannot select database. ' . mysql_error());

?>
View.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>View Records</title>
</head>
<body>
<?php

include('config.php');

$result = mysql_query("SELECT * FROM employee")
or die(mysql_error());

echo "<table border='1' cellpadding='10'>";
echo "<tr>
<th><font color='Red'>Id</font></th>
<th><font color='Red'>Name</font></th>
<th><font color='Red'>Address</font></th>
<th><font color='Red'>City</font></th>
<th><font color='Red'>Edit</font></th>
<th><font color='Red'>Delete</font></th>
</tr>";

while($row = mysql_fetch_array( $result ))
{

echo "<tr>";
echo '<td><b><font color="#663300">' . $row['id'] . '</font></b></td>';
echo '<td><b><font color="#663300">' . $row['name'] . '</font></b></td>';
echo '<td><b><font color="#663300">' . $row['address'] . '</font></b></td>';
echo '<td><b><font color="#663300">' . $row['city'] . '</font></b></td>';
echo '<td><b><font color="#663300"><a href="edit.php?id=' . $row['id'] . '">Edit</a></font></b></td>';
echo '<td><b><font color="#663300"><a href="delete.php?id=' . $row['id'] . '">Delete</a></font></b></td>';
echo "</tr>";

}

echo "</table>";
?>
<p><a href="insert.php">Insert new record</a></p>
</body>
</html>
Insert.php

<?php
function valid($name, $address,$city, $error)
{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Insert Records</title>
</head>
<body>
<?php

if ($error != '')
{
echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
}
?>

<form action="" method="post">
<table border="1">
<tr>
<td colspan="2"><b><font color='Red'>Insert Records </font></b></td>
</tr>
<tr>
<td width="179"><b><font color='#663300'>Name<em>*</em></font></b></td>
<td><label>
<input type="text" name="name" value="<?php echo $name; ?>" />
</label></td>
</tr>

<tr>
<td width="179"><b><font color='#663300'>Address<em>*</em></font></b></td>
<td><label>
<input type="text" name="address" value="<?php echo $address; ?>" />
</label></td>
</tr>

<tr>
<td width="179"><b><font color='#663300'>City<em>*</em></font></b></td>
<td><label>
<input type="text" name="city" value="<?php echo $city; ?>" />
</label></td>
</tr>

<tr align="Right">
<td colspan="2"><label>
<input type="submit" name="submit" value="Insert Records">
</label></td>
</tr>
</table>
</form>
</body>
</html>
<?php
}

include('config.php');

if (isset($_POST['submit']))
{

$name = mysql_real_escape_string(htmlspecialchars($_POST['name']));
$address = mysql_real_escape_string(htmlspecialchars($_POST['address']));
$city = mysql_real_escape_string(htmlspecialchars($_POST['city']));

if ($name == '' || $address == '' || $city == '')
{

$error = 'Please enter the details!';

valid($name, $address, $city,$error);
}
else
{

mysql_query("INSERT employee SET name='$name', address='$address', city='$city'")
or die(mysql_error());

header("Location: view.php");
}
}
else
{
valid('','','','');
}
?>
Edit.php

<?php
function valid($id, $name, $address,$city, $error)
{
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Edit Records</title>
</head>
<body>
<?php

if ($error != '')
{
echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
}
?>

<form action="" method="post">
<input type="hidden" name="id" value="<?php echo $id; ?>"/>

<table border="1">
<tr>
<td colspan="2"><b><font color='Red'>Edit Records </font></b></td>
</tr>
<tr>
<td width="179"><b><font color='#663300'>Name<em>*</em></font></b></td>
<td><label>
<input type="text" name="name" value="<?php echo $name; ?>" />
</label></td>
</tr>

<tr>
<td width="179"><b><font color='#663300'>Address<em>*</em></font></b></td>
<td><label>
<input type="text" name="address" value="<?php echo $address; ?>" />
</label></td>
</tr>

<tr>
<td width="179"><b><font color='#663300'>City<em>*</em></font></b></td>
<td><label>
<input type="text" name="city" value="<?php echo $city; ?>" />
</label></td>
</tr>

<tr align="Right">
<td colspan="2"><label>
<input type="submit" name="submit" value="Edit Records">
</label></td>
</tr>
</table>
</form>
</body>
</html>
<?php
}

include('config.php');

if (isset($_POST['submit']))
{

if (is_numeric($_POST['id']))
{

$id = $_POST['id'];
$name = mysql_real_escape_string(htmlspecialchars($_POST['name']));
$address = mysql_real_escape_string(htmlspecialchars($_POST['address']));
$city = mysql_real_escape_string(htmlspecialchars($_POST['city']));

if ($name == '' || $address == '' || $city == '')
{

$error = 'ERROR: Please fill in all required fields!';

valid($id, $name, $address,$city, $error);
}
else
{

mysql_query("UPDATE employee SET name='$name', address='$address' ,city='$city' WHERE id='$id'")
or die(mysql_error());

header("Location: view.php");
}
}
else
{

echo 'Error!';
}
}
else

{

if (isset($_GET['id']) && is_numeric($_GET['id']) && $_GET['id'] > 0)
{

$id = $_GET['id'];
$result = mysql_query("SELECT * FROM employee WHERE id=$id")
or die(mysql_error());
$row = mysql_fetch_array($result);

if($row)
{

$name = $row['name'];
$address = $row['address'];
$city = $row['city'];

valid($id, $name, $address,$city,'');
}
else
{
echo "No results!";
}
}
else

{
echo 'Error!';
}
}
?>
Delete.php

<?php
include('config.php');

if (isset($_GET['id']) && is_numeric($_GET['id']))
{
$id = $_GET['id'];

$result = mysql_query("DELETE FROM employee WHERE id=$id")
or die(mysql_error());

header("Location: view.php");
}
else

{
header("Location: view.php");
}
?>

Output

After inserting 6 records

After editing the address of Name (Raj)

Thursday 22 August 2013

Connecting Nav Web Services from PHP Part - 3

Previously we have discussed Part - 1 and Part - 2, here I will discussed how to Invoke the Codeunit methods.

<?php

try
{
            require_once("NTLMStream.php");

            require_once("NTLMSoapClient.php");

            stream_wrapper_unregister('http');

            stream_wrapper_register('http', 'NTLMStream') or die("Failed to register protocol");


            $pageURL = 'http://localhost:7047/dynamicsnav/ws/Test%20Products/Codeunit/acceptwebcustomerdetail';
            echo "<br>URL of Customer page: $pageURL<br><br>";

            // Initialize Page Soap Client
            $page = new NTLMSoapClient($pageURL);
            $page->fuck_you_ms = TRUE;


            //echo '<pre>acceptwebcustomerdetail class functions</pre>';

            //$functions =$page->__getFunctions();

            //var_dump($functions);

            echo '<pre></pre>';
            $accept=array('SecurityTokenID'=> '*786','MemberID'=>'12885','CustomerName'=>'TestUser','EmailAddress'=>'test@gmail.com','ReponsibilityCenter'=>'BIND',
                            'Website'=>'http://testcompany.com','GroupID'=>'8','Group'=>'General','CreatedIn'=>'Admin');
            $acceptp=array('acceptcustomer'=>$accept);

            $outsec='';
            $status='';
            $errorcode='';

            $params=array('acceptwebcustomerp'=>$acceptp,'outsecuritytokenidp'=>$outsec,'statusp'=>$status,'errorcodep'=>$errorcode);


            $Cre = $page->Addorupdatecustomer($params);

            echo '<br/>';

            echo 'Create Order successfully <br/>';



// restore the original http protocole
stream_wrapper_restore('http');

}
catch(Exception $ex){
    echo 'Soap Exception<br/>';
    echo $ex->getMessage();
}

?>

Friday 9 August 2013

Connecting to NAV Web Services from PHP Part - 2

Prerequisites

Please read this post to get an explanation on how to modify the service tier to use NTLM authentication and for a brief explanation of the scenario I will implement in PHP.
BTW. Basic knowledge about PHP is required to understand the following post:-)

Version and download

In my sample I am using PHP version 5.3, which I downloaded from http://www.php.net, but it should work with any version after that.
In order to make this work you need to make sure that SOAP and CURL extensions are installed and enabled in php.ini.
PHP does not natively have support for NTLM nor SPNEGO authentication protocols, so we need to implement that manually. Now that sounds like something, which isn’t straightforward and something that takes an expert.   Fortunately there are a lot of these experts on the internet and I found this post (written by Thomas Rabaix), which explains about how what’s going on, how and why. Note that this implements NTLM authentication and you will have to change the Web Services listener to use that.

<?php
class NTLMStream
{
    private $path;
    private $mode;
    private $options;
    private $opened_path;
    private $buffer;
    private $pos;
    /**
     * Open the stream
      *
     * @param unknown_type $path
     * @param unknown_type $mode
     * @param unknown_type $options
     * @param unknown_type $opened_path
     * @return unknown
     */
    public function stream_open($path, $mode, $options, $opened_path) {
        $this->path = $path;
        $this->mode = $mode;
        $this->options = $options;
        $this->opened_path = $opened_path;
        $this->createBuffer($path);
        return true;
    }
    /**
     * Close the stream
     *
     */
    public function stream_close() {
        curl_close($this->ch);
    }
    /**
     * Read the stream
     *
     * @param int $count number of bytes to read
     * @return content from pos to count
     */
    public function stream_read($count) {
        if(strlen($this->buffer) == 0) {
            return false;
        }
        $read = substr($this->buffer,$this->pos, $count);
        $this->pos += $count;
        return $read;
    }
    /**
     * write the stream
     *
     * @param int $count number of bytes to read
     * @return content from pos to count
     */
    public function stream_write($data) {
        if(strlen($this->buffer) == 0) {
            return false;
        }
        return true;
    }
    /**
     *
     * @return true if eof else false
     */
    public function stream_eof() {
        return ($this->pos > strlen($this->buffer));
    }
    /**
     * @return int the position of the current read pointer
     */
    public function stream_tell() {
        return $this->pos;
    }
    /**
     * Flush stream data
     */
    public function stream_flush() {
        $this->buffer = null;
        $this->pos = null;
    }
    /**
     * Stat the file, return only the size of the buffer
     *
     * @return array stat information
     */
    public function stream_stat() {
        $this->createBuffer($this->path);
        $stat = array(
            'size' => strlen($this->buffer),
        );
        return $stat;
    }
    /**
     * Stat the url, return only the size of the buffer
     *
     * @return array stat information
     */
    public function url_stat($path, $flags) {
        $this->createBuffer($path);
        $stat = array(
            'size' => strlen($this->buffer),
        );
        return $stat;
    }
    /**
     * Create the buffer by requesting the url through cURL
     *
     * @param unknown_type $path
     */
    private function createBuffer($path) {
        if($this->buffer) {
            return;
        }
        $this->ch = curl_init($path);
        curl_setopt($this->ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($this->ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
        curl_setopt($this->ch, CURLOPT_HTTPAUTH, CURLAUTH_NTLM);
        curl_setopt($this->ch, CURLOPT_USERPWD, USERPWD);
        $this->buffer = curl_exec($this->ch);
        $this->pos = 0;
    }
}

class NTLMSoapClient extends SoapClient {

    public $fuck_you_ms = FALSE; // very important variable -> indicates if we are going to process request "before" sending (all thanks to MS SOAP implementation - hence the name)

    //function __doRequest($request, $location, $action, $version) {
    function __doRequest($request, $location, $action, $version, $one_way = 0) {
        $headers = array(
            'Method: POST',
            'Connection: Keep-Alive',
            //'User-Agent: PHP-SOAP-CURL',
            'Content-Type: text/xml; charset=UTF-8',
            'SOAPAction: "'.$action.'"',
        );

        if($this->fuck_you_ms) {
            // some fancy processing before passing stuff to ms
            $request = fix_ms_xml($request);
            //echo "\n<hr><pre>\n".htmlspecialchars( str_replace("<Item>","\n\t<Item>",$request) )."\n</pre><hr>\n";
        }

        $request = str_replace(array("\n","\r","\t"),"",$request); // clean XML before sending

        $this->__last_request_headers = $headers;
        $ch = curl_init($location);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
        curl_setopt($ch, CURLOPT_POST, true );
        curl_setopt($ch, CURLOPT_POSTFIELDS, $request);
        curl_setopt($ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
        curl_setopt($ch, CURLOPT_HTTPAUTH, CURLAUTH_NTLM);
        curl_setopt($ch, CURLOPT_USERPWD, USERPWD);
        $response = curl_exec($ch);
        //echo "\n<hr>response:".$response."\n";
        return $response;
    }

    function __getLastRequestHeaders() {
        return implode("\n", $this->__last_request_headers)."\n";
    }
}

function fix_ms_xml($request,$level=1){
    $out='';

    $dom = new DOMDocument();
    $dom->loadXML($request, LIBXML_NOBLANKS);
    $envelopes = $dom->getElementsByTagName('Envelope');
    foreach($envelopes as $envelope){ // i know we have only one attr...
        //echo '<hr>GOT ENVELOPE:';
        if($envelope->hasAttribute("xmlns:ns1")){
//            echo '<hr> - YAAHAH WE HAVE ATTR';
            $attr1 = (String)$envelope->getAttribute("xmlns:ns1");
            ///$attr2 = (String)$envelope->getAttribute("xmlns:ns2");
            //var_dump($envelope->getAttribute("xmlns:ns1"));

            $envelope->removeAttributeNS($attr1,"ns1");
            //$envelope->removeAttributeNS($attr2,"ns2");

//            $envelope->setAttributeNS('urn:microsoft-dynamics-schemas/codeunit/Drupal_OrderImport',"drup");
/*
            echo "<hr>--[";
            var_dump($envelope->removeAttributeNS($attr1,"ns1"));
            echo "]--<hr>";
*/
            //var_dump($envelope->removeAttributeNS($attr2,"ns2"));
            //$envelope->setAttribute("xmlns:ns1",111);
        }

    }

/*     $node_list = $dom->getElementsByTagName('Body');
    $node = $node_list->item(0)->childNodes->item(0);
    $node->setAttribute('xmlns:ns1',$attr2);

    $node = $node_list->item(0)->childNodes->item(0);
    $node->setAttribute('xmlns:ns2',$attr1);    */

/*
    $node_list = $dom->getElementsByTagName('Address');
    $node = $node_list->item(0);
    echo "ADDR:".$node->nodeValue;
*/
    //$attr1=$envelope->getAttribute('ns1');




    return $dom->saveXML();
    //return $dom->C14N(true, false);
}
?>
Putting this into the PHP script now allows you to connect to NAV Web Services system service in PHP and output the companies available on the service tier:
<?php

require('nav_soap_client.php'); // or... die it's not gonna work without it anyway

class commerce_nav_crons{

    function __construct() {
        $this->service_url_main = 'http://localhost:7047/dynamicsnav/ws'; // just a sample - i read it from db
        // we unregister the current HTTP wrapper
        stream_wrapper_unregister('http');
        // we register the new HTTP wrapper
        stream_wrapper_register('http', 'NTLMStream') or die("Failed to register protocol");
        define('USERPWD', 'testdomain\testuser:testpassword'); // put your own values - mine are actually taken from db
    }

    function __destruct() {
        // restore the original http protocole
        stream_wrapper_restore('http');
    }

    /**
     *
     * this is a main run function to send Order Returns to NAV, very basic, just to illustrate sample
     *
     * @return bool
     */
    function sample_run(){
        $ret = FALSE;
        $service_url = $this->service_url_main . '/Test%20Products/Company';
        $service_params = array(
            'trace' => TRUE,
            'cache_wsdl' => WSDL_CACHE_NONE,
        );

        //echo $service_url . '<br/>';

        $service = new NTLMSoapClient($service_url, $service_params);
        $service->fuck_you_ms = TRUE; // because fuck you, that's why!


// Find the first Company in the Companies
            $result = $client->Companies();
            $companies = $result->return_value;
            echo "Companies:<br>";
            if (is_array($companies)) {
              foreach($companies as $company) {
                echo "$company<br>";
              }
              $cur = $companies[4];
            }
            else {
              echo "$companies<br>";
              $cur = $companies;
            }


        return $cur ;
    }

}

$cl = new commerce_nav_crons();
$cl->sample_run();

Next part -3 I will explain how to connect the CodeUnit services..

Note:
Here I collecting the information from Freedy and Arte blogs. They are explain the two different ways here I explain the both scenarios.

always welcome any queries and comments....

Pages