zwLUAbSv

1<?php
2
3function connect() {
4  $username = 'callmemoo';
5  $password = '';
6  $mysqlhost = '127.0.0.1';
7  $dbname = $username;
8  $pdo = new PDO('mysql:host='.$mysqlhost.';dbname='.$dbname.';charset=utf8', $username, $password);
9  if ( $pdo) {
10    // make errors throw exceptions
11    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
12    return $pdo;
13  } else {
14    die("Could not create PDO connection.");
15  }
16}
17
18
19function tableExists( $pdo, $table) {
20  $sql = "SHOW TABLES LIKE '$table'";
21  return ( $pdo->query( $sql)->rowCount() > 0);    
22}
23
24function createDemoTable( $pdo) {  
25  if ( !tableExists( $pdo, 'demotable')) {    
26    $sql = "CREATE TABLE IF NOT EXISTS `demotable` (
27      `idnumber` int(11) NOT NULL auto_increment,
28      `firstname` varchar(255) NOT NULL,
29      `lastname` varchar(255) NOT NULL,
30      `age` int(11) NOT NULL,
31      PRIMARY KEY  (`idnumber`)
32    )";
33    $pdo->exec( $sql);
34    $sql = "INSERT INTO `demotable` 
35      (`firstname`, `lastname`, `age`)
36      VALUES
37      ('John','Smith',23),
38      ('Sue','Davis',13),
39      ('Peter','Young',45),
40      ('Alice','Brown',56),
41      ('Frank','Gray',22),
42      ('Betty','Redwood',27),
43      ('Jane','Corner',29),
44      ('Bob','Church',19),
45      ('Mary','Collins',67),
46      ('Herbert','River',42),
47      ('Lucy','Hardy',13),
48      ('Charles','Winter',81),
49      ('Samantha','Weather',37),
50      ('Karl','Saunders',34),
51      ('Angela','Brown',35)"; 
52    $pdo->exec( $sql);
53  }
54}
55
56function dumpTable( $pdo, $table) {
57  $sql = "SELECT * FROM `".$table."`";
58  $stmt = $pdo->query( $sql);
59  // get all rows in an array
60  $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
61  print_r( $results);
62}
63
64function htmlTable( $pdo, $table) {
65  $sql = "DESCRIBE `".$table."`";
66  $stmt = $pdo->query( $sql);
67  print "<table>";
68  print "<tr>";
69  foreach( $stmt as $v) {
70    print "<th>".$v['Field']."</th>";
71  }
72  print "</tr>";
73  
74  $sql = "SELECT * FROM `".$table."`";
75  // specify only an associative array to be returned
76  $stmt = $pdo->query( $sql, PDO::FETCH_ASSOC);
77  foreach( $stmt as $row) {
78    print "<tr>";
79    foreach( $row as $v) {
80      print "<td>".$v."</td>";
81    }
82    print "</tr>";
83  }
84  print "</table>";  
85}
86
87function importTable( $pdo, $table, $file) {
88  $data = file( $file, FILE_IGNORE_NEW_LINES);
89  $numLines = count( $data);
90  $columns = explode("\t",$data[0]);
91  for( $i = 1; $i < $numLines; $i++) {
92    $values = explode("\t", $data[$i]);
93    $sql = "INSERT INTO `$table` SET ";
94    for( $j = 0; $j < count( $columns); $j++) {
95      if ( $j > 0) {
96        $sql .= ", ";
97      }      
98      $sql .= "`".$columns[$j]."`=";
99      // permit specifying NULL values with the word NULL
100      if ( $values[$j] == 'NULL') {
101        $sql .= "NULL";  
102      } else {
103        $sql .= "'".$values[$j]."'";  
104      }      
105    }
106    $pdo->exec( $sql);
107  }
108  print ($numLines - 1)." rows imported into table $table.";
109}
110
111
112
113?>