PHPExcel

Posted on 2017-06-02 by hoyo

PHPExcel

使用 PHP 處理 Excel 格式檔案

—

讀取 Excel 檔案

<?php
require_once WEB_ROOT .'/Component_Back/PHPExcel-1.8.1/Classes/PHPExcel/IOFactory.php';

// 讓PHPExcel決定使用哪種格式讀取Excel
$excel= \PHPExcel_IOFactory::load($_FILES['File']['tmp_name']);

// 抓取第一個Sheet
$sheet = $excel->getSheet(0);

// 讀出資料變成陣列
$rowData = $sheet->toArray();

// 資料筆數
$Count = count($rowData);

for ($i = 1; $i<$Count; $i++) {

    // 顯示 A 欄資料
    echo $rowData[$i][0];
    
    $j++;
}

<?php

require_once WEB_ROOT .'/Component_Back/PHPExcel-1.8.1/Classes/PHPExcel/IOFactory.php';

// 讓PHPExcel決定使用哪種格式讀取Excel

$excel= \PHPExcel_IOFactory::load($_FILES['File']['tmp_name']);

// 抓取第一個Sheet

$sheet = $excel->getSheet(0);

// 讀出資料變成陣列

$rowData = $sheet->toArray();

// 資料筆數

$Count = count($rowData);

for ($i = 1; $i<$Count; $i++) {

// 顯示 A 欄資料

echo $rowData[$i][0];

$j++;

}

—

產生 Excel

18. 憑空生出Excel檔

require_once( OS_PATH .'/Component_Back/PHPExcel_1.8.0/Classes/PHPExcel.php' );
$objPHPExcel= new PHPExcel();

// A1 儲存格填入 123
$objPHPExcel->getActiveSheet()->setCellValue('A1', 123);
// B1 填入字串
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Test value');
// C1 儲存格設定小數點兩位百分比格式
$objPHPExcel->getActiveSheet()->getStyle('C1')->getNumberFormat()->setFormatCode('0.00%');

// 寫入儲存
$objWriter = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');
$objWriter->save( sys_get_temp_dir() .'/Report_'.$_GET['Instrument_DATA_id'].'.xls' );

require_once( OS_PATH .'/Component_Back/PHPExcel_1.8.0/Classes/PHPExcel.php' );

$objPHPExcel= new PHPExcel();

// A1 儲存格填入 123

$objPHPExcel->getActiveSheet()->setCellValue('A1', 123);

// B1 填入字串

$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Test value');

// C1 儲存格設定小數點兩位百分比格式

$objPHPExcel->getActiveSheet()->getStyle('C1')->getNumberFormat()->setFormatCode('0.00%');

// 寫入儲存

$objWriter = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');

$objWriter->save( sys_get_temp_dir() .'/Report_'.$_GET['Instrument_DATA_id'].'.xls' );

儲存格格式 PHPExcel_Style_NumberFormat

範例 2

<?php
require_once WEB_ROOT .'/Component_Back/PHPExcel-1.8.1/Classes/PHPExcel/IOFactory.php';

//
$objPHPExcel = new \PHPExcel();  //實體化Excel

$objPHPExcel->setActiveSheetIndex(0);  //設定預設顯示的工作表
$objActSheet = $objPHPExcel->getActiveSheet(); //指定預設工作表為 $objActSheet
$objActSheet->setTitle("wwww");  //設定標題
//$objPHPExcel->createSheet(); //建立新的工作表，上面那三行再來一次，編號要改

//

$objActSheet->setCellValue('A1', 'xx');
$objActSheet->setCellValue('B1', 'yyy');
$objActSheet->setCellValue('C1', 'zzz');

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='. $Filename .'aaa.xls');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

<?php

require_once WEB_ROOT .'/Component_Back/PHPExcel-1.8.1/Classes/PHPExcel/IOFactory.php';

$objPHPExcel = new \PHPExcel(); //實體化Excel

$objPHPExcel->setActiveSheetIndex(0); //設定預設顯示的工作表

