WebAppDev -Day1

Share

Few developers has strategy of “Design Before Code”. I fall into that category. Thus, I create few very basic tables, (External Link).


-- to create a new database
create database db_classicmodels;

-- to laod the database in the memory
USE db_classicmodels;

-- crate the parent tables first
CREATE TABLE productLines(
productLine VARCHAR(50) PRIMARY KEY,
textDescription VARCHAR(4000),
htmlDescription MEDIUMTEXT,
image mediumblob
);
-- Creating table products
CREATE TABLE products (
productCode VARCHAR(15) PRIMARY KEY,
productName VARCHAR(70) NOT NULL,
productLine VARCHAR(50) NOT NULL,
productScale VARCHAR(10) NOT NULL,
productVendor VARCHAR(50) NOT NULL,
productDescription TEXT NOT NULL,
quantityInStock SMALLINT(6) NOT NULL,
buyPrice DOUBLE NOT NULL,
MSRP DOUBLE NOT NULL,
INDEX(productLine),
FOREIGN KEY(productLine) REFERENCES productLines(productLine)
ON UPDATE CASCADE ON DELETE RESTRICT
);
-- creating table offices
CREATE TABLE offices(
officeCode VARCHAR(10) PRIMARY KEY,
city VARCHAR(50),
phone VARCHAR(50) NOT NULL,
addressLine1 VARCHAR(50) NOT NULL,
addressLine2 VARCHAR(50),
state VARCHAR(50),
country VARCHAR(50) NOT NULL,
postalCode VARCHAR(15) NOT NULL,
territory VARCHAR(50) NOT NULL
);
-- Create TABLE employees
CREATE TABLE employees (
employeeNumber INT(11) PRIMARY KEY,
lastName VARCHAR(50) NOT NULL,
firstName VARCHAR(50) NOT NULL,
extension VARCHAR(10) NOT NULL,
email VARCHAR(100) NOT NULL,
officeCode VARCHAR(10) NOT NULL,
reportTo INT(11),
jobTitle VARCHAR(50) NOT NULL,
INDEX(reportTo),
FOREIGN KEY(reportTo) REFERENCES employees(employeeNumber)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX(officeCode),
FOREIGN KEY(officeCode) REFERENCES offices(officeCode)
ON UPDATE CASCADE ON DELETE RESTRICT
);
-- Create table customers
CREATE TABLE customers(
customerNumber INT(11) PRIMARY KEY,
customerName VARCHAR(50) NOT NULL,
contactLastName VARCHAR(50) NOT NULL,
contactFirstName VARCHAR(50) NOT NULL,
phone VARCHAR(50) NOT NULL,
addressLine1 VARCHAR(50) NOT NULL,
addressLine2 VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL,
state VARCHAR(50),
postalCode VARCHAR(15),
country VARCHAR(50) NOT NULL,
saleRepEmployeeNumber INT,
creditLimit DOUBLE,
INDEX(saleRepEmployeeNumber),
FOREIGN KEY (saleRepEmployeeNumber) REFERENCES employees(employeeNumber)
ON UPDATE CASCADE ON DELETE RESTRICT
);
-- Creating table payments
CREATE TABLE payments(
customerNumber INT NOT NULL,
checkNumber VARCHAR(50) PRIMARY KEY,
paymentDate DATETIME NOT NULL,
amount DOUBLE,
INDEX(customerNumber),
FOREIGN KEY(customerNumber)
REFERENCES customers(customerNumber)
ON UPDATE CASCADE ON DELETE RESTRICT
);
-- Creating table orders
CREATE TABLE orders(
orderNumber INT(11) PRIMARY KEY,
orderDate DATETIME NOT NULL,
requiredDate DATETIME NOT NULL,
shippedDate DATETIME NOT NULL,
status VARCHAR(15) NOT NULL,
comments TEXT,
customerNumber INT NOT NULL,
INDEX(customerNumber),
FOREIGN KEY(customerNumber)
REFERENCES customers(customerNumber)
ON UPDATE CASCADE ON DELETE RESTRICT
);
-- Creating table orderdetails
CREATE TABLE orderdetails(
orderNumber INT NOT NULL,
productCode VARCHAR(15) NOT NULL,
quantityOrdered INT NOT NULL,
priceEach DOUBLE NOT NULL,
orderLineNumber SMALLINT(6) NOT NULL,
INDEX(productCode),
FOREIGN KEY(productCode)REFERENCES products(productCode)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX(orderNumber),
FOREIGN KEY(orderNumber)REFERENCES orders(orderNumber)
ON UPDATE CASCADE ON DELETE RESTRICT
);

Since, the database is ready, now we have different ways of writing PHP scripts for the same need:

Today, we wish to add an office:

Strategy:

  • Create a form
  • Connect to the database
  • Load the form values
  • Filter the values
  • Store the data in the database table

Now, since the form is ready, we connect to the MySQL database.


<?php
//database.php
//define the connecting parameters:
define('HOST', 'localhost'); //mark they are constants
define('USER', 'root');
define('PWD', '');
define('DB', 'db_classicmodels');

/////////////////////////////////////////////////////
//let's connect to the server now
///////////////////////////////////////////////////
$conxn = mysqli_connect(HOST, USER, PWD, DB)
or trigger_error(mysqli_error($conxn));

//to verify if the connection is success: debugging code
echo '
<pre>';
print_r($conxn);
echo '</pre>';
?>

Now, we need to process the code


<?php 
//process_add_office.php 
//connect to the server 
require_once('database.php');
 //laod the form values
 if(isset($_POST['cmdSubmit'])){
 $officeCode = mysqli_real_escape_string($conxn, $_POST['officeCode']);
 $city = mysqli_real_escape_string($conxn, $_POST['city']); 
$phone = mysqli_real_escape_string($conxn, $_POST['phone']);
 $addressLine1 = mysqli_real_escape_string($conxn, $_POST['addressLine1']); 
$addressLine2 = mysqli_real_escape_string($conxn, $_POST['addressLine2']);
 $state = mysqli_real_escape_string($conxn, $_POST['state']);
 $country = mysqli_real_escape_string($conxn, $_POST['country']);
 $postalCode = mysqli_real_escape_string($conxn, $_POST['postalCode']);
 $territory = mysqli_real_escape_string($conxn, $_POST['territory']);
 $officeCode = mysqli_real_escape_string($conxn, $_POST['officeCode']); 
}else{ 
echo "There's security breach, the action is reported"; 
exit; // do not progress with the control 
} 
//prepare the sql 
// we might use prepared stataments as well, but more on it later 
$sql = "INSERT INTO offices (officeCode, city, phone, addressLine1, 
addressLine2, state, country, postalCode, territory) VALUES ('$officeCode', 
'$city', '$phone', '$addressLine1', '$addressLine2', '$state','$country', 
'$postalCode', '$territory') ";
 //execute the sql 
$res = mysqli_query($conxn, $sql) 
or trigger_error(mysqli_error($conxn)); 
//decisive parmaeter 
$affRows = mysqli_affected_rows($conxn); // returns integer 
//close the server 
@mysqli_close($conxn); //@ sign is used to suppress the warnings 
//inform the user 
if($affRows > 0){
//success
echo "The office has been created";
}else{
echo "Sorry, the office could not be created";
}

?>

DOWNLOAD CODE and DATABASE

About Dhiraj 44 Articles
Blogger on development, securities, and efficiency on web technologies.

Be the first to comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.