-
Notifications
You must be signed in to change notification settings - Fork 0
/
mysqli.php
138 lines (120 loc) · 4.03 KB
/
mysqli.php
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
<?php
// SAFE MYSQL API FOR PHP
// COPYRIGHT (C) 2009 Buhacoff Information Assurance
$hostname='my.server.com';
$username='username';
$password='password';
$dbname='mydb';
// reference: http://www.php.net/manual/en/ref.mysql.php
function dbopen() {
global $hostname,$username,$password,$dbname;
$link = new mysqli($hostname, $username, $password, $dbname);
return $link;
}
$sql_fields = array();
$sql_types = array();
function db_select($sql) {
global $sql_fields, $sql_types;
$sql_fields = array();
$sql_types = array();
$db_connection = dbopen();
$statement = $db_connection->query($sql);
$data = array();
while( $row = $statement->fetch_assoc() ) {
$data[] = $row;
}
$statement->free_result();
return $data;
}
// example usage:
// $pet = array();
// $pet["pet_name"] = "sparky"; // :pet_name[s] s means string
// $pet["pet_age"] = 2; // :pet_name[i] i means integer
// $pet["pet_weight"] = 15.2; // :pet_name[d] d means double or float
// db_select_param("select name,age,weight from pets where name=:pet_name[s] and age=:pet_age[i] and weight=:pet_weight[d]", $pet);
function db_select_param($sql, $data) {
$db_connection = dbopen();
$statement = prepare_statement($sql,$data,$db_connection);
$statement->execute();
$data = fetch_assoc_for_prepared_stmt($statement);
$statement->free_result();
return $data;
}
// for some reason php doesn't implement the "fetch_assoc" function for result sets of prepared statements, so we have to do this in order to get the same effect:
// XXX this should either accept a callback function to handle each row, OR instead of fetching & returning
// all the records, it needs to create an anonymous function that is all set up and ready to be called for each
// row and return that row.
function fetch_assoc_for_prepared_stmt($statement) {
$result = array(); // each time we call statement->fetch the result will be in this variable
$bind_results = array();
$meta = $statement->result_metadata();
while($field = $meta->fetch_field()) {
$bind_results[] = &$result[$field->name];
}
call_user_func_array( array($statement,'bind_result'), $bind_results );
$data = array();
while( $statement->fetch() ) {
$row = array();
foreach( $result as $key => $value ) {
$row[$key] = $value;
}
$data[] = $row;
}
return $data;
}
function db_insert($sql, $data) {
$db_connection = dbopen();
$statement = prepare_statement($sql,$data,$db_connection);
$statement->execute();
$newid = $db_connection->insert_id;
$statement->free_result();
return $newid;
}
function db_update($sql, $data) {
$db_connection = dbopen();
$statement = prepare_statement($sql,$data,$db_connection);
$statement->execute();
$statement->free_result();
return;
}
function db_delete($sql, $data) {
$db_connection = dbopen();
$statement = prepare_statement($sql,$data,$db_connection);
$statement->execute();
$statement->free_result();
return;
}
function prepare_statement($sql,$data,$conn) {
global $sql_fields, $sql_types;
$sql_fields = array();
$sql_types = array();
// turn query with named parameters into query with mysql placeholders and populate the sql_fields and sql_types arrays for the php bind_param statement
$sql_with_question_marks = preg_replace_callback('/(:\w+)\[([sidb])\]/','process_named_parameter',$sql);
$values = array();
foreach( $sql_fields as $f ) {
$values[] = $data[$f];
}
$types = join("",$sql_types);
$bind_params = array_merge( array($types), $values );
$statement = $conn->prepare($sql_with_question_marks);
if( $statement == null ) {
$result = array();
$result["error"] = $db_connection->error;
$result["sql"] = $sql_with_question_marks;
custom_error($result);
}
call_user_func_array( array($statement,'bind_param') , $bind_params );
return $statement;
}
function process_named_parameter($matches) {
global $sql_fields, $sql_types;
$sql_fields[] = substr($matches[1],1);
$sql_types[] = $matches[2];
return "?";
}
function custom_error($info) {
error_log("mysql query failed: ". $info['error'] . ": " . $info['sql']);
echo json_encode($result);
exit;
}
?>