Last Updated:

1C+PHP+MySQL

In the modern world, people use the Internet not only to search for information, but also to purchase any goods and services. Therefore, organizations, for example, having the status of a medium-sized business, try to place their price lists on the Internet or even create an online store. It's not that easy to create. If you have a "real" business and use a certain system of accounting for goods (or services), you need to consider several important factors:

  • Every self-respecting online store should show the balance of the goods in stock, as well as not show the goods that are no longer in stock. (and it may be that the goods are in different regional warehouses)
  • Prices should not diverge from prices in a real store. Not counting the delivery, although it has long been free almost everywhere.

I gave only 2 facts, in fact there may be much more, it all depends on what goal you set for your Internet project.

Many organizations use 1C software to keep records of goods or services rendered, and as practice shows, most organizations work with version 7.7, and are in no hurry to switch to 8.x. as this entails overhead costs.

To implement work with WEB in 1C7.7 there is an excellent solution - WEB-extension, but it works only with Microsoft Internet Information Server (IIS). IIS is not used by all hosting providers, and those who use it are unlikely to allow you to connect your server to someone else's database.

And to keep "at home" an IIS server is quite expensive for medium-sized businesses (equipment + Internet channel + OS and software licenses = increase in the cost of goods).

Another thing is "free" software and OS: Linux, Apache, PHP, MySQL. These four products are used by most hosting providers, so their use as a platform for creating Internet projects is most optimal.

How to link 1C 7.7 with MySQL? You can use the external component MySQL,dll, but its use is not always possible, especially if your Web server is located remotely or you buy hosting from a third-party organization, since it is not safe to open access to the database from the Internet.

To solve this problem, I used the technology of transmitting data through the URL.

Perhaps this sounds a little wild, and many will think that this is the wrong decision. However, this system works and works quite well and correctly.

And now I would like to share with my colleagues the experience gained in implementing this technology.

Briefly about the principle:

It looks like this: the url is called from 1C and through it the data is transmitted to the PHP-creak, which in turn does with them everything that is necessary.

For example, we need to transfer the rest of the goods in the warehouse: from 1C will have to form a url: http://www.server.com/script.php?good=201&count=302&date=2009-06-09, where:

  • http://www.server.com – server address
  • script.php – executing script
  • good=201 – variable and product code
  • count=302 – variable and amount of balance
  • date=2009-06-09 – date to which the balance is transferred

Moreover, for the operation of this scheme, it is enough only to have access to the Internet from the computer on which the data transfer operation is performed. (there is no need to connect the database server to the Internet)

Encounter with possible errors:

To begin with, I would like to talk about the problems that I have encountered and how they can be most effectively solved:

1. The problem of passing fractional numbers, namely the difficulties that arise when using the symbol "dot" (.)

If it is necessary to transmit any fractional number, then in 1C it will be stored in the form 1.1. When passing a number through a url, a dot separating an integer part from a fractional part causes an error, since the "period" character is used to separate domains. Therefore, it is necessary to transform this very "point" into another symbol most suitable for this. And that symbol is a comma (,). In order to perform this conversion, I wrote a function, the listing of which is shown below:

Function NumberURL(Page)
Return strReplace(String(str),".",",");
EndFunctions

This function returns a string in which all the characters "." are replaced by "," in the string variable Page. This applies not only to numbers, but also to text data.

Now, when transmitting, the number 1.1 will have a format of 1.1. Next, when receiving, we must bring this number back to the "usual" form, this can be done on the side of the PHP script with the strtr() function. A brief description of it I gave below, the full one can be found here

Description of the string strtr function ( string $str , string $from , string $to ):

This function returns the string str , in which each occurrence of any character listed in from is replaced by the corresponding character from the string to . (www.php.net)

That is, in our case, the function will have the form strtr("1,1", ",", ".") and the result of its work will be: 1.1

2. Date transfer problem. In 1C, the date is of the form "DD.MM.YYYY" for example, 01.01.2009. When transmitting this kind of data, the same error will be caused as when transmitting numeric data. Of course, to solve this problem, you can use the above function NumberURL, but I decided to go the other way and converted the date to The MySQL format, that is, "YYYY-MM-DD". To do this, I wrote the MuData function, here is its listing:

Function MuDate(Str) //date in MySQL format (YYYY-MM-DD)
Return "" + DateYear(Str) + "-" +DateMonth(Str) + "-" + DateNumber(Str);
EndFunctions

