Skip to content

Instantly share code, notes, and snippets.

@edoves

edoves/Relational_Database.sql

Last active September 4, 2022 02:35
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save edoves/fdae23361201c76b58ebd41ca9757fa3 to your computer and use it in GitHub Desktop.
Save edoves/fdae23361201c76b58ebd41ca9757fa3 to your computer and use it in GitHub Desktop.
Download ZIP
mysqlcommand, mysqlconnect php, mysqliconnectOOP version php
Raw
auth_functions.php
<?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
}
}
?>
Raw
command_creating_globe_bank_DB.sql
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,&nbsp;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&mdash;whether a farm or condo or anything in between&mdash;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);
Raw
mysqlcommand.sql
-- 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);
Raw
mysqlconnect.php
<?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;
}
Raw
mysqliconnectOOP.php
<?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);
Raw
query_functions.php
<?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;
}
}
?>
Raw
Relational_Database.sql
-- 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;
Raw
validation_functions.php
<?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