$objActSheet = $objPHPExcel->getActiveSheet(); //指定預設工作表為 $objActSheet

$objActSheet->setTitle("wwww"); //設定標題

//$objPHPExcel->createSheet(); //建立新的工作表，上面那三行再來一次，編號要改

$objActSheet->setCellValue('A1', 'xx');

$objActSheet->setCellValue('B1', 'yyy');

$objActSheet->setCellValue('C1', 'zzz');

header('Content-Type: application/vnd.ms-excel');

header('Content-Disposition: attachment;filename='. $Filename .'aaa.xls');

header('Cache-Control: max-age=0');

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');

—

Fatal error: Allowed memory size

在寫入大量資料時，出現了 Fatal error: Allowed memory size 的錯誤，解決方法不是把 PHP 的 Memory Limit 加大，而是加上 PHPExcel 的記憶體控制

Allowed memory size of 33554432 bytes exhausted (tried to allocate 43148176 bytes) in php

<?php
set_time_limit(0);
ini_set("memory_limit", -1);

// 記憶體控制
$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;
$cacheSettings = array( 'memoryCacheSize' => '1MB');
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

//
$objPHPExcel = new \PHPExcel();  //實體化Excel

<?php

set_time_limit(0);

ini_set("memory_limit", -1);

// 記憶體控制

$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

$cacheSettings = array( 'memoryCacheSize' => '1MB');

\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

$objPHPExcel = new \PHPExcel(); //實體化Excel

—

指定欄位格式

Correct format for strings / numbers beginning with zero?

$objActSheet->setCellValueExplicit('A'.$i, $row['Name'], \PHPExcel_Cell_DataType::TYPE_STRING);

1	$objActSheet->setCellValueExplicit('A'.$i, $row['Name'], \PHPExcel_Cell_DataType::TYPE_STRING);

要注意 setCellValue 修改成 setCellValueExplicit

—

Fatal error: Class ‘XMLWriter’ not found

補一個 php-xml 就好了

yum install php-xml

1	yum install php-xml

—

hinet hichannel 取得 index.m3u8 播放路徑

Posted on 2017-02-08 by hoyo

先看一下可播放的實際案例網址

http://radio-hichannel.cdn.hinet.net/live/pool/hich-ra000073/ra-hls/index.m3u8?token1=ICzRSfp8Qy6wVuIDVIlOLg&token2=cSVWYP2kBp49hiIha41Y-A&expire1=1462880161&expire2=1462908961

1	http://radio-hichannel.cdn.hinet.net/live/pool/hich-ra000073/ra-hls/index.m3u8?token1=ICzRSfp8Qy6wVuIDVIlOLg&token2=cSVWYP2kBp49hiIha41Y-A&expire1=1462880161&expire2=1462908961

解讀

ra-000073 是頻道的代號名稱，ra-000073 是 NEWS98 ，KissRadio 是 ra-000042
index.m3u8 表示使用 HTTP HLS 串流技術
expire1 & expire2 表示可以執行播放的時間區間，你拿一個早上八點到下午四點的播放網址在晚上執行是不能播放的
token1 & token2 和 expire 是相呼應的，意思就是如果 expire 的數值為 1000 算出 token 是 ABC999 ，那一樣的執行環境算出的 token 就會相同

—

參考資料

不用開hichannel網頁也能聽中廣流行網 · GitHub

#!/usr/bin/env python
import time
import base64
import hashlib
import urllib
import urlparse
import urllib2
import subprocess
from collections import OrderedDict

def gen_token(path, timestamp, ip_addr, token_ord):
    const_str = 'radio@himediaservice#t'
    cat_str = path + str(timestamp) + ip_addr + const_str + str(token_ord)
    hashed = hashlib.md5(unicode(cat_str)).digest()
    b64_md5hash = base64.b64encode(hashed)
    return b64_md5hash.replace('+', '-').replace('/', '_').replace('=', '')

def build_url():
    base_url = 'http://radio-hichannel.cdn.hinet.net/'
    ip_addr = urllib2.urlopen('http://ipinfo.io/ip').read().rstrip()
    path = '/live/pool/hich-ra000009/ra-hls/'
    expire1 = int(time.time())
    expire2 = expire1 + (60 * 60 * 8)
    params = OrderedDict([
        ('token1', gen_token(path, expire1, ip_addr, 1)),
        ('token2', gen_token(path, expire2, ip_addr, 2)),
        ('expire1', expire1),
        ('expire2', expire2)
    ])
    params_str = urllib.urlencode(params)
    return urlparse.urljoin(base_url, path + 'index.m3u8') + '?' + params_str

def check_url_alive(url):
    try:
        urllib2.urlopen(url)
        return True
    except urllib2.HTTPError:
        return False

def ffplay(source):
    _prefix_url = "ffmpeg://{}".format(source)
    print "HIT RETURN TO QUIT\n"
    subprocess.call(['mplayer', '-msglevel', 'all=-1',
                     '-cache', '1024', _prefix_url])

def main():
    is_alive = None
    while is_alive != True:
        url = build_url()
        is_alive = check_url_alive(url)

    try:
        ffplay(url)
    except KeyboardInterrupt:
        exit()

if __name__ == '__main__':
    main()

#!/usr/bin/env python

import time

import base64

import hashlib

import urllib

import urlparse

import urllib2

import subprocess

from collections import OrderedDict

def gen_token(path, timestamp, ip_addr, token_ord):

const_str = 'radio@himediaservice#t'

cat_str = path + str(timestamp) + ip_addr + const_str + str(token_ord)

hashed = hashlib.md5(unicode(cat_str)).digest()

b64_md5hash = base64.b64encode(hashed)

return b64_md5hash.replace('+', '-').replace('/', '_').replace('=', '')

def build_url():

base_url = 'http://radio-hichannel.cdn.hinet.net/'

ip_addr = urllib2.urlopen('http://ipinfo.io/ip').read().rstrip()

path = '/live/pool/hich-ra000009/ra-hls/'

expire1 = int(time.time())

expire2 = expire1 + (60 * 60 * 8)

params = OrderedDict([

('token1', gen_token(path, expire1, ip_addr, 1)),

('token2', gen_token(path, expire2, ip_addr, 2)),

('expire1', expire1),

('expire2', expire2)

])

params_str = urllib.urlencode(params)

return urlparse.urljoin(base_url, path + 'index.m3u8') + '?' + params_str

def check_url_alive(url):

try:

urllib2.urlopen(url)

return True

except urllib2.HTTPError:

return False

def ffplay(source):

_prefix_url = "ffmpeg://{}".format(source)

print "HIT RETURN TO QUIT\n"

subprocess.call(['mplayer', '-msglevel', 'all=-1',

'-cache', '1024', _prefix_url])

def main():

is_alive = None

while is_alive != True:

url = build_url()

is_alive = check_url_alive(url)

try:

ffplay(url)

except KeyboardInterrupt:

exit()

if __name__ == '__main__':

main()

寫 PHP 的人轉到 python 會遇到的問題有

結束不用加分號
\t 和空白是坑爹的不一樣
縮行不是爽縮就可以縮
相同功能的 function 預設的輸出和 PHP 不同

好好研究一下網頁是如何運作，追到了 radio_proxy.swf 這個檔案，網頁的播放最後就是由這個 Flash 檔案生成播放路徑，這時又要尋找「反組譯」黑科技

可惜的是，閃客 Sothink SWF Decompile 解不開，大概也有反制黑科技的技術吧

還好這種東西有線上版可以用！？…

Show My Code | Flash decompiler

不知道當時哪來的腦洞想到上網找 online 版，不過結果是美好的，至少是解開有看到類似 ActionScript 的輸出了。

下面是有關產生 token 的片段程式

public function buildPlayUrl(clientIP:String, url:String):String{
    if ((clientIP == null)){
    } else {
        (clientIP == null);
    };
    if ((clientIP == null)){
        return ("null ip or url");
    };
    var pos:int = url.lastIndexOf("/");
    if (pos >= 0){
    } else {
        throw (new ArgumentError(("error stream url format:" + url)));
    };
    this.debug(("build token => client ip:" + clientIP));
    var len:int = url.length;
    var location:String = url.substr((pos + 1), len).toLowerCase();
    var path:String = (("/live/pool/hich-" + location) + "/ra-hls/");
    var now:Date = new Date();
    now.time = (now.time + ((1000 * 60) * 5));
    var expire1:String = this.toSecTimeStr(now.time);
    now.time = (now.time + (((1000 * 60) * 60) * 8));
    var expire2:String = this.toSecTimeStr(now.time);
    var key1:String = (this.KEYS[0] + "1");
    var key2:String = (this.KEYS[0] + "2");
    var token1:String = this.buildToken(path, expire1.toString(), clientIP, key1);
    var token2:String = this.buildToken(path, expire2.toString(), clientIP, key2);
    var mp4Url:String = ((this.DOMAIN[0] + path) + "index.m3u8");
    return (StringUtil.substitute("{0}?token1={1}&token2={2}&expire1={3}&expire2={4}", mp4Url, token1, token2, expire1.toString(), expire2.toString()));
}

private function toSecTimeStr(timeMillis:Number):String{
    var secs:Number = int((timeMillis / 1000));
    return (secs.toString());
}

private function buildToken(path:String, expire:String, ip:String, key:String):String{
    var sec:String = (((path + expire) + ip) + key);
    var bytes:ByteArray = new ByteArray();
    bytes.writeUTFBytes(sec);
    var md5:MD5 = new MD5();
    var hash:ByteArray = md5.hash(bytes);
    var token:String = Base64.encodeByteArray(hash);
    var pat1:RegExp = /\+/gi;
    var pat2:RegExp = /\//gi;
    var pat3:RegExp = /=/gi;
    token = token.replace(pat1, "-").replace(pat2, "_").replace(pat3, "");
    return (token);
}

public function buildPlayUrl(clientIP:String, url:String):String{

if ((clientIP == null)){

} else {

(clientIP == null);

};

if ((clientIP == null)){

return ("null ip or url");

};

var pos:int = url.lastIndexOf("/");

if (pos >= 0){

} else {

throw (new ArgumentError(("error stream url format:" + url)));

};

this.debug(("build token => client ip:" + clientIP));

var len:int = url.length;

var location:String = url.substr((pos + 1), len).toLowerCase();

var path:String = (("/live/pool/hich-" + location) + "/ra-hls/");

var now:Date = new Date();

now.time = (now.time + ((1000 * 60) * 5));

var expire1:String = this.toSecTimeStr(now.time);

now.time = (now.time + (((1000 * 60) * 60) * 8));

var expire2:String = this.toSecTimeStr(now.time);

var key1:String = (this.KEYS[0] + "1");

var key2:String = (this.KEYS[0] + "2");

var token1:String = this.buildToken(path, expire1.toString(), clientIP, key1);

var token2:String = this.buildToken(path, expire2.toString(), clientIP, key2);

var mp4Url:String = ((this.DOMAIN[0] + path) + "index.m3u8");

return (StringUtil.substitute("{0}?token1={1}&token2={2}&expire1={3}&expire2={4}", mp4Url, token1, token2, expire1.toString(), expire2.toString()));

}

private function toSecTimeStr(timeMillis:Number):String{

var secs:Number = int((timeMillis / 1000));

return (secs.toString());

}

private function buildToken(path:String, expire:String, ip:String, key:String):String{

var sec:String = (((path + expire) + ip) + key);

var bytes:ByteArray = new ByteArray();

bytes.writeUTFBytes(sec);

var md5:MD5 = new MD5();

var hash:ByteArray = md5.hash(bytes);

var token:String = Base64.encodeByteArray(hash);

var pat1:RegExp = /\+/gi;

var pat2:RegExp = /\//gi;

var pat3:RegExp = /=/gi;

token = token.replace(pat1, "-").replace(pat2, "_").replace(pat3, "");

return (token);

}

基本上和 python 架構是一樣的，只是反組譯有少東西，最關鍵的 KEYS 沒有解開，只好回頭繼續研究 python

後來證實 python 有關 token 的演算法是正確的

—

Token 演算法

token1 字串組成

path + expire timestamp + IP + Key + 1

1	path + expire timestamp + IP + Key + 1

token2 字串組成

path + expire timestamp + IP + Key + 2

1	path + expire timestamp + IP + Key + 2

token1 範例，token2 只要將最後的 1 改成 2 即可

/live/pool/hich-ra000073/ra-hls/146295177759.125.207.208radio@himediaservice#t1

1	/live/pool/hich-ra000073/ra-hls/146295177759.125.207.208radio@himediaservice#t1

然後拿去 md5() 再跑 base64_encode() 就可以得到 Token 了

—

index.m3u8 內容

http://radio-hichannel.cdn.hinet.net/live/pool/hich-ra000036/ra-hls/index.m3u8?token1=IKGH3KBN_Fy5_mAvsgaibQ&token2=Af9RZuEDZ_yK9TIBmIuFTw&expire1=1462943429&expire2=1462972229

#EXTM3U
#EXT-X-VERSION:3
#EXT-X-STREAM-INF:PROGRAM-ID=1,BANDWIDTH=135680,CODECS="avc1.100.30,mp4a.40.2",RESOLUTION=362x242
hich-ra000036-video=0-audio=128000.m3u8?token1=IKGH3KBN_Fy5_mAvsgaibQ&token2=Af9RZuEDZ_yK9TIBmIuFTw&expire1=1462943429&expire2=1462972229

http://radio-hichannel.cdn.hinet.net/live/pool/hich-ra000036/ra-hls/index.m3u8?token1=IKGH3KBN_Fy5_mAvsgaibQ&token2=Af9RZuEDZ_yK9TIBmIuFTw&expire1=1462943429&expire2=1462972229

#EXTM3U

#EXT-X-VERSION:3

#EXT-X-STREAM-INF:PROGRAM-ID=1,BANDWIDTH=135680,CODECS="avc1.100.30,mp4a.40.2",RESOLUTION=362x242

hich-ra000036-video=0-audio=128000.m3u8?token1=IKGH3KBN_Fy5_mAvsgaibQ&token2=Af9RZuEDZ_yK9TIBmIuFTw&expire1=1462943429&expire2=1462972229

—

PHP 程式碼

<?php
header("Content-Type: text/html; charset=utf-8");
date_default_timezone_set("Asia/Taipei");
set_time_limit(0);
ini_set('error_reporting',E_ALL & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED);

function gen_token( $path, $timestamp, $ip_addr, $token_ord ) {
    $const_str = 'radio@himediaservice#t';
    $cat_str = $path . $timestamp . $ip_addr . $const_str . $token_ord;
    $hashed = md5($cat_str, true);
    $b64_md5hash = base64_encode($hashed);
    return str_replace( '=', '', str_replace( '/', '_', str_replace( '+', '-', $b64_md5hash)));
}

$path = '/live/pool/hich-ra000073/ra-hls/';
$expire1 = time()+1200;
//$expire1 = 1462951777;
$expire2 = $expire1 + (60 * 60 * 8);
//$expire2 = 1462980577;
$ip_addr = '執行環境對外的 IP'; // 123.1.2.3

$token1 = gen_token($path, $expire1, $ip_addr, 1);
$token2 = gen_token($path, $expire2, $ip_addr, 2);

$m3u8 = 'http://radio-hichannel.cdn.hinet.net/live/pool/hich-ra000073/ra-hls/index.m3u8?token1='. $token1 .'&token2=' . $token2 . '&expire1='. $expire1 .'&expire2='. $expire2;
//echo $m3u8;

$Filename = date('YmdHi00');
exec('/usr/bin/ffmpeg -i "'. $m3u8 .'" -t 01:00:00 /tmp/mp3/'. $Filename .'.mp3 &');

<?php

header("Content-Type: text/html; charset=utf-8");

date_default_timezone_set("Asia/Taipei");

set_time_limit(0);

ini_set('error_reporting',E_ALL & ~E_NOTICE & ~E_STRICT & ~E_DEPRECATED);

function gen_token( $path, $timestamp, $ip_addr, $token_ord ) {

$const_str = 'radio@himediaservice#t';

$cat_str = $path . $timestamp . $ip_addr . $const_str . $token_ord;

$hashed = md5($cat_str, true);

$b64_md5hash = base64_encode($hashed);

return str_replace( '=', '', str_replace( '/', '_', str_replace( '+', '-', $b64_md5hash)));

}

$path = '/live/pool/hich-ra000073/ra-hls/';

$expire1 = time()+1200;

//$expire1 = 1462951777;

$expire2 = $expire1 + (60 * 60 * 8);

//$expire2 = 1462980577;

$ip_addr = '執行環境對外的 IP'; // 123.1.2.3

$token1 = gen_token($path, $expire1, $ip_addr, 1);

$token2 = gen_token($path, $expire2, $ip_addr, 2);

$m3u8 = 'http://radio-hichannel.cdn.hinet.net/live/pool/hich-ra000073/ra-hls/index.m3u8?token1='. $token1 .'&token2=' . $token2 . '&expire1='. $expire1 .'&expire2='. $expire2;

//echo $m3u8;

$Filename = date('YmdHi00');

exec('/usr/bin/ffmpeg -i "'. $m3u8 .'" -t 01:00:00 /tmp/mp3/'. $Filename .'.mp3 &');

—

讓 PHP 等待系統指令執行完成後才繼續

Posted on 2017-01-09 by hoyo

一般使用 ffmpeg 程式時，當執行 ffmpeg 之後，程式不管轉檔是否完成都會繼續往下執行，如果需要使用轉檔完成的檔案進行後續處理就會發生錯誤。

這時只要根據下面程式思路修改即可

參考

結合這兩個程式思路，自行修改為下列程式

<?php
$cmdMP3 = ' ffmpeg -i '. $_FILES['SoundFile']['tmp_name'] .' -acodec mp3 -b:a 196k -vn -y "'. $_FILES['SoundFile']['tmp_name'] .'.mp3" ';
$handle = shell_exec($cmdMP3);

// 迴圈等待轉檔處理完成
while($handle == true) {
    break;
}

// ffmpeg 轉檔處理完成才會往下執行

<?php

$cmdMP3 = ' ffmpeg -i '. $_FILES['SoundFile']['tmp_name'] .' -acodec mp3 -b:a 196k -vn -y "'. $_FILES['SoundFile']['tmp_name'] .'.mp3" ';

$handle = shell_exec($cmdMP3);

// 迴圈等待轉檔處理完成

while($handle == true) {

break;

}

// ffmpeg 轉檔處理完成才會往下執行

推薦在聲音轉檔才使用，影片因為轉檔時間需要更久，應該會遇到 HTTP Server Timeout 逾時的問題。

—

PHP preg_match 取不到值

Posted on 2016-12-28 by hoyo

參考

使用 PHPWord 的 replaceBlock() ，明明是正確的搜尋條件，在搜尋字串相對較短時可以正常運作，完整字串時卻無法取得結果。

搞超久才想到是否是字串太長導致，最後證實確實有長度限制，可是 PHP 卻「完全」不給任何警告或錯誤訊息 …

<?php
ini_set('pcre.backtrack_limit', 2000000);

1 2	<?php ini_set('pcre.backtrack_limit', 2000000);

—

Joomla 1.5 使用者第三方 PHP 管理

Posted on 2016-12-27 by hoyo

批次新增

參考

How can I create a new Joomla user account from within a script?

