Mysql Easily insert and update records

1.Create a database.php file

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
<?php

define ('DB_SERVER', 'localhost');
define ('DB_USERNAME', 'username');
define ('DB_PASSWORD', 'password');
define ('DB_DATABASE', 'database');

$mysqli = new mysqli (DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_DATABASE);

if (mysqli_connect_errno()) {
trigger_error ('DB Connect Error (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
exit ('Sorry! We were unable to connect to the database. Please try again.');
}

function escape_data ($data) {
global $mysqli;
if (get_magic_quotes_gpc()) $data = stripslashes($data);
return $mysqli->real_escape_string(trim($data));
}

function db_query ($query) {
global $mysqli;
if (!$result = $mysqli->query ($query)) trigger_error("Query: {$query}<br />Error: {$mysqli->error}");
return $result;
}

function db_insert ($table, $array) {
global $mysqli;
$unquote = array('NULL', 'NOW()');
foreach ($array as $key => $value) {
$columns[] = $key;
if (is_numeric($value) || in_array($value, $unquote)) {
$data[] = $value;
} else {
$data[] = "'{$value}'";
}
}
db_query ('INSERT INTO `' . $table . '` (`' . implode('`, `', $columns) . '`) VALUES (' . implode(', ', $data) . ')');
return $mysqli->insert_id;
}

function db_update ($table, $array, $column, $id, $add='') {
global $mysqli;
$unquote = array('NULL', 'NOW()');
foreach ($array as $key => $value) {
if (is_numeric($value) || in_array($value, $unquote)) {
$data[] = '`' . $key . '`=' . $value;
} else {
$data[] = '`' . $key . "`='{$value}'";
}
}
$id = (is_numeric($id)) ? $id : "'{$id}'";
db_query ("UPDATE `{$table}` SET " . implode(', ', $data) . " WHERE `{$column}`={$id} {$add}");
return $mysqli->affected_rows;
}

?>

2. include  database.php

include_once ( ‘database.php’);

2.1. Insert Query use function  

db_insert ( string $table, array $array )

1
2
3
4
5
6
$insert = array();
$insert['name'] = 'username';  //insert['table column name']='value you want insert'];
$insert['password'] = 'password';
$insert['email'] = 'email@address.com';
$insert['registered'] = 'NOW()';
$userid = db_insert('users', $insert);

2.2.Update Query use function 

db_update ( string $table, array $array, string $column, mixed $id [, string $add ] )

1
2
3
4
5
$update = array();

$ update ['email'] = 'email@address.com';

db_update ('users', $update, 'user_id', 1);

2.3.Write query use function

db_query ( string $query )

1
2
3
4
$result = db_query ("SELECT data FROM table WHERE column='{$value}'");
while (list($data) = $result->fetch_row()) {
$html .= $data . '<br />';
}

2.4.Escape any problematic characters use function

escape_data ( string $data )

1
$username = escape_data ("Hacker'); hello;--");

 

Leave a Reply