PHP classes for simple operation of mysql database_PHP tutorial

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

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

/**

* Database class

*

* @version: 2.2

* @revised: 27 may 2007

*

**/

class Database {

var $host;

var $name;

var $user;

var $pass;

var $prefix;

var $linkId;

function Database($mysql) {

foreach($mysql as $k => $v){

$this->$k = $v;

}

if(strlen($this->prefix)>0 && substr($this->prefix, -1) !== "_")

$prefix .= "_";

$this->prefix = $prefix;

}

function getLastID() {

$id = mysql_fetch_row(mysql_query("SELECT LAST_INSERT_ID()", $this->linkId));

return $id[0];

}

function getPossibleValues($tableA, $whereA) {

if(gettype($tableA) == "array") {

$table = "";

foreach($tableA as $t) {

$table .= $this->prefix.$t.", ";

}

$table = substr($table, 0, -2);

} else $table = $this->prefix.$tableA;

if(gettype($whereA) != "array") {

$whereA = array($whereA);

}

$return = array();

foreach($whereA as $where) {

$sql = mysql_query("SHOW COLUMNS FROM ".$table." LIKE '%".$where."%'");

while($arr = mysql_fetch_array($sql)) {

if(strpos($arr['Type'], 'enum')===0) {

$vals = substr($arr['Type'], 5, -1);

} else {

$vals = substr($arr['Type'], 4, -1);

}

$vals = str_replace("'","",$vals);

$vals = explode(",",$vals);

$i = 1;

foreach($vals as $val) {

$return[$arr['Field']][$i ] = $val;

}

$return[$arr['Field']]['default'] = $arr['Default'];

if($arr['Null'] != "NO") $return[$arr['Field']][0] = NULL;

}

}

return $return;

}

function connect() {

$this->linkId = mysql_connect($this->host, $this->user, $this->pass);

if(!$this->linkId) {

return false;

}

if(mysql_select_db($this->name, $this->linkId)) return true;

mysql_close($this->linkId);

return false;

}

function runSelect($tables, $where = "1", $fieldsA = "*", $order = false, $limit = false, $offset = false, $group = false) {

if(gettype($tables) == "array") {

$table = "";

foreach($tables as $t) {

$table .= $this->prefix.$t.", ";

}

$table = substr($table, 0, -2);

} else $table = $this->prefix.$tables;

if(gettype($fieldsA) == "array") {

$fields = "";

$keys = array_keys($fieldsA);

if($keys[0] != '0') {

foreach($keys as $key) {

$fields .= $key.' AS '.$fieldsA[$key].', ';

}

} else {

foreach($fieldsA as $field) {

$fields .= $field.', ';

}

}

$fields = substr($fields, 0, -2);

} else $fields = $fieldsA;

$query = "SELECT ".$fields." FROM ".$table." WHERE ".$where.

($order!== false?" ORDER BY ".$order:($group!==false ? " GROUP BY ".$group : "")).

($limit !== false?" LIMIT ".$limit:"").

($offset !== false?" OFFSET ".$offset:"");

return mysql_query($query, $this->linkId);

}

function runUpdate($table, $valuesA, $where = "1") {

if(gettype($valuesA) == "array") {

$fields = "";

$values = "";

$keys = array_keys($valuesA);

foreach($keys as $key) {

if($valuesA[$key] !== NULL)

$values .= "`".$key."`='".str_replace("'",''', $valuesA[$key])."',";

else

$values .= $key."=NULL,";

}

$fields = substr($fields, 0, -1);

$values = substr($values, 0, -1);

} else $values = $valuesA;

$query = "UPDATE ".$this->prefix.$table." SET ".$values." WHERE ".$where;

if(mysql_query($query,

$this->linkId))

return mysql_affected_rows($this->linkId);

return false;

}

function runDelete($table, $where = "1") {

if(mysql_query("DELETE FROM ".$this->prefix.$table." WHERE ".$where, $this->linkId))

return mysql_affected_rows($this->linkId);

return false;

}

function runInsert($table, $valuesA, $onDuplicate = NULL) {

if(gettype($valuesA) == "array") {

$fields = "";

$values = "";

$keys = array_keys($valuesA);

foreach($keys as $key) {

$fields .= "`".$key."`, ";

$values .= ($valuesA[$key]===NULL?"NULL, ":"'".str_replace("'", ''', $valuesA[$key])."', ");

}

$fields = substr($fields, 0, -2);

$values = substr($values, 0, -2);

}

$onDup = "";

if($onDuplicate != NULL) {

$onDup = " ON DUPLICATE KEY UPDATE ";

if(gettype($onDuplicate) == "array") {

$keys = array_keys($onDuplicate);

foreach($keys as $key) {

$onDup .= '`'.$key.'`='.($onDuplicate[$key]===NULL?"NULL,":"'".str_replace("'", ''', $onDuplicate[$key])."', ");

}

$onDup = substr($onDup, 0, -2);

} else $onDup .= $onDuplicate;

}

$query = "INSERT INTO ".$this->prefix.$table.($fields!==NULL?"(".$fields.")":"").

" VALUES (".$values.")".$onDup;

if(mysql_query($query, $this->linkId))

return mysql_affected_rows($this->linkId);

return false;

}

function getCells($table){

$query = "SHOW COLUMNS FROM `".$table."`";

$fields = mysql_query($query, $this->linkId) or die('hej');

return $fields;

}

function translateCellName($cellName){

$sql = $this->runSelect("mysql_cell_translation","mysql_name = '".$cellName."'");

$row = mysql_fetch_assoc($sql);

return $row['human_name']?$row['human_name']:'['.$cellName.']';

}

function getError() {

return mysql_error($this->linkId);

}

function close()

{

mysql_close($this->linkId);

}

}

?>