dongmei9020
2014-08-20 23:04
浏览 57
已采纳

将字符串拆分为零件并加载到数据库

I am trying to write a php script which would take a string value, really long one, like:

host.canada.example.com ID: 1402944178480070 Unix hostname: ns1.example.com IP address: 232.99.185.193 MAC address: 02:10:00:2b:33:fb Description: Type: KVM Type code: kvm Status: Virtualmin Status code: virt Status changed: 15/Aug/2014 07:40 OS: CentOS Linux OS code: centos Webmin version: 1.690 Webmin OS type: exampleOS Linux Webmin OS version: 6.5 Virtualmin version: 4.09.gpl Webmin URL: https://host.canada.example.com:10000/ Created: 16/Jun/2014 14:42 Age: 65 days, 3 hours, 36:00 minutes Kernel version: Linux 2.6.32-431.17.1.el6.i686 CPU architecture: i686 Attempt SSH login: Yes SSH login: root SSH password: gxzPksT2 SSH version: 2 SSH port: 22 Webmin same as SSH: No Webmin login: root Webmin password: 5VXrAQPTw Webmin port: 10000 Webmin SSL: Yes Start at boot: Yes Boot device: c Hosted on: canada.example.com KVM process ID: 7026 KVM total disk size: 81.50 GB KVM disk driver: VirtIO KVM high resolution graphics?: No Block spoofed IPs and MACs?: No Memory limit: 2 GB Maximum CPU use: Unlimited Virtual CPUs: 6 Virtual cores: 6 Disk space used on host: 81.50 GB IO class: 0 Allowed IPs: Any Resource accounting period: One month Start of period: 01/Aug/2014 00:00 (1707538 seconds ago) End of period: 20/Aug/2014 18:15 (234 seconds ago) System uptime over period: 19 days, 18 hours, 15:04 minutes (1707304 seconds) Memory used over period: 948.50 GB hours (3666407422164992 byte seconds) Memory average over period: 2147483648 bytes Memory average while up: 2147483648 bytes CPU allocated over period: 47425.11 percent hours (170730400 percent seconds) CPU used over period: 13760.24 percent hours (49536856 percent seconds) CPU used average over period: 29 percent CPU used average while up: 29 percent Disk limit over period: 30686.78 GB hours (118618837351399424 byte seconds) Disk used over period: 30686.78 GB hours (118618837351399424 byte seconds) Disk average over period: 69477279588 bytes CPU load: 0.43 0.37 0.34 Real memory: 1.85 GB Real memory free: 712.57 MB Virtual memory: 1.50 GB Virtual memory free: 891.25 MB Disk space: 78.74 GB Disk space free: 62.39 GB Filesystem: / size 78.74 GB used 12.34 GB free 62.39 GB Filesystem: /dev/shm size 946.59 MB used 0 bytes free 946.59 MB Running services: Apache Webserver, BIND DNS Domain, Postfix Mail Domain, Dovecot IMAP / POP3 Domain, SSH Domain, MySQL Database Domain Down services: 

split it into parts and load to a MySQL database. If I output similar string putting it between <pre> and </pre> tags then I can see it is split to lines like:

anotherhost.canada.example.com
ID: 1403913554296370
Unix hostname: den.canada.example.com
IP address: 232.99.185.195
MAC address: 00:51:56:04:43:ea
Description: 
Type: KVM
Type code: kvm
Status: Virtualmin
Status code: virt
Status changed: 16/Aug/2014 15:40
OS: CentOS Linux
OS code: centos
Webmin version: 1.690
Webmin OS type: exampleOS Linux
Webmin OS version: 6.5
Virtualmin version: 4.09.gpl
Webmin URL: https://zabbix.canada.example.com:10000/
Created: 27/Jun/2014 19:59
Age: 53 days, 22 hours, 59:25 minutes
Kernel version: Linux 2.6.32-431.20.3.el6.i686
CPU architecture: i686
Attempt SSH login: Yes
SSH login: root
SSH password: 1WYKBwa8sEV
SSH version: 2
SSH port: 19759
Webmin same as SSH: Yes
Webmin port: 10000
Webmin SSL: Yes
Start at boot: Yes
Boot device: c
Hosted on: canada.example.com
KVM process ID: 12148
KVM total disk size: 11.50 GB
KVM disk driver: VirtIO
KVM high resolution graphics?: No
Block spoofed IPs and MACs?: No
Memory limit: 1.50 GB
Maximum CPU use: Unlimited
Virtual CPUs: 1
Virtual cores: 1
Disk space used on host: 11.50 GB
IO class: 0
Allowed IPs: Any
Resource accounting period: One month
Start of period: 01/Aug/2014 00:00 (1709925 seconds ago)
End of period: 20/Aug/2014 18:55 (221 seconds ago)
System uptime over period: 19 days, 18 hours, 55:04 minutes (1709704 seconds)
Memory used over period: 712.38 GB hours (2753671037190144 byte seconds)
Memory average over period: 1610612736 bytes
Memory average while up: 1610612736 bytes
CPU allocated over period: 47491.78 percent hours (170970400 percent seconds)
CPU used over period: 6806.22 percent hours (24502393 percent seconds)
CPU used average over period: 14 percent
CPU used average while up: 14 percent
Disk limit over period: 5461.55 GB hours (21111477951791104 byte seconds)
Disk used over period: 5461.55 GB hours (21111477951791104 byte seconds)
Disk average over period: 12348030976 bytes
CPU load: 0.08 0.09 0.09
Real memory: 1.48 GB
Real memory free: 445.94 MB
Virtual memory: 1.50 GB
Virtual memory free: 820.61 MB
Disk space: 9.84 GB
Disk space free: 1.63 GB
Filesystem: / size 9.84 GB used 7.71 GB free 1.63 GB
Filesystem: /dev/shm size 757.23 MB used 0 bytes free 757.23 MB
Running services: Apache Webserver, BIND DNS Domain, Postfix Mail Domain, Dovecot IMAP / POP3 Domain, SSH Domain, MySQL Database Domain
Down services: 

