{"id":111,"date":"2020-02-12T10:36:34","date_gmt":"2020-02-12T10:36:34","guid":{"rendered":"https:\/\/www.lcn.com\/support\/knowledge-base\/how-to-use-a-database-with-php\/"},"modified":"2020-03-05T13:33:09","modified_gmt":"2020-03-05T13:33:09","slug":"how-to-use-a-database-with-php","status":"publish","type":"ht_kb","link":"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/","title":{"rendered":"How to use a database with PHP"},"content":{"rendered":"\n

Using a database is a very common task for PHP scripts but it can be a very involved process. Below we’ll step through connecting to, inserting records and querying a database and displaying back the results.<\/p>\n

PHP has many predefined functions for dealing with MySQL databases, you can find a full description of these at: http:\/\/php.net\/manual\/en\/book.mysqli.php<\/a>.<\/p>\n

Your database details<\/h3>\n

If you haven’t already created a MySQL database you can follow the How to create a MYSQL database<\/a> guide. For the purpose of this example we’ll use the database name DB_name<\/i>, remember to replace this with your actual database name.<\/p>\n

Connecting to the database<\/h3>\n
<?php
$connection = mysqli_connect(\"database4.lcn.com\", \"DB_username\", \"DB_password\", \"DB_name\");

if (!$connection) {
echo \"Error: Unable to connect to MySQL.\" . PHP_EOL;
exit;
}<\/pre>\n<\/br>
Lines 1-2<\/strong> – Creates a connection to our database using mysqli_connect including parameters for: the database hostname, username, password and database name. A reference to our database connection is now stored in the $connection variable \u2013 which we’ll use next to perform some actions on our database. The fields for “database4.lcn.com” “DB_username”, “DB_password” and “DB_name” should be updated to include your database hostname, username, password and database name.<\/p>\n
Lines 4-6<\/strong> – If we couldn’t connect for any reason an error message is returned.<\/p>\n
\u00a0<\/p>\n
Creating a database table<\/h3>\n
Databases organise their records into tables. Each table should only contain one type of information.<\/p>\n
In this example we’ll create a simple table to store contact information that hold First Name, Last Name and Email Address.<\/p>\n
<?php
$sql = \"CREATE TABLE IF NOT EXISTS Contacts (
FirstName varchar(64),
LastName varchar(64),
EmailAddress varchar(255))\";

mysqli_query($connection, $sql);

?><\/pre>\n<\/br>
Lines 2-5<\/strong> – Store a SQL query that will create our contacts table. We’ve declared FirstName<\/i> and LastName<\/i> as VARCHARs (variable characters, which is what we usually use for short text) with a length of 64 characters which should work for some pretty long names, and EmailAddress<\/i> as a VARCHAR with a length of 255 characters, as Email Addresses can be quite long.<\/p>\n
Line 8<\/strong> – Will run our SQL query on the database, and create our table.<\/p>\n
Inserting Records into our new table<\/h3>\n
Our next step is to start filling our table with information.<\/p>\n
mysqli_query($connection, \"INSERT INTO Contacts (FirstName, LastName, EmailAddress)
VALUES ('Peter', 'Griffin', 'peter@thehappygoluckytoyfactory.com')\");

mysqli_query($connection, \"INSERT INTO Contacts (FirstName, LastName, EmailAddress)
VALUES ('Homer', 'Simpson', 'homer@springfieldpowerplant.com')\");

mysqli_query($connection, \"INSERT INTO Contacts (FirstName, LastName, EmailAddress)VALUES ('Philip', 'Fry', 'pjfry@planetexpress.com')\");<\/pre>\n<\/br>
This code will insert 3 records into our Contacts<\/i> table. Now that we have some data, we’ll take a look at how we look up and fetch data from our table.<\/p>\n
Querying our database<\/h3>\n
We’re now going to find all the records from our Contacts<\/i> table that have the FirstName<\/i> “Peter”.<\/p>\n
$results = mysqli_query($connection, \"SELECT FirstName, LastName, EmailAddress FROM Contacts WHERE FirstName='Peter'\");<\/pre>\n<\/br>
This code will put an Array containing the FirstName<\/i>, LastName<\/i> and EmailAddress<\/i> fields from records with a FirstName<\/i> of ‘Peter’ into the variable $results<\/i>.<\/p>\n
Displaying our results<\/h3>\n
We’re now going to print our results into an HTML table.<\/p>\n
echo \"<table border='1'>
<tr>
<th>FirstName<\/th>
<th>LastName<\/th>
<th>EmailAddress<\/th>
<\/tr>\";

while($row = mysqli_fetch_array($results)) {
echo \"<tr>\";
echo \"<td>\" . $row['FirstName'] . \"<\/td>\"; 
echo \"<td>\" . $row['LastName'] . \"<\/td>\"; 
echo \"<td>\" . $row['EmailAddress'] . \"<\/td>\"; 
echo \"<\/tr>\"; 
}
echo \"<\/table>\";<\/pre>\n<\/br>
Lines 1-6<\/strong> – Begins our table and creates some headings for our table.<\/p>\n
Lines 8-15<\/strong> – Loop over each of our results, and prints out a new row in our table.<\/p>\n
Line 16<\/strong> – Closes our table.<\/p>\n
Disconnecting from the database<\/h3>\n
mysqli_close($connection);<\/pre>\n<\/br>
It’s important that we make sure to close our connection to the database at the end of our script, so that we don’t end up leaving connections open. Having a lot of unclosed connections can cause issues.<\/p>\n
The final script<\/h3>\n
<?php
\/\/ Connect to the database

$connection = mysqli_connect(\"database4.lcn.com\", \"LCN345596_db\", \"terminal1\", \"ranoutofideas_com_db\");

if (!$connection) {
echo \"Error: Unable to connect to MySQL.\" . PHP_EOL;    
exit;
}

\/\/ Create our table

$sql = \"CREATE TABLE IF NOT EXISTS Contacts (
FirstName varchar(64),
LastName varchar(64),
EmailAddress varchar(255))\";

mysqli_query($connection, $sql);

\/\/ Insert some records

mysqli_query($connection, \"INSERT INTO Contacts (FirstName, LastName, EmailAddress)VALUES ('Peter', 'Griffin', 'peter@thehappygoluckytoyfactory.com')\");

mysqli_query($connection, \"INSERT INTO Contacts (FirstName, LastName, EmailAddress)VALUES ('Homer', 'Simpson', 'homer@springfieldpowerplant.com')\");

mysqli_query($connection, \"INSERT INTO Contacts (FirstName, LastName, EmailAddress)VALUES ('Philip', 'Fry', 'pjfry@planetexpress.com')\");

\/\/ Query our Database

$results = mysqli_query($connection, \"SELECT FirstName, LastName, EmailAddress FROM Contacts WHERE FirstName='Peter'\");

\/\/ Print out our results

echo \"<table border='1'>
<tr>
<th>FirstName<\/th>
<th>LastName<\/th>
<th>EmailAddress<\/th>
<\/tr>\";

while($row = mysqli_fetch_array($results))
{
echo \"<tr>\"; 
echo \"<td>\" . $row['FirstName'] . \"<\/td>\"; 
echo \"<td>\" . $row['LastName'] . \"<\/td>\"; 
echo \"<td>\" . $row['EmailAddress'] . \"<\/td>\"; 
echo \"<\/tr>\"; 
}
echo \"<\/table>\";

\/\/ Close our connection to the database

mysqli_close($connection);

?><\/pre>\n<\/br>
That’s it! You now have all you need to use a database with PHP.<\/p>\n","protected":false},"excerpt":{"rendered":"
Using a database is a very common task for PHP scripts but it can be a very involved process. Below we’ll step through connecting to, inserting records and querying a database and displaying back the results. PHP has many predefined functions for dealing with MySQL databases, you can find a full description of these at: […]<\/p>\n","protected":false},"author":1,"comment_status":"closed","ping_status":"open","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[15],"ht-kb-tag":[5],"class_list":["post-111","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-creating-a-new-website","ht_kb_tag-web-hosting"],"yoast_head":"\nHow to use a database with PHP - LCN.com<\/title>\n<meta name=\"description\" content=\"How to use a database with PHP at LCN.comFollow this simple guide on how to connect, query and insert records into a database with PHP scripts.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to use a database with PHP - LCN.com\" \/>\n<meta property=\"og:description\" content=\"How to use a database with PHP at LCN.comFollow this simple guide on how to connect, query and insert records into a database with PHP scripts.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/\" \/>\n<meta property=\"og:site_name\" content=\"Customer Support Guides - LCN\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/lcndotcom\" \/>\n<meta property=\"article:modified_time\" content=\"2020-03-05T13:33:09+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.lcn.com\/support\/wp-content\/uploads\/support-facebook-feed.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@lcndotcom\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/\",\"url\":\"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/\",\"name\":\"How to use a database with PHP - LCN.com\",\"isPartOf\":{\"@id\":\"https:\/\/www.lcn.com\/support\/#website\"},\"datePublished\":\"2020-02-12T10:36:34+00:00\",\"dateModified\":\"2020-03-05T13:33:09+00:00\",\"description\":\"How to use a database with PHP at LCN.comFollow this simple guide on how to connect, query and insert records into a database with PHP scripts.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.lcn.com\/support\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to use a database with PHP\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.lcn.com\/support\/#website\",\"url\":\"https:\/\/www.lcn.com\/support\/\",\"name\":\"Customer Support Guides - LCN\",\"description\":\"- LCN\",\"publisher\":{\"@id\":\"https:\/\/www.lcn.com\/support\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.lcn.com\/support\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.lcn.com\/support\/#organization\",\"name\":\"LCN.com\",\"url\":\"https:\/\/www.lcn.com\/support\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.lcn.com\/support\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.lcn.com\/support\/wp-content\/uploads\/2020\/02\/ZF_-hcc3.jpg\",\"contentUrl\":\"https:\/\/www.lcn.com\/support\/wp-content\/uploads\/2020\/02\/ZF_-hcc3.jpg\",\"width\":461,\"height\":461,\"caption\":\"LCN.com\"},\"image\":{\"@id\":\"https:\/\/www.lcn.com\/support\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/lcndotcom\",\"https:\/\/x.com\/lcndotcom\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to use a database with PHP - LCN.com","description":"How to use a database with PHP at LCN.comFollow this simple guide on how to connect, query and insert records into a database with PHP scripts.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/","og_locale":"en_US","og_type":"article","og_title":"How to use a database with PHP - LCN.com","og_description":"How to use a database with PHP at LCN.comFollow this simple guide on how to connect, query and insert records into a database with PHP scripts.","og_url":"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/","og_site_name":"Customer Support Guides - LCN","article_publisher":"https:\/\/www.facebook.com\/lcndotcom","article_modified_time":"2020-03-05T13:33:09+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/www.lcn.com\/support\/wp-content\/uploads\/support-facebook-feed.jpg","type":"image\/jpeg"}],"twitter_card":"summary_large_image","twitter_site":"@lcndotcom","twitter_misc":{"Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/","url":"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/","name":"How to use a database with PHP - LCN.com","isPartOf":{"@id":"https:\/\/www.lcn.com\/support\/#website"},"datePublished":"2020-02-12T10:36:34+00:00","dateModified":"2020-03-05T13:33:09+00:00","description":"How to use a database with PHP at LCN.comFollow this simple guide on how to connect, query and insert records into a database with PHP scripts.","breadcrumb":{"@id":"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.lcn.com\/support\/articles\/how-to-use-a-database-with-php\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.lcn.com\/support\/"},{"@type":"ListItem","position":2,"name":"How to use a database with PHP"}]},{"@type":"WebSite","@id":"https:\/\/www.lcn.com\/support\/#website","url":"https:\/\/www.lcn.com\/support\/","name":"Customer Support Guides - LCN","description":"- LCN","publisher":{"@id":"https:\/\/www.lcn.com\/support\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.lcn.com\/support\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.lcn.com\/support\/#organization","name":"LCN.com","url":"https:\/\/www.lcn.com\/support\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.lcn.com\/support\/#\/schema\/logo\/image\/","url":"https:\/\/www.lcn.com\/support\/wp-content\/uploads\/2020\/02\/ZF_-hcc3.jpg","contentUrl":"https:\/\/www.lcn.com\/support\/wp-content\/uploads\/2020\/02\/ZF_-hcc3.jpg","width":461,"height":461,"caption":"LCN.com"},"image":{"@id":"https:\/\/www.lcn.com\/support\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/lcndotcom","https:\/\/x.com\/lcndotcom"]}]}},"_links":{"self":[{"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/ht-kb\/111","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/comments?post=111"}],"version-history":[{"count":2,"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/ht-kb\/111\/revisions"}],"predecessor-version":[{"id":1373,"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/ht-kb\/111\/revisions\/1373"}],"wp:attachment":[{"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/media?parent=111"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/ht-kb-category?post=111"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.lcn.com\/support\/wp-json\/wp\/v2\/ht-kb-tag?post=111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}