· 9 years ago · Apr 01, 2017, 08:20 AM
1<?php
2/*
3 * @Author Rory Standley <rorystandley@gmail.com>
4 * @Version 1.3
5 * @Package Database
6 */
7class Database{
8 /*
9 * Create variables for credentials to MySQL database
10 * The variables have been declared as private. This
11 * means that they will only be available with the
12 * Database class
13 */
14
15 private $db_host = "localhost"; // Change as required
16 private $db_user = "root"; // Change as required
17 private $db_pass = ""; // Change as required
18 private $db_name = "crud"; // Change as required
19
20 /*
21 * Extra variables that are required by other function such as boolean con variable
22 */
23 private $connection = false;
24 private $con = false; // Check to see if the connection is active
25 private $result = array(); // Any results from a query will be stored here
26 private $myQuery = ""; // used for debugging process with SQL return
27 private $numResults = ""; // used for returning the number of rows
28 public $errors = [];
29
30 public function __construct(){
31 $this->connect();
32 }
33
34 // Function to make connection to database
35 public function connect(){
36 if(!$this->con){
37 $connection = mysqli_connect($this->db_host,$this->db_user,$this->db_pass,$this->db_name); // mysql_connect() with variables defined at the start of Database class
38 $this->connection = $connection;
39 if($err = mysqli_connect_error($connection)){
40 var_dump($err);
41 array_push($this->result,mysqli_error($this->connection));
42 return false; // Problem selecting database return FALSE
43 } else {
44 var_dump("bravo, baki");
45 $this->con = true;
46 return true; // Connection has been made return TRUE
47 }
48 }else{
49 return true; // Connection has already been made return TRUE
50 }
51 }
52
53 // Function to disconnect from the database
54 public function disconnect(){
55 // If there is a connection to the database
56 if($this->con){
57 // We have found a connection, try to close it
58 if (mysqli_close($this->connection)) {
59 // We have successfully closed the connection, set the connection variable to false
60 $this->con = false;
61 // Return true tjat we have closed the connection
62 return true;
63 } else {
64 // We could not close the connection, return false
65 return false;
66 }
67 }
68 }
69
70 public function sql($sql){
71 $query = mysqli_query($this->connection, $sql);
72 $this->myQuery = $sql; // Pass back the SQL
73 if($query){
74 // If the query returns >= 1 assign the number of rows to numResults
75 $this->numResults = mysqli_num_rows($query);
76 // Loop through the query results by the number of rows returned
77 for($i = 0; $i < $this->numResults; $i++){
78 $r = mysqli_fetch_array($query);
79 $key = array_keys($r);
80 for($x = 0; $x < count($key); $x++){
81 // Sanitizes keys so only alphavalues are allowed
82 if(!is_int($key[$x])){
83 if(mysqli_num_rows($query) >= 1){
84 $this->result[$i][$key[$x]] = $r[$key[$x]];
85 }else{
86 $this->result = null;
87 }
88 }
89 }
90 }
91 return true; // Query was successful
92 }else{
93 array_push($this->result,mysqli_error($this->connection));
94 return false; // No rows where returned
95 }
96 }
97
98 // Function to SELECT from the database
99 public function select($table, $rows = '*', $join = null, $where = null, $order = null, $limit = null){
100 // Create query from the variables passed to the function
101 $q = 'SELECT '.$rows.' FROM '.$table;
102 if($join != null){
103 $q .= ' JOIN '.$join;
104 }
105 if($where != null){
106 $q .= ' WHERE '.$where;
107 }
108 if($order != null){
109 $q .= ' ORDER BY '.$order;
110 }
111 if($limit != null){
112 $q .= ' LIMIT '.$limit;
113 }
114 $this->myQuery = $q; // Pass back the SQL
115 // Check to see if the table exists
116 if($this->tableExists($table)){
117 // The table exists, run the query
118 $query = mysqli_query($this->connection, $q);
119 if($query){
120 // If the query returns >= 1 assign the number of rows to numResults
121 $this->numResults = mysqli_num_rows($query);
122 // Loop through the query results by the number of rows returned
123 if (($this->numResults) > 0) {
124 $row_cntr = 0;
125 while ($row = mysqli_fetch_assoc($query)) {
126 $this->result[$row_cntr] = [];
127 foreach ($row as $key => $value) {
128 $this->result[$row_cntr][$key] = $value;
129 }
130 $row_cntr++;
131 }
132 }
133 return true; // Query was successful
134 }else{
135 array_push($this->result,mysqli_error($this->connection));
136 return false; // No rows where returned
137 }
138 }else{
139 return false; // Table does not exist
140 }
141 }
142
143 // Function to insert into the database
144 public function insert($table,$params=array()){
145 // Check to see if the table exists
146 if($this->tableExists($table)){
147 $sql='INSERT INTO `'.$table.'` (`'.implode('`, `',array_keys($params)).'`) VALUES ("' . implode('", "', $params) . '")';
148 $this->myQuery = $sql; // Pass back the SQL
149 // Make the query to insert to the database
150 if($ins = mysqli_query($this->connection, $sql)){
151 array_push($this->result,mysqli_insert_id($this->connection));
152 return true; // The data has been inserted
153 }else{
154 array_push($this->result,mysqli_error($this->connection));
155 return false; // The data has not been inserted
156 }
157 }else{
158 return false; // Table does not exist
159 }
160 }
161
162 //Function to delete table or row(s) from database
163 public function delete($table,$where = null){
164 // Check to see if table exists
165 if($this->tableExists($table)){
166 // The table exists check to see if we are deleting rows or table
167 if($where == null){
168 $delete = 'DROP TABLE '.$table; // Create query to delete table
169 }else{
170 $delete = 'DELETE FROM '.$table.' WHERE '.$where; // Create query to delete rows
171 }
172 // Submit query to database
173 if($del = mysqli_query($this->connection, $delete)){
174 array_push($this->result,mysqli_affected_rows($this->connection));
175 $this->myQuery = $delete; // Pass back the SQL
176 return true; // The query exectued correctly
177 }else{
178 array_push($this->result,mysqli_error($this->connection));
179 return false; // The query did not execute correctly
180 }
181 }else{
182 return false; // The table does not exist
183 }
184 }
185
186 // Function to update row in database
187 public function update($table,$params=array(),$where){
188 // Check to see if table exists
189 if($this->tableExists($table)){
190 // Create Array to hold all the columns to update
191 $args=array();
192 foreach($params as $field=>$value){
193 // Seperate each column out with it's corresponding value
194 $args[]=$field.'="'.$value.'"';
195 }
196 // Create the query
197 $sql='UPDATE '.$table.' SET '.implode(',',$args).' WHERE '.$where;
198 // Make query to database
199 $this->myQuery = $sql; // Pass back the SQL
200 if($query = mysqli_query($this->connection, $sql)){
201 array_push($this->result,mysqli_affected_rows($this->connection));
202 return true; // Update has been successful
203 }else{
204 array_push($this->result,mysqli_error($this->connection));
205 return false; // Update has not been successful
206 }
207 }else{
208 return false; // The table does not exist
209 }
210 }
211
212 // Private function to check if table exists for use with queries
213 private function tableExists($table){
214 $tablesInDb = mysqli_query($this->connection, 'SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"');
215 if($tablesInDb){
216 if(mysqli_num_rows($tablesInDb)==1){
217 return true; // The table exists
218 }else{
219 array_push($this->result,$table." does not exist in this database");
220 return false; // The table does not exist
221 }
222 }
223 }
224
225 // Public function to return the data to the user
226 public function getResult(){
227 $val = $this->result;
228 $this->result = array();
229 return $val;
230 }
231
232 //Pass the SQL back for debugging
233 public function getSql(){
234 $val = $this->myQuery;
235 $this->myQuery = array();
236 return $val;
237 }
238
239 //Pass the number of rows back
240 public function numRows(){
241 $val = $this->numResults;
242 $this->numResults = array();
243 return $val;
244 }
245
246 // Escape your string
247 public function escapeString($data){
248 return mysqli_real_escape_string($this->connection, $data);
249 }
250}
251
252
253
254$nasa_baza = new Database();
255var_dump(nasa);
256//$db->select('mysqlcrud');
257//$res = $db->getResult();
258//var_dump($res);
259
260
261//$db->insert('mysqlcrud', $params=array('id'=> 666, 'name'=>'Marta', 'email'=>'dsadsdsa'));
262//$db->select('mysqlcrud');
263//$res = $db->getResult();
264//var_dump($res);