php_mysql: dlt - 数据库操作

php_mysql:    dlt - 数据库操作

一、数据库的建立和数据插入

　　1、数据库建立

 1 DROP DATABASE `tmp`;
 2 
 3 
 4 CREATE DATABASE IF NOT EXISTS `tmp`;
 5 
 6 
 7 USE tmp;
 8 
 9 
10 CREATE TABLE IF NOT EXISTS `dlt2023` (
11     `uid` INT NOT NULL,
12     `udate` DATE NOT NULL,
13     `f1` INT,
14     `f2` INT,
15     `f3` INT,
16     `f4` INT,
17     `f5` INT,
18     `f6` INT,
19     `f7` INT,
20     `f8` INT,
21     `f9` INT,
22     `f10` INT,
23     `f11` INT,
24     `f12` INT,
25     `f13` INT,
26     `f14` INT,
27     `f15` INT,
28     `f16` INT,
29     `f17` INT,
30     `f18` INT,
31     `f19` INT,
32     `f20` INT,
33     `f21` INT,
34     `f22` INT,
35     `f23` INT,
36     `f24` INT,
37     `f25` INT,
38     `f26` INT,
39     `f27` INT,
40     `f28` INT,
41     `f29` INT,
42     `f30` INT,
43     `f31` INT,
44     `f32` INT,
45     `f33` INT,
46     `f34` INT,
47     `f35` INT,
48     `b1` INT,
49     `b2` INT,
50     `b3` INT,
51     `b4` INT,
52     `b5` INT,
53     `b6` INT,
54     `b7` INT,
55     `b8` INT,
56     `b9` INT,
57     `b10` INT,
58     `b11` INT,
59     `b12` INT
60 );

　　2、数据插入数据库

1 INSERT INTO dlt2023 VALUES(2023000, "2023-01-01", 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,1,2,3,4,5,6,7,8,9,10,11,12);
2 
3 
4 INSERT INTO dlt2023(uid, udate, f18, f19, f24, f27, f34, b11, b12) VALUES(2023001, "2023-01-02", 18, 19, 24, 27, 34, 11, 12);
5 
6 INSERT INTO dlt2023(uid, udate, f11, f14, f21, f26, f27, b2, b7) VALUES(2023002, "2023-01-04", 11, 14, 21, 26, 27, 2, 7);
7 
8 INSERT INTO dlt2023(uid, udate, f5, f6, f14, f21, f22, b2, b9) VALUES(2023003, "2023-01-07", 5, 6, 14, 21, 22, 2, 9);

二、PHP 格式化输出dlt数据

　　1、PHP 源程序

 1 [wit@fedora mysql]$ cat    php_mysql 
 2 #!/usr/bin/php
 3 
 4 
 5 <?php
 6 
 7 
 8 
 9 
10 // filename = php_mysql
11 
12 
13 
14 
15 function test()
16 {
17         echo "hello, php!\n";
18 }
19 
20 
21 function php_mysql()
22 {
23 
24         $server = "localhost";
25         $user = "admin";
26         $passwd = "211224ln";
27         $dbname = "tmp";
28 
29 
30         try
31         {
32                 $dbh = new PDO("mysql:host=$server;dbname=$dbname", $user, $passwd);
33                 $dbh -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
34 
35                 $icount = 0;
36                 echo "\n\n";
37                 foreach ( $dbh->query('select * from dlt2023') as $row)
38                 {
39                         // echo "\n\t----  [tmp:$icount]  ----\t\n";
40                         // print_r($row);
41 
42                         // gongji 4 ge kong ge
43                         // row[i] != 0, row[i] = "1 kongge" + row[i];    row[i] == NULL, row[i] = "2 kongge".
44                         for($i=0; $i<49; $i++)
45                         {
46                                 if ( ($row[$i] != 0) and ($row[$i] < 10) )
47                                 {
48                                         $row[$i] = " "  . $row[$i];
49                                 }
50                                 else if ( $row[$i] == NULL )
51                                 {
52                                         $row[$i] = "  ";
53                                 }
54                         }
55 
56                         echo "$row[0]  $row[1]  $row[2]  $row[3]  $row[4]  $row[5]  $row[6]  $row[7]  $row[8]  $row[9]";
57                         echo "  $row[10]  $row[11]  $row[12]  $row[13]  $row[14]  $row[15]  $row[16] $row[17] $row[18]  $row[19]";
58                         echo "  $row[20]  $row[21]  $row[22]  $row[23]  $row[24]  $row[25]  $row[26] $row[27] $row[28]  $row[29]";
59                         echo "  $row[30]  $row[31]  $row[32]  $row[33]  $row[34]  $row[35]  $row[36]  --  $row[37]  $row[38]  $row[39]";
60                         echo "  $row[40]  $row[41]  $row[42]  $row[43]  $row[44]  $row[45]  $row[46]  $row[47]  $row[48]";
61                         $icount = $icount + 1;
62                         echo "\n";
63                         echo "\n";
64                 }
65                 echo "\n\n";
66 
67                 $dbh = null;
68 
69         }
70         catch(PDOException $e)
71         {
72                 die( "Error!: " . $e->getMessage() . "<br/>");
73         }
74 
75 }
76 
77 
78 
79 
80 
81 
82 // test php_mysql
83 php_mysql();
84 
85 
86 ?>
87 [wit@fedora mysql]$ 
88 [wit@fedora mysql]$ 
89 [wit@fedora mysql]$ 
90 [wit@fedora mysql]$ 

　　2、PHP 输出内容

 1 [wit@fedora mysql]$ ./php_mysql 
 2 
 3 
 4 
 5 
 6 2023000  2023-01-01   1   2   3   4   5   6   7   8   9  10  11  12  13  14  15 16 17  18  19  20  21  22  23  24  25 26 27  28  29  30  31  32  33  34  35  --   1   2   3   4   5   6   7   8   9  10  11  12
 7 
 8 2023001  2023-01-02                                                                    18  19                  24        27                          34      --                                          11  12
 9 
10 2023002  2023-01-04                                          11          14                        21                 26 27                                  --       2                   7                    
11 
12 2023003  2023-01-07                   5   6                              14                        21  22                                                    --       2                           9            
13 
14 
15 
16 [wit@fedora mysql]$ 
17 [wit@fedora mysql]$ 

三、参考文档

　　1、php_mysql:  PHP 格式化输出数据　　　　https://www.cnblogs.com/lnlidawei/p/17840034.html