-
Notifications
You must be signed in to change notification settings - Fork 1
/
databases_mindmap.html
42 lines (42 loc) · 17.8 KB
/
databases_mindmap.html
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
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Markmap</title>
<style>
* {
margin: 0;
padding: 0;
}
#mindmap {
display: block;
width: 100vw;
height: 100vh;
}
</style>
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/prismjs@1.25.0/themes/prism.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/markmap-toolbar@0.2.0/dist/style.css">
</head>
<body>
<svg id="mindmap"></svg>
<script src="https://cdn.jsdelivr.net/npm/d3@6.7.0"></script><script src="https://cdn.jsdelivr.net/npm/markmap-view@0.2.7"></script><script src="https://cdn.jsdelivr.net/npm/markmap-toolbar@0.2.0/dist/index.umd.min.js"></script><script>(r => {
setTimeout(r);
})(() => {
const {
markmap,
mm
} = window;
const toolbar = new markmap.Toolbar();
toolbar.attach(mm);
const el = toolbar.render();
el.setAttribute('style', 'position:absolute;bottom:20px;right:20px');
document.body.append(el);
})</script><script>((getMarkmap, getOptions, data) => {
const {
Markmap
} = getMarkmap();
window.mm = Markmap.create('svg#mindmap', getOptions == null ? void 0 : getOptions(), data);
})(() => window.markmap,null,{"t":"root","d":0,"v":"","c":[{"t":"heading","d":1,"p":{"lines":[0,1]},"v":"Databases","c":[{"t":"heading","d":2,"p":{"lines":[2,3]},"v":"OOP Note","c":[{"t":"heading","d":3,"p":{"lines":[3,4]},"v":"In PHP we access class method or properties using <code>-></code> not <code>.</code> like the most languages","c":[{"t":"fence","d":4,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token variable\">$name</span> <span class=\"token operator\">=</span> <span class=\"token keyword type-declaration\">object</span> <span class=\"token operator\">-></span> <span class=\"token function\">get_name</span><span class=\"token punctuation\">(</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span> <span class=\"token comment\">// like: name = object.get_name() </span>\n</code></pre>\n"}]}]},{"t":"heading","d":2,"p":{"lines":[8,9]},"v":"info","c":[{"t":"list_item","d":4,"p":{"lines":[9,10]},"v":"PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases."},{"t":"list_item","d":4,"p":{"lines":[10,11]},"v":"we will use MySQLi object oriented"}]},{"t":"heading","d":2,"p":{"lines":[12,13]},"v":"Connect to Database (MySQLi Object-Oriented)","c":[{"t":"heading","d":3,"p":{"lines":[13,14]},"v":"<code>new mysqli</code>","c":[{"t":"fence","d":4,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token variable\">$connection</span> <span class=\"token operator\">=</span> <span class=\"token keyword\">new</span> <span class=\"token class-name\">mysqli</span><span class=\"token punctuation\">(</span><span class=\"token string single-quoted-string\">'servername'</span><span class=\"token punctuation\">,</span> <span class=\"token string single-quoted-string\">'admin name'</span><span class=\"token punctuation\">,</span> <span class=\"token string single-quoted-string\">'admin password'</span><span class=\"token punctuation\">,</span> <span class=\"token string single-quoted-string\">'database name'</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span>\n</code></pre>\n"},{"t":"heading","d":4,"p":{"lines":[17,18]},"v":"<code>sqli</code> stands for SQL Improved not SQL injection 😄"}]}]},{"t":"heading","d":2,"p":{"lines":[19,20]},"v":"Check connection errors","c":[{"t":"heading","d":3,"p":{"lines":[20,21]},"v":"1- <code>if($con)</code>","c":[{"t":"fence","d":4,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token keyword\">if</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$connection</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">{</span>\n <span class=\"token keyword\">echo</span> <span class=\"token string double-quoted-string\">\"connected successfully :)\"</span><span class=\"token punctuation\">;</span>\n<span class=\"token punctuation\">}</span>\n<span class=\"token keyword\">else</span><span class=\"token punctuation\">{</span>\n <span class=\"token keyword\">echo</span> <span class=\"token string double-quoted-string\">\"Database Connection Error :(\"</span><span class=\"token punctuation\">;</span>\n<span class=\"token punctuation\">}</span>\n</code></pre>\n"}]},{"t":"heading","d":3,"p":{"lines":[29,30]},"v":"2- <code>if (mysqli_connect_errno())</code>","c":[{"t":"fence","d":4,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token keyword\">if</span> <span class=\"token punctuation\">(</span><span class=\"token function\">mysqli_connect_errno</span><span class=\"token punctuation\">(</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">)</span> <span class=\"token punctuation\">{</span> <span class=\"token comment\">// errno -> error Number (0 if no errors)</span>\n <span class=\"token keyword\">echo</span> <span class=\"token string double-quoted-string\">\"Failed to connect to MySQL: \"</span> <span class=\"token operator\">.</span> <span class=\"token function\">mysqli_connect_error</span><span class=\"token punctuation\">(</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span> <span class=\"token comment\">// print the error </span>\n <span class=\"token keyword\">exit</span><span class=\"token punctuation\">(</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span>\n<span class=\"token punctuation\">}</span>\n</code></pre>\n"}]},{"t":"heading","d":3,"p":{"lines":[36,37]},"v":"3- <code>if($con -> connect_error</code>","c":[{"t":"fence","d":4,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token keyword\">if</span> <span class=\"token punctuation\">(</span><span class=\"token variable\">$con</span> <span class=\"token operator\">-></span> <span class=\"token property\">connect_error</span><span class=\"token punctuation\">)</span> <span class=\"token punctuation\">{</span> \n <span class=\"token keyword\">die</span><span class=\"token punctuation\">(</span><span class=\"token string double-quoted-string\">\"Connection failed: \"</span> <span class=\"token operator\">.</span> <span class=\"token variable\">$conn</span><span class=\"token operator\">-></span><span class=\"token property\">connect_error</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span> \n<span class=\"token punctuation\">}</span>\n\n</code></pre>\n"}]}]},{"t":"heading","d":2,"p":{"lines":[44,45]},"v":"Query the Database","c":[{"t":"heading","d":3,"p":{"lines":[45,46]},"v":"1- <code>mysqli_query()</code>","c":[{"t":"fence","d":4,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token variable\">$result</span> <span class=\"token operator\">=</span> <span class=\"token function\">mysqli_query</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$connection</span><span class=\"token punctuation\">,</span> <span class=\"token variable\">$sql_query</span><span class=\"token punctuation\">)</span>\n</code></pre>\n"}]},{"t":"heading","d":3,"p":{"lines":[50,51]},"v":"2- <code>$con -> query($query);</code>","c":[{"t":"fence","d":4,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token variable\">$result</span> <span class=\"token operator\">=</span> <span class=\"token variable\">$connection</span> <span class=\"token operator\">-></span> <span class=\"token function\">query</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$sql_query</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span>\n</code></pre>\n"}]}]},{"t":"heading","d":2,"p":{"lines":[55,56]},"v":"Check query error","c":[{"t":"bullet_list","d":3,"p":{"lines":[56,57]},"v":"","c":[{"t":"list_item","d":4,"p":{"lines":[56,57]},"v":"it's very simple, just type the previous code inside a condition"}]},{"t":"heading","d":3,"p":{"lines":[57,58]},"v":"1- using <code>mysqli_query()</code>","c":[{"t":"fence","d":4,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token keyword\">if</span> <span class=\"token punctuation\">(</span><span class=\"token function\">mysqli_query</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$con</span><span class=\"token punctuation\">,</span> <span class=\"token variable\">$query</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">{</span>\n <span class=\"token keyword\">echo</span> <span class=\"token string single-quoted-string\">'Done :)'</span><span class=\"token punctuation\">;</span>\n<span class=\"token punctuation\">}</span>\n<span class=\"token keyword\">else</span><span class=\"token punctuation\">{</span>\n <span class=\"token keyword\">echo</span> <span class=\"token string single-quoted-string\">'Error :( ( '</span> <span class=\"token operator\">.</span> <span class=\"token variable\">$con</span> <span class=\"token operator\">-></span> <span class=\"token property\">error</span> <span class=\"token operator\">.</span> <span class=\"token string single-quoted-string\">')'</span><span class=\"token punctuation\">;</span>\n<span class=\"token punctuation\">}</span>\n</code></pre>\n"}]},{"t":"heading","d":3,"p":{"lines":[66,67]},"v":"2- using <code>query()</code>","c":[{"t":"fence","d":4,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token keyword\">if</span> <span class=\"token punctuation\">(</span><span class=\"token variable\">$con</span> <span class=\"token operator\">-></span> <span class=\"token function\">query</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$sql_query</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">{</span>\n <span class=\"token keyword\">echo</span> <span class=\"token string single-quoted-string\">'Done :)'</span><span class=\"token punctuation\">;</span>\n<span class=\"token punctuation\">}</span>\n</code></pre>\n"},{"t":"bullet_list","d":4,"p":{"lines":[72,73]},"v":"","c":[{"t":"list_item","d":5,"p":{"lines":[72,73]},"v":"Don't query the database then use this code to check because this code will query the database"}]}]}]},{"t":"heading","d":2,"p":{"lines":[74,75]},"v":"Fetching the result","c":[{"t":"bullet_list","d":3,"p":{"lines":[75,76]},"v":"","c":[{"t":"list_item","d":4,"p":{"lines":[75,76]},"v":"fetch_assoc : fetch associative array"}]},{"t":"fence","d":3,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token keyword\">while</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$row</span> <span class=\"token operator\">=</span> <span class=\"token function\">mysqli_fetch_assoc</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$result</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">)</span> <span class=\"token punctuation\">{</span> <span class=\"token comment\">// $row = mysqli_fetch_assoc($result_object)</span>\n <span class=\"token keyword\">echo</span> <span class=\"token string double-quoted-string\">\"id: \"</span> <span class=\"token operator\">.</span> <span class=\"token variable\">$row</span><span class=\"token punctuation\">[</span><span class=\"token string double-quoted-string\">\"id\"</span><span class=\"token punctuation\">]</span><span class=\"token operator\">.</span> <span class=\"token string double-quoted-string\">\" - Name: \"</span> <span class=\"token operator\">.</span> <span class=\"token variable\">$row</span><span class=\"token punctuation\">[</span><span class=\"token string double-quoted-string\">\"name\"</span><span class=\"token punctuation\">]</span><span class=\"token punctuation\">;</span>\n <span class=\"token punctuation\">}</span>\n\n</code></pre>\n"},{"t":"fence","d":3,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token keyword\">while</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$row</span> <span class=\"token operator\">=</span> <span class=\"token variable\">$result</span> <span class=\"token operator\">-></span> <span class=\"token function\">fetch_assoc</span><span class=\"token punctuation\">(</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">)</span> <span class=\"token punctuation\">{</span> <span class=\"token comment\">// $row = $result_object -> fetch_assoc()</span>\n <span class=\"token keyword\">echo</span> <span class=\"token string double-quoted-string\">\"id: \"</span> <span class=\"token operator\">.</span> <span class=\"token variable\">$row</span><span class=\"token punctuation\">[</span><span class=\"token string double-quoted-string\">\"id\"</span><span class=\"token punctuation\">]</span><span class=\"token operator\">.</span> <span class=\"token string double-quoted-string\">\"| Name: \"</span> <span class=\"token operator\">.</span> <span class=\"token variable\">$row</span><span class=\"token punctuation\">[</span><span class=\"token string double-quoted-string\">\"name\"</span><span class=\"token punctuation\">]</span><span class=\"token punctuation\">;</span>\n <span class=\"token punctuation\">}</span>\n\n</code></pre>\n"}]}]},{"t":"heading","d":1,"p":{"lines":[91,92]},"v":"SQL Basics","c":[{"t":"heading","d":2,"p":{"lines":[92,93]},"v":"Insert","c":[{"t":"fence","d":3,"v":"<pre class=\"language-sql\"><code class=\"language-sql\"><span class=\"token keyword\">INSERT</span> <span class=\"token keyword\">INTO</span> table_name<span class=\"token punctuation\">(</span>column1<span class=\"token punctuation\">,</span> column2<span class=\"token punctuation\">)</span> <span class=\"token keyword\">VALUES</span><span class=\"token punctuation\">(</span><span class=\"token string\">'value1'</span><span class=\"token punctuation\">,</span> <span class=\"token string\">'value2'</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span>\n</code></pre>\n"}]},{"t":"heading","d":2,"p":{"lines":[97,98]},"v":"Select","c":[{"t":"fence","d":3,"v":"<pre class=\"language-sql\"><code class=\"language-sql\"><span class=\"token keyword\">SELECT</span> column1<span class=\"token punctuation\">,</span> column3 <span class=\"token keyword\">FROM</span> table_name <span class=\"token keyword\">WHERE</span> column1<span class=\"token operator\">=</span><span class=\"token string\">'value1'</span> <span class=\"token operator\">AND</span> column3<span class=\"token operator\">=</span><span class=\"token string\">'value3'</span><span class=\"token punctuation\">;</span>\n</code></pre>\n"}]},{"t":"heading","d":2,"p":{"lines":[102,103]},"v":"Update","c":[{"t":"fence","d":3,"v":"<pre class=\"language-sql\"><code class=\"language-sql\"><span class=\"token keyword\">UPDATE</span> table_name <span class=\"token keyword\">SET</span> column1<span class=\"token operator\">=</span><span class=\"token string\">'new value1'</span><span class=\"token punctuation\">,</span> column2<span class=\"token operator\">=</span><span class=\"token string\">'new value2'</span> <span class=\"token keyword\">WHERE</span> column3<span class=\"token operator\">=</span><span class=\"token string\">'value3'</span><span class=\"token punctuation\">;</span>\n</code></pre>\n"}]},{"t":"heading","d":2,"p":{"lines":[107,108]},"v":"Delete","c":[{"t":"fence","d":3,"v":"<pre class=\"language-sql\"><code class=\"language-sql\"><span class=\"token keyword\">DELETE</span> <span class=\"token keyword\">FROM</span> table_name <span class=\"token keyword\">WHERE</span> column2<span class=\"token operator\">=</span><span class=\"token string\">'value2'</span><span class=\"token punctuation\">;</span>\n</code></pre>\n"}]}]},{"t":"heading","d":1,"p":{"lines":[113,114]},"v":"Security #PHP_security","c":[{"t":"heading","d":2,"p":{"lines":[114,115]},"v":"sanitizing input","c":[{"t":"fence","d":3,"v":"<pre class=\"language-php\"><code class=\"language-php\"><span class=\"token variable\">$sanitized_username</span> <span class=\"token operator\">=</span> <span class=\"token function\">mysqli_real_escape_string</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$connection</span><span class=\"token punctuation\">,</span> <span class=\"token variable\">$username</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span>\n<span class=\"token variable\">$sanitized_password</span> <span class=\"token operator\">=</span> <span class=\"token function\">mysqli_real_escape_string</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$connection</span><span class=\"token punctuation\">,</span> <span class=\"token variable\">$password</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span>\n</code></pre>\n"}]},{"t":"heading","d":2,"p":{"lines":[119,120]},"v":"Hashing the password","c":[{"t":"fence","d":3,"v":"<pre class=\"language-php\"><code class=\"language-php\"> <span class=\"token variable\">$hashed_passwd</span> <span class=\"token operator\">=</span> <span class=\"token function\">password_hash</span><span class=\"token punctuation\">(</span><span class=\"token variable\">$plain_text_passwd</span><span class=\"token punctuation\">,</span> <span class=\"token constant\">PASSWORD_DEFAULT</span><span class=\"token punctuation\">)</span><span class=\"token punctuation\">;</span>`\n<span class=\"token doc-comment comment\">/** * We just want to hash our password using the current DEFAULT algorithm. * * Beware that DEFAULT may change over time, so you would want to prepare \n</span></code></pre>\n"}]}]}],"p":{}})</script>
</body>
</html>