The function works as follows - again, it selects Page, Year, Month, Number from the variable and arranges them in the YYYY-MM-DD format, the most familiar when working with MySQL.

That's it.

Preparation of 1C:

Now I will talk about how to make 1C work with WEB.

In order to be able to work with the HTTP protocol in 1C, I used the V7+ library, which is supplied by 1C and serves to expand the capabilities of 1C 7.7.

First, you need to connect an external component (more details here) using the "DownloadExternalComponent("v7plus.dll)" function. Next, you need to create an addin.v7httpreader object, for which I used the HTTP variable. And to send and receive data, the Get Method is used AsTroke (Address, StringFor Reception); which receives data in the Variable StringFor Reception from the address specified in the address variable. Overall, it will all look something like this:

Form() procedure
  VARIABLE StringToReceive;
LoadExternalComponent("v7plus.dll");
HTTP=CreateObject("addin.v7httpreader");
address="http://www.google.com";
HTTP. GetAsString(address, ReceiveString);
report(StringToReceive);
EndProcedure

The above procedure will display in the message box the HTML code of the main page of the site http://www.google.com

Thus, now we know how, from 1C, to turn to the desired site, and get its HTML code.

A little bit about PHP:

Now let's write a PHP script that will simply output the data obtained by the GET method. (you can see how PHP gets data here) Listing of such a script:

<?php
$text=$_GET['text']; // get the value of the text variable
print $text;
?>

Let's call it test.php and place it in the root folder of the web server. Next, to check the work of the script, enter the http://localhost/test.php?text=hello%20wolrd in the address bar of the browser In the event that everything is done correctly, a page with the text hello world will appear on the screen.

"Connect" 1C and PHP:

Next, create an external processing in 1C and insert the following code into its module:

Form() procedure
    VARIABLE StringToReceive;
LoadExternalComponent("v7plus.dll");
HTTP=CreateObject("addin.v7httpreader");
url="http://localhost/test.php?text="+text;
HTTP. GetAsString(address, ReceiveString);
Notify(StringToReceive);
EndProcedure

It differs from the algorithm that we created above only by the value in the address variable.

And so in case of successful operation of 1C and PHP-crypt in the message window 1C we will see the inscription hello world.

Now it is obvious that with the help of 1C you can transfer data to PHP scripts, and they, in turn, do with them everything that is necessary, for example, write them to the database.

Example of writing data to the database:

Writing to the MYSQL database will be done using PHP, this has been said a lot. Therefore, in order not to repeat myself, I will describe an example from my own practice.

First, let's create processing in 1C:

Put an input field on the form and assign it an identifier of the text, then slightly redo the procedure described in the section above:

 Form() procedure
    VARIABLE StringToReceive;
LoadExternalComponent("v7plus.dll");
HTTP=CreateObject("addin.v7httpreader");
address=address="http://localhost/test.php?text="+Page;
HTTP. GetAsString(address, ReceiveString);
return(StringToReceive);
EndProcedure

Now you need to create a database in MySQL (as described here). Let's create a database and give it the name TESTDB. Next, let's create a table named TEST:

CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT ,
`text` TEXT NOT NULL ,
PRIMARY KEY ( `id` ) 
);

As you can see from the query, there are only 2 fields in the table:

  • id – unique number of terms;
  • text – a text field where data from 1C will be placed.

Now you need to write a PHP script that will write data from 1C to MySQL. The listing of this script is as follows:

<?php
$host='localhost'; //server address
$db='testdb'; //database name
$user='root'; // Username
$password=''; //password to the database
$link = mysql_connect($host, $user, $password); //establish connection
mysql_select_db($db, $link); // database connections

$text=$_GET['text']; // get the value of the text variable

$query = "insert into test values (NULL, '$text')"; //generate request
mysql_query($query, $link); //send request
print mysql_error(); //report an error if there were no errors then returns an empty value.

?>

You're done! Now open The External Processing in 1C and write the word Hello! Click the "Generate" button. If everything is done correctly, then an entry with the word Hello! Otherwise, the error text will appear in the 1C message box.

Conclusion:

All the experience that I shared in this article can be used not only in Internet commerce, but also for corporate purposes, for example, in order to:

  • Consolidate data into a single MySQL database;
  • Provide reports to competent persons wherever they may be.

And this is not the whole list, because WEB-technologies are a very flexible tool with an unlimited range of possibilities, subject to their skillful use.

In general, I not only implemented a mechanism for sending data, but also receiving via HTTP, but this is a completely different story!