but it still is just a string. I guess I could turn this into array, but it is still confusing how do I do it and how to make the php string to correctly put everything into right MySQL database tables.

Edit: I have some progress. With the following piece of code:

$string = '<pre>'.$string.'</pre';
$string = split("
",$string);
$func = function($value) { return preg_split("/\s{2,}/",$value ); };
print_r(array_map($func, $string));

I've got this array:

Array ( [0] => Array ( [0] =>

host.canada.example.com )

[1] => Array
    (
        [0] => 
        [1] => ID: 1402945178280070
    )

[2] => Array
    (
        [0] => 
        [1] => Unix hostname: ns1.domain.tld
    )

[3] => Array
    (
        [0] => 
        [1] => IP address: 232.99.185.193
    )

[4] => Array
    (
        [0] => 
        [1] => MAC address: 02:00:04:2b:33:fb
    )

[5] => Array
    (
        [0] => 
        [1] => Description: 
    )

[6] => Array
    (
        [0] => 
        [1] => Type: KVM
    )

[7] => Array
    (
        [0] => 
        [1] => Type code: kvm
    )

[8] => Array
    (
        [0] => 
        [1] => Status: Virtualmin
    )

[9] => Array
    (
        [0] => 
        [1] => Status code: virt
    )

[10] => Array
    (
        [0] => 
        [1] => Status changed: 15/Aug/2014 07:40
    )

[11] => Array
    (
        [0] => 
        [1] => OS: CentOS Linux
    )

[12] => Array
    (
        [0] => 
        [1] => OS code: centos
    )

[13] => Array
    (
        [0] => 
        [1] => Webmin version: 1.690
    )

[14] => Array
    (
        [0] => 
        [1] => Webmin OS type: exampleOS Linux
    )

[15] => Array
    (
        [0] => 
        [1] => Webmin OS version: 6.5
    )

[16] => Array
    (
        [0] => 
        [1] => Virtualmin version: 4.09.gpl
    )

[17] => Array
    (
        [0] => 
        [1] => Webmin URL: https://host.canada.example.com:10000/
    )

[18] => Array
    (
        [0] => 
        [1] => Created: 16/Jun/2014 14:42
    )

[19] => Array
    (
        [0] => 
        [1] => Age: 65 days, 5 hours, 05:55 minutes
    )

[20] => Array
    (
        [0] => 
        [1] => Kernel version: Linux 2.6.32-431.17.1.el6.i686
    )

[21] => Array
    (
        [0] => 
        [1] => CPU architecture: i686
    )

[22] => Array
    (
        [0] => 
        [1] => Attempt SSH login: Yes
    )

[23] => Array
    (
        [0] => 
        [1] => SSH login: root
    )

[24] => Array
    (
        [0] => 
        [1] => SSH password: VXrAQPTw
    )

[25] => Array
    (
        [0] => 
        [1] => SSH version: 2
    )

[26] => Array
    (
        [0] => 
        [1] => SSH port: 19759
    )

[27] => Array
    (
        [0] => 
        [1] => Webmin same as SSH: No
    )

[28] => Array
    (
        [0] => 
        [1] => Webmin login: root
    )

[29] => Array
    (
        [0] => 
        [1] => Webmin password: VXrAQPTw
    )

[30] => Array
    (
        [0] => 
        [1] => Webmin port: 10000
    )

[31] => Array
    (
        [0] => 
        [1] => Webmin SSL: Yes
    )

[32] => Array
    (
        [0] => 
        [1] => Start at boot: Yes
    )

[33] => Array
    (
        [0] => 
        [1] => Boot device: c
    )

[34] => Array
    (
        [0] => 
        [1] => Hosted on: canada.example.com
    )

[35] => Array
    (
        [0] => 
        [1] => KVM process ID: 7026
    )

[36] => Array
    (
        [0] => 
        [1] => KVM total disk size: 81.50 GB
    )

[37] => Array
    (
        [0] => 
        [1] => KVM disk driver: VirtIO
    )

[38] => Array
    (
        [0] => 
        [1] => KVM high resolution graphics?: No
    )

[39] => Array
    (
        [0] => 
        [1] => Block spoofed IPs and MACs?: No
    )

[40] => Array
    (
        [0] => 
        [1] => Memory limit: 2 GB
    )

[41] => Array
    (
        [0] => 
        [1] => Maximum CPU use: Unlimited
    )

[42] => Array
    (
        [0] => 
        [1] => Virtual CPUs: 6
    )

[43] => Array
    (
        [0] => 
        [1] => Virtual cores: 6
    )

[44] => Array
    (
        [0] => 
        [1] => Disk space used on host: 81.50 GB
    )

[45] => Array
    (
        [0] => 
        [1] => IO class: 0
    )

[46] => Array
    (
        [0] => 
        [1] => Allowed IPs: Any
    )

[47] => Array
    (
        [0] => 
        [1] => Resource accounting period: One month
    )

[48] => Array
    (
        [0] => 
        [1] => Start of period: 01/Aug/2014 00:00 (1712933 seconds ago)
    )

[49] => Array
    (
        [0] => 
        [1] => End of period: 20/Aug/2014 19:45 (224 seconds ago)
    )

[50] => Array
    (
        [0] => 
        [1] => System uptime over period: 19 days, 19 hours, 45:09 minutes (1712709 seconds)
    )

[51] => Array
    (
        [0] => 
        [1] => Memory used over period: 951.50 GB hours (3678014571282432 byte seconds)
    )

[52] => Array
    (
        [0] => 
        [1] => Memory average over period: 2147483648 bytes
    )

[53] => Array
    (
        [0] => 
        [1] => Memory average while up: 2147483648 bytes
    )

[54] => Array
    (
        [0] => 
        [1] => CPU allocated over period: 47575.25 percent hours (171270900 percent seconds)
    )

[55] => Array
    (
        [0] => 
        [1] => CPU used over period: 13811.84 percent hours (49722630 percent seconds)
    )

[56] => Array
    (
        [0] => 
        [1] => CPU used average over period: 29 percent
    )

[57] => Array
    (
        [0] => 
        [1] => CPU used average while up: 29 percent
    )

[58] => Array
    (
        [0] => 
        [1] => Disk limit over period: 30809.14 GB hours (119091828677935104 byte seconds)
    )

[59] => Array
    (
        [0] => 
        [1] => Disk used over period: 30809.14 GB hours (119091828677935104 byte seconds)
    )

[60] => Array
    (
        [0] => 
        [1] => Disk average over period: 69534187464 bytes
    )

[61] => Array
    (
        [0] => 
        [1] => CPU load: 0.23 0.25 0.30
    )

[62] => Array
    (
        [0] => 
        [1] => Real memory: 1.85 GB
    )

[63] => Array
    (
        [0] => 
        [1] => Real memory free: 705.05 MB
    )

[64] => Array
    (
        [0] => 
        [1] => Virtual memory: 1.50 GB
    )

[65] => Array
    (
        [0] => 
        [1] => Virtual memory free: 892.21 MB
    )

[66] => Array
    (
        [0] => 
        [1] => Disk space: 78.74 GB
    )

[67] => Array
    (
        [0] => 
        [1] => Disk space free: 62.39 GB
    )

[68] => Array
    (
        [0] => 
        [1] => Filesystem: / size 78.74 GB used 12.35 GB free 62.39 GB
    )

[69] => Array
    (
        [0] => 
        [1] => Filesystem: /dev/shm size 946.59 MB used 0 bytes free 946.59 MB
    )

[70] => Array
    (
        [0] => 
        [1] => Running services: Apache Webserver, BIND DNS Domain, Postfix Mail Domain, Dovecot IMAP / POP3 Domain, SSH Domain, MySQL Database Domain
    )

[71] => Array
    (
        [0] => 
        [1] => Down services: 
    )

[72] => Array
    (
        [0] => 

Now, let's guess there is a MySQL database with "id", "unix hostname", "ip address", etc. tables. How can I get value parts like "1402945178280070", "ns1.domain.tld", "232.99.185.193" from the array and to put them into corresponding database tables?

Unfortunately, I can't rely to array item numbers as they constantly change. I mean this following array member:

[2] => Array
    (
        [0] => 
        [1] => Unix hostname: ns1.domain.tld

could easily become:

[3] => Array
(
    [0] => 
    [1] => Unix hostname: ns1.domain.tld

next time.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongshuang0011 2014-08-21 00:13
    已采纳

    Well, you could try something like this:

    $string   = [your original long string];
    $lines    = explode("
    ", $string); // Create initial raw array from the string at newline characters.
    $hostname = array_shift($lines); // Remove the first hostname from the first index.
    $data     = array('hostname' => $hostname); // Start the data array with the hostname first.
    
    foreach ($lines as $line)
    {
        $line = explode(': ', $line);
    
        // You can probably simplify this a LOT, but I wanted to break out what is happening
        $key  = strtolower($line[0]); // Set to lower case.
        $key  = preg_replace('/[^a-z0-9]+/', '_', $key); // Replace anything that is not a letter or a digit with an underscore.
        $key  = trim($key, '_'); // Remove leading or trailing underscores.
    
        $data[$key] = $line[1];
    }
    

    Then you at least have a $data array that looks like this:

    Array
    (
        [hostname] => anotherhost.canada.example.com
        [id] => 1403913554296370
        [unix_hostname] => den.canada.example.com
        [ip_address] => 232.99.185.195
        [mac_address] => 00:51:56:04:43:ea
        [description] => 
        [type] => KVM
        [type_code] => kvm
        [status] => Virtualmin
        [status_code] => virt
        [status_changed] => 16/Aug/2014 15:40
        [os] => CentOS Linux
        [os_code] => centos
        [webmin_version] => 1.690
        [webmin_os_type] => exampleOS Linux
        [webmin_os_version] => 6.5
        [virtualmin_version] => 4.09.gpl
        [webmin_url] => https://zabbix.canada.example.com:10000/
        [created] => 27/Jun/2014 19:59
        [age] => 53 days, 22 hours, 59:25 minutes
        [kernel_version] => Linux 2.6.32-431.20.3.el6.i686
        [cpu_architecture] => i686
        [attempt_ssh_login] => Yes
        [ssh_login] => root
        [ssh_password] => 1WYKBwa8sEV
        [ssh_version] => 2
        [ssh_port] => 19759
        [webmin_same_as_ssh] => Yes
        [webmin_port] => 10000
        [webmin_ssl] => Yes
        [start_at_boot] => Yes
        [boot_device] => c
        [hosted_on] => canada.example.com
        [kvm_process_id] => 12148
        [kvm_total_disk_size] => 11.50 GB
        [kvm_disk_driver] => VirtIO
        [kvm_high_resolution_graphics] => No
        [block_spoofed_ips_and_macs] => No
        [memory_limit] => 1.50 GB
        [maximum_cpu_use] => Unlimited
        [virtual_cpus] => 1
        [virtual_cores] => 1
        [disk_space_used_on_host] => 11.50 GB
        [io_class] => 0
        [allowed_ips] => Any
        [resource_accounting_period] => One month
        [start_of_period] => 01/Aug/2014 00:00 (1709925 seconds ago)
        [end_of_period] => 20/Aug/2014 18:55 (221 seconds ago)
        [system_uptime_over_period] => 19 days, 18 hours, 55:04 minutes (1709704 seconds)
        [memory_used_over_period] => 712.38 GB hours (2753671037190144 byte seconds)
        [memory_average_over_period] => 1610612736 bytes
        [memory_average_while_up] => 1610612736 bytes
        [cpu_allocated_over_period] => 47491.78 percent hours (170970400 percent seconds)
        [cpu_used_over_period] => 6806.22 percent hours (24502393 percent seconds)
        [cpu_used_average_over_period] => 14 percent
        [cpu_used_average_while_up] => 14 percent
        [disk_limit_over_period] => 5461.55 GB hours (21111477951791104 byte seconds)
        [disk_used_over_period] => 5461.55 GB hours (21111477951791104 byte seconds)
        [disk_average_over_period] => 12348030976 bytes
        [cpu_load] => 0.08 0.09 0.09
        [real_memory] => 1.48 GB
        [real_memory_free] => 445.94 MB
        [virtual_memory] => 1.50 GB
        [virtual_memory_free] => 820.61 MB
        [disk_space] => 9.84 GB
        [disk_space_free] => 1.63 GB
        [filesystem] => /dev/shm size 757.23 MB used 0 bytes free 757.23 MB
        [running_services] => Apache Webserver, BIND DNS Domain, Postfix Mail Domain, Dovecot IMAP / POP3 Domain, SSH Domain, MySQL Database Domain
        [down_services] => 
    )
    

    You could then you could build an INSERT statement from there. It would require a field for every every key in the array though. If the database fields did not match exactly with the slugs (keys) in the array, you'd have to map them to something else.

    点赞 打赏 评论

相关推荐 更多相似问题