{"id":7348,"date":"2019-10-21T07:07:25","date_gmt":"2019-10-21T07:07:25","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=7348"},"modified":"2021-11-16T05:44:10","modified_gmt":"2021-11-16T05:44:10","slug":"complete-reference-guide-of-php-databases","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/complete-reference-guide-of-php-databases\/","title":{"rendered":"Complete reference guide of PHP Databases!"},"content":{"rendered":"\n<h3 class=\"wp-block-heading\">PHP&nbsp;MySQL&nbsp;Introduction<\/h3>\n\n\n\n<p>MySQL is one of the most popular relational database system being used on the Web today. It is freely available and easy to install, however if you have installed Wampserver it already there on your machine. MySQL database server offers several advantages:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>MySQL is easy to use, yet extremely powerful, fast, secure, and scalable.<\/li><li>MySQL runs on a wide range of operating systems, including UNIX or Linux, Microsoft Windows, Apple Mac OS X, and others.<\/li><li>MySQL supports standard SQL (Structured Query Language).<\/li><li>MySQL is ideal database solution for both small and large applications.<\/li><li>MySQL is developed, and distributed by Oracle Corporation.<\/li><li>MySQL includes data security layers that protect sensitive data from intruders.<\/li><\/ul>\n\n\n\n<p>MySQL database stores data into tables like other relational database. A table is a collection of related data, and it is divided into rows and columns.<\/p>\n\n\n\n<p>Each row in a table represents a data record that are inherently connected to each other such as information related to a particular person, whereas each column represents a specific field such as&nbsp;<em>id<\/em>,&nbsp;<em>first_name<\/em>,&nbsp;<em>last_name<\/em>,&nbsp;<em>email<\/em>, etc. The structure of a simple MySQL table that contains person&#8217;s general information may look something like this:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p> <strong>Tip:<\/strong>&nbsp;Websites like Facebook, Twitter, Wikipedia uses MySQL for their storage need. So you can easily understand what MySQL is capable of. <\/p><\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">Talking to MySQL Databases with SQL<\/h2>\n\n\n\n<p>SQL, the Structured Query Language, is a simple, standardized language for communicating with relational databases like MySQL. With SQL you can perform any database-related task, such as creating databases and tables, saving data in database tables, query a database for specific records, deleting and updating data in databases.<\/p>\n\n\n\n<p>Look at the following standard SQL query that returns the email address of a person whose first name is equal to &#8216;Peter&#8217; in the&nbsp;<em>persons<\/em>&nbsp;table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">SELECT email FROM persons WHERE first_name=<span class=\"hljs-string\">\"Peter\"<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>\n\n If you execute the SQL query above it will return the following record: \n\n<\/p>\n\n\n\n<p> peterparker@mail.com <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Ways of Connecting to MySQL through PHP<\/h2>\n\n\n\n<p>In order to store or access the data inside a MySQL database, you first need to connect to the MySQL database server. PHP offers two different ways to connect to MySQL server:&nbsp;<strong>MySQLi<\/strong>&nbsp;(Improved MySQL) and&nbsp;<strong>PDO<\/strong>&nbsp;(PHP Data Objects) extensions.<\/p>\n\n\n\n<p>While the PDO extension is more portable and supports more than twelve different databases, MySQLi extension as the name suggests supports MySQL database only. MySQLi extension however provides an easier way to connect to, and execute queries on, a MySQL database server. Both PDO and MySQLi offer an object-oriented API, but MySQLi also offers a procedural API which is relatively easy for beginners to understand.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p> <strong>Tip:<\/strong>&nbsp;The PHP&#8217;s MySQLi extension provides both speed and feature benefits over the PDO extension, so it could be a better choice for MySQL-specific projects. <\/p><\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">Connecting to MySQL Database Server<\/h2>\n\n\n\n<p>In PHP you can easily do this using the&nbsp;<code>mysqli_connect()<\/code>&nbsp;function. All communication between PHP and the MySQL database server takes place through this connection. Here&#8217;re the basic syntaxes for connecting to MySQL using MySQLi and PDO extensions:<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Syntax: MySQLi, Procedural way<\/h4>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">$link = mysqli_connect(<span class=\"hljs-string\">\"hostname\"<\/span>, <span class=\"hljs-string\">\"username\"<\/span>, <span class=\"hljs-string\">\"password\"<\/span>, <span class=\"hljs-string\">\"database\"<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Syntax: MySQLi, Object Oriented way<\/h4>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">$mysqli = <span class=\"hljs-keyword\">new<\/span> mysqli(<span class=\"hljs-string\">\"hostname\"<\/span>, <span class=\"hljs-string\">\"username\"<\/span>, <span class=\"hljs-string\">\"password\"<\/span>, <span class=\"hljs-string\">\"database\"<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h4 class=\"wp-block-heading\">Syntax: PHP Data Objects (PDO) way<\/h4>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">$pdo = <span class=\"hljs-keyword\">new<\/span> PDO(<span class=\"hljs-string\">\"mysql:host=hostname;dbname=database\"<\/span>, <span class=\"hljs-string\">\"username\"<\/span>, <span class=\"hljs-string\">\"password\"<\/span>); <\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The&nbsp;<em>hostname<\/em>&nbsp;parameter in the above syntax specify the host name (e.g.&nbsp;<code>localhost<\/code>), or IP&nbsp;address of the MySQL server, whereas the&nbsp;<em>username<\/em>&nbsp;and&nbsp;<em>password<\/em>&nbsp;parameters specifies the credentials to access MySQL server, and the&nbsp;<em>database<\/em>&nbsp;parameter, if provided will specify the default MySQL database to be used when performing queries.<\/p>\n\n\n\n<p>The following example shows how to connect to MySQL database server using MySQLi (both&nbsp;<em>procedural<\/em>&nbsp;and&nbsp;<em>object oriented<\/em>&nbsp;way) and PDO extension.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n<span class=\"hljs-comment\">\/* Attempt MySQL server connection. Assuming you are running MySQL\nserver with default setting (user 'root' with no password) *\/<\/span>\n$link = mysqli_connect(<span class=\"hljs-string\">\"localhost\"<\/span>, <span class=\"hljs-string\">\"root\"<\/span>, <span class=\"hljs-string\">\"\"<\/span>);\n \n<span class=\"hljs-comment\">\/\/ Check connection<\/span>\n<span class=\"hljs-keyword\">if<\/span>($link === <span class=\"hljs-keyword\">false<\/span>){\n    <span class=\"hljs-keyword\">die<\/span>(<span class=\"hljs-string\">\"ERROR: Could not connect. \"<\/span> . mysqli_connect_error());\n}\n \n<span class=\"hljs-comment\">\/\/ Print host information<\/span>\n<span class=\"hljs-keyword\">echo<\/span> <span class=\"hljs-string\">\"Connect Successfully. Host info: \"<\/span> . mysqli_get_host_info($link);\n<span class=\"hljs-meta\">?&gt;<\/span><\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>Note: The default username for MySQL database server is root and there is no password. However to prevent your databases from intrusion and unauthorized access you should set password for MySQL accounts.<\/p><\/blockquote>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p> <strong>Tip:<\/strong>&nbsp;Setting the&nbsp;<code>PDO::ATTR_ERRMODE<\/code>&nbsp;attribute to&nbsp;<code>PDO::ERRMODE_EXCEPTION<\/code>&nbsp;tells PDO to throw exceptions whenever a database error occurs. <\/p><\/blockquote>\n\n\n\n<h2 class=\"wp-block-heading\">Closing the MySQL Database Server Connection<\/h2>\n\n\n\n<p>The connection to the MySQL database server will be closed automatically as soon as the execution of the script ends. However, if you want to close it earlier you can do this by simply calling the PHP&nbsp;<code>mysqli_close()<\/code>&nbsp;function.<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"HTML, XML\" data-shcb-language-slug=\"xml\"><span><code class=\"hljs language-xml\"><span class=\"php\"><span class=\"hljs-meta\">&lt;?php<\/span>\n<span class=\"hljs-comment\">\/* Attempt MySQL server connection. Assuming you are running MySQL\nserver with default setting (user 'root' with no password) *\/<\/span>\n$link = mysqli_connect(<span class=\"hljs-string\">\"localhost\"<\/span>, <span class=\"hljs-string\">\"root\"<\/span>, <span class=\"hljs-string\">\"\"<\/span>);\n \n<span class=\"hljs-comment\">\/\/ Check connection<\/span>\n<span class=\"hljs-keyword\">if<\/span>($link === <span class=\"hljs-keyword\">false<\/span>){\n    <span class=\"hljs-keyword\">die<\/span>(<span class=\"hljs-string\">\"ERROR: Could not connect. \"<\/span> . mysqli_connect_error());\n}\n \n<span class=\"hljs-comment\">\/\/ Print host information<\/span>\n<span class=\"hljs-keyword\">echo<\/span> <span class=\"hljs-string\">\"Connect Successfully. Host info: \"<\/span> . mysqli_get_host_info($link);\n \n<span class=\"hljs-comment\">\/\/ Close connection<\/span>\nmysqli_close($link);\n<span class=\"hljs-meta\">?&gt;<\/span><\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">HTML, XML<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">xml<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n<div class=\"epyt-gallery\" data-currpage=\"1\" id=\"epyt_gallery_88988\"><iframe loading=\"lazy\"  id=\"_ytid_59917\"  width=\"760\" height=\"427\"  data-origwidth=\"760\" data-origheight=\"427\" src=\"https:\/\/www.youtube.com\/embed\/?enablejsapi=1&#038;autoplay=0&#038;cc_load_policy=0&#038;cc_lang_pref=&#038;iv_load_policy=1&#038;loop=0&#038;rel=1&#038;fs=1&#038;playsinline=0&#038;autohide=2&#038;theme=dark&#038;color=red&#038;controls=1&#038;disablekb=0&#038;\" class=\"__youtube_prefs__  no-lazyload\" title=\"YouTube player\"  data-epytgalleryid=\"epyt_gallery_88988\"  allow=\"fullscreen; accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen data-no-lazy=\"1\" data-skipgform_ajax_framebjll=\"\"><\/iframe><div class=\"epyt-gallery-list\"><div>Sorry, there was a YouTube error.<\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>PHP&nbsp;MySQL&nbsp;Introduction MySQL is one of the most popular relational database system being used on the Web today. It is freely available and easy to install, however if you have installed&#8230; <\/p>\n","protected":false},"author":14,"featured_media":7343,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[5150],"tags":[5355,493,5440,177,399],"class_list":["post-7348","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-php","tag-database","tag-mysql","tag-mysqli-object","tag-php","tag-server"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/7348","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=7348"}],"version-history":[{"count":3,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/7348\/revisions"}],"predecessor-version":[{"id":25368,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/7348\/revisions\/25368"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media\/7343"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=7348"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=7348"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=7348"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}