Last active
September 4, 2022 02:35
-
-
Save edoves/fdae23361201c76b58ebd41ca9757fa3 to your computer and use it in GitHub Desktop.
mysqlcommand, mysqlconnect php, mysqliconnectOOP version php
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
// Performs all actions necessary to log in an admin | |
function log_in_admin($admin) { | |
// Renerating the ID protects the admin from session fixation. | |
session_regenerate_id(); | |
$_SESSION['admin_id'] = $admin['id']; | |
$_SESSION['last_login'] = time(); | |
$_SESSION['username'] = $admin['username']; | |
return true; | |
} | |
// Performs all actions necessary to log out an admin | |
function log_out_admin() { | |
unset($_SESSION['admin_id']); | |
unset($_SESSION['last_login']); | |
unset($_SESSION['username']); | |
// session_destroy(); // optional: destroys the whole session | |
return true; | |
} | |
// is_logged_in() contains all the logic for determining if a | |
// request should be considered a "logged in" request or not. | |
// It is the core of require_login() but it can also be called | |
// on its own in other contexts (e.g. display one link if an admin | |
// is logged in and display another link if they are not) | |
function is_logged_in() { | |
// Having a admin_id in the session serves a dual-purpose: | |
// - Its presence indicates the admin is logged in. | |
// - Its value tells which admin for looking up their record. | |
return isset($_SESSION['admin_id']); | |
} | |
// Call require_login() at the top of any page which needs to | |
// require a valid login before granting acccess to the page. | |
function require_login() { | |
if(!is_logged_in()) { | |
redirect_to(url_for('/staff/login.php')); | |
} else { | |
// Do nothing, let the rest of the page proceed | |
} | |
} | |
?> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP TABLE IF EXISTS `admins`; | |
CREATE TABLE `admins` ( | |
`id` int(11) NOT NULL AUTO_INCREMENT, | |
`first_name` varchar(255) DEFAULT NULL, | |
`last_name` varchar(255) DEFAULT NULL, | |
`email` varchar(255) DEFAULT NULL, | |
`username` varchar(255) DEFAULT NULL, | |
`hashed_password` varchar(255) DEFAULT NULL, | |
PRIMARY KEY (`id`), | |
KEY `index_username` (`username`) | |
); | |
INSERT INTO `admins` VALUES (1,'John','Public','john@somewhere.com','johnqpublic','$2y$10$lkeLxdtcxhm3QZSvixDkpeI/6qvy2Z8GVKWoSzLMXqv0M5k3J67J6'); | |
DROP TABLE IF EXISTS `pages`; | |
CREATE TABLE `pages` ( | |
`id` int(11) NOT NULL AUTO_INCREMENT, | |
`subject_id` int(11) DEFAULT NULL, | |
`menu_name` varchar(255) DEFAULT NULL, | |
`position` int(3) DEFAULT NULL, | |
`visible` tinyint(1) DEFAULT NULL, | |
`content` text, | |
PRIMARY KEY (`id`), | |
KEY `fk_subject_id` (`subject_id`) | |
); | |
INSERT INTO `pages` VALUES (1,1,'Globe Bank',1,0,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/about us_96582054.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>About Globe Bank</h1>\r\n <p>Our mission at Globe Bank International is simple: To serve every coordinate in every hemisphere as if it were our own. From the beginning, it\'s been our goal to incorporate world-class services with an unmatched level of responsiveness and thoughtfulness, no matter what your level of banking need. </p>\r\n\r\n <p>Globe Bank International (NYSE: GBI), founded in 1950, is one of the newer financial institutions widely active in the world financial market. Despite our youth, we have a history solidly built on hard work, common-sense business practices, empowering investments, and an unyielding dedication to excellence.</p>\r\n\r\n <p>We currently operate in 42 countries and have nearly 130,000 employees. Our client base is in the millions, from individuals to worldwide conglomerates, and our assets total approximately $1.8 trillion. Learn more about our services and our history, and let us know how we can work together to help you.</p>\r\n\r\n</div>\r\n'),(2,1,'History',2,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/history_643025418.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>History</h1>\r\n <p>Founded in 1950 by brothers Otis and Isaac Bott, Globe Bank launched as a community financial institution with a fervent mission to help small businesses get their footing in the new economy of Postwar England. While the promise of prosperity was rampant, it was unfortunate that those willing to help with financial backing were understandably cautious and scarce.</p>\r\n\r\n <p>The Botts believed in the power of community and ingenuity, so much so that several organizations, even to this day, credit the brothers for both their initial formation and their continued success. Among those businesses in the early days is what we now know as The Landon Hotel chain. As others repeatedly turned down requests for financing from its passionate, but inexperienced founder, Arthur Landon, the Botts appreciated and supported Landon\'s verve and vision. What started as a single hotel in London\'s West End, backed by Globe Bank, is now an impressive hotel empire that thrives in cities all around the world.</p>\r\n\r\n <p>More recently, Globe Bank International has received acclaim for its high-profile investment in Winged Achene Technologies, giving it the means to expand from a small specialty helicopter blade company to a transnational rotorcraft resource, expanding not only the company\'s financial horizons, but creating several thousand sustainable jobs in three cities.</p>\r\n\r\n <p>The Botts\' keen instincts for solid investments grew just as swiftly as those companies it supported. In 1974, it moved its home office from London to Washington D.C. to better serve its growing clientele, and changed its name to Globe Bank International. Globe Bank International rapidly became key in corporate, commercial, and investment banking in the United States and internationally, assisting in intercontinental and domestic asset management and growth.</p>\r\n\r\n <p>Still, at its core, Globe Bank International remains a community bank, focusing on the specific needs of individuals and organizations in each community in which its branches are located. Today, Globe Bank is overseen by Otis Bott, President, alongside his son, Gerald Bott, CEO. They have successfully maintained the values and ideals of the family business, despite the bank\'s tremendous growth over the last half century.</p>\r\n\r\n</div>\r\n'),(3,1,'Leadership',3,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/leadership_469723021.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Leadership</h1>\r\n\r\n <h2>Board of Directors</h2>\r\n <ul>\r\n <li>Robert Otis Bott, President</li>\r\n <li>Sarah M. Bott</li>\r\n <li>Alisha Bryan</li>\r\n <li>Henry Terry</li>\r\n <li>Meredith Jewel Coffey</li>\r\n <li>Jesse Gould</li>\r\n <li>Lea Sheryl Rodriquez</li>\r\n <li>Joseph Riley</li>\r\n <li>Martin Stephens</li>\r\n <li>Jimmie Frank</li>\r\n </ul>\r\n\r\n <h2>Executive Team</h2>\r\n <ul>\r\n <li>Gerald Bott, Chairman and Chief Executive Officer</li>\r\n <li>Stewart Talley, Chief Risk Officer</li>\r\n <li>Judson Phillips, General Counsel</li>\r\n <li>Naomi Ballard, VP Human Resources</li>\r\n <li>Dominique Stein, Asset Management CEO </li>\r\n <li>Cantby Bott, Chief Financial Officer</li>\r\n <li>Frederic Owen, Commercial Banking CEO</li>\r\n <li>Freeman McConnell, Corporate & Investment Bank CEO</li>\r\n <li>Saul Hunt, Consumer & Community Banking CEO</li>\r\n <li>Cheri Karla Mann, Chief Operating Officer</li>\r\n </ul>\r\n\r\n</div>\r\n'),(4,1,'Contact Us',4,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/contact_dv1060035.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n\r\n<div id=\"content\">\r\n <h1>Contact Us</h1>\r\n <p>We\'re available 24 hours a day, 7 days a week for all your customer service needs.</p>\r\n <ul>\r\n <li><a href=\"#\">General banking</a></li>\r\n <li><a href=\"#\">Online banking</a></li>\r\n <li><a href=\"#\">Mobile banking</a></li>\r\n <li><a href=\"#\">Report fraud</a></li>\r\n </ul>\r\n\r\n</div>\r\n'),(5,2,'Banking',1,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/banking_57278269.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Banking</h1>\r\n <h2>Branch, ATM, and Online Banking </h2>\r\n <p>Bank from anywhere around the globe! With hundreds of branches and even more ATMs, it\'s almost guaranteed that you\'re within a short walk or drive from one of our locations. We go beyond the typical banking hours with our secure online banking services. If you need to manage or move your money, your accounts are available 24 hours a day. </p>\r\n\r\n <ul>\r\n <li><a href=\"#\">Find a branch</a></li>\r\n\r\n <li><a href=\"#\">Find an ATM</a></li>\r\n\r\n <li><a href=\"#\">Learn about online banking</a></li>\r\n\r\n <li><a href=\"#\">Learn about Bott Bill Pay</a></li>\r\n </ul>\r\n\r\n</div>\r\n'),(6,2,'Credit Cards',2,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/creditcards_598949380.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Credit Cards</h1>\r\n <p>Our credit card program has been redesigned to help everyone build and improve their credit rather than sink deep into debt. With variable rates that suite your financial profile and needs, we grow with you, rather than against you.</p>\r\n <ul>\r\n <li><a href=\"#\">Compare our credit cards</a></li>\r\n <li><a href=\"#\">Cash back credit cards</a></li>\r\n <li><a href=\"#\">Rewards cards</a></li>\r\n <li><a href=\"#\">Check your credit score</a></li>\r\n <li><a href=\"#\">Transfer a balance</a></li>\r\n </ul>\r\n</div>\r\n'),(7,2,'Mortgages',3,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/homeloans_84513610.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Mortgages</h1>\r\n <p>People shouldn\'t have to buy the farm before they buy the farm. We believed that in 1950, and we believe that today. A home—whether a farm or condo or anything in between—is a place where you should feel safe and secure, not afraid that your mortgage is going to drain you of all happiness. Our home loan and home equity professionals take the time to discuss all options, and combine our pre-approval screening with financial forecasts so you know exactly what you can afford for your first or next home.</p>\r\n <ul>\r\n <li><a href=\"#\">Check current mortgage rates</a></li>\r\n <li><a href=\"#\">Check current refinance rates</a></li>\r\n <li><a href=\"#\">Learn how to buy a home</a></li>\r\n <li><a href=\"#\">Learn how to refinance</a></li>\r\n <li><a href=\"#\">Mortgage rate calculator</a></li>\r\n </ul>\r\n</div>\r\n'),(8,3,'Checking',1,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/bizchecking_86519574.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Business Checking</h1>\r\n <p>Options abound when it comes to selecting a Globe Bank business checking account. With so many choices, it might seem daunting to select the account that\'s right for your business. However, our talented customer service team is always available to help, whether in person, by phone, or online. Take a peek at some of our options, and when you\'re ready to get started, give us a shout.</p>\r\n\r\n <ul>\r\n <li><a href=\"#\">Compare our checking accounts</a></li>\r\n <li><a href=\"#\">What you\'ll need to open an account</a></li>\r\n <li><a href=\"#\">How to choose the right checking account for your business</a></li>\r\n <li><a href=\"#\">Accounts with no monthly service fee</a></li>\r\n <li><a href=\"#\">Order checks</a></li>\r\n </ul>\r\n\r\n</div>\r\n'),(9,3,'Loans',2,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/bizloans_539438468.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Business Loans</h1>\r\n <p>Businesses need upkeep to stay profitable in a competitive market. Whether you need to purchase new equipment, have plans for an expansion, or want to remodel your facility, Globe Bank can help you finance your vision.</p>\r\n <ul>\r\n <li><a href=\"#\">Compare our business term loans</a></li>\r\n <li><a href=\"#\">Learn about SBA loan options</a></li>\r\n <li><a href=\"#\">Estimate monthly payments</a></li>\r\n <li><a href=\"#\">Check your application status</a></li>\r\n </ul>\r\n\r\n</div>\r\n'),(10,3,'Merchant Services',3,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/merchant_619738814.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Merchant Services</h1>\r\n <p>Whether onsite, online, or on-the-go, your business needs to be nimble in processing payments. We\'ve got the experience to navigate the ever-changing world of payment processing technology. We\'ll make it easy for you to get started with a merchant account, so you can start taking payments almost immediately. </p>\r\n <ul>\r\n <li><a href=\"#\">Compare our merchant accounts</a></li>\r\n <li><a href=\"#\">Credit card processing options</a></li>\r\n <li><a href=\"#\">Accepting mobile payments</a></li>\r\n <li><a href=\"#\">POS systems</a></li>\r\n </ul>\r\n\r\n</div>\r\n'),(11,5,'Financing',1,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/financing_522867260.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Financing</h1>\r\n <p>From simple loans to long-term financing for sustained corporate growth, our advisors are here to help with a comprehensive selection of solutions and products to build your business.</p>\r\n <ul>\r\n <li><a href=\"#\">Lines of credit</a></li>\r\n <li><a href=\"#\">Asset-based loans</a></li>\r\n <li><a href=\"#\">Real estate loans</a></li>\r\n <li><a href=\"#\">Employee stock ownership plans</a></li>\r\n </ul>\r\n\r\n</div>\r\n'),(12,5,'Investments',2,1,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/investments_97489446.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Investments and Asset Management</h1>\r\n <p>We\'ll guide you through the global market like no one else can—from global liquidity, real estate securities, hedge funds, and more. We\'ll help you understand investment risk, dealing with inflation and soft economies, as well as asset allocation and regulatory requirements.</p>\r\n <ul>\r\n <li><a href=\"#\">Investments for insurance companies</a></li>\r\n <li><a href=\"#\">Pension fund investments</a></li>\r\n <li><a href=\"#\">Investment goals for non-profits and charitable institutions</a></li>\r\n <li><a href=\"#\">Educational establishments investments and endowments</a></li>\r\n <li><a href=\"#\">Social and environmental impact of investments</a></li>\r\n </ul>\r\n\r\n</div>\r\n'),(13,5,'Treasury Services',3,0,'<div id=\"hero-image\">\r\n <img src=\"images/page_assets/treasury_591423262.png\" width=\"900\" height=\"200\" alt=\"\" />\r\n</div>\r\n\r\n<div id=\"content\">\r\n <h1>Treasury Services</h1>\r\n <p>Let Globe Bank be your co-CFO. We\'ll help you improve cash flow, improve efficiency, streamline payroll and payment processing, and help you to build a leaner, cleaner organization.</p>\r\n <ul>\r\n <li><a href=\"#\">Collections and cash applications</a></li>\r\n <li><a href=\"#\">Deposit and account management options</a></li>\r\n <li><a href=\"#\">Payment processing</a></li>\r\n <li><a href=\"#\">Payroll procedures</a></li>\r\n <li><a href=\"#\">Fraud protection</a></li>\r\n <li><a href=\"#\">Liquidity management</a></li>\r\n <li><a href=\"#\">International expansion</a></li>\r\n <li><a href=\"#\">Healthcare program management</a></li>\r\n </ul>\r\n\r\n</div>\r\n'); | |
DROP TABLE IF EXISTS `subjects`; | |
CREATE TABLE `subjects` ( | |
`id` int(11) NOT NULL AUTO_INCREMENT, | |
`menu_name` varchar(255) DEFAULT NULL, | |
`position` int(3) DEFAULT NULL, | |
`visible` tinyint(1) DEFAULT NULL, | |
PRIMARY KEY (`id`) | |
); | |
INSERT INTO `subjects` VALUES (1,'About Globe Bank',1,1),(2,'Consumer',2,1),(3,'Small Business',3,0),(5,'Commercial',4,1); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- There are four main commands we need to know about creartin databases. | |
1. SHOW DATABASES; | |
2. CREATE DATABASE db_name; | |
3. USE db_name; | |
4. DROP DATABASE db_name; | |
-- command to create DATABASE | |
CREATE DATABASE aliendatabase; | |
-- Before you can create the table inside the database, you need to make sure you’ve got our new database selected. Enter the command | |
USE aliendatabase; | |
-- command for creating username and password of specific data base | |
GRANT ALL PRIVILEGES ON db_name.* | |
TO 'username'@'localhost' | |
IDENTIFIED BY 'password'; | |
-- put a password to root database | |
SET PASSWORD FOR root@localhost = password('password'); | |
-- Check the PRIVILEGES iod the database | |
SHOW GRANTS FOR 'username'@'localhost'; | |
cd c:\xampp\mysql\bin | |
mysql.exe -u root -p | |
--if you just created or setup a new user name and password to a new database | |
--you can access it by typing | |
=> mysql -u widget_cms -p widget_corp | |
--then it will ask for aa password | |
-- SQL command that creates a new table CREATE TABLE All the other stuff is detailed information about what kinds of data can be stored in the table. | |
CREATE TABLE table_name ( | |
-- collation or character set | |
first_name VARCHAR(30), | |
last_name VARCHAR(30), | |
when_it_happened VARCHAR(30), | |
how_long VARCHAR(30), | |
how_many VARCHAR(30), | |
alien_description VARCHAR(100), | |
what_they_did VARCHAR(100), | |
fang_spotted VARCHAR(10), | |
other VARCHAR(100), | |
email VARCHAR(50) | |
); | |
CREATE TABLE subjects ( | |
id INT(11) NOT NULL AUTO_INCREMENT, | |
menu_name VARCHAR(255), | |
position INT(3), | |
visible TINYINT(1), | |
PRIMARY KEY (id) | |
); | |
-- Example of creating tables from lynda.com | |
-- I BIG NOTE | |
-- cReate tables as many as you can right from the start of your project if the data modeling reveals any problems | |
-- you wanna address those before you start it made change your approch to designing pages | |
CREATE TABLE subjects ( | |
id INT(11) NOT NULL AUTO_INCREMENT, | |
menu_name VARCHAR(30) NOT NuLL, | |
position INT(3) NOT NULL, | |
visible TINYINT(1) NOT NULL, | |
PRIMARY KEY (id) | |
); | |
SHOW TABLES; | |
-- Showing the tables columns all threee return the same thing | |
SHOW COLUMNS FROM table_name; | |
DESCRIBE table_name -- SHOW COLUMNS FROM table_name is doing the same thing as DESCRBE table_name | |
SHOW FIELDS FROM table_name | |
-- DELETING TABLES | |
DROP TABLE table_name; | |
-- creatin another table on a database that can connect to other table | |
CREATE TABLE pages ( | |
id INT(11) NOT NULL AUTO_INCREMENT, | |
subject_id INT(11) NOT NULL, -- Foreign key | |
menu_name VARCHAR(30) NOT NULL, | |
position INT(3) NOT NULL, | |
visible TINYINT(1) NOT NULL, -- for bolean vakues if 1 meand true if -1 means false | |
content TEXT, | |
PRIMARY KEY (ID), | |
INDEX (subject_id) -- index is going to tell mysql that it should also created index for it for past look ups | |
); | |
ALTER TABLE pages ADD INDEX fk_subject_id (subject_id); | |
DROP TABLE IF EXISTS `admins`; | |
CREATE TABLE `admins` ( | |
`id` int(11) NOT NULL AUTO_INCREMENT, | |
`first_name` varchar(255) DEFAULT NULL, | |
`last_name` varchar(255) DEFAULT NULL, | |
`email` varchar(255) DEFAULT NULL, | |
`username` varchar(255) DEFAULT NULL, | |
`hashed_password` varchar(255) DEFAULT NULL, | |
PRIMARY KEY (`id`), | |
KEY `index_username` (`username`) | |
); | |
-- adding index if is not exists | |
ALTER TABLE admins ADD INDEX index_username (username); | |
CREATE TABLE IF NOT EXISTS `products` ( | |
`sku` int(11) DEFAULT NULL, | |
`name` varchar(255) DEFAULT NULL, | |
`img` varchar(255) DEFAULT NULL, | |
`email` varchar(255) DEFAULT NULL, | |
`price` decimal(10,2) DEFAULT NULL, | |
`paypal` varchar(32) DEFAULT NULL | |
); | |
--Popilate a MYSQL table | |
INSERT INTO subjects (menu_name, position, visible) VALUES ('About Globe Bank', 1,1); | |
INSERT INTO subjects (menu_name, position, visible) VALUES ('Consumer', 2,1); | |
INSERT INTO subjects (menu_name, position, visible) VALUES ('Small Business', 3,0); | |
INSERT INTO subjects (menu_name, position, visible) VALUES ('Junk', 3,1); | |
--Ralational | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (1, 'Globe Bank', 1, 1); | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (1, 'History', 2, 1); | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (1, 'Leadership', 3, 1); | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (1, 'Contact Us', 4, 1); | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (2, 'Banking', 1, 1); | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (2, 'Credit Cards', 2, 1); | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (2, 'Mortgages', 3, 1); | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (3, 'Checking', 1, 1); | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (3, 'Loans', 2, 1); | |
INSERT INTO pages (subject_id, menu_name, position, visible) VALUES (3, 'Merchant Services', 3, 1); | |
--########### CRUD CREAT, READ, UPDATE, DELETE ###########-- | |
--SQL INSERT(CREAT) | |
INSERT INTO table_name (column1, column2, column2, column4) | |
VALUES (val1, val2, val3, val4); | |
--SQL SELECT(READ) | |
SELECT * | |
FROM table_name | |
WHERE columnName = 'RowName' | |
ORDER BY column1 ASC; | |
--SQL SELECT(UPDATE) | |
UPDATE table_name | |
SET column1 = 'some text' | |
WHERE id = 1; | |
--SQL SELECT(DELETE) | |
DELETE FROM table_name | |
WHERE id = 1; | |
------------------------NOTE FILTERING------------------------------------- | |
ORDER BY has to be after every WHERE clause, LIMIT and OFFSET needds to be last and FROM clause needs to be first after all | |
-- Select key word for reading the data into the data base | |
-- Select all of the data for customers with a first name of Martin: | |
SELECT * FROM email_list WHERE first_name = 'Martin' | |
-- Select only the last name for customers with a first name of Bubba: | |
SELECT last_name FROM email_list WHERE first_name = 'Bubba' | |
-- Selecting individual column by naming them after SELECT clause | |
-- Select the first name and last name for the customer with an email address of ls@objectville.net. | |
SELECT first_name, last_name FROM email_list WHERE email = 'ls@objectville.net' | |
-- Select all of the columns for customers with a first name of Amber and a last name of McCarthy: | |
-- The WHERE clause can be made dependent on multiple pieces of information, in this case a match for both a first name AND a last name. | |
SELECT * FROM email_list WHERE first_name = 'Amber' AND last_name = 'McCarthy' | |
------------------------TEAM TREEHOUSE------------------------------------- | |
-- Searching within a set of values | |
SELECT columnName, columnName FROM tableName WHERE columnName = 'value' OR columnName = 'value'; | |
SELECT columnName, columnName FROM tableName WHERE columnName IN ('value', 'value'); | |
-- Opposite Result | |
SELECT columnName, columnName FROM tableName WHERE columnName NOT IN ('value', 'value'); | |
Seaching within a range of values | |
-- Same out put | |
SELECT title, author FROM books WHERE fist_published >= 1800 AND first_published <= 1899 | |
SELECT title, author FROM books WHERE fist_published BETWEEN 1800 AND 1899 | |
SELECT <columuns> FROM <table> WHERE <columun> BETWEEN <minimun> AND <maximum> - lower value has to be first | |
SELECT * FROM subjects WHERE visible = 1 ORDER BY position DESC; | |
-- The INSERT INTO keyword is use to CREATe data into the data base | |
INSERT INTO table_name (column_name1, column_name2, ... ) VALUES ('value1', 'value2', ... ) | |
INSERT INTO movies (title, year) VALUES ('Avatar', 2009), ("Avatar 2", NULL ) | |
-- Another way of inserting data into the table using the SET keyword | |
INSERT INTO movies SET title = "Back to the future", year = 1985 | |
-- Updating data into the data base using the UPDATE keyword with the SET keyword example below: | |
UPDATE subjects SET visible = 1 WHERE id = 4; | |
UPDATE movies SET year = 2015 WHERE title = 'Avatar 2' | |
-- updating multiplae values | |
--bedore the year is 2015 ansd he tite was Avatar 2 with this code it will update the data into the database | |
UPDATE movies SET year = 2016, title = "Avatar Reloaded" WHERE title = 'Avatar 2' | |
-- DDeleting data into the database with keyword DELETE | |
-- IT will delete everting from the table | |
DELETE FROM table_name; | |
-- Delete the data on the specific column on the table | |
DELETE FROM movies WHERE title = "Avatar Reloaded " AND year = 2016 | |
-- Maniulating Schema with the use of DDL or Data Definition Language; | |
--reanme the tabl movies to movie_table | |
RENAME TABLE movies TO movie_table; | |
-- If you want to change the two table in one go this is how you do it | |
RENAME TABLE movies TO movie_table, actors TO actor_table; | |
-- This the command for remove/delete the table | |
DROP TABLE table_name; | |
-- Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables. | |
TRUNCATE TABLE table_name; | |
-- Rename the database | |
-- ways to read from the database | |
$query = "SELECT * FROM users"; | |
while($row = mysqli_fetch_assoc($result) ) { | |
echo '<pre>'; | |
print_r( $row ); | |
echo '</pre>'; | |
} | |
--Create data from form nd insert to the database | |
$query = "INSERT INTO users( username, password ) "; | |
$query .= "VALUES ( '$username', '$password' )"; | |
-- other way in doing the connection to the database using an array and foreach loop | |
$db['db_host'] = 'localhost'; | |
$db['db_user'] = 'edwin_cms'; | |
$db['db_pass'] = 'msDYw6w37aqx9EnV'; | |
$db['db_name'] = 'edwin_cms'; | |
foreach ($db as $key => $value) { | |
define(strtoupper($key), $value); | |
} | |
------------------------------------------------------------------------ | |
$connection = mysqli_connect( DB_HOST, DB_USER, DB_PASS, DB_NAME ); | |
-- PHP to MySQL connection | |
-- 1. Create a DATABASE connection | |
$dbhost = 'localhost'; | |
$dbuser = 'widget_cms'; | |
$dbpass = 'secretpassword'; | |
$dbname = 'widget_corp'; | |
$connection = mysqli_connect( $dbhost, $dbuser, $dbpass, $dbname ); | |
-- Test if Connection occured. | |
if( mysqli_connect_errno() ) { | |
die( 'Database connection failed: ' . mysqli_connect_error() . ' (' . mysqli_connect_errno() . ')' ); | |
} | |
-- another way for creatign database connection using array and contant variable | |
$db['db_host'] = 'localhost'; | |
$db['db_user'] = 'root'; | |
$db['db_pass'] = ''; | |
$db['db_name'] = 'cms'; | |
-- loop through the array and make the key big letters o define on constant | |
foreach( $db as $key => $value ) { | |
define(strtoupper( $key), $value ); | |
} | |
$connection = mysqli_connect( DB_HOST, DB_USER, DB_PASS, DB_NAME ); | |
if( $connection ) { | |
echo 'We are connected'; | |
} | |
-- 2. Perform daabase query | |
$query = 'SELECT * FROM subjects'; | |
-- Test to see if the query is succeded or not | |
$result = mysqli_query ($connection, $query ); | |
-- test to see if the query is succeded or not | |
if( !$result ) { | |
die( 'Database query failed.'); | |
} | |
-- 3 use returned data (if any) yu can ut this code on the inside the body html | |
while ( $row = mysqli_fetch_row($result) ) { | |
-- output data from eah row | |
var_dump($row); | |
echo '</hr>'; | |
} | |
-- 4 realeas the retrun data | |
mysqli_free_result($result); | |
// 5. Closer database connection | |
mysqli_close( $connection ); | |
-- creating records in to the data base | |
$query = "INSERT INTO subjects ("; | |
$query .= " menu_name, position, visible"; | |
$query = ") VALUES ("; | |
$query = " '{$menu_name}', {$position}, {$visible}"; -- curly brase is optional | |
$query = ")"; | |
$result = mysqli_query ($connection, $query ); | |
--$result check the query more details information | |
if($result) { | |
-- success | |
-- redirect_to(somepage.php) | |
echo 'Success!!!'; | |
} else { | |
--failure | |
die( 'Database query failed.' . mysqli_error($connection)); | |
} | |
-- UPDATE records in to the data base | |
$query = "UPDATE subjects SET "; | |
$query .= "menu_name = {$menu_name}', "; | |
$query = "position = {position}, "; | |
$query = "visible = {$visible} "; -- curly brase is optional | |
$query = "WHERE id = {$id}"; | |
-- SQL function that ignore the some special characters like the ' | |
$username = mysqli_real_escape_string( $connection, $username ); | |
$password = mysqli_real_escape_string( $connection, $password ); | |
-- testing the query | |
if (!$create_category) { | |
die("QUERY FAILED" . mysqli_error($connection)); | |
} } | |
-- note: we are going to redirect user to a certain page | |
-- <?php ob_start(); ?> is a output buffering. putting this to a header.php is to turn on your output buffering | |
-- header("Location: ../index.php"); header() function dependent on the output buffering function | |
-- if you already have a file for a database and want it to import the mysql database | |
mysqli -u webuser -p globe_bank < path/to/file.sql | |
-- ###### TreeHouse TUTs ###### -- | |
-- ###### TreeHouse TUTs ###### -- | |
-- ###### TreeHouse TUTs ###### -- | |
-- ###### TreeHouse TUTs ###### -- | |
-- ###### TreeHouse TUTs ###### -- | |
-- ###### TreeHouse TUTs ###### -- | |
-- ###### TreeHouse TUTs ###### -- | |
-- This is the basic SQL statement to create a table. | |
CREATE TABLE actors(name VARCHAR(50)); -- actors is the table name and inside the parenthesis is the olumns definition | |
-- Table with two columns | |
CREATE TABLE movies(title VARCHAR(200), year INTEGER); | |
-- Insert date into the movies table | |
INSERT INTO movies VALUES("Avatar",2009) ; | |
--#Retrieving a Result Set | |
SELECT * FROM movies; --# Select all or show all from movies table | |
SELECT movies.title, movies.year FROM movies; | |
SELECT movies.year, movies.title FROM movies; --#alter the oder of the column by switching the year and title | |
SELECT year, title FROM movies; --#alter the oder of the column by switching the year and title | |
SELECT title, year FROM movies; --#alter the oder of the column by switching the year and title | |
SELECT title FROM movies; --#Show only the title column | |
--#Querying Tables | |
SELECT * FROM movies WHERE year = 1999; --#show only movies that has adate of 1999 | |
SELECT * FROM movies WHERE year != 1999; --#show moveis that not include movies from 1999 | |
SELECT * FROM movies WHERE year > 1999; --#get movies after year 1999 | |
SELECT * FROM movies WHERE year >= 1999; --#get movies from 1999 to the present time | |
SELECT * FROM movies WHERE year < 1999; --#get movies before 1999 | |
SELECT * FROM movies WHERE year <= 1999; --#get movies before and in the year 1999 | |
SELECT * FROM movies WHERE year = 1999 AND title = "The Matrix"; --#get the movies from the year 199 and the title is the matrix | |
SELECT * FROM movies WHERE year = 1998 OR year = 2000; --#get the movies from year 1998 2000 only | |
SELECT * FROM movies WHERE year BETWEEN 1998 AND 2000; --#get the movies from year 1998 up to year 2000 | |
--#### if you want perform a basic search over a string by using part of a sting you can us the keyword LIKE with a combination of wildcards.#### | |
SELECT * FROM movies WHERE title LIKE "godfather"; --#get or look to all movies in the database that has the string of "godfather" in the title. | |
SELECT * FROM movies WHERE title LIKE "%godfather"; --#use a wild card in the string. a wild card is a special character that can be substitued by any number of characters. A wildcard can be placed anywere in the string. | |
SELECT * FROM movies WHERE title LIKE "%godfather%"; --#A wildcard can be placed anywere in the string. in SQL a wild card is the percent "%" symbol | |
--#Ordering the Result Set | |
SELECT * FROM movies ORDER BY year; | |
SELECT * FROM movies ORDER BY year DESC; | |
SELECT * FROM movies ORDER BY year ASC; | |
SELECT * FROM movies ORDER BY year ASC, title DESC; | |
--#Limiting the Result Set | |
SELECT * FROM movies LIMIT 10; --# show the 10 Result | |
SELECT * FROM movies LIMIT 10 OFFSET 1; --#showing 10 result but starting to the second row ofsetting ht first row | |
SELECT * FROM movies LIMIT 10 OFFSET 0; --#show the 10 | |
SELECT * FROM movies LIMIT 10 OFFSET 10; --#showing 10 result but start in row 11 | |
SELECT * FROM movies LIMIT 10 OFFSET 20; --#showing 10 result but stari t00 20th row | |
SELECT * FROM movies LIMIT 10 OFFSET 250; | |
SELECT * FROM movies LIMIT 20, 10; --#should start at the 21sr row. and bring back only 10 results sets. | |
SELECT * FROM movies LIMIT 0, 1000; | |
SELECT * FROM actors LIMIT 0, 1000; | |
--#Dealing with NULL | |
SELECT * FROM movies WHERE year IS NULL; | |
SELECT * FROM movies WHERE year IS NOT NULL ORDER BY year; | |
-- DATABASE relational | |
-- Join users.id and posts.author_id | |
SELECT * FROM posts INNER JOIN users ON users.id = posts.author_id; | |
SELECT * FROM title, body, first_name, last_name FROM posts INNER JOIN users ON users.id = posts.author_id; | |
select users.id, username, number as order_number from users inner join orders on orders.user_id = users.id; | |
-- Alter table | |
ALTER TABLE posts CHANGE title subject varchar(100); | |
ALTER TABLE posts CHANGE subject title varchar(150); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/** | |
* | |
* TODO: commandline from php | |
* * php -S localhost:8002 run to http://localhost:8002/ | |
* | |
*/ | |
// init.php set up | |
ob_start(); // output buffering is turned on | |
// File path navigation | |
// Assign file paths to PHP constants | |
// __FILE__ returns the current path to this file | |
// dirname() returns the path to the parent directory | |
define("PRIVATE_PATH", dirname(__FILE__)); | |
define("PROJECT_PATH", dirname(PRIVATE_PATH)); | |
define("PUBLIC_PATH", PROJECT_PATH . '/public'); | |
define("SHARED_PATH", PRIVATE_PATH . '/shared'); | |
// echo PRIVATE_PATH . '<br>'; | |
// echo PROJECT_PATH . '<br>'; | |
// echo PUBLIC_PATH . '<br>'; | |
// echo SHARED_PATH . '<br>'; | |
// Browser path navigation | |
// Assign the root URL to a PHP constant | |
// * Do not need to include the domain | |
// * Use same document root as webserver | |
// * Can set a hardcoded value: | |
// define("WWW_ROOT", '/~kevinskoglund/globe_bank/public'); | |
// define("WWW_ROOT", ''); | |
// * Can dynamically find everything in URL up to "/public" | |
$public_end = strpos($_SERVER['SCRIPT_NAME'], '/public') + 7; | |
$doc_root = substr($_SERVER['SCRIPT_NAME'], 0, $public_end); | |
define("WWW_ROOT", $doc_root); | |
// sFuntions to connect to the database | |
// Procedural way | |
mysqli_connect(); | |
mysqli_connect_errno(); | |
mysqli_connect_error(); | |
mysqli_real_escape_string(); | |
mysqli_query(); | |
mysqli_fetch_assoc(); //or// | |
mysqli_close(); | |
// Objested-oriented way | |
// $mysqli = new mysqli(); | |
// $mysqli->mysqli_connect(); | |
// $mysqli->mysqli_connect_errno(); | |
// $mysqli->mysqli_connect_error(); | |
// $mysqli->mysqli_real_escape_string(); | |
// $mysqli->mysqli_query(); | |
// $mysqli->mysqli_fetch_assoc(); | |
// $mysqli->mysqli_close(); | |
// Other way of connecting to a database | |
// PHP Database Interaction in Five Steps | |
// 1. Create a database connection -- only happens once per php script | |
$dbhost = 'localhost'; | |
$dbuser = 'widget_cms'; | |
$dbpass = 'secretpassword'; | |
$dbname = 'widget_corp'; | |
$connection = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname); | |
// other way of createing database connection | |
// Check DB connection | |
if(mysqli_connect_errno()) { // mysqli_connect_errno() its gonna return the error code from the last call that we made to connect to the database. | |
die("Data base connection failed:" . | |
mysqli_connect_error() . // mysqli_connect_error() is going to return a string description of last connect error. | |
"(" .mysqli_connect_errno(). ")"); | |
} | |
// Other way of doing DB connection check | |
if(mysqli_connect_errno()) { | |
$msg = "Database connection failed: "; | |
$msg .= mysqli_connect_error(); | |
$msg .= " (" . mysqli_connect_errno() . ")"; | |
exit($msg); | |
} | |
// 2. Perform database query | |
$query = "SELECT * "; | |
$query .= "FROM subjects "; | |
$query .= "WHERE visible = 1 "; | |
$query .= "ORDER BY position ASC"; | |
$query = "SELECT * FROM subjects"; | |
$result = mysqli_query($connection, $query); // it will return an special kind of object called resourse | |
// test if there was a query error | |
if(!$result) { | |
exit('Database query failed' . mysqli_error($connection)); | |
} | |
// function way | |
function confirm_result_set($result_set) { | |
if(!$result_set) { | |
exit("Database query failed." . mysqli_error($connection)); | |
} | |
} | |
// 3. Use returnd data if(any) (The best way to loop while loop for data!!!!! ) | |
while($row = mysqli_fetch_row($result)) { | |
echo "<pre>".var_dump($row)."</pre>"; | |
} | |
// other option for step number 3 of Use return data if (any) | |
$count = mysqli_num_rows($result); | |
for ($i=0; $i < $count ; $i++) { | |
$subject = mysqli_fetch_row($result); | |
echo $subject['menu_name']; | |
} | |
// 4. Release returned data | |
mysqli_free_result($result); | |
// 5.Close data base connection -- only happens once per php script | |
mysqli_close($connection); | |
// function way | |
function db_disconnect($connection) { | |
if(isset($connection)) { | |
mysqli_close($connection); | |
} | |
} | |
//################# INSERT | |
function insert_subject($subject) { | |
global $db; | |
$sql = "INSERT INTO subjects "; | |
$sql .= "( menu_name, position, visible) "; | |
$sql .= "VALUES ("; | |
$sql .= "'" . db_escape($db,$subject['menu_name']) . "',"; | |
$sql .= "'" . db_escape($db,$subject['position']) . "',"; | |
$sql .= "'" . db_escape($db,$subject['visible']) . "'"; | |
$sql .= ")"; | |
$result = mysqli_query($db, $sql); | |
// for INSERT statements $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// Create failed | |
echo mysqli_error($db); //display the error message | |
db_disconnect($db); //disconnecte everthying | |
exit(); // and quit everything | |
} | |
} | |
//################# INSERT | |
//################# UPDATE | |
if(!isset($_GET['id'])) { | |
redirect_to(url_for('/staff/subjects/index.php')); | |
} | |
$id = $_GET['id']; | |
if(is_post_request()) { | |
$subject = []; | |
$subject['menu_name'] = $_POST['menu_name'] ?? ''; | |
$subject['position'] = $_POST['position'] ?? ''; | |
$subject['visible'] = $_POST['visible'] ?? ''; | |
$sql = "UPDATE subjects SET "; | |
$sql .= "menu_name ='".$subject['menu_name']."', "; | |
$sql .= "position ='".$subject['position']."', "; | |
$sql .= "visible ='".$subject['visible']."' "; | |
$sql .= "WHERE id = '".$id."' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For UPDATE staments, $result is true/false | |
if($result) { | |
redirect_to(url_for('/staff/subjects/show.php?id=' . $id)); | |
} else { | |
// UPDATE failed | |
echo mysqli_error($db); //display the error message | |
db_disconnect($db); //disconnecte everthying | |
exit(); // and quit everythings | |
} | |
} else { | |
$subject = find_subject_by_id($id); | |
} | |
//################# UPDATE | |
//################# DELETE | |
$id = $_GET['id']; | |
$page_title = 'Delete Subject'; | |
include( SHARED_PATH . '/staff_header.php'); | |
if(is_post_request()) { | |
$query = "DELETE FROM subjects "; | |
$query .= "WHERE id = '" . $id . "' "; | |
$query .= "LIMIT 1"; | |
$result = mysqli_query($db, $query); | |
if($result) { | |
redirect_to(url_for('/staff/subjects/index.php')); | |
} else { | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} else { | |
$subject = find_subject_by_id($id); | |
} | |
//################# DELETE | |
// working with retrieved data | |
/** | |
* ! First option | |
* ! mysqli_fetch_row(); | |
* Results are in standard attay | |
* key are integers | |
*/ | |
/** | |
* ! Second option | |
* ! mysqli_fetch_assoc(); | |
* Results are in a associative array | |
* keys are columns names | |
*/ | |
/** | |
* ! Third option | |
* ! mysqli_fetch_array(); | |
* Results in either or both types of arrays | |
* MYSQL_NUM, MYSQL_ASSOC, MYSQL_BOTH | |
*/ | |
// NOTE Encode URL parameters urlencode vs. rawurlencode | |
// urlencode, is going yo use more often than rawurlencode | |
/** | |
* ! rawurlencode | |
* * rawurlencode($string) | |
* todo: Path is the part before the "?" | |
* todo: Spaces must be encoded as "%20" | |
* * TRUTH: in truth, your rarely use rawurlencode. | |
* * because most of the time the path is not something that is being dynamically generated by php | |
* | |
* ! urlencode | |
* * urlencode($string) | |
* todo: Query string is the part after the "?" | |
* todo: Spaces are better encode as "+" | |
* * the query string is ver dynamic. It can contain values that come from the database, | |
* * it can be values that you've assembled together in PHP, | |
* * or it can be values that user has given you. | |
* * May be they type a query into a form. | |
*/ | |
// Encode for HTML/ Watched out for reserved haracters in the HTML | |
/** | |
* ! the character that we gonna watch out for that may harm the website | |
* todo: cover this characters < > & " to reserve character in HTML | |
* * htmlspecialchars($string) | |
* todo: evertime we output dynamic data from $_GET request we make sure that we use htmlspecialchars() | |
* ? sample: $id = $_GET['id'] ?? null; //PHP > 7.0 | |
* todo: echo htmlspecialchars($id) | |
* todo: everthing that is dynamic data you have to escape it first to make sure that it doesn't have any poweful HTML characters in it. | |
*/ | |
// header() | |
/** | |
* Todo: to check the result og header in the command line | |
* ? curl --head http://localhost/phpcodepractice/globe_bank/public/staff/pages/show.php?id=4 | |
* ? **** OUTPUT **** | |
* HTTP/1.1 200 OK | |
* Date: Sat, 05 Oct 2019 04:51:48 GMT | |
* Server: Apache/2.4.41 (Win64) OpenSSL/1.1.1c PHP/7.3.9 | |
* X-Powered-By: PHP/7.3.9 | |
* Content-Type: text/html; charset=UTF-8 | |
*/ | |
function url_for($script_path) { | |
// add the leading '/' if not present | |
if($script_path[0] != '/') { | |
$script_path = "/" . $script_path; | |
} | |
return WWW_ROOT . $script_path; | |
} | |
function u($string="") { | |
return urlencode($string); | |
} | |
function raw_u($string="") { | |
return rawurlencode($string); | |
} | |
function h($string="") { | |
return htmlspecialchars($string); | |
} | |
function error_404() { | |
header($_SERVER["SERVER_PROTOCOL"] . " 404 Not Found"); | |
exit(); | |
} | |
function error_500() { | |
header($_SERVER["SERVER_PROTOCOL"] . " 500 Internal Server Error"); | |
exit(); | |
} | |
function redirect_to($location) { | |
header("Location: " . $location); | |
exit; | |
} | |
function is_post_request() { | |
return $_SERVER['REQUEST_METHOD'] == 'POST'; | |
} | |
function is_get_request() { | |
return $_SERVER['REQUEST_METHOD'] == 'GET'; | |
} | |
function format_date($date) { | |
return date('F j, Y, g:i a', strtotime($date)); | |
} | |
// timestamp is the number of seconds that has elapsed since the Unix Epoch (January 1, 1970); | |
// %b - abbreviated mong name | |
// %d - day of the month (01 to 31) | |
// %Y - year includging the century | |
function form_date2($date) { | |
return strftime("%b %d, %Y", strtotime($date)) | |
} | |
function display_errors($errors=array()) { | |
$output = ''; | |
if(!empty($errors)) { | |
$output .= "<div class=\"errors\">"; | |
$output .= "Please fix the following errors:"; | |
$output .= "<ul>"; | |
foreach($errors as $error) { | |
$output .= "<li>" . h($error) . "</li>"; | |
} | |
$output .= "</ul>"; | |
$output .= "</div>"; | |
} | |
return $output; | |
} | |
function get_and_clear_session_message() { | |
if(isset($_SESSION['message']) && $_SESSION['message'] != '') { | |
$msg = $_SESSION['message']; | |
unset($_SESSION['message']); | |
return $msg; | |
} | |
} | |
function diplay_session_message() { | |
$msg = get_and_clear_session_message(); | |
if(!is_blank($msg)) { | |
return '<div id="message">' . h($msg) . '</div>'; | |
} | |
} | |
/** | |
* * DELETING DATA | |
* * 1. Use DELETE query | |
* * 2. Deleting a single record requires subjects.id | |
* * 3. Form is optional; POST is best practice | |
With a delete, having a form for it is optional. You could simply have a | |
link and you click the link and the record gets deleted. Some people like to put | |
JavaScript on that link that pops up a confirm that says, 'Are you sure you want to delete?' | |
and then it proceeds to delete and then that keeps it nice and simple. | |
But I like actually having another page that has a form on it, for two reasons. | |
One, having this page gives us a way to double-check before we're deleting. | |
It gives us a chance to have a page that says, 'Hey user, are you sure you really want to delete?' | |
and then if they are, they'll submit it and it's a form that submits. The reason why that's important | |
is because a Post request is desirable. You want to put your deletes behind Post requests, not Get requests. | |
Why is that? Imagine for a moment that a search engine visits your site. Search engine spiders | |
will click on all links, which are Get requests. That's how they move around the site. | |
But they will not submit any forms, which are Post requests. Imagine if a link simply deleted | |
a record in the database. A search engine spider could delete everything in your database, | |
just by following all those different links. Now of course, you probably | |
would have these pages password protected to keep search engines out, but it still illustrates the principle. | |
It's a good idea to have Deleting records only work when you have Post requests. Of course, once we have our Delete query, | |
it's only going to return True or False. We're not going to get back a record set to work with, | |
just going to tell us whether it's succeeded. | |
*/ | |
// Detect form submission | |
/** | |
* !There are three main techniques that developers use to know whether a form has been submitted. | |
* * 1. Test if a key parameter has a value | |
* ! sample: if(isset($_POST['username'])) {echo "something"} | |
* * 2. Test if submit parameter was sent | |
* ! sample: if(isset($_POST['submit'])) {echo "something"} <input type="submit" value="Edit Subject" /> | |
* * 3. Test if request method is POST | |
* * But I think an even better way to do it, | |
* todo: if($_SERVER['REQUEST_METHOD] === "POST") {// process form} | |
*/ | |
/** | |
* ! Remember: Editing page, menu or anthing that going to edit requires a page ID or an ID. | |
*/ | |
/** | |
* ! functions to count the table rows | |
* * mysqli_num_rows($varibale) -> it will return a integer | |
*/ | |
/** | |
* * Prevent SQL Injection | |
* ? addslashes($string) | |
* Todo: addslashes() available all the time | |
* Todo: it takes a string as an argument, and then its returns a string with backslaches before characters that need to be escaped. that is single qoute double qoute, backslash and null | |
* Todo: Sample code addslashes($subject['menu_name']) = "David's Story"; return "David\'s Story" | |
* | |
* | |
* * Designed specific for MySQL | |
* ? mysqli_real_escape_string($db_name. $string); | |
* Todo: it escape single qoute, double qoute, backslash and null, but it also escapes line return and other odd control characters that you might not think about. | |
* * first argument is the database connection handle, then the string comes next. | |
* ! remember we have to provide a database connection first and it means that we can only use this function mysqli_real_escape_string() when we have a connection to the database. | |
*/ | |
/** | |
* * Surprisingly true | |
* ? 0 == false // true | |
* ? 4 == true // true | |
* ? 0 == null // true | |
* ? 0 == 0 // true | |
* ? 0 == '' // true | |
* ? 0 == a // true | |
* ? '' == null // true | |
* ? 'abc' == true // true | |
* ? 100 == 100.00 // true | |
* ? 3 == '3 dogs' // true | |
* ? '1' == '01' // true | |
* ? '123' == ' 123' // true | |
* ? '123' == '+0123' // true | |
* ? 100 == '1e2' // true | |
* | |
* * Surprisingly Empty empty() | |
* ? '' // true | |
* ? 0 // true | |
* ? '0' // true | |
* ? null // true | |
* ? false // true | |
* ? array() // true | |
* ! all considered empty in php | |
*/ | |
// DATABASE CONNECTION | |
function db_connect() { | |
$connection = mysqli_connect(DB_SERVER, DB_USER, DB_PASS, DB_NAME); | |
confirm_db_connect(); | |
return $connection; | |
} | |
function db_disconnect($connection) { | |
if(isset($connection)) { | |
mysqli_close($connection); | |
} | |
} | |
function db_escape($connection, $string) { | |
return mysqli_real_escape_string($connection, $string); | |
} | |
function confirm_db_connect() { | |
if(mysqli_connect_errno()) { | |
$msg = "Database connection failed: "; | |
$msg .= mysqli_connect_error(); | |
$msg .= " (" . mysqli_connect_errno() . ")"; | |
exit($msg); | |
} | |
} | |
function confirm_result_set($result_set) { | |
if (!$result_set) { | |
exit("Database query failed."); | |
} | |
} | |
// Set and read cookie values | |
// It takes a number of different arguments, but there are three that are the most important. | |
setcookie($name, $value, $expire); | |
// all parameters in setcookie() function | |
setcookie( | |
$name, //the name of the cookie | |
$value, // the value we want to assign to that name, | |
$expire, // expiration, that is we can control how long these cookies stick around for | |
$path, // The path is the path on hte server to which the cookies available. s ofor example, we could have a cookie thats only vailable if our path is in the staff area. | |
$domain, //typicaly the entire domain or the entire website | |
$secure, // coookie security. If secure is set to true, then a cookie will only be transmitted when theres a secure https connection. Otherwise, the cookie will not be transmitted. | |
$httponly // coookie security. This cookie should only be sent along with the header. in other words, it shoud not be available to Javascript. keeping hackers from being able to use Javascript to steal cookie data from us. | |
); | |
/** | |
* The cookie expiration is always going to be a unix timestamp. | |
* That is, the number of seconds since 1970. | |
* TODO $expire = time() + 60*60*24*14; it will expire in 20 days | |
* ? The time() function returns the current time, right now, expressed as a unix timestamp. Or the number of seconds since 1970. | |
*/ | |
// sample actual code | |
$expire = time() + 60*60*24*14; | |
setcookie($name, $value, $expire); | |
// option 2 with the other parameters | |
setcookie( | |
'lang', | |
'English', | |
$expires, | |
'/', // $path | |
'mysite.com', //$domain | |
false, | |
true | |
); | |
$lang = $_COOKIE['lang']; //read value back from $_COOKIE store it the varialbe $lang | |
// always the $_COOKIE value | |
$lang = isset($_COOKIE['lang']) ? $_COOKIE['lang'] : ''; | |
// checking _COOKIE in PHP 7 > | |
$lang = $_COOKIE['lang'] ?? ''; | |
// UNSET COOKIE | |
// the wrong way | |
unset($_COOKIE['admin_id']); | |
// two ways to unset the cookie the rightt way | |
// 1. if you dont provide the third parameter which is the expiration date it will set to expire in 1 year | |
setcookie($name, false); | |
// 2. we can set the expiration time | |
setcookie($name, $value, (time() - 3600)); | |
// Work with sessions | |
$_SESSION['lang'] = 'English'; // set value in $_SESSION | |
$lang = $_SESSION['lang']; //read value back from session store "English" to $lang | |
// checking session if it has a value | |
$lang = isset($_SESSION['lang']) ? $_SESSION['lang'] : ''; | |
// checking session in PHP 7 | |
$lang = $_SESSION['lang'] ?? ''; | |
// unset $_SESSION | |
unset($_SESSION['lang']); | |
// Password enctypting | |
/** | |
* * password_hash($password,PASSWORD_DEFAULT); | |
* * password_hash($password,PASSWORD_BCRYPT, ['cost' => 10]); | |
* * password_verify($password,$hashpassword); | |
*/ | |
$hash_password = password_hash($admin['password'], PASSWORD_DEFAULT); | |
// or | |
$hash_password = password_hash($admin['password'], PASSWORD_BCRYPT); | |
pre { | |
display: block; | |
padding: 9.5px; | |
margin: 0 0 10px; | |
font-size: 13px; | |
line-height: 1.42857143; | |
color: #333; | |
word-break: break-all; | |
word-wrap: break-word; | |
background-color: #f5f5f5; | |
border: 1px solid #ccc; | |
border-radius: 4px; | |
} | |
// ######################################## Query Functions | |
// Subjects | |
function find_all_subjects() { | |
global $db; | |
$sql = "SELECT * FROM subjects "; | |
$sql .= "ORDER BY position ASC"; | |
//echo $sql; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
return $result; | |
} | |
function find_subject_by_id($id) { | |
global $db; | |
$sql = "SELECT * FROM subjects "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "'"; | |
// echo $sql; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
$subject = mysqli_fetch_assoc($result); | |
mysqli_free_result($result); | |
return $subject; // returns an assoc. array | |
} | |
function validate_subject($subject) { | |
$errors = []; | |
// menu_name | |
if(is_blank($subject['menu_name'])) { | |
$errors[] = "Name cannot be blank."; | |
} elseif(!has_length($subject['menu_name'], ['min' => 2, 'max' => 255])) { | |
$errors[] = "Name must be between 2 and 255 characters."; | |
} | |
// position | |
// Make sure we are working with an integer | |
$postion_int = (int) $subject['position']; | |
if($postion_int <= 0) { | |
$errors[] = "Position must be greater than zero."; | |
} | |
if($postion_int > 999) { | |
$errors[] = "Position must be less than 999."; | |
} | |
// visible | |
// Make sure we are working with a string | |
$visible_str = (string) $subject['visible']; | |
if(!has_inclusion_of($visible_str, ["0","1"])) { | |
$errors[] = "Visible must be true or false."; | |
} | |
return $errors; | |
} | |
function insert_subject($subject) { | |
global $db; | |
$errors = validate_subject($subject); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
$sql = "INSERT INTO subjects "; | |
$sql .= "(menu_name, position, visible) "; | |
$sql .= "VALUES ("; | |
$sql .= "'" . db_escape($db, $subject['menu_name']) . "',"; | |
$sql .= "'" . db_escape($db, $subject['position']) . "',"; | |
$sql .= "'" . db_escape($db, $subject['visible']) . "'"; | |
$sql .= ")"; | |
$result = mysqli_query($db, $sql); | |
// For INSERT statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// INSERT failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function update_subject($subject) { | |
global $db; | |
$errors = validate_subject($subject); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
$sql = "UPDATE subjects SET "; | |
$sql .= "menu_name='" . db_escape($db, $subject['menu_name']) . "', "; | |
$sql .= "position='" . db_escape($db, $subject['position']) . "', "; | |
$sql .= "visible='" . db_escape($db, $subject['visible']) . "' "; | |
$sql .= "WHERE id='" . db_escape($db, $subject['id']) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For UPDATE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// UPDATE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function delete_subject($id) { | |
global $db; | |
$sql = "DELETE FROM subjects "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For DELETE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// DELETE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
// Pages | |
function find_all_pages() { | |
global $db; | |
$sql = "SELECT * FROM pages "; | |
$sql .= "ORDER BY subject_id ASC, position ASC"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
return $result; | |
} | |
function find_page_by_id($id) { | |
global $db; | |
$sql = "SELECT * FROM pages "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "'"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
$page = mysqli_fetch_assoc($result); | |
mysqli_free_result($result); | |
return $page; // returns an assoc. array | |
} | |
function validate_page($page) { | |
$errors = []; | |
// subject_id | |
if(is_blank($page['subject_id'])) { | |
$errors[] = "Subject cannot be blank."; | |
} | |
// menu_name | |
if(is_blank($page['menu_name'])) { | |
$errors[] = "Name cannot be blank."; | |
} elseif(!has_length($page['menu_name'], ['min' => 2, 'max' => 255])) { | |
$errors[] = "Name must be between 2 and 255 characters."; | |
} | |
$current_id = $page['id'] ?? '0'; | |
if(!has_unique_page_menu_name($page['menu_name'], $current_id)) { | |
$errors[] = "Menu name must be unique."; | |
} | |
// position | |
// Make sure we are working with an integer | |
$postion_int = (int) $page['position']; | |
if($postion_int <= 0) { | |
$errors[] = "Position must be greater than zero."; | |
} | |
if($postion_int > 999) { | |
$errors[] = "Position must be less than 999."; | |
} | |
// visible | |
// Make sure we are working with a string | |
$visible_str = (string) $page['visible']; | |
if(!has_inclusion_of($visible_str, ["0","1"])) { | |
$errors[] = "Visible must be true or false."; | |
} | |
// content | |
if(is_blank($page['content'])) { | |
$errors[] = "Content cannot be blank."; | |
} | |
return $errors; | |
} | |
function insert_page($page) { | |
global $db; | |
$errors = validate_page($page); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
$sql = "INSERT INTO pages "; | |
$sql .= "(subject_id, menu_name, position, visible, content) "; | |
$sql .= "VALUES ("; | |
$sql .= "'" . db_escape($db, $page['subject_id']) . "',"; | |
$sql .= "'" . db_escape($db, $page['menu_name']) . "',"; | |
$sql .= "'" . db_escape($db, $page['position']) . "',"; | |
$sql .= "'" . db_escape($db, $page['visible']) . "',"; | |
$sql .= "'" . db_escape($db, $page['content']) . "'"; | |
$sql .= ")"; | |
$result = mysqli_query($db, $sql); | |
// For INSERT statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// INSERT failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function update_page($page) { | |
global $db; | |
$errors = validate_page($page); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
$sql = "UPDATE pages SET "; | |
$sql .= "subject_id='" . db_escape($db, $page['subject_id']) . "', "; | |
$sql .= "menu_name='" . db_escape($db, $page['menu_name']) . "', "; | |
$sql .= "position='" . db_escape($db, $page['position']) . "', "; | |
$sql .= "visible='" . db_escape($db, $page['visible']) . "', "; | |
$sql .= "content='" . db_escape($db, $page['content']) . "' "; | |
$sql .= "WHERE id='" . db_escape($db, $page['id']) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For UPDATE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// UPDATE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function delete_page($id) { | |
global $db; | |
$sql = "DELETE FROM pages "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For DELETE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// DELETE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function find_page_by_subject_id($subject_id) { | |
global $db; | |
$sql = "SELECT * FROM pages "; | |
$sql .= "WHERE subject_id='" . db_escape($db, $subject_id) . "' "; | |
$sql .= "ORDER BY position ASC"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
return $result; | |
} | |
// Admins | |
function find_all_admins() { | |
global $db; | |
$sql = "SELECT * FROM admins "; | |
$sql .= "ORDER BY last_name ASC, first_name ASC"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
return $result; | |
} | |
function find_admin_by_id($id) { | |
global $db; | |
$sql = "SELECT * FROM admins "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "'"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
$page = mysqli_fetch_assoc($result); | |
mysqli_free_result($result); | |
return $page; // returns an assoc. array | |
} | |
function find_admin_by_username($username) { | |
global $db; | |
$sql = "SELECT * FROM admins "; | |
$sql .= "WHERE username='" . db_escape($db, $username) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
$admin = mysqli_fetch_assoc($result); | |
mysqli_free_result($result); | |
return $admin; // returns an assoc. array | |
} | |
function validate_admin($admin) { | |
$errors = []; | |
// menu_name | |
if(is_blank($admin['first_name'])) { | |
$errors[] = "First name cannot be blank."; | |
} elseif(!has_length($admin['first_name'], array('min' => 2, 'max' => 255))) { | |
$errors[] = "First name must be between 2 and 255 characters."; | |
} | |
if(is_blank($admin['last_name'])) { | |
$errors[] = "Last name cannot be blank."; | |
} elseif(!has_length($admin['first_name'], array('min' => 2, 'max' => 255))) { | |
$errors[] = "Last name must be between 2 and 255 characters."; | |
} | |
if(is_blank($admin['email'])) { | |
$errors[] = "Email cannot be blank."; | |
} elseif(!has_length($admin['email'], array('max' => 255))) { | |
$errors[] = "Last name must be between 2 and 255 characters."; | |
} elseif(!has_valid_email_format($admin['email'])) { | |
$errors[] = "Email must be a valid format."; | |
} | |
if(is_blank($admin['username'])) { | |
$errors[] = "Username cannot be blank."; | |
} elseif(!has_length($admin['username'], array('min' => 8, 'max' => 255))) { | |
$errors[] = "Username name must be between 8 and 255 characters."; | |
} elseif(!has_unique_username($admin['username'], $admin['id'] ?? 0)) { | |
$errors[] = "Username not allowed. Try another."; | |
} | |
if(is_blank($admin['password'])) { | |
$errors[] = "Password cannot be blank."; | |
} elseif (!has_length($admin['password'], array('min' => 12))) { | |
$errors[] = "Password must contain 12 or more characters"; | |
} elseif (!preg_match('/[A-Z]/', $admin['password'])) { | |
$errors[] = "Password must contain at least 1 uppercase letter"; | |
} elseif (!preg_match('/[a-z]/', $admin['password'])) { | |
$errors[] = "Password must contain at least 1 lowercase letter"; | |
} elseif (!preg_match('/[0-9]/', $admin['password'])) { | |
$errors[] = "Password must contain at least 1 number"; | |
} elseif (!preg_match('/[^A-Za-z0-9\s]/', $admin['password'])) { | |
$errors[] = "Password must contain at least 1 symbol"; | |
} | |
if(is_blank($admin['confirm_password'])) { | |
$errors[] = "Confirm password cannot be blank."; | |
} elseif ($admin['password'] !== $admin['confirm_password']) { | |
$errors[] = "Password and confirm password must match."; | |
} | |
return $errors; | |
} | |
function insert_admin($admin) { | |
global $db; | |
$errors = validate_admin($admin); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
$hash_password = password_hash($admin['password'], PASSWORD_BCRYPT); | |
$sql = "INSERT INTO admins "; | |
$sql .= "(first_name, last_name, email, username, hashed_password) "; | |
$sql .= "VALUES ("; | |
$sql .= "'" . db_escape($db, $admin['first_name']) . "',"; | |
$sql .= "'" . db_escape($db, $admin['last_name']) . "',"; | |
$sql .= "'" . db_escape($db, $admin['email']) . "',"; | |
$sql .= "'" . db_escape($db, $admin['username']) . "',"; | |
$sql .= "'" . db_escape($db, $hash_password) . "'"; | |
$sql .= ")"; | |
$result = mysqli_query($db, $sql); | |
// For INSERT statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// INSERT failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function update_admin($admin) { | |
global $db; | |
$password_sent = !is_blank($admin['password']); | |
$errors = validate_admin($admin); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
$hash_password = password_hash($admin['password'], PASSWORD_BCRYPT); | |
$sql = "UPDATE admins SET "; | |
$sql .= "first_name='" . db_escape($db, $admin['first_name']) . "', "; | |
$sql .= "last_name='" . db_escape($db, $admin['last_name']) . "', "; | |
$sql .= "email='" . db_escape($db, $admin['email']) . "', "; | |
if($password_sent) { | |
$sql .= "hashed_password='" . db_escape($db, $hash_password) . "', "; | |
} | |
$sql .= "username='" . db_escape($db, $admin['username']) . "' "; | |
$sql .= "WHERE id='" . db_escape($db, $admin['id']) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For UPDATE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// UPDATE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function delete_admin($admin) { | |
global $db; | |
$sql = "DELETE FROM admins "; | |
$sql .= "WHERE id='" . db_escape($db, $admin) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For DELETE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// DELETE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
// Magic Constant | |
/** | |
* Todo __FILE__ | |
* Todo __LINE__ | |
* Todo __DIR__ | |
* | |
* * Method to use with magic contstant | |
* ? file_exists() | |
* ? is_file() check to see if its file | |
* ? is_dir() check to see if its a directory | |
*/ | |
echo __FILE__ . "<br>"; | |
echo __LINE__ . "<br>"; | |
echo __DIR__ . "<br>"; | |
if(file_exists(__DIR__ )) { | |
echo "Yes"; | |
} | |
if(file_exists(__FILE__ )) { | |
echo "Yes"; | |
} | |
if(is_file(__DIR__)) { // it will echo no because __DIR__ is not a file | |
echo "Yes"; | |
} else { | |
echo "No"; | |
} | |
if(is_dir(__FILE__)) { // it will echo yes | |
echo "Yes"; | |
} else { | |
echo "No"; | |
} | |
//Ternary operator | |
echo file_exists(__FILE__) ? 'yes' : 'no'; | |
/** | |
* * Uploading files | |
* Todo php.ini default configuration | |
* ? file_uploads=On | |
* ? post_max_size=8M | |
* ? upload_max_filesize=2M | |
* ? memory_limit=128M | |
* ? max_input_time=60 | |
* ? upload_tmp_dir="C:\xampp\tmp" | |
* ? max_execution_time=30 | |
*/ | |
/** | |
* * Uploaded File Structure | |
* * Super Global - $_FILES['example_file'] | |
* * Keys in the associative array | |
* ? name = file name | |
* ? type = jpg, png, gif, doc, txt | |
* ? size = in bytes | |
* ? tmp_name = temporary name | |
* ? error = the error code | |
* | |
*/ | |
// Sample associative array from super global $_FILES | |
// Array | |
// ( | |
// [name] => Todo MongoDB CRUD Application.JPG | |
// [type] => image/jpeg | |
// [tmp_name] => C:\xampp\tmp\phpD423.tmp | |
// [error] => 0 | |
// [size] => 72443 | |
// ) | |
/** | |
* File Upload Erros Lists | |
* ! Constant and value are the same thing the value represent the constant | |
* Todo Constant Value Description | |
* ? UPLOAD_ERR_OK 0 There is no error | |
* ? UPLOAD_ERR_INI_SIZE 1 Bigger than the upload_max_filesize directive | |
* ? UPLOAD_ERR_FORM_SIZE 2 The uploaded file exceeds the MAX_FILE_SIZE | |
* ? UPLOAD_ERR_PARTIAL 3 The uploaded file was only partially uploaded. | |
* ? UPLOAD_ERR_NO_FILE 4 No file was uploaded. | |
* ? UPLOAD_ERR_NO_TMP_DIR 6 Missing a temporary folder. Introduce in PHP 5.0.3. | |
* ? UPLOAD_ERR_CANT_WRITE 7 Failed to write file to disk. Introduce in PHP 5.1.0. | |
* ? UPLOAD_ERR_EXTENSION 8 A PHP extention stopped the file upload. | |
*/ | |
/** | |
* ! Sample code error handling | |
*/ | |
if(isset($_POST['submit'])) { | |
echo "<pre>"; | |
print_r($_FILES['file_upload']); | |
echo "</pre>"; | |
// | |
$upload_errors = array( | |
UPLOAD_ERR_OK => "There is no error", | |
UPLOAD_ERR_INI_SIZE => "The uploaded file exceeds the upload_max_filesize directive in php.ini", | |
UPLOAD_ERR_FORM_SIZE => "The uploaded file exceeds the MAX_FILE_SIZE directive in php.ini", | |
UPLOAD_ERR_PARTIAL => "The uploaded file was only partially uploaded.", | |
UPLOAD_ERR_NO_FILE => "No file was uploaded.", | |
UPLOAD_ERR_NO_TMP_DIR => "Missing a temporary folder", | |
UPLOAD_ERR_CANT_WRITE => "Failed to write file to disk", | |
UPLOAD_ERR_EXTENSION => "A PHP extention stopped the file upload" | |
); | |
$the_error = $_FILES['file_upload']['error']; | |
$the_message = $upload_errors[$the_error]; | |
// Upload file on the server | |
$the_file = $_FILES['file_upload']['name']; // name of the file to be uploaded | |
$temp_name = $_FILES['file_upload']['tmp_name']; // temporary location | |
$directory = "uploads"; | |
// more uploaded file return true or false | |
move_uploaded_file(filename, destination); | |
move_uploaded_file( $temp_name, "$directory" ); | |
} | |
// $upload_errors = []; | |
// $the_message = null; | |
?> | |
<!DOCTYPE html> | |
<html lang="en"> | |
<head> | |
<meta charset="UTF-8"> | |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> | |
<meta http-equiv="X-UA-Compatible" content="ie=edge"> | |
<title>Document</title> | |
<style> | |
pre { | |
display: block; | |
padding: 9.5px; | |
margin: 0 0 10px; | |
font-size: 13px; | |
line-height: 1.42857143; | |
color: #333; | |
word-break: break-all; | |
word-wrap: break-word; | |
background-color: #f5f5f5; | |
border: 1px solid #ccc; | |
border-radius: 4px; | |
} | |
</style> | |
</head> | |
<body> | |
<form action="" method="post" enctype="multipart/form-data"> | |
<h2> | |
<?php | |
if(!empty($upload_errors)) { | |
echo $the_message; | |
} | |
?> | |
</h2> | |
<input type="file" name="file_upload"><br> | |
<input type="submit" value="Submit" name="submit"> | |
</form> | |
</body> | |
</html> | |
/** | |
* Prepared Statement | |
*/ | |
<?php | |
$sql = "SELECT id, first_name, last_name "; | |
$sql .= "FROM users "; | |
$sql .= "WHERE username = ? AND PASSWORD = ?"; | |
$stmt = mysqli_prepare($database, $sql); | |
mysqli_stmt_bind_param($stmt, 'ss', $username, $password); | |
mysqli_stmt_execute($stmt); | |
mysqli_stmt_result($stmt, $id, $first_name, $last_name); | |
mysqli_stmt_fetch($stmt); | |
mysqli_stmt_close($stmt); | |
/** | |
* ? code for showing different poges depending on the condition * | |
*/ | |
if(isset($_GET['source'])) { | |
$source = $_GET['source']; | |
} else { | |
$source = null; | |
} | |
switch($source) { | |
case 'add_post'; | |
include "inc/add_post.php"; | |
break; | |
case 'edit_post'; | |
include "inc/edit_post.php"; | |
break; | |
case '34'; | |
echo "Nice 34"; | |
break; | |
default: | |
include "inc/view_all_posts.php"; | |
break; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/** | |
* | |
* TODO: commandline from php | |
* * php -S localhost:8002 run to http://localhost:8002/ | |
* | |
*/ | |
define('DB_HOST', 'localhost'); | |
define('DB_USER', 'webuser_gallery'); | |
define('DB_PASS', 'A39Iz8ejyZzI7CTl'); | |
define('DB_NAME', 'gallery_db'); | |
// File path navigation | |
// Assign file paths to PHP constants | |
// __FILE__ returns the current path to this file | |
// dirname() returns the path to the parent directory | |
define("PRIVATE_PATH", dirname(__FILE__)); | |
define("PROJECT_PATH", dirname(PRIVATE_PATH)); | |
define("PUBLIC_PATH", PROJECT_PATH . '/public'); | |
define("SHARED_PATH", PRIVATE_PATH . '/shared'); | |
// echo PRIVATE_PATH . '<br>'; | |
// echo PROJECT_PATH . '<br>'; | |
// echo PUBLIC_PATH . '<br>'; | |
// echo SHARED_PATH . '<br>'; | |
// Browser path navigation | |
// Assign the root URL to a PHP constant | |
// * Do not need to include the domain | |
// * Use same document root as webserver | |
// * Can set a hardcoded value: | |
// define("WWW_ROOT", '/~kevinskoglund/globe_bank/public'); | |
// define("WWW_ROOT", ''); | |
// * Can dynamically find everything in URL up to "/public" | |
$public_end = strpos($_SERVER['SCRIPT_NAME'], '/public') + 7; | |
$doc_root = substr($_SERVER['SCRIPT_NAME'], 0, $public_end); | |
define("WWW_ROOT", $doc_root); | |
// Load class definitions manually | |
// -> Individually | |
// require_once('classes/bicycle.class.php'); | |
// -> All classes in directory | |
// grab all of the files that are in classes directory that have dot class dot php at the end of them. and foreach of them loop through and require it | |
foreach(glob('classes/*.class.php') as $file) { | |
require_once($file); | |
} | |
// Autoload class definitions | |
function my_autoload($class) { | |
if(preg_match('/\A\w+\Z/', $class)) { | |
include('classes/' . $class . '.class.php'); | |
} | |
} | |
spl_autoload_register('my_autoload'); | |
function check_class_definitions($class) { | |
echo "Difinition for ${class} is missing"; | |
} | |
spl_autoload_register('check_class_definitions'); | |
$db = new Database; | |
// ################################## Helper functions | |
function url_for($script_path) { | |
// add the leading '/' if not present | |
if($script_path[0] != '/') { | |
$script_path = "/" . $script_path; | |
} | |
return WWW_ROOT . $script_path; | |
} | |
function u($string="") { | |
return urlencode($string); | |
} | |
function raw_u($string="") { | |
return rawurlencode($string); | |
} | |
function h($string="") { | |
return htmlspecialchars($string); | |
} | |
function error_404() { | |
header($_SERVER["SERVER_PROTOCOL"] . " 404 Not Found"); | |
exit(); | |
} | |
function error_500() { | |
header($_SERVER["SERVER_PROTOCOL"] . " 500 Internal Server Error"); | |
exit(); | |
} | |
function redirect_to($location) { | |
header("Location: " . $location); | |
exit; | |
} | |
function is_post_request() { | |
return $_SERVER['REQUEST_METHOD'] == 'POST'; | |
} | |
function is_get_request() { | |
return $_SERVER['REQUEST_METHOD'] == 'GET'; | |
} | |
function format_date($date) { | |
return date('F j, Y, g:i a', strtotime($date)); | |
} | |
// ################################## Helper functions End | |
// Object-oriented way | |
$mysqli = new mysqli(); | |
$mysqli->mysqli_connect(); | |
$mysqli->connect_errno; | |
$mysqli->connect_error; | |
$mysqli->real_escape_string(); | |
$mysqli->query(); | |
$mysqli->fetch_assoc(); | |
$mysqli->close(); | |
// Magic Constant | |
/** | |
* Todo __FILE__ | |
* Todo __LINE__ | |
* Todo __DIR__ | |
* | |
* * Method to use with magic contstant | |
* ? file_exists() | |
* ? is_file() check to see if its file | |
* ? is_dir() check to see if its a directory | |
*/ | |
echo __FILE__ . "<br>"; | |
echo __LINE__ . "<br>"; | |
echo __DIR__ . "<br>"; | |
if(file_exists(__DIR__ )) { | |
echo "Yes"; | |
} | |
if(file_exists(__FILE__ )) { | |
echo "Yes"; | |
} | |
if(is_file(__DIR__)) { // it will echo no because __DIR__ is not a file | |
echo "Yes"; | |
} else { | |
echo "No"; | |
} | |
if(is_dir(__FILE__)) { // it will echo yes | |
echo "Yes"; | |
} else { | |
echo "No"; | |
} | |
//Ternary operator | |
echo file_exists(__FILE__) ? 'yes' : 'no'; | |
/** | |
* * Uploading files | |
* Todo php.ini default configuration | |
* ? file_uploads=On | |
* ? post_max_size=8M | |
* ? upload_max_filesize=2M | |
* ? memory_limit=128M | |
* ? max_input_time=60 | |
* ? upload_tmp_dir="C:\xampp\tmp" | |
* ? max_execution_time=30 | |
*/ | |
/** | |
* * Uploaded File Structure | |
* * Super Global - $_FILES['example_file'] | |
* * Keys in the associative array | |
* ? name = file name | |
* ? type = jpg, png, gif, doc, txt | |
* ? size = in bytes | |
* ? tmp_name = temporary name | |
* ? error = the error code | |
* | |
*/ | |
/** | |
* File Upload Erros Lists | |
* ! Constant and value are the same thing the value represent the constant | |
* Todo Constant Value Description | |
* ? UPLOAD_ERR_OK 0 There is no error | |
* ? UPLOAD_ERR_INI_SIZE 1 Bigger than the upload_max_filesize directive | |
* ? UPLOAD_ERR_FORM_SIZE 2 The uploaded file exceeds the MAX_FILE_SIZE | |
* ? UPLOAD_ERR_PARTIAL 3 The uploaded file was only partially uploaded. | |
* ? UPLOAD_ERR_NO_FILE 4 No file was uploaded. | |
* ? UPLOAD_ERR_NO_TMP_DIR 6 Missing a temporary folder. Introduce in PHP 5.0.3. | |
* ? UPLOAD_ERR_CANT_WRITE 7 Failed to write file to disk. Introduce in PHP 5.1.0. | |
* ? UPLOAD_ERR_EXTENSION 8 A PHP extention stopped the file upload. | |
*/ | |
/** | |
* ! Sample code error handling | |
*/ | |
if(isset($_POST['submit'])) { | |
echo "<pre>"; | |
print_r($_FILES['file_upload']); | |
echo "</pre>"; | |
// | |
$upload_errors = array( | |
UPLOAD_ERR_OK => "There is no error", | |
UPLOAD_ERR_INI_SIZE => "The uploaded file exceeds the upload_max_filesize directive in php.ini", | |
UPLOAD_ERR_FORM_SIZE => "The uploaded file exceeds the MAX_FILE_SIZE directive in php.ini", | |
UPLOAD_ERR_PARTIAL => "The uploaded file was only partially uploaded.", | |
UPLOAD_ERR_NO_FILE => "No file was uploaded.", | |
UPLOAD_ERR_NO_TMP_DIR => "Missing a temporary folder", | |
UPLOAD_ERR_CANT_WRITE => "Failed to write file to disk", | |
UPLOAD_ERR_EXTENSION => "A PHP extention stopped the file upload" | |
); | |
// Upload file on the server | |
$the_file = $_FILES['file_upload']['name']; // name of the file to be uploaded | |
$temp_name = $_FILES['file_upload']['tmp_name']; // temporary location | |
$directory = "uploads"; | |
// more uploaded file return true or false | |
// move_uploaded_file(tempfilename, destination); | |
$uploaded_file = move_uploaded_file( $temp_name, $directory . "/" . $the_file); | |
if($uploaded_file) { | |
$the_the_message = "File uploaded successfully"; | |
} else { | |
$the_error = $_FILES['file_upload']['error']; | |
$the_message = $upload_errors[$the_error]; | |
} | |
} | |
$upload_errors = []; | |
$the_message = null; | |
?> | |
<?php | |
echo "<pre>"; | |
print_r($_FILES['file_upload']); | |
echo "</pre>"; | |
?> | |
<!-- Out put --> | |
// Sample associative array from super global $_FILES | |
// Array | |
// ( | |
// [name] => Todo MongoDB CRUD Application.JPG | |
// [type] => image/jpeg | |
// [tmp_name] => C:\xampp\tmp\phpD423.tmp | |
// [error] => 0 | |
// [size] => 72443 | |
// ) | |
<!DOCTYPE html> | |
<html lang="en"> | |
<head> | |
<meta charset="UTF-8"> | |
<meta name="viewport" content="width=device-width, initial-scale=1.0"> | |
<meta http-equiv="X-UA-Compatible" content="ie=edge"> | |
<title>Document</title> | |
<style> | |
pre { | |
display: block; | |
padding: 9.5px; | |
margin: 0 0 10px; | |
font-size: 13px; | |
line-height: 1.42857143; | |
color: #333; | |
word-break: break-all; | |
word-wrap: break-word; | |
background-color: #f5f5f5; | |
border: 1px solid #ccc; | |
border-radius: 4px; | |
} | |
</style> | |
</head> | |
<body> | |
<form action="" method="post" enctype="multipart/form-data"> | |
<h2> | |
<?php | |
if(!empty($upload_errors)) { | |
echo $the_message; | |
} | |
?> | |
</h2> | |
<input type="file" name="file_upload"><br> | |
<input type="submit" value="Submit" name="submit"> | |
</form> | |
</body> | |
</html> | |
<?php | |
############################ OOP basic database connection | |
require_once('config.php'); | |
class Database { | |
private $host = DB_HOST; | |
private $user = DB_USER; | |
private $pass = DB_PASS; | |
private $db_name = DB_NAME; | |
private $mysqli; | |
public function __construct() { | |
$this->connect(); | |
$this->db_disconnect($this->$mysqli); | |
} | |
private function connect() { | |
$this->mysqli = new mysqli($this->host,$this->user,$this->pass,$this->db_name); | |
$this->confirm_result_set(); | |
} | |
private function confirm_result_set() { | |
if($this->mysqli->connect_errno) { | |
$msg = "Database connection failed: "; | |
$msg .= $this->mysqli->connect_error; | |
$msg .= " (" . $this->mysqli->connect_errno . ")"; | |
exit($msg); | |
} | |
} | |
private function db_disconnect($connection) { | |
if(isset($connection)) { | |
$this->mysqli->close(); | |
} | |
} | |
public function select($query) { | |
$result = $this->mysqli->query($query); | |
// echo $result; | |
if($result->num_rows > 0) { | |
return $result; | |
} else { | |
return false; | |
} | |
} | |
public function insert($query) { | |
$insert = $this->mysqli->query($query); | |
if($insert) { | |
header("Location: index.php?msg= Post inserted..."); | |
} else { | |
echo "Posts did not submited"; | |
} | |
} | |
public function update($query) { | |
$update = $this->mysqli->query($query); | |
if($update) { | |
header("Location: index.php?msg= Post updated..."); | |
} else { | |
echo "Posts did not update"; | |
} | |
} | |
public function delete($query) { | |
$delete = $this->mysqli->query($query); | |
if($delete) { | |
header("Location: index.php?msg= Post deleted..."); | |
} else { | |
echo "Posts did not delete"; | |
} | |
} | |
public function delete_cat_db($query) { | |
$delete = $this->mysqli->query($query); | |
if($delete) { | |
header("Location: index.php?msg= Category Deleted..."); | |
} else { | |
echo "Posts did not delete"; | |
} | |
} | |
} | |
// create data for testing | |
$user = new User(); | |
$user->username = "felix"; | |
$user->password = "1234"; | |
$user->first_name = "Felix"; | |
$user->last_name = "Bacat"; | |
$user->create(); | |
// update data for testing | |
$user = User::find_users_by_id(1); | |
$user->last_name = "Suave"; | |
$user->update(); | |
final class Database { | |
private $connection; | |
public function __construct() { | |
$this->open_db_connection(); | |
} | |
public function open_db_connection() { | |
$this->connection = new mysqli(DB_HOST, DB_USER, DB_PASS,DB_NAME); | |
if($this->connection->connect_errno) { | |
$error_message = "Database connection failed: "; | |
$error_message .= $this->connection->mysqli_connect_error; | |
$error_message .= " (" . $this->connection->connect_errno . ")"; | |
exit($error_message); | |
} | |
} | |
//Read | |
public function query($sql) { | |
$result = $this->connection->query($sql); | |
$this->confirm_query($result); | |
return $result; | |
} | |
private function confirm_query($result) { | |
if(!$result) { | |
exit('Database query failed'); | |
} | |
} | |
private function escape_string($string) { | |
return $this->connection->real_escape_string($this->connection, $string); | |
} | |
public function the_insert_id() { | |
return mysqli_insert_id($this->connection); | |
} | |
} | |
class User { | |
public static function find_all_users() { | |
$sql = "SELECT * FROM users"; | |
return self::find_this_query($sql); | |
} | |
public static function find_all_user_by_id($id) { | |
$sql = "SELECT * FROM users "; | |
$sql .= "WHERE id ='" . $id . "' "; | |
$sql .= "LIMIT 1"; | |
return self::find_this_query($sql); | |
} | |
private static function find_this_query($sql) { | |
global $db; | |
$result_set = $db->query($sql); | |
return $result_set; | |
} | |
} | |
// Refactored USer class | |
class User { | |
public $id; | |
public $username; | |
public $first_name; | |
public $last_name; | |
public static function find_all_users() { | |
$sql = "SELECT * FROM users"; | |
return self::find_this_query($sql); | |
} | |
public static function find_user_by_id($id) { | |
$sql = "SELECT * FROM users "; | |
$sql .= "WHERE id ='" . $id . "' "; | |
$sql .= "LIMIT 1"; | |
$result_set = self::find_this_query($sql); | |
return !empty($the_result_array) ? array_shift($the_result_array) : false ; // array_shift will return onr item | |
} | |
private static function find_this_query($sql) { | |
global $db; | |
$result_set = $db->query($sql); | |
$the_object_array = []; | |
while($row = mysqli_fetch_assoc($result_set)){ | |
$the_object_array[] = self::instantiation($row); | |
} | |
return $the_object_array; | |
} | |
public static function instantiation($found_user) { | |
$the_object = new self; | |
// $the_object->id = $found_user['id']; | |
// $the_object->username = $found_user['username']; | |
// $the_object->first_name = $found_user['first_name']; | |
// $the_object->last_name = $found_user['last_name']; | |
// Short Way Auto Instantiation | |
foreach ($found_user as $property => $value) { | |
if($the_object->has_the_attribute($property)) { | |
$the_object->$property = $value; | |
} | |
} | |
return $the_object; | |
} | |
private function has_the_attribute($property) { | |
$object_properties = get_object_vars($this); // $this is the class User itself | |
return array_key_exists($property, $object_properties); | |
} | |
public function create() { | |
global $db; | |
$sql = "INSERT INTO users "; | |
$sql .= "(username, password, first_name, last_name) "; | |
$sql .= "VALUES ('"; | |
$sql .= $db->escape_string($this->username) . "', '"; | |
$sql .= $db->escape_string($this->password) . "', '"; | |
$sql .= $db->escape_string($this->first_name) . "', '"; | |
$sql .= $db->escape_string($this->last_name) . "')"; | |
// echo $sql; | |
$result = $db->query($sql); | |
if($result) { | |
$this->id = $db->the_insert_id(); | |
return true; | |
} else { | |
echo mysqli_error($db->connection); | |
$db->db_disconnect(); | |
exit; | |
} | |
} | |
// new added functions CRUD | |
public function update() { | |
global $db; | |
$sql = "UPDATE users SET "; | |
$sql .= "username= '" . $db->escape_string($this->username) . "', "; | |
$sql .= "password= '" . $db->escape_string($this->password) . "', "; | |
$sql .= "first_name= '" . $db->escape_string($this->first_name) . "', "; | |
$sql .= "last_name= '" . $db->escape_string($this->last_name) . "' "; | |
$sql .= " WHERE id= '" . $db->escape_string($this->id) . "' LIMIT 1"; | |
$db->query($sql); | |
return mysqli_affected_rows($db->connection) == 1 ? true: false; | |
} | |
public function delete() { | |
global $db; | |
$sql = "DELETE FROM users "; | |
$sql .= "WHERE id='" . $db->escape_string($this->id) . "' "; | |
$sql .= "LIMIT 1"; | |
$db->query($sql); | |
return mysqli_affected_rows($db->connection) == 1 ? true: false; | |
// if($db->query($sql)) { | |
// return true; | |
// } else { | |
// DELETE failed | |
// echo "hello"; | |
// echo mysqli_error($db->connection); | |
// $db->db_disconnect(); | |
// exit; | |
// } | |
} | |
} | |
// how to use | |
$users = User::find_all_users(); | |
foreach($users as $user) { | |
echo $user->id . "<br>"; | |
} | |
// snippets css for diplaying | |
echo "<pre>"; | |
echo print_r($user); | |
echo "</pre>"; | |
// pre { | |
// display: block; | |
// padding: 9.5px; | |
// margin: 0 0 10px; | |
// font-size: 13px; | |
// line-height: 1.42857143; | |
// color: #333; | |
// word-break: break-all; | |
// word-wrap: break-word; | |
// background-color: #f5f5f5; | |
// border: 1px solid #ccc; | |
// border-radius: 4px; | |
// } | |
class Session { | |
private $signed_in = false; | |
public $user_id; | |
public function __construct() { | |
session_start(); | |
$this->check_the_login(); | |
} | |
public function is_signed_in() { | |
return $this->signed_in; | |
} | |
public function login($user) { | |
if($user) { | |
$this->user_id = $_SESSION['user_id'] = $user->id; | |
$this->signed_in = true; | |
} | |
} | |
public function logout() { | |
unset($_SESSION['user_id']); | |
unset($this->user_id); | |
$this->signed_in = false; | |
} | |
private function check_the_login() { | |
if(isset($_SESSION['user_id'])) { | |
$this->user_id = $_SESSION['user_id']; | |
$this->signed_in = true; | |
} else { | |
unset($this->user_id); | |
$this->signed_in = false; | |
} | |
} | |
} | |
// absctraction version | |
class User { | |
protected static $db_table = "users"; | |
protected static $db_table_fields = ['username', 'password', 'first_name', 'last_name']; | |
public $id; | |
public $username; | |
public $password; | |
public $first_name; | |
public $last_name; | |
public static function find_all_users() { | |
$sql = "SELECT * FROM users"; | |
return self::find_this_query($sql); | |
} | |
public static function find_user_by_id($id) { | |
$sql = "SELECT * FROM users "; | |
$sql .= "WHERE id ='" . $id . "' "; | |
$sql .= "LIMIT 1"; | |
$the_result_array = self::find_this_query($sql); | |
return !empty($the_result_array) ? array_shift($the_result_array) : false ; // array_shift will return onr item | |
} | |
public static function find_user_by_username($username) { | |
$sql = "SELECT * FROM users "; | |
$sql .= "WHERE username ='" . $username . "' "; | |
$sql .= "LIMIT 1"; | |
$the_result_array = self::find_this_query($sql); | |
return !empty($the_result_array) ? array_shift($the_result_array) : false ; // array_shift will return onr item | |
} | |
public static function find_this_query($sql) { | |
global $db; | |
$result_set = $db->query($sql); | |
$the_object_array = []; | |
while($row = mysqli_fetch_assoc($result_set)){ | |
$the_object_array[] = self::instantiation($row); | |
} | |
return $the_object_array; | |
} | |
public static function verify_user($username,$password) { | |
global $db; | |
$username = $db->escape_string($username); | |
$password = $db->escape_string($password); | |
$sql = "SELECT * FROM users "; | |
$sql .= "WHERE username ='" . $username . "' "; | |
$sql .= "AND password ='" . $password . "'" ; | |
$sql .= "LIMIT 1"; | |
$the_result_array = self::find_this_query($sql); | |
return !empty($the_result_array) ? array_shift($the_result_array) : false ; // array_shift will return onr item | |
} | |
public static function instantiation($the_record) { | |
$the_object = new self; | |
// $the_object->id = $found_user['id']; | |
// $the_object->username = $found_user['username']; | |
// $the_object->first_name = $found_user['first_name']; | |
// $the_object->last_name = $found_user['last_name']; | |
// Short Way Auto Instantiation | |
foreach ($the_record as $property => $value) { | |
if($the_object->has_the_attribute($property)) { | |
$the_object->$property = $value; | |
} | |
} | |
return $the_object; | |
} | |
private function has_the_attribute($property) { | |
$object_properties = get_object_vars($this); // $this is the class User itself | |
return array_key_exists($property, $object_properties); | |
} | |
protected function properties() { | |
// return get_object_vars($this); | |
$properties = []; | |
foreach(self::$db_table_fields as $db_field) { | |
if(property_exists($this, $db_field)) { | |
$properties[$db_field] = $this->$db_field; | |
} | |
} | |
return $properties; | |
} | |
protected function clean_properties() { | |
global $db; | |
$clean_properties = []; | |
foreach($this->properties() as $key => $value) { | |
$clean_properties[$key] = $db->escape_string($value); | |
} | |
return $clean_properties; | |
} | |
public function save() { | |
return isset($this->id) ? $this->update() : $this->create(); | |
} | |
public function create() { | |
global $db; | |
$properties = $this->clean_properties(); | |
$sql = "INSERT INTO " . self::$db_table . " "; | |
$sql .= "(".implode(", ", array_keys($properties)).") "; | |
$sql .= "VALUES ('" . implode("','", array_values($properties)). "')"; | |
echo $sql; | |
// $sql = "INSERT INTO " . self::$db_table . " "; | |
// $sql .= "(".implode(", ", array_keys($properties)).")"; | |
// $sql .= "VALUES ('"; | |
// $sql .= $db->escape_string($this->username) . "', '"; | |
// $sql .= $db->escape_string($this->password) . "', '"; | |
// $sql .= $db->escape_string($this->first_name) . "', '"; | |
// $sql .= $db->escape_string($this->last_name) . "')"; | |
// echo $sql; | |
$result = $db->query($sql); | |
if($result) { | |
$this->id = $db->the_insert_id(); | |
return true; | |
} else { | |
echo mysqli_error($db->connection); | |
$db->db_disconnect(); | |
exit; | |
} | |
} | |
public function update() { | |
global $db; | |
$properties = $this->clean_properties(); | |
$properties_pairs = []; | |
foreach($properties as $key => $value) { | |
$properties_pairs[] = "{$key}='{$value}'"; | |
} | |
$sql = "UPDATE " . self::$db_table . " SET "; | |
$sql .= implode(", ", $properties_pairs); | |
$sql .= " WHERE id= " . $db->escape_string($this->id); | |
// echo $sql; | |
// $sql .= "username= '" . $db->escape_string($this->username) . "', "; | |
// $sql .= "password= '" . $db->escape_string($this->password) . "', "; | |
// $sql .= "first_name= '" . $db->escape_string($this->first_name) . "', "; | |
// $sql .= "last_name= '" . $db->escape_string($this->last_name) . "' "; | |
// $sql .= "WHERE id= " . $db->escape_string($this->id); | |
$db->query($sql); | |
return mysqli_affected_rows($db->connection) == 1 ? true: false; | |
} | |
public function delete() { | |
global $db; | |
$sql = "DELETE FROM " . self::$db_table . " "; | |
$sql .= "WHERE id='" . $db->escape_string($this->id) . "' "; | |
$sql .= "LIMIT 1"; | |
$db->query($sql); | |
return mysqli_affected_rows($db->connection) == 1 ? true: false; | |
// if($db->query($sql)) { | |
// return true; | |
// } else { | |
// DELETE failed | |
// echo "hello"; | |
// echo mysqli_error($db->connection); | |
// $db->db_disconnect(); | |
// exit; | |
// } | |
} | |
} | |
// The parent Class Db_object | |
// it uses late static binding | |
class Db_object { | |
public static function find_all() { | |
$sql = "SELECT * FROM " . static::$db_table ." "; | |
return static::find_by_query($sql); | |
} | |
public static function find_by_id($id) { | |
$sql = "SELECT * FROM " . static::$db_table ." "; | |
$sql .= "WHERE id ='" . $id . "' "; | |
$sql .= "LIMIT 1"; | |
$the_result_array = static::find_by_query($sql); | |
return !empty($the_result_array) ? array_shift($the_result_array) : false ; // array_shift will return onr item | |
} | |
public static function find_by_query($sql) { | |
global $db; | |
$result_set = $db->query($sql); | |
$the_object_array = []; | |
while($row = mysqli_fetch_assoc($result_set)){ | |
$the_object_array[] = static::instantiation($row); | |
} | |
return $the_object_array; | |
} | |
public static function instantiation($the_record) { | |
$calling_class = get_called_class(); | |
$the_object = new $calling_class; | |
// $the_object->id = $found_user['id']; | |
// $the_object->username = $found_user['username']; | |
// $the_object->first_name = $found_user['first_name']; | |
// $the_object->last_name = $found_user['last_name']; | |
// Short Way Auto Instantiation | |
foreach ($the_record as $property => $value) { | |
if($the_object->has_the_attribute($property)) { | |
$the_object->$property = $value; | |
} | |
} | |
return $the_object; | |
} | |
private function has_the_attribute($property) { | |
$object_properties = get_object_vars($this); // $this is the class User itself | |
return array_key_exists($property, $object_properties); | |
} | |
protected function properties() { | |
// return get_object_vars($this); | |
$properties = []; | |
foreach(static::$db_table_fields as $db_field) { | |
if(property_exists($this, $db_field)) { | |
$properties[$db_field] = $this->$db_field; | |
} | |
} | |
return $properties; | |
} | |
protected function clean_properties() { | |
global $db; | |
$clean_properties = []; | |
foreach($this->properties() as $key => $value) { | |
$clean_properties[$key] = $db->escape_string($value); | |
} | |
return $clean_properties; | |
} | |
public function save() { | |
return isset($this->id) ? $this->update() : $this->create(); | |
} | |
public function create() { | |
global $db; | |
$properties = $this->clean_properties(); | |
$sql = "INSERT INTO " . static::$db_table . " "; | |
$sql .= "(".implode(", ", array_keys($properties)).") "; | |
$sql .= "VALUES ('" . implode("','", array_values($properties)). "')"; | |
// $sql = "INSERT INTO " . self::$db_table . " "; | |
// $sql .= "(".implode(", ", array_keys($properties)).")"; | |
// $sql .= "VALUES ('"; | |
// $sql .= $db->escape_string($this->username) . "', '"; | |
// $sql .= $db->escape_string($this->password) . "', '"; | |
// $sql .= $db->escape_string($this->first_name) . "', '"; | |
// $sql .= $db->escape_string($this->last_name) . "')"; | |
// echo $sql; | |
$result = $db->query($sql); | |
if($result) { | |
$this->id = $db->the_insert_id(); | |
return true; | |
} else { | |
echo mysqli_error($db->connection); | |
$db->db_disconnect(); | |
exit; | |
} | |
} | |
public function update() { | |
global $db; | |
$properties = $this->clean_properties(); | |
$properties_pairs = []; | |
foreach($properties as $key => $value) { | |
$properties_pairs[] = "{$key}='{$value}'"; | |
} | |
$sql = "UPDATE " . static::$db_table . " SET "; | |
$sql .= implode(", ", $properties_pairs); | |
$sql .= " WHERE id= " . $db->escape_string($this->id); | |
// echo $sql; | |
// $sql .= "username= '" . $db->escape_string($this->username) . "', "; | |
// $sql .= "password= '" . $db->escape_string($this->password) . "', "; | |
// $sql .= "first_name= '" . $db->escape_string($this->first_name) . "', "; | |
// $sql .= "last_name= '" . $db->escape_string($this->last_name) . "' "; | |
// $sql .= "WHERE id= " . $db->escape_string($this->id); | |
$db->query($sql); | |
return mysqli_affected_rows($db->connection) == 1 ? true: false; | |
} | |
public function delete() { | |
global $db; | |
$sql = "DELETE FROM " . static::$db_table . " "; | |
$sql .= "WHERE id='" . $db->escape_string($this->id) . "' "; | |
$sql .= "LIMIT 1"; | |
$db->query($sql); | |
return mysqli_affected_rows($db->connection) == 1 ? true: false; | |
// if($db->query($sql)) { | |
// return true; | |
// } else { | |
// DELETE failed | |
// echo "hello"; | |
// echo mysqli_error($db->connection); | |
// $db->db_disconnect(); | |
// exit; | |
// } | |
} | |
} | |
// Photo cLass | |
class Photo extends Db_object { | |
protected static $db_table = "photos"; | |
protected static $db_table_fields = ['title', 'description', 'filename', 'type', 'size']; | |
public $photo_id; | |
public $title; | |
public $discription; | |
public $filename; | |
public $type; | |
public $size; | |
public $tmp_path; | |
public $upload_directory = "img"; | |
public $errors = []; | |
public $upload_errors_array = array( | |
UPLOAD_ERR_OK => "There is no error", | |
UPLOAD_ERR_INI_SIZE => "The uploaded file exceeds the upload_max_filesize directive in php.ini", | |
UPLOAD_ERR_FORM_SIZE => "The uploaded file exceeds the MAX_FILE_SIZE directive in php.ini", | |
UPLOAD_ERR_PARTIAL => "The uploaded file was only partially uploaded.", | |
UPLOAD_ERR_NO_FILE => "No file was uploaded.", | |
UPLOAD_ERR_NO_TMP_DIR => "Missing a temporary folder", | |
UPLOAD_ERR_CANT_WRITE => "Failed to write file to disk", | |
UPLOAD_ERR_EXTENSION => "A PHP extention stopped the file upload" | |
); | |
// This is passing $_FILES['uploaded_file'] as an argument | |
public function set_file($file) { | |
// error checking just to make sure the file as uploaded | |
if( empty($file) || !$file || !is_array($file) ) { | |
$this->errors[] = "There was no file uploaded here"; | |
return false; | |
} elseif($file['error'] !=0) { | |
$this->errors[] = $this->upload_errors_array[$file['error']]; | |
return false; | |
} else { //success | |
$this->filename = basename($file['name']); | |
$this->tmp_path = $file['tmp_name']; | |
$this->type = $file['type']; | |
$this->size = $file['size']; | |
} | |
} | |
public function save() { | |
if($this->photo_id) { | |
$this->update(); | |
} else { | |
if(!empty($this->errors)) { | |
return false; | |
} | |
if(empty($this->filename) || empty($this->tmp_path)) { | |
$this->errors[] = "The file was not available"; | |
return false; | |
} | |
$target_path = IMG_PATH . DS . $this->filename; | |
if(file_exists($target_path)) { | |
$this->errors[] = "The file {$this->filename} already exists"; | |
return false; | |
} | |
// move_uploaded_file(tempfilename, destination); | |
if(move_uploaded_file($this->tmp_path, $target_path)) { | |
if($this->create()) { // if its avable to create it | |
unset($this->tmp_path); // unset the tmp_path | |
return true; | |
} | |
} else { | |
$this->errors[] = "The file directory probably does not have permission"; | |
return false; | |
} | |
} | |
} | |
public function picture_path() { | |
return $this->upload_directory.DS.$this->filename; | |
} | |
} | |
// Prepared Statements | |
/** | |
* * Prepare statement once and reuse it many times | |
* * Can be faster | |
* * Separate the query from the dynamic data | |
* * Prevent SQL injection | |
*/ | |
$sql = "SELECT id, first_name, last_name "; | |
$sql .= "FROM users "; | |
$sql .= "WHERE username = ? AND PASSWORD = ?"; | |
$stmt = mysqli_prepare($db, $sql); | |
mysqli_stmt_bind_param($stmt, 'ss', $username, $password); | |
mysqli_stmt($stmt); | |
mysqli_stmt_bind_result($stmt, $id, $first_name, $last_name); | |
mysqli_stmt_fetch($stmt); | |
mysqli_stmt_close($stmt); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
// Subjects | |
function find_all_subjects($options=[]) { | |
global $db; | |
$visible = $options['visible'] ?? false; | |
$sql = "SELECT * FROM subjects "; | |
if($visible) { | |
$sql .= "WHERE visible = true "; | |
} | |
$sql .= "ORDER BY position ASC"; | |
//echo $sql; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
return $result; | |
} | |
function find_subject_by_id($id, $options=[]) { | |
global $db; | |
$visible = $options['visible'] ?? false; | |
$sql = "SELECT * FROM subjects "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "' "; | |
if($visible) { | |
$sql .= "AND visible = true"; | |
} | |
// echo $sql; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
$subject = mysqli_fetch_assoc($result); | |
mysqli_free_result($result); | |
return $subject; // returns an assoc. array | |
} | |
function validate_subject($subject) { | |
$errors = []; | |
// menu_name | |
if(is_blank($subject['menu_name'])) { | |
$errors[] = "Name cannot be blank."; | |
} elseif(!has_length($subject['menu_name'], ['min' => 2, 'max' => 255])) { | |
$errors[] = "Name must be between 2 and 255 characters."; | |
} | |
// position | |
// Make sure we are working with an integer | |
$postion_int = (int) $subject['position']; | |
if($postion_int <= 0) { | |
$errors[] = "Position must be greater than zero."; | |
} | |
if($postion_int > 999) { | |
$errors[] = "Position must be less than 999."; | |
} | |
// visible | |
// Make sure we are working with a string | |
$visible_str = (string) $subject['visible']; | |
if(!has_inclusion_of($visible_str, ["0","1"])) { | |
$errors[] = "Visible must be true or false."; | |
} | |
return $errors; | |
} | |
function insert_subject($subject) { | |
global $db; | |
$errors = validate_subject($subject); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
shift_subject_positions(0, $subject['position']); | |
$sql = "INSERT INTO subjects "; | |
$sql .= "(menu_name, position, visible) "; | |
$sql .= "VALUES ("; | |
$sql .= "'" . db_escape($db, $subject['menu_name']) . "',"; | |
$sql .= "'" . db_escape($db, $subject['position']) . "',"; | |
$sql .= "'" . db_escape($db, $subject['visible']) . "'"; | |
$sql .= ")"; | |
$result = mysqli_query($db, $sql); | |
// For INSERT statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// INSERT failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function update_subject($subject) { | |
global $db; | |
$errors = validate_subject($subject); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
$old_subject = find_subject_by_id($subject['id']); | |
$old_position = $old_subject['position']; | |
shift_subject_positions($old_position, $subject['position'], $subject['id']); | |
$sql = "UPDATE subjects SET "; | |
$sql .= "menu_name='" . db_escape($db, $subject['menu_name']) . "', "; | |
$sql .= "position='" . db_escape($db, $subject['position']) . "', "; | |
$sql .= "visible='" . db_escape($db, $subject['visible']) . "' "; | |
$sql .= "WHERE id='" . db_escape($db, $subject['id']) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For UPDATE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// UPDATE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function delete_subject($id) { | |
global $db; | |
$old_subject = find_subject_by_id($id); | |
$old_position = $old_subject['position']; | |
shift_subject_positions($old_position, 0, $id); | |
$sql = "DELETE FROM subjects "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For DELETE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// DELETE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function shift_subject_positions($start_pos, $end_pos, $current_id=0) { | |
global $db; | |
if($start_pos == $end_pos) { return; } | |
$sql = "UPDATE subjects "; | |
if($start_pos == 0) { | |
// new item, +1 to items greater than $end_pos | |
$sql .= "SET position = position + 1 "; | |
$sql .= "WHERE position >= '" . db_escape($db, $end_pos) . "' "; | |
} elseif($end_pos == 0) { | |
// delete item, -1 from items greater than $start_pos | |
$sql .= "SET position = position - 1 "; | |
$sql .= "WHERE position > '" . db_escape($db, $start_pos) . "' "; | |
} elseif($start_pos < $end_pos) { | |
// move later, -1 from items between (including $end_pos) | |
$sql .= "SET position = position - 1 "; | |
$sql .= "WHERE position > '" . db_escape($db, $start_pos) . "' "; | |
$sql .= "AND position <= '" . db_escape($db, $end_pos) . "' "; | |
} elseif($start_pos > $end_pos) { | |
// move earlier, +1 to items between (including $end_pos) | |
$sql .= "SET position = position + 1 "; | |
$sql .= "WHERE position >= '" . db_escape($db, $end_pos) . "' "; | |
$sql .= "AND position < '" . db_escape($db, $start_pos) . "' "; | |
} | |
// Exclude the current_id in the SQL WHERE clause | |
$sql .= "AND id != '" . db_escape($db, $current_id) . "' "; | |
$result = mysqli_query($db, $sql); | |
// For UPDATE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// UPDATE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
// Pages | |
function find_all_pages() { | |
global $db; | |
$sql = "SELECT * FROM pages "; | |
$sql .= "ORDER BY subject_id ASC, position ASC"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
return $result; | |
} | |
function find_page_by_id($id, $options=[]) { | |
global $db; | |
$visible = $options['visible'] ?? false; | |
$sql = "SELECT * FROM pages "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "' "; | |
if($visible) { | |
$sql .= "AND visible = true"; | |
} | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
$page = mysqli_fetch_assoc($result); | |
mysqli_free_result($result); | |
return $page; // returns an assoc. array | |
} | |
function validate_page($page) { | |
$errors = []; | |
// subject_id | |
if(is_blank($page['subject_id'])) { | |
$errors[] = "Subject cannot be blank."; | |
} | |
// menu_name | |
if(is_blank($page['menu_name'])) { | |
$errors[] = "Name cannot be blank."; | |
} elseif(!has_length($page['menu_name'], ['min' => 2, 'max' => 255])) { | |
$errors[] = "Name must be between 2 and 255 characters."; | |
} | |
$current_id = $page['id'] ?? '0'; | |
if(!has_unique_page_menu_name($page['menu_name'], $current_id)) { | |
$errors[] = "Menu name must be unique."; | |
} | |
// position | |
// Make sure we are working with an integer | |
$postion_int = (int) $page['position']; | |
if($postion_int <= 0) { | |
$errors[] = "Position must be greater than zero."; | |
} | |
if($postion_int > 999) { | |
$errors[] = "Position must be less than 999."; | |
} | |
// visible | |
// Make sure we are working with a string | |
$visible_str = (string) $page['visible']; | |
if(!has_inclusion_of($visible_str, ["0","1"])) { | |
$errors[] = "Visible must be true or false."; | |
} | |
// content | |
if(is_blank($page['content'])) { | |
$errors[] = "Content cannot be blank."; | |
} | |
return $errors; | |
} | |
function insert_page($page) { | |
global $db; | |
$errors = validate_page($page); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
shift_page_positions(0, $page['position'], $page['subject_id']); | |
$sql = "INSERT INTO pages "; | |
$sql .= "(subject_id, menu_name, position, visible, content) "; | |
$sql .= "VALUES ("; | |
$sql .= "'" . db_escape($db, $page['subject_id']) . "',"; | |
$sql .= "'" . db_escape($db, $page['menu_name']) . "',"; | |
$sql .= "'" . db_escape($db, $page['position']) . "',"; | |
$sql .= "'" . db_escape($db, $page['visible']) . "',"; | |
$sql .= "'" . db_escape($db, $page['content']) . "'"; | |
$sql .= ")"; | |
$result = mysqli_query($db, $sql); | |
// For INSERT statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// INSERT failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function update_page($page) { | |
global $db; | |
$errors = validate_page($page); | |
if(!empty($errors)) { | |
return $errors; | |
} | |
$old_page = find_page_by_id($page['id']); | |
$old_position = $old_page['position']; | |
shift_page_positions($old_position, $page['position'], $page['subject_id'], $page['id']); | |
$sql = "UPDATE pages SET "; | |
$sql .= "subject_id='" . db_escape($db, $page['subject_id']) . "', "; | |
$sql .= "menu_name='" . db_escape($db, $page['menu_name']) . "', "; | |
$sql .= "position='" . db_escape($db, $page['position']) . "', "; | |
$sql .= "visible='" . db_escape($db, $page['visible']) . "', "; | |
$sql .= "content='" . db_escape($db, $page['content']) . "' "; | |
$sql .= "WHERE id='" . db_escape($db, $page['id']) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For UPDATE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// UPDATE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function delete_page($id) { | |
global $db; | |
$old_page = find_page_by_id($id); | |
$old_position = $old_page['position']; | |
shift_page_positions($old_position, 0, $old_page['subject_id'], $id); | |
$sql = "DELETE FROM pages "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For DELETE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// DELETE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function find_pages_by_subject_id($subject_id, $options=[]) { | |
global $db; | |
$visible = $options['visible'] ?? false; | |
$sql = "SELECT * FROM pages "; | |
$sql .= "WHERE subject_id='" . db_escape($db, $subject_id) . "' "; | |
if($visible) { | |
$sql .= "AND visible = true "; | |
} | |
$sql .= "ORDER BY position ASC"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
return $result; | |
} | |
function count_pages_by_subject_id($subject_id, $options=[]) { | |
global $db; | |
$visible = $options['visible'] ?? false; | |
$sql = "SELECT COUNT(id) FROM pages "; | |
$sql .= "WHERE subject_id='" . db_escape($db, $subject_id) . "' "; | |
if($visible) { | |
$sql .= "AND visible = true "; | |
} | |
$sql .= "ORDER BY position ASC"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
$row = mysqli_fetch_row($result); | |
mysqli_free_result($result); | |
$count = $row[0]; | |
return $count; | |
} | |
function shift_page_positions($start_pos, $end_pos, $subject_id, $current_id=0) { | |
global $db; | |
if($start_pos == $end_pos) { return; } | |
$sql = "UPDATE pages "; | |
if($start_pos == 0) { | |
// new item, +1 to items greater than $end_pos | |
$sql .= "SET position = position + 1 "; | |
$sql .= "WHERE position >= '" . db_escape($db, $end_pos) . "' "; | |
} elseif($end_pos == 0) { | |
// delete item, -1 from items greater than $start_pos | |
$sql .= "SET position = position - 1 "; | |
$sql .= "WHERE position > '" . db_escape($db, $start_pos) . "' "; | |
} elseif($start_pos < $end_pos) { | |
// move later, -1 from items between (including $end_pos) | |
$sql .= "SET position = position - 1 "; | |
$sql .= "WHERE position > '" . db_escape($db, $start_pos) . "' "; | |
$sql .= "AND position <= '" . db_escape($db, $end_pos) . "' "; | |
} elseif($start_pos > $end_pos) { | |
// move earlier, +1 to items between (including $end_pos) | |
$sql .= "SET position = position + 1 "; | |
$sql .= "WHERE position >= '" . db_escape($db, $end_pos) . "' "; | |
$sql .= "AND position < '" . db_escape($db, $start_pos) . "' "; | |
} | |
// Exclude the current_id in the SQL WHERE clause | |
$sql .= "AND id != '" . db_escape($db, $current_id) . "' "; | |
$sql .= "AND subject_id = '" . db_escape($db, $subject_id) . "'"; | |
$result = mysqli_query($db, $sql); | |
// For UPDATE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// UPDATE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
// Admins | |
// Find all admins, ordered last_name, first_name | |
function find_all_admins() { | |
global $db; | |
$sql = "SELECT * FROM admins "; | |
$sql .= "ORDER BY last_name ASC, first_name ASC"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
return $result; | |
} | |
function find_admin_by_id($id) { | |
global $db; | |
$sql = "SELECT * FROM admins "; | |
$sql .= "WHERE id='" . db_escape($db, $id) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
$admin = mysqli_fetch_assoc($result); // find first | |
mysqli_free_result($result); | |
return $admin; // returns an assoc. array | |
} | |
function find_admin_by_username($username) { | |
global $db; | |
$sql = "SELECT * FROM admins "; | |
$sql .= "WHERE username='" . db_escape($db, $username) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
confirm_result_set($result); | |
$admin = mysqli_fetch_assoc($result); // find first | |
mysqli_free_result($result); | |
return $admin; // returns an assoc. array | |
} | |
function validate_admin($admin, $options=[]) { | |
$password_required = $options['password_required'] ?? true; | |
if(is_blank($admin['first_name'])) { | |
$errors[] = "First name cannot be blank."; | |
} elseif (!has_length($admin['first_name'], array('min' => 2, 'max' => 255))) { | |
$errors[] = "First name must be between 2 and 255 characters."; | |
} | |
if(is_blank($admin['last_name'])) { | |
$errors[] = "Last name cannot be blank."; | |
} elseif (!has_length($admin['last_name'], array('min' => 2, 'max' => 255))) { | |
$errors[] = "Last name must be between 2 and 255 characters."; | |
} | |
if(is_blank($admin['email'])) { | |
$errors[] = "Email cannot be blank."; | |
} elseif (!has_length($admin['email'], array('max' => 255))) { | |
$errors[] = "Last name must be less than 255 characters."; | |
} elseif (!has_valid_email_format($admin['email'])) { | |
$errors[] = "Email must be a valid format."; | |
} | |
if(is_blank($admin['username'])) { | |
$errors[] = "Username cannot be blank."; | |
} elseif (!has_length($admin['username'], array('min' => 8, 'max' => 255))) { | |
$errors[] = "Username must be between 8 and 255 characters."; | |
} elseif (!has_unique_username($admin['username'], $admin['id'] ?? 0)) { | |
$errors[] = "Username not allowed. Try another."; | |
} | |
if($password_required) { | |
if(is_blank($admin['password'])) { | |
$errors[] = "Password cannot be blank."; | |
} elseif (!has_length($admin['password'], array('min' => 12))) { | |
$errors[] = "Password must contain 12 or more characters"; | |
} elseif (!preg_match('/[A-Z]/', $admin['password'])) { | |
$errors[] = "Password must contain at least 1 uppercase letter"; | |
} elseif (!preg_match('/[a-z]/', $admin['password'])) { | |
$errors[] = "Password must contain at least 1 lowercase letter"; | |
} elseif (!preg_match('/[0-9]/', $admin['password'])) { | |
$errors[] = "Password must contain at least 1 number"; | |
} elseif (!preg_match('/[^A-Za-z0-9\s]/', $admin['password'])) { | |
$errors[] = "Password must contain at least 1 symbol"; | |
} | |
if(is_blank($admin['confirm_password'])) { | |
$errors[] = "Confirm password cannot be blank."; | |
} elseif ($admin['password'] !== $admin['confirm_password']) { | |
$errors[] = "Password and confirm password must match."; | |
} | |
} | |
return $errors; | |
} | |
function insert_admin($admin) { | |
global $db; | |
$errors = validate_admin($admin); | |
if (!empty($errors)) { | |
return $errors; | |
} | |
$hashed_password = password_hash($admin['password'], PASSWORD_BCRYPT); | |
$sql = "INSERT INTO admins "; | |
$sql .= "(first_name, last_name, email, username, hashed_password) "; | |
$sql .= "VALUES ("; | |
$sql .= "'" . db_escape($db, $admin['first_name']) . "',"; | |
$sql .= "'" . db_escape($db, $admin['last_name']) . "',"; | |
$sql .= "'" . db_escape($db, $admin['email']) . "',"; | |
$sql .= "'" . db_escape($db, $admin['username']) . "',"; | |
$sql .= "'" . db_escape($db, $hashed_password) . "'"; | |
$sql .= ")"; | |
$result = mysqli_query($db, $sql); | |
// For INSERT statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// INSERT failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function update_admin($admin) { | |
global $db; | |
$password_sent = !is_blank($admin['password']); | |
$errors = validate_admin($admin, ['password_required' => $password_sent]); | |
if (!empty($errors)) { | |
return $errors; | |
} | |
$hashed_password = password_hash($admin['password'], PASSWORD_BCRYPT); | |
$sql = "UPDATE admins SET "; | |
$sql .= "first_name='" . db_escape($db, $admin['first_name']) . "', "; | |
$sql .= "last_name='" . db_escape($db, $admin['last_name']) . "', "; | |
$sql .= "email='" . db_escape($db, $admin['email']) . "', "; | |
if($password_sent) { | |
$sql .= "hashed_password='" . db_escape($db, $hashed_password) . "', "; | |
} | |
$sql .= "username='" . db_escape($db, $admin['username']) . "' "; | |
$sql .= "WHERE id='" . db_escape($db, $admin['id']) . "' "; | |
$sql .= "LIMIT 1"; | |
$result = mysqli_query($db, $sql); | |
// For UPDATE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// UPDATE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
function delete_admin($admin) { | |
global $db; | |
$sql = "DELETE FROM admins "; | |
$sql .= "WHERE id='" . db_escape($db, $admin['id']) . "' "; | |
$sql .= "LIMIT 1;"; | |
$result = mysqli_query($db, $sql); | |
// For DELETE statements, $result is true/false | |
if($result) { | |
return true; | |
} else { | |
// DELETE failed | |
echo mysqli_error($db); | |
db_disconnect($db); | |
exit; | |
} | |
} | |
?> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 02 JOIN | |
-- test.db | |
-- join example tables, left and right | |
CREATE TABLE left ( id INTEGER, description TEXT ); | |
CREATE TABLE right ( id INTEGER, description TEXT ); | |
INSERT INTO left VALUES ( 1, 'left 01' ); | |
INSERT INTO left VALUES ( 2, 'left 02' ); | |
INSERT INTO left VALUES ( 3, 'left 03' ); | |
INSERT INTO left VALUES ( 4, 'left 04' ); | |
INSERT INTO left VALUES ( 5, 'left 05' ); | |
INSERT INTO left VALUES ( 6, 'left 06' ); | |
INSERT INTO left VALUES ( 7, 'left 07' ); | |
INSERT INTO left VALUES ( 8, 'left 08' ); | |
INSERT INTO left VALUES ( 9, 'left 09' ); | |
INSERT INTO right VALUES ( 6, 'right 06' ); | |
INSERT INTO right VALUES ( 7, 'right 07' ); | |
INSERT INTO right VALUES ( 8, 'right 08' ); | |
INSERT INTO right VALUES ( 9, 'right 09' ); | |
INSERT INTO right VALUES ( 10, 'right 10' ); | |
INSERT INTO right VALUES ( 11, 'right 11' ); | |
INSERT INTO right VALUES ( 11, 'right 12' ); | |
INSERT INTO right VALUES ( 11, 'right 13' ); | |
INSERT INTO right VALUES ( 11, 'right 14' ); | |
SELECT * FROM left; | |
SELECT * FROM right; | |
SELECT l.description AS left, r.description AS right | |
FROM left AS l | |
JOIN right AS r ON l.id = r.id | |
; | |
-- restore database | |
DROP TABLE left; | |
DROP TABLE right; | |
-- sale example | |
SELECT * FROM sale; | |
SELECT * FROM item; | |
SELECT s.id AS sale, i.name, s.price | |
FROM sale AS s | |
JOIN item AS i ON s.item_id = i.id | |
; | |
SELECT s.id AS sale, s.date, i.name, i.description, s.price | |
FROM sale AS s | |
JOIN item AS i ON s.item_id = i.id | |
; | |
-- 03 Junction Table | |
-- test.db | |
SELECT * FROM customer; | |
SELECT * FROM item; | |
SELECT * FROM sale; | |
SELECT i.name AS Item, c.name AS Cust, s.price AS Price | |
FROM sale AS s | |
JOIN item AS i ON s.item_id = i.id | |
JOIN customer AS c ON s.customer_id = c.id | |
ORDER BY Cust, Item | |
; | |
-- a customer without sales | |
INSERT INTO customer ( name ) VALUES ( 'Jane Smith' ); | |
SELECT * FROM customer; | |
-- left joins | |
SELECT c.name AS Cust, c.zip, i.name AS Item, i.description, s.quantity AS Quan, s.price AS Price | |
FROM customer AS c | |
LEFT JOIN sale AS s ON s.customer_id = c.id | |
LEFT JOIN item AS i ON s.item_id = i.id | |
ORDER BY Cust, Item | |
; | |
-- restore database | |
DELETE FROM customer WHERE id = 4; | |
CREATE TABLE customer ( | |
id INT(11) NOT NULL AUTO_INCREMENT, | |
name VARCHAR(255), | |
address VARCHAR(255), | |
city VARCHAR(255), | |
state VARCHAR(255), | |
state VARCHAR(255), | |
PRIMARY KEY (id) | |
); | |
CREATE TABLE item ( | |
id INT(11) NOT NULL AUTO_INCREMENT, | |
name VARCHAR(255), | |
description VARCHAR(255), | |
PRIMARY KEY (id) | |
); | |
CREATE TABLE sale ( | |
id INT(11) NOT NULL AUTO_INCREMENT, | |
name VARCHAR(255), | |
description VARCHAR(255), | |
PRIMARY KEY (id) | |
); | |
CREATE TABLE pages ( | |
id INT(11) NOT NULL AUTO_INCREMENT, | |
item_id INT(11) NOT NULL, -- Foreign key | |
customer_id INT(11) NOT NULL, -- Foreign key | |
date VARCHAR(30) NOT NULL, | |
quantity INT(3) NOT NULL, | |
price TINYINT(1) NOT NULL, -- for bolean vakues if 1 meand true if -1 means false | |
PRIMARY KEY (ID), | |
INDEX (item_id, ), -- index is going to tell mysql that it should also created index for it for past look ups | |
INDEX (customer_id) | |
); | |
// LENGTH() | |
SELECT Name, LENGTH(Name) as Len FROM City ORDER BY Len DESC; //return Name column and Len | |
SUBSTR(string, start, end ) | |
SELECT SUBSTR('this string', 6); return srting | |
SELECT SUBSTR('this string', 6); | |
SELECT released, | |
SUBSTR(released, 1, 4) AS year, | |
SUBSTR(released, 6, 2) AS month, | |
SUBSTR(released, 9, 2) AS day | |
FROM album | |
ORDER BY released | |
; | |
Out put | |
released year month day | |
1959-08-17 1959 08 17 | |
1965-12-03 1965 12 03 | |
1971-05-00 1971 05 00 | |
1972-01-00 1972 01 00 | |
1973-03-00 1973 03 00 | |
1974-04-22 1974 04 22 | |
2008-07-08 2008 07 08 | |
SELECT 'StRiNg'; | |
SELECT 'StRiNg' = 'string'; | |
SELECT LOWER('StRiNg') = LOWER('string'); | |
SELECT UPPER('StRiNg') = UPPER('string'); | |
SELECT UPPER(Name) FROM City ORDER BY Name; | |
SELECT LOWER(Name) FROM City ORDER BY Name; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
// is_blank('abcd') | |
// * validate data presence | |
// * uses trim() so empty spaces don't count | |
// * uses === to avoid false positives | |
// * better than empty() which considers "0" to be empty | |
function is_blank($value) { | |
return !isset($value) || trim($value) === ''; | |
} | |
// has_presence('abcd') | |
// * validate data presence | |
// * reverse of is_blank() | |
// * I prefer validation names with "has_" | |
function has_presence($value) { | |
return !is_blank($value); | |
} | |
// has_length_greater_than('abcd', 3) | |
// * validate string length | |
// * spaces count towards length | |
// * use trim() if spaces should not count | |
function has_length_greater_than($value, $min) { | |
$length = strlen($value); | |
return $length > $min; | |
} | |
// has_length_less_than('abcd', 5) | |
// * validate string length | |
// * spaces count towards length | |
// * use trim() if spaces should not count | |
function has_length_less_than($value, $max) { | |
$length = strlen($value); | |
return $length < $max; | |
} | |
// has_length_exactly('abcd', 4) | |
// * validate string length | |
// * spaces count towards length | |
// * use trim() if spaces should not count | |
function has_length_exactly($value, $exact) { | |
$length = strlen($value); | |
return $length == $exact; | |
} | |
// has_length('abcd', ['min' => 3, 'max' => 5]) | |
// * validate string length | |
// * combines functions_greater_than, _less_than, _exactly | |
// * spaces count towards length | |
// * use trim() if spaces should not count | |
function has_length($value, $options) { | |
if(isset($options['min']) && !has_length_greater_than($value, $options['min'] - 1)) { | |
return false; | |
} elseif(isset($options['max']) && !has_length_less_than($value, $options['max'] + 1)) { | |
return false; | |
} elseif(isset($options['exact']) && !has_length_exactly($value, $options['exact'])) { | |
return false; | |
} else { | |
return true; | |
} | |
} | |
// has_inclusion_of( 5, [1,3,5,7,9] ) | |
// * validate inclusion in a set | |
function has_inclusion_of($value, $set) { | |
return in_array($value, $set); | |
} | |
// has_exclusion_of( 5, [1,3,5,7,9] ) | |
// * validate exclusion from a set | |
function has_exclusion_of($value, $set) { | |
return !in_array($value, $set); | |
} | |
// has_string('nobody@nowhere.com', '.com') | |
// * validate inclusion of character(s) | |
// * strpos returns string start position or false | |
// * uses !== to prevent position 0 from being considered false | |
// * strpos is faster than preg_match() | |
function has_string($value, $required_string) { | |
return strpos($value, $required_string) !== false; | |
} | |
// has_valid_email_format('nobody@nowhere.com') | |
// * validate correct format for email addresses | |
// * format: [chars]@[chars].[2+ letters] | |
// * preg_match is helpful, uses a regular expression | |
// returns 1 for a match, 0 for no match | |
// http://php.net/manual/en/function.preg-match.php | |
function has_valid_email_format($value) { | |
$email_regex = '/\A[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\Z/i'; | |
return preg_match($email_regex, $value) === 1; | |
} | |
// has_unique_page_menu_name('History') | |
// * Validates uniqueness of pages.menu_name | |
// * For new records, provide only the menu_name. | |
// * For existing records, provide current ID as second arugment | |
// has_unique_page_menu_name('History', 4) | |
function has_unique_page_menu_name($menu_name, $current_id="0") { | |
global $db; | |
$sql = "SELECT * FROM pages "; | |
$sql .= "WHERE menu_name='" . db_escape($db, $menu_name) . "' "; | |
$sql .= "AND id != '" . db_escape($db, $current_id) . "'"; | |
$page_set = mysqli_query($db, $sql); | |
$page_count = mysqli_num_rows($page_set); | |
mysqli_free_result($page_set); | |
return $page_count === 0; | |
} | |
// has_unique_username('johnqpublic') | |
// * Validates uniqueness of admins.username | |
// * For new records, provide only the username. | |
// * For existing records, provide current ID as second argument | |
// has_unique_username('johnqpublic', 4) | |
function has_unique_username($username, $current_id="0") { | |
global $db; | |
$sql = "SELECT * FROM admins "; | |
$sql .= "WHERE username='" . db_escape($db, $username) . "' "; | |
$sql .= "AND id != '" . db_escape($db, $current_id) . "'"; | |
$result = mysqli_query($db, $sql); | |
$admin_count = mysqli_num_rows($result); | |
mysqli_free_result($result); | |
return $admin_count === 0; | |
} | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment