Wednesday, October 19, 2016

Working with Database PHP MYSQL Wampstack Data Show on Bootstrap Table

October 19, 2016 , , , , , , , , , , , ,

Working with wampstack:

First of all download bitnami wampstack from this link,
https://bitnami.com/stack/wamp

Install the wampstack. Keep in mind when installing it will ask to set a username and password. It easier to keep user name as root and choose password that is easy to remember.

After installation is done next task is set wamp mysql to path. Go to, 
System Properties -> Advanced System Settings -> Environment Variables 
-> System Variables -> Path

Add a semicolon after whatever is already there and paste, "C:\Bitnami\wampstack-5.6.26-2\mysql\bin". Do not put a semicolon after it. Alternatively go to (version may differ), 
C:\Bitnami\wampstack-5.6.26-2\mysql\bin

Now that path is set it is possible to open command line and log in to mysql. In order to do it use the following, 
mysql -u root -p

Remember to set the path or do the alternative option. Now it will ask for password. Use the password that was given for phpmyadmin during bitnami installation.


Now create a database using, 
CREATE DATABASE cse

It could also be done from phpmyadmin. Now next task is to open phpmyadmin by going to "localhost/phpmyadmin" on web browser. Notice a new database called cse on the left.



That was just an example of terminal. Next task is to create tables. Open sql tab again and paste the code below and press go.
create table classroom
(building varchar(15),
room_number varchar(7),
capacity numeric(4,0),
primary key (building, room_number)
);
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key (dept_name),
constraint check (budget > 0)
);
create table course
(course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0),
primary key (course_id),
foreign key (dept_name) references department(dept_name)
on delete set null,
constraint check (credits > 0)
);
create table instructor
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department(dept_name)
on delete set null,
constraint check (salary > 29000)
);
create table section
(course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
constraint check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
year numeric(4,0),
constraint check (year > 1701 and year < 2100),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),
primary key (course_id, sec_id, semester, year),
foreign key (course_id) references course(course_id)
on delete cascade,
foreign key (building, room_number) references classroom(building, room_number)
on delete set null
);
create table teaches
(ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
primary key (ID, course_id, sec_id, semester, year),
foreign key (course_id,sec_id, semester, year) references section(course_id,sec_id, semester, year)
on delete cascade,
foreign key (ID) references instructor(ID)
on delete cascade
);
create table student
(ID varchar(5),
name varchar(20) not null,
dept_name varchar(20),
tot_cred numeric(3,0),
constraint check (tot_cred >= 0),
primary key (ID),
foreign key (dept_name) references department(dept_name)
on delete set null
);
create table takes
(ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),
primary key (ID, course_id, sec_id, semester, year),
foreign key (course_id,sec_id, semester, year) references section(course_id,sec_id, semester, year)
on delete cascade,
foreign key (ID) references student(ID)
on delete cascade
);
create table advisor
(s_ID varchar(5),
i_ID varchar(5),
primary key (s_ID),
foreign key (i_ID) references instructor (ID)
on delete set null,
foreign key (s_ID) references student (ID)
on delete cascade
);
create table time_slot
(time_slot_id varchar(4),
day varchar(1),
start_hr numeric(2),
constraint check (start_hr >= 0 and start_hr < 24),
start_min numeric(2),
constraint check (start_min >= 0 and start_min < 60),
end_hr numeric(2),
constraint check (end_hr >= 0 and end_hr < 24),
end_min numeric(2),
constraint check (end_min >= 0 and end_min < 60),
primary key (time_slot_id, day, start_hr, start_min)
);
create table prereq
(course_id varchar(8),
prereq_id varchar(8),
primary key (course_id, prereq_id),
foreign key (course_id) references course(course_id)
on delete cascade,
foreign key (prereq_id) references course(course_id)
);
view raw fixed ddl table.sql hosted with ❤ by GitHub

Next task is to get some data into database. Download the data given here,
http://codex.cs.yale.edu/avi/db-book/db6/lab-dir/sample_tables-dir/smallRelations/smallRelationsInsertFile.sql

Now go to import tab on phpmyadmin and select the smallRelationsInsertFile.sql file and press go. Now all the data will be inserted into cse database. These data can now be shown using php.

Go to "C:\Bitnami\wampstack-5.6.26-2\apache2\htdocs" and create a new folder named project where all files will be kept. Create a new php file there and name it "sample.php".

Code sample.php:

