Skip to content

Instantly share code, notes, and snippets.

@danleyb2

danleyb2/dbcontroller.php

Created May 7, 2016 07:00
Show Gist options
  • Save danleyb2/77d539c0f1a8396a61eef23acb6f02fe to your computer and use it in GitHub Desktop.
Save danleyb2/77d539c0f1a8396a61eef23acb6f02fe to your computer and use it in GitHub Desktop.
Download ZIP
Raw
dbcontroller.php
<?php
// A simple PHP script demonstrating how to connect to MySQL.
// Press the 'Run' button on the top to start the web server,
// then click the URL that is emitted to the Output tab of the console.
$servername = getenv('IP');
$username = getenv('C9_USER');
$password = "";
$database = "c9";
$dbport = 3306;
// Create connection
$db = new mysqli($servername, $username, $password, $database, $dbport);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
#echo "Connected successfully (".$db->host_info.")";
$con=$db;
Raw
get_course.php
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
require_once("dbcontroller.php");
if(!empty($_POST["campus_id"])) {
$campus_id = $_POST["campus_id"];
$faculty_id=$_POST["faculty_id"];
$query ="SELECT * FROM `course` WHERE `campus_id`=$campus_id and `faculty_id` = $faculty_id";
$result = mysqli_query($con, $query);
if ( $result === false ) {
echo mysqli_error($con);
exit;
}
echo '<option value="">Select Course</option>';
while ( $course = mysqli_fetch_assoc($result) ) :
echo '<option value="' . $course['course_id'] . '">';
echo $course['course_name'];
echo '</option>';
endwhile;
}
?>
Raw
get_faculty.php
<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);
require_once("dbcontroller.php");
if(!empty($_POST["campus_id"])) {
$campus_id = $_POST["campus_id"];
$query ="SELECT * FROM faculty WHERE campus_id = $campus_id";
$result = mysqli_query($con, $query);
if ( $result === false ) {
echo mysqli_error($con);
exit;
}
echo '<option value="">Select Campus</option>';
while ( $faculty = mysqli_fetch_assoc($result) ) :
echo '<option value="' . $faculty['faculty_id'] . '">';
echo $faculty['faculty_name'];
echo '</option>';
endwhile;
}
?>
Raw
index.php
<?php
require_once("dbcontroller.php");
$query ="SELECT * FROM campus";
?>
<html>
<head>
<TITLE>Campus and Faculty Select</TITLE>
<head>
<style>
body{width:610px;}
.frmDronpDown {border: 1px solid #F0F0F0;background-color:#C8EEFD;margin: 2px 0px;padding:40px;}
.demoInputBox {padding: 10px;border: #F0F0F0 1px solid;border-radius: 4px;background-color: #FFF;width: 50%;}
.row{padding-bottom:15px;}
</style>
<script src="jquery.min.js" type="text/javascript"></script>
<script>
function getcampus_id(val) {
$.ajax({
type: "POST",
url: "get_faculty.php",
data:'campus_id='+val,
success: function(data){
$("#faculty-list").html(data);
}
});
}
function getfaculty_id(val) {
$.ajax({
type: "POST",
url: "get_course.php",
data:{
'faculty_id':val,
'campus_id':$('#campus-list').val()
},
success: function(data){
$("#course-list").html(data);
}
});
}
function selectcampus_id(val) {
$("#search-box").val(val);
$("#suggesstion-box").hide();
}
</script>
</head>
<body>
<form id="rightcol" action="form.php" method="post" class="container">
<div class="frmDronpDown">
<div class="row">
<label>Campus:</label><br/>
<select name="campus" id="campus-list" class="demoInputBox" onChange="getcampus_id(this.value);">
<option value="">Select Campus</option>
<?php
$query ="SELECT * FROM campus";
$result = mysqli_query($con, $query);
//loop
while($campus = mysqli_fetch_assoc($result)) :
//foreach ($result as $campus){
?>
<option value="<?php echo $campus["campus_id"]; ?>"> <?php echo $campus["name"]; ?></option>
<?php
endwhile;
?>
</select>
</div>
<div class="row">
<label>Faculty:</label><br/>
<select name="faculty" id="faculty-list" class="demoInputBox" onChange="getfaculty_id(this.value);">
<option value="">Select Faculty</option>
</select>
</div>
<div class="row">
<label>Course:</label><br/>
<select name="course" id="course-list" class="demoInputBox" >
<option value="">Select Course</option>
</select>
</div>
</div>
</body>
</html>
Raw
schema.sql
-- MySQL dump 10.13 Distrib 5.5.49, for debian-linux-gnu (x86_64)
--
-- Host: 0.0.0.0 Database: c9
-- ------------------------------------------------------
-- Server version 5.5.49-0ubuntu0.14.04.1
/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `campus`
--
DROP TABLE IF EXISTS `campus`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `campus` (
`campus_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`campus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `campus`
--
LOCK TABLES `campus` WRITE;
/*!40000 ALTER TABLE `campus` DISABLE KEYS */;
INSERT INTO `campus` VALUES (1,'Campus A'),(2,'Campus B'),(3,'campus C'),(4,'campus D'),(5,'campus E');
/*!40000 ALTER TABLE `campus` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `course`
--
DROP TABLE IF EXISTS `course`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `course` (
`course_id` int(11) NOT NULL AUTO_INCREMENT,
`course_name` varchar(30) DEFAULT NULL,
`campus_id` int(11) NOT NULL,
`faculty_id` int(11) NOT NULL,
PRIMARY KEY (`course_id`),
KEY `campus_id` (`campus_id`),
KEY `faculty_id` (`faculty_id`),
CONSTRAINT `course_ibfk_2` FOREIGN KEY (`faculty_id`) REFERENCES `faculty` (`faculty_id`),
CONSTRAINT `course_ibfk_1` FOREIGN KEY (`campus_id`) REFERENCES `campus` (`campus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `course`
--
LOCK TABLES `course` WRITE;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` VALUES (1,'Course A',1,1),(2,'Course B',1,1);
/*!40000 ALTER TABLE `course` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `faculty`
--
DROP TABLE IF EXISTS `faculty`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `faculty` (
`faculty_name` varchar(20) DEFAULT NULL,
`faculty_id` int(11) NOT NULL,
`campus_id` int(11) NOT NULL,
PRIMARY KEY (`faculty_id`),
KEY `campus_id` (`campus_id`),
CONSTRAINT `faculty_ibfk_1` FOREIGN KEY (`campus_id`) REFERENCES `campus` (`campus_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `faculty`
--
LOCK TABLES `faculty` WRITE;
/*!40000 ALTER TABLE `faculty` DISABLE KEYS */;
INSERT INTO `faculty` VALUES ('Faculty A',1,1),('Faculty B',2,1),('Faculty C',3,1);
/*!40000 ALTER TABLE `faculty` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-05-07 6:58:01
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment