Few developers has strategy of “Design Before Code”. I fall into that category. Thus, I create few very basic tables, (External Link).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
-- 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:
- On the same page (not recommended for freshers)
- On separate page
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
<?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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<?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"; } ?> |
Leave a Reply
You must be logged in to post a comment.