<!DOCTYPE html>
<html lang="en">
<head>
<title>Sample PHP Database Application</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0-alpha.5/css/bootstrap.min.css">
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.0.0-alpha.5/js/bootstrap.min.js"></script>
</head>
<body>
<?php
//Change the password to match your configuration
$link = mysqli_connect("localhost", "root", "yourPassword", "cse");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
echo "<br>";
$sql = "SELECT id, name, salary FROM instructor";
$result = $link->query($sql);
echo "<div class='container'>";
echo "<div class='row-fluid'>";
echo "<div class='col-xs-6'>";
echo "<div class='table-responsive'>";
echo "<table class='table table-hover table-inverse'>";
echo "<tr>";
echo "<th>ID</th>";
echo "<th>Name</th>";
echo "<th>Salary</th>";
echo "</tr>";
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row["id"] . "</td>";
echo "<td>" . $row["name"] . "</td>";
echo "<td>" . $row["salary"] . "</td>";
echo "</tr>";
}
} else {
echo "0 results";
}
echo "</table>";
$sql = "SELECT semester, year FROM section";
$result = $link->query($sql);
echo "<table class='table table-hover table-inverse'>";
echo "<tr>";
echo "<th>semester</th>";
echo "<th>year</th>";
echo "</tr>";
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row["semester"] . "</td>";
echo "<td>" . $row["year"] . "</td>";
echo "</tr>";
}
} else {
echo "0 results";
}
echo "</table>";
echo "</div>";
echo "</div>";
/*
* second table
*/
$sql = "SELECT course_id, title, dept_name, credits FROM course";
$result = $link->query($sql);
echo "<div class='col-xs-6'>";
echo "<div class='table-responsive'>";
echo "<table class='table table-hover table-inverse'>";
echo "<tr>";
echo "<th>Course ID</th>";
echo "<th>Title</th>";
echo "<th>Department</th>";
echo "<th>credits</th>";
echo "</tr>";
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row["course_id"] . "</td>";
echo "<td>" . $row["title"] . "</td>";
echo "<td>" . $row["dept_name"] . "</td>";
echo "<td>" . $row["credits"] . "</td>";
echo "</tr>";
}
} else {
echo "0 results";
}
echo "</table>";
echo "</div>";
echo "</div>";
echo "</div>";
/*
* second row
*/
$sql = "SELECT building, room_number, time_slot_id FROM section";
$result = $link->query($sql);
echo "<div class='row-fluid'>";
echo "<div class='col-xs-6'>";
echo "<div class='table-responsive'>";
echo "<table class='table table-hover table-inverse'>";
echo "<tr>";
echo "<th>building</th>";
echo "<th>room_number</th>";
echo "<th>time_slot_id</th>";
echo "</tr>";
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row["building"] . "</td>";
echo "<td>" . $row["room_number"] . "</td>";
echo "<td>" . $row["time_slot_id"] . "</td>";
echo "</tr>";
}
} else {
echo "0 results";
}
echo "</table>";
echo "</div>";
echo "</div>";
/*
* second table
*/
$sql = "SELECT course_id, prereq_id FROM prereq";
$result = $link->query($sql);
echo "<div class='col-xs-6'>";
echo "<div class='table-responsive'>";
echo "<table class='table table-hover table-inverse'>";
echo "<tr>";
echo "<th>Course ID</th>";
echo "<th>Prerequite ID</th>";
echo "</tr>";
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td>" . $row["course_id"] . "</td>";
echo "<td>" . $row["prereq_id"] . "</td>";
echo "</tr>";
}
} else {
echo "0 results";
}
echo "</table>";
echo "</div>";
echo "</div>";
echo "</div>";
echo "</div>";
// Close connection
mysqli_close($link);
?>
</body>
</html>
view raw mysql database to bootstrap table.php hosted with ❤ by GitHub

Final Result:

If everything above was done correctly the following will be shown. In order type the url in browser, 
localhost/project/sample.php


Problem:
A problem here can be port number. If "localhost/index.html" doesn't work, use something like "localhost:81/index.html". Change the port number accordingly. A way to know port number is open bitnami control panel and click "open phpmyadmin" and notice the port number.

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

POPULAR POSTS

BLOG ARCHIVE

Information

This site is continuation of:
https://quickgrid.wordpress.com

Version

QuickGrid Version 0.1.ß

Changelog

- Google Pagespeed ranking decreased to 80/100
- Not Fixed: Navbar scrolling smooth transition
- Not Fixed: Article header redesign
- Not Fixed: Tablet view webpage style