INSERT INTO jos_users( `name`, `username`, `password`, `email`, `usertype`, `gid` )
VALUES( 'Иванов Иван', 'ivanov', md5('12345'), 'ivanov@mail.ru', 'Registered', 18 );

INSERT INTO jos_core_acl_aro( `section_value`, `value` )
VALUES ( 'users', LAST_INSERT_ID() );

INSERT INTO jos_core_acl_groups_aro_map( `group_id`, `aro_id` )
VALUES ( 18, LAST_INSERT_ID() );

INSERT INTO jos_users( `name`, `username`, `password`, `email`, `usertype`, `gid` )

VALUES( 'Иванов Иван', 'ivanov', md5('12345'), 'ivanov@mail.ru', 'Registered', 18 );

INSERT INTO jos_core_acl_aro( `section_value`, `value` )

VALUES ( 'users', LAST_INSERT_ID() );

INSERT INTO jos_core_acl_groups_aro_map( `group_id`, `aro_id` )

VALUES ( 18, LAST_INSERT_ID() );

<?php
require_once ("extra01.php");

for( $i=1; $i<=100; $i++ ) {
    
    $password = rand( 234567, 891234 );
    
    $sql = "INSERT INTO jos_users( `name`, `username`, `password`, `email`, `usertype`, `gid`, registerDate )
VALUES( '外包{$i}', 'out{$i}', md5('{$password}'), 'out{$i}@sly-ha.com.tw', 'Registered', 18, NOW() );";
    $pre = $PDO->prepare($sql);
    $pre->execute();
    
    $userID = $PDO->lastInsertId();
    
    $sql = "INSERT INTO jos_core_acl_aro( `section_value`, `value` )
VALUES ( 'users', {$userID} );";
    $pre = $PDO->prepare($sql);
    $pre->execute();

    $sql = " INSERT INTO jos_core_acl_groups_aro_map( `group_id`, `aro_id` )
VALUES ( 18, {$PDO->lastInsertId()} ); ";
    $PDO->query($sql);
    
    $sql = " INSERT INTO jos_comprofiler ( id, user_id ) VALUES ( {$userID}, {$userID} ) ";
    $PDO->query($sql);

    echo 'out'.$i.' : '.$password.'<br/>';
}

<?php

require_once ("extra01.php");

for( $i=1; $i<=100; $i++ ) {

$password = rand( 234567, 891234 );

$sql = "INSERT INTO jos_users( `name`, `username`, `password`, `email`, `usertype`, `gid`, registerDate )

VALUES( '外包{$i}', 'out{$i}', md5('{$password}'), 'out{$i}@sly-ha.com.tw', 'Registered', 18, NOW() );";

$pre = $PDO->prepare($sql);

$pre->execute();

$userID = $PDO->lastInsertId();

$sql = "INSERT INTO jos_core_acl_aro( `section_value`, `value` )

VALUES ( 'users', {$userID} );";

$pre = $PDO->prepare($sql);

$pre->execute();

$sql = " INSERT INTO jos_core_acl_groups_aro_map( `group_id`, `aro_id` )

VALUES ( 18, {$PDO->lastInsertId()} ); ";

$PDO->query($sql);

$sql = " INSERT INTO jos_comprofiler ( id, user_id ) VALUES ( {$userID}, {$userID} ) ";

$PDO->query($sql);

echo 'out'.$i.' : '.$password.'<br/>';

}

—

使用者登入檢查

Joomla 的密碼使用 md5() 加密，登入後或是由系統建立的使用者會有另一個方式儲存密碼

例如密碼是 123456 ，原來的 md5() 是 e10adc3949ba59abbe56e057f20f883e

Joomla 會亂數產生一個 salt 值來和原密碼連結後，重新 md5() 來當作最後密碼

例如產生了 334u9rih5apoHuW0DqrVICIUvXy3Vsau 的 salt 亂數值之後，密碼就是 md(‘123456334u9rih5apoHuW0DqrVICIUvXy3Vsau’) = b6a8e243b8e54fe5a55e33fde056c09f

