使用PHP來連接到SteemSQL資料庫 Using PHP to connect to the SteemSQL database

最近看到O哥 @oflyhigh提到使用PHP來查詢区块链，之後O哥又提到使用pymssql來查詢 @arcange所創之SteemSQL資料庫，這兩篇文章給我了靈感想到要用PHP來連接到這個SteemSQL資料庫。

Recently I saw @oflyhigh's post on using PHP to query the Steemit blockchain, he then discussed using pymssql to query the SteemSQL database made by @arcange. These two articles gave me the idea to use PHP to query the SteemSQL database.

我是用Namecheap這一家網站託管服務。這一家公司對我實在不錯，十分穩定， @yuxi之前也有提到這家公司的域名管理服务好用。

I use Namecheap as my web host. They have been very good to me, very stable. @yuxi also talked about this company having a good domain name administration service.

我剛開始使用在steemit.chat的steemsql頻道上找到的PHP代碼，貼到我的網站上的.php檔案裡面，竟然不能用。我跟NameCheap的服務員聊了一陣，又在steemit.chat上詢問 @arcange, 感謝他們幫我解決了問題。在這裡把問題與解決辦法分享給大家，以免太多人找 @arcange問同樣問題。

I started off with using some PHP code I retrieved off steemit.chat's steemsql channel. I copied and pasted the code into my .php file, but it did not work. I talked to NameCheap's technichian for a while as well as enquired @arcange on steemit.chat, and thanks to them the problem was solved. I thought I would share the problems and solutions here, so not everyone has to track down @arcange to ask the same thing.

SteemSQL的資料管理系統不是mysql，而是mssql (Microsoft SQL). 如果要用PHP來連接到mssql資料庫，需要打開一個擴展程序。請看以下圖。

SteemSQL's database management system is not MySQL, but it is MsSQL (Microsoft SQL). In order to connect to MsSQL with PHP, you need to turn on an extension. Follow the pictures below.

首先，登入網站控制台Cpanel之後，選擇Select PHP Version.

Firstly, after logging into the Cpanel website hosting control panel, click "Select PHP Version".

再來，如果沒有看到所有的php擴展程序清單，請按右邊的 "Switch to PHP Extensions".

And then, if you can't see the checklist for all PHP extensions, please click on the right "Switch to PHP Extensions".

然後，再把mssql旁的方塊勾起來就好了。

Finally, click the checkbox next to mssql.

接下來，我先告訴大家用PHP連接到SteemSQL 資料庫的代碼。

Now, I will show you the PHP code for connecting to the SteemSQL database.


$connection = mssql_connect("sql.steemsql.com:1433", "steemit", "steemit") or die('Could not connect to the server!');
mssql_select_db('DBSteem') or die('Could not select a database');


您可以從代碼裡看到我們需要連到1433端口。Namecheap封鎖了到這個端口的聯繫，所以必須要跟NameCheap的工作人員說要打開這個端口才行。跟他們說: "Could you please unblock outgoing traffic to port 1433". 看以下圖顯示如何跟他們談話。

You can see from the code that we need to connect to port 1433. Namecheap has blocked outgoing traffic to port 1433, so you will need to tell them to open it. Say this to them: "Could you please unblock outgoing traffic to port 1433". See picture below on how to live chat with them.

這件事辦成後就容易了。建立一個新的.php檔案，把以下的代碼打進去，再用浏览器打開。

From now on it's a lot easier. Create a new .php file, type in the code below, and open it with a browser.


$connection = mssql_connect("sql.steemsql.com:1433", "steemit", "steemit") or die('Could not connect to the server!');
mssql_select_db('DBSteem') or die('Could not select a database');
$SQL = "SELECT id FROM Accounts WHERE Id='116327'";
$result = mssql_query($SQL) or die('A error occured: ' . mysql_error());
$row = mssql_fetch_array($result);
echo $row[0];
mssql_close($connection);


你的網頁裡顯示的是116327 嗎? 如果是，就成功的連接上資料庫了。116327是在Accounts表裡的一個id數據。

Does your webspage say 116327? If so, you have successfully connected to the database. 116327 the id of a record in the Accounts table.

我把這個php檔原始碼放在Github, 讓大家更容易的下載。

I put the source of this php file on Github for people to obtain easily as well.

我建議用 @joythewanderer的 文章裡介紹的Linqpad來測試SQL能不能用，能用了再打到PHP裡面。

I recommend using @joythewanderer's article on using Linqpad to connect to the steemSQL database to test your queries, before using PHP to execute them.

如果喜歡這篇文章，請關注魔術僧 @magicmonk
If you liked this article, follow the Magic Monk at @magicmonk.