BroadOakData.UK

Getting data from MySQL database using PHP

—

by

broadoakdata
in

db4free.net provides a testing service for the latest version of the MySQL Server. You can easily create an account for free and test your applications. db4free.net is also a good resource for education and to make yourself familiar with MySQL database via phpMyAdmin. For more advance example, please read this artcle – Contact Form using HTML, JavaScript, CSS, PHP and MySQL.

Prerequisites

  • access to MySQL database
  • PHP installed in your machine

Example

Getting data from a table as array. Let’s say you have a table which contains invoice data with the following fields:

invoice

Steps

  • store credentials in .env file
  • load .env in your php script
  • connect to MySQL using mysqli_connect function
  • fetch data from MySQL database and store each row in an array and use print_r to print out the result
<?php

// get database credentials from .env file
$env = parse_ini_file('.env');
$dbhost = $env["DBHOST"];
$dbuser = $env["DBUSER"];
$dbpass = $env["DBPASS"];
$dbname = $env["DBNAME"];

if (!$conn = mysqli_connect($dbhost,$dbuser,$dbpass,$dbname)) {
  die(mysql_error());
}

// get data and store them row in an array
$sql = "SELECT * FROM invoice;";
if ($result = $conn->query($sql)) {
  while ( $row = $result->fetch_assoc()) {
    $data[] = $row;
  }
  echo "<pre>";
  print_r($data);
  echo "</pre>";
}
// close connection
$conn->close();
?>

DDL Command

--
-- Table structure for table `invoice`
--

CREATE TABLE `invoice` (
  `id` int NOT NULL,
  `headquarter` varchar(90) DEFAULT NULL,
  `invoice_month` varchar(20) DEFAULT NULL,
  `invoice_year` int DEFAULT NULL,
  `customer` varchar(100) DEFAULT NULL,
  `invoice_no` int DEFAULT NULL,
  `invoice_value` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

--
-- Dumping data for table `invoice`
--

INSERT INTO `invoice` (`id`, `headquarter`, `invoice_month`, `invoice_year`, `customer`, `invoice_no`, `invoice_value`) VALUES
(1, 'Mirpur', 'January', 2021, 'Mini Market', 15057, 7283),
(2, 'Nozipur', 'February', 2020, 'Barakat', 19043, 11188);

Content of .env

DBHOST="db4free.net"
DBUSER="userid"
DBPASS="password"
DBNAME="database"

Dataset

We used dataset used in Google sheets exercise – see Import Data From Google Sheets To A MySQL Table

Output

invoice output