資料庫 password 欄位就是 b6a8e243b8e54fe5a55e33fde056c09f:334u9rih5apoHuW0DqrVICIUvXy3Vsau

—

PHP 的 SQL Server 存取及語法使用備註

Posted on 2016-11-14 by hoyo

使用自訂 repo 的方式無法順利安裝 php-gd 等待測試更新

—

參考

CentOS6／CentOS7にPHP5.6／PHP7をyumでインストール

—

安裝 php-mssql

yum install php-mssql

1	yum install php-mssql

—

PHP 範例程式

<?php
$link = mssql_connect('Server IP', 'Username', 'Password');
if (!$link)
    die('error');
else
    echo('ok');

$sql = " SELECT * FROM dbo.batch_id ";
$query = mssql_query($sql);
$row = mssql_fetch_array($query);

print_r($row);

<?php

$link = mssql_connect('Server IP', 'Username', 'Password');

if (!$link)

die('error');

else

echo('ok');

$sql = " SELECT * FROM dbo.batch_id ";

$query = mssql_query($sql);

$row = mssql_fetch_array($query);

print_r($row);

<?php
$dsn = 'dblib:host=Server IP;dbname=Database Name;';
$user = 'Username';
$password = 'Password';

// 建立 PDO 物件
$pdo = new PDO($dsn, $user, $password);

$sql = " SELECT * FROM database.dbo.table ";
$pre = $pdo->prepare($sql);
$pre->execute();
$row = $pre->fetchAll(2);

print_r($row);

<?php

$dsn = 'dblib:host=Server IP;dbname=Database Name;';

$user = 'Username';

$password = 'Password';

// 建立 PDO 物件

$pdo = new PDO($dsn, $user, $password);

$sql = " SELECT * FROM database.dbo.table ";

$pre = $pdo->prepare($sql);

$pre->execute();

$row = $pre->fetchAll(2);

print_r($row);

—

UTF-8 設定

參考

vi /etc/freetds.conf

[global]
    # TDS protocol version
    tds version = 7.0

    client charset = UTF-8

[global]

# TDS protocol version

tds version = 7.0

client charset = UTF-8

—

[root@localhost:~] # tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v0.91
             freetds.conf directory: /etc
     MS db-lib source compatibility: yes
        Sybase binary compatibility: yes
                      Thread safety: yes
                      iconv library: yes
                        TDS version: 4.2
                              iODBC: no
                           unixodbc: yes
              SSPI "trusted" logins: no
                           Kerberos: yes

[root@localhost:~] # tsql -C

Compile-time settings (established with the "configure" script)

Version: freetds v0.91

freetds.conf directory: /etc

MS db-lib source compatibility: yes

Sybase binary compatibility: yes

Thread safety: yes

iconv library: yes

TDS version: 4.2

iODBC: no

unixodbc: yes

SSPI "trusted" logins: no

Kerberos: yes

—

MS SQL Server 語法

INSERT INTO table ( [column] ) VALUES ( 'column' )

1	INSERT INTO table ( [column] ) VALUES ( 'column' )

這是一個很簡單的範例，SQL Server 的欄位名稱以 [] 方括弧包住，值也是使用 ‘ 單引號

—

hoyo 學習紀錄

PHP 網頁應用 IOT 物聯網自動化

Category Archives: PHP

PHPExcel

PHPExcel

讀取 Excel 檔案

產生 Excel

Fatal error: Allowed memory size

指定欄位格式

Fatal error: Class ‘XMLWriter’ not found

hinet hichannel 取得 index.m3u8 播放路徑

Token 演算法

PHP 程式碼

讓 PHP 等待系統指令執行完成後才繼續

PHP preg_match 取不到值

Joomla 1.5 使用者第三方 PHP 管理

批次新增

使用者登入檢查

PHP 的 SQL Server 存取及語法使用備註

安裝 php-mssql

PHP 範例程式

UTF-8 設定

MS SQL Server 語法