How do I integrate a db into this html table?

Question

Lighterning 0 Newbie Poster

4 Years Ago

Spent a day trying to gen any info on this but didn't find anything so maybe someone here could help me out .
here's the html code and a css style. Maybe it's not that difficult in the end but I have never done this and php is not my cup of tea, so I'd be really thankful if someone could help me out!

<html> 
<head>
    <link href="example.css" rel="stylesheet">
</head>
<body>

<div class="container">
  <h2>ACCOUNTS</h2>
  <ul class="responsive-table">
  <ul>

    <li class="table-header">
      <div class="col col-1">Department</div>
      <div class="col col-2">Account</div>
      <div class="col col-3">Account status</div>
      <div class="col col-4">Account info</div>
    </li>
    <li class="table-row">
      <div class="col col-1" data-label="Department">-</div>
      <div class="col col-2" data-label="Account">-</div>
      <div class="col col-3" data-label="Account status">-</div>
      <div class="col col-4" data-label="Account info">-</div>
    </li>
     <li class="table-row">
      <div class="col col-1" data-label="Department">-</div>
      <div class="col col-2" data-label="Account">-</div>
      <div class="col col-3" data-label="Account status">-</div>
      <div class="col col-4" data-label="Account info">-</div>
    </li>
    <li class="table-row">
      <div class="col col-1" data-label="Department">-</div>
      <div class="col col-2" data-label="Account">-</div>
      <div class="col col-3" data-label="Account status">-</div>
      <div class="col col-4" data-label="Account info">-</div>
    </li>
    <li class="table-row">
      <div class="col col-1" data-label="Department">-</div>
      <div class="col col-2" data-label="Account">-</div>
      <div class="col col-3" data-label="Account status">-</div>
      <div class="col col-4" data-label="Account info">-</div>
    </li>
    <li class="table-row">
      <div class="col col-1" data-label="Department">-</div>
      <div class="col col-2" data-label="Account">-</div>
      <div class="col col-3" data-label="Account status">-</div>
      <div class="col col-4" data-label="Account info">-</div>
    </li>
    <li class="table-row">
      <div class="col col-1" data-label="Department">-</div>
      <div class="col col-2" data-label="Account">-</div>
      <div class="col col-3" data-label="Account status">-</div>
      <div class="col col-4" data-label="Account info">-</div>
    </li>
    <li class="table-row">
      <div class="col col-1" data-label="Department">-</div>
      <div class="col col-2" data-label="Account">-</div>
      <div class="col col-3" data-label="Account status">-</div>
      <div class="col col-4" data-label="Account info">-</div>
    </li>
     <li class="table-row">
      <div class="col col-1" data-label="Department">-</div>
      <div class="col col-2" data-label="Account">-</div>
      <div class="col col-3" data-label="Account status">-</div>
      <div class="col col-4" data-label="Account info">-</div>
    </li>
     <li class="table-row">
      <div class="col col-1" data-label="Department">-</div>
      <div class="col col-2" data-label="Account">-</div>
      <div class="col col-3" data-label="Account status">-</div>
      <div class="col col-4" data-label="Account info">-</div>
    </li>
    </ul>
  </ul>

</div>
</body>
</html>

**css**

body {
  font-family: 'lato', sans-serif;
}
.container {
  max-width: 1400px;
  margin-left: auto;
  margin-right: auto;
  padding-left: 10px;
  padding-right: 10px;
}
h2 {
  font-size: 26px;
  margin: 20px 0;
  text-align: center;
}
h2 small {
  font-size: 0.7em;
}
.responsive-table li {
  border-radius: 3px;
  padding: 25px 30px;
  display: flex;
  justify-content: space-between;
  margin-bottom: 25px;
}
.responsive-table .table-header {
  background-color:  #5de383;
  font-size: 14px;
  text-transform: uppercase;
  letter-spacing: 0.03em;
}
.responsive-table .table-row {
  background-color:  #d9f3e1;
  box-shadow: 0px 0px 9px 0px rgba(0, 0, 0, 0.1);
}
.responsive-table .col-1 {
  flex-basis: 20%;
}
.responsive-table .col-2 {
  flex-basis: 40%;
}
.responsive-table .col-3 {
  flex-basis: 25%;
}
.responsive-table .col-4 {
  flex-basis: 25%;
}
@media (max-width: 800px) {
  .responsive-table .table-header {
    display: none;
  }
  .responsive-table li {
    display: block;
  }
  .responsive-table .col {
    flex-basis: 100%;
  }
  .responsive-table .col {
    display: flex;
    padding: 10px 0;
  }
  .responsive-table .col:before {
    color: #6C7A89;
    padding-right: 10px;
    content: attr(data-label);
    flex-basis: 50%;
    text-align: right;
  }
}

So there's also a code for the db that i need to integrate in particular the table called ac_account_management.

-- phpMyAdmin SQL Dump
-- version 3.5.1
-- http://www.phpmyadmin.net
--
-- Хост: 127.0.0.1
-- Время создания: Дек 12 2019 г., 19:33
-- Версия сервера: 5.5.25
-- Версия PHP: 5.3.13

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- База данных: `accounts`
--
CREATE DATABASE `accounts` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `accounts`;

-- --------------------------------------------------------

--
-- Структура таблицы `ac_account_management`
--

CREATE TABLE IF NOT EXISTS `ac_account_management` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Department` varchar(255) NOT NULL,
  `Account` varchar(255) NOT NULL,
  `Account_Status` varchar(255) NOT NULL,
  `Account_Info` varchar(255) CHARACTER SET utf32 NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Department` (`Department`),
  KEY `Department_2` (`Department`),
  KEY `Department_3` (`Department`),
  KEY `Account` (`Account`,`Account_Status`,`Account_Info`(191)),
  KEY `Account_Status` (`Account_Status`),
  KEY `Account_Info` (`Account_Info`(191)),
  KEY `Account_Info_2` (`Account_Info`(191))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

--
-- Дамп данных таблицы `ac_account_management`
--

INSERT INTO `ac_account_management` (`id`, `Department`, `Account`, `Account_Status`, `Account_Info`) VALUES
(1, 'Engineering', 'AD112-453-987012', 'pending', 'New Equipment'),
(2, 'Engineering', 'BM11-354-578963', 'paid', '-'),
(3, 'IT', 'FJ40-646-647985', 'pending', 'Order delivery'),
(5, 'Finances', 'HP57-777-986547', 'expired', 'New desks'),
(6, 'Finances', 'TU30-411-456278', 'paid', 'Planned flight'),
(7, 'Others', 'DR341-763-877588', 'failed', '-'),
(8, 'Others', 'LH76-670-867433', 'pending', '-'),
(9, 'Others', 'PI46-563-678097', 'paid', 'Unplanned expenses');

-- --------------------------------------------------------

--
-- Структура таблицы `ac_accounts`
--

CREATE TABLE IF NOT EXISTS `ac_accounts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Account` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Account` (`Account`),
  KEY `Account_2` (`Account`),
  KEY `Account_3` (`Account`),
  KEY `Account_4` (`Account`),
  KEY `Account_5` (`Account`),
  KEY `Account_6` (`Account`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

--
-- Дамп данных таблицы `ac_accounts`
--

INSERT INTO `ac_accounts` (`id`, `Account`) VALUES
(1, 'AD112-453-987012'),
(7, 'BM11-354-578963'),
(2, 'DR341-763-877588'),
(6, 'FJ40-646-647985'),
(5, 'HP57-777-986547'),
(9, 'LH76-670-867433'),
(8, 'PI46-563-678097'),
(4, 'TU30-411-456278');

-- --------------------------------------------------------

--
-- Структура таблицы `ac_departments`
--

CREATE TABLE IF NOT EXISTS `ac_departments` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Department` varchar(255) NOT NULL,
  `Dep_Desc` varchar(255) NOT NULL,
  `Units` varchar(255) NOT NULL,
  `Info` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Department` (`Department`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Дамп данных таблицы `ac_departments`
--

INSERT INTO `ac_departments` (`id`, `Department`, `Dep_Desc`, `Units`, `Info`) VALUES
(1, 'Finances', 'Financial management', '5', '-'),
(2, 'IT', 'IT dep', '3', '-'),
(4, 'Engineering', 'Engineer'' team', '15', '-'),
(5, 'Others', 'No particular department', '70', '-');

--
-- Ограничения внешнего ключа сохраненных таблиц
--

--
-- Ограничения внешнего ключа таблицы `ac_account_management`
--
ALTER TABLE `ac_account_management`
  ADD CONSTRAINT `ac_account_management_ibfk_1` FOREIGN KEY (`Department`) REFERENCES `ac_departments` (`Department`),
  ADD CONSTRAINT `ac_account_management_ibfk_2` FOREIGN KEY (`Account`) REFERENCES `ac_accounts` (`Account`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

html-css

4 Contributors
3 Replies
263 Views
3 Weeks Discussion Span
Latest Post 4 Years Ago Latest Post by Biiim

DGPickett 20 Newbie Poster

4 Years Ago

Wow, I just write a service that runs the query and reformats the column titles and values into an HTML table. If I wanted it to be more spreadsheet-like, there are caned javascript bits out there to allow you to re-sort the table.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Purvi_1 -12 Light Poster Banned · Answer 1 · 2019-12-19T04:35:19+00:00

The mysqli_connect() function has 4 parameters, the 4th is the name of your database

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = 'Something';

$conn = new mysqli($servername,$username,$password, $dbname);

For understand it deeply please refer following link

https://www.php.net/manual/en/function.mysqli-connect.php

Biiim 182 Junior Poster · Answer 2 · 2020-01-10T12:43:50+00:00

I have extracted the mysqli connection commands from my files here and done a quick query based on your tables, hopefully you can work out using the data in the $data array. (something like <?php foreach($data as $key->$value){echo $value['Department'];}?>)

<?php
$connection = mysqli_connect($host,$username,$pass,$database) or die(mysqli_connect_error());

$query = "SELECT dept.id `dept_id`, dept.Department, dept.Dep_desc, dept.Units, acc.id `acc_id` acc.Account FROM ac_departments dept"
    ." LEFT JOIN ac_account_management dept_acc_ref ON dept.id = dept_acc_ref.dept_id"
    ." LEFT JOIN ac_accounts acc ON dept_acc_ref.acc_id = acc.id";
$result = mysqli_query($connection,$query);
if($result === false){
    die(mysqli_error($connection));
}
$data = [];
while($row = mysqli_fetch_assoc($result)){
    $data[] = $row;
}

var_dump($data);
?>