SQL

SQL - Data Manipulation Statements

Tips:

SQL is case-insensitive, but it is best practice to type SQL keywords in all caps
Always end query with a semicolon ;
To SELECT all columns use: SELECT *
SQL doesn't care about whitespace. You can type a statement on one line, or break it up onto multiple lines. Whatever works best for you.

SELECT

SELECT column1, column2 (use names of columns you want to select)
FROM table_name;

How to use SELECT

Click "Query" tab at top
Type in preferred code, Click "Run Current"
Results will show in bottom half of window

WHERE

If you are making a comparison to a string (ie, title = 'First article') then you need to enclose the text in single quotes

SELECT column1, column2
FROM table_name
WHERE condition;

Can also use AND, OR if you want to include two conditions in WHERE clause

Examples:

WHERE title = 'An interesting post';
WHERE id = 2 OR id = 3;

INDEXES

Indexes make queries faster and order the result set

Click "Structure" tab
Find "Indexes" halfway down the window
Click "+" below that box
Leave - Key Type: Index
You can add a Key Name or leave it blank and the database will fill it in for you
Under "Field" click the drop down menu and choose the field you would like to index
Click "Add"
List of indexes will show in "Indexes" field on "Structure" tab

ORDER BY

You can set one or more columns for ordering the results. By default they sort in ascending order. To order by descending order use: ORDER BY column1 DESC;

You should have an index set for any information you want to ORDER BY

SELECT *
FROM table_name
ORDER BY published_at;

Connect to the Database from PHP

Need four pieces of information:

Address of database server (localhost)
Username of database
Password for database
Name of database

Using MYSQLI connect

Create a file with the following information, save, and run in browser

<?php
$db_host = "localhost";
$db_user = "enterUsernameHere";
$db_pass = "enterPasswordHere";
$db_name = "enterDatabaseName";
$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name)

→ $conn must be listed in this exact order

if (mysqli_connect_error()) {
echo mysqli_connect_error();
exit;
}
echo "Connected successfully";

Run in browser and check if it connected

Query the Database from PHP and Get Results

Use the same file with login info
At bottom, add query (procedural style):

$sql = "SELECT * (or multiple items separated with a comma)
FROM article
ORDER BY published_at;";
$results = mysqli_query($conn, $sql);
if ($results === false) {
echo mysqli_error($conn);
} else {
$articles = mysqli_fetch_all($results, MYSQLI_ASSOC);
print_r($articles);
}

By using MYSQLI_ASSOC it gives you the column names from the table of indexes instead of just index numbers

Combine PHP & HTML

It is best practice to put all of the PHP at the top of the file with opening and closing brackets.

HTML should follow, and should start out with !DOCTYPE.

Example showing Results as a Formatted List in HTML:

<?php

$db_host = '127.0.0.1';
$db_user = "#";
$db_pass = "#";
$db_name = "#";

$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);

if (mysqli_connect_error()) {
echo mysqli_connect_error();
exit;
}

$sql = "SELECT *
FROM article
ORDER BY published_at;";

$results = mysqli_query($conn, $sql);

if ($results === false) {
echo mysqli_error($conn);
} else {
$articles = mysqli_fetch_all($results, MYSQLI_ASSOC);
}
?>

<!DOCTYPE html>
<html>
<head>
<title>My blog</title>
<meta charset="utf-8">
</head>
<body>
<header>
<h1>My blog</h1>
</header>
<main>
<?php if (empty($articles)): ?>
<p>No articles found.</p>
<?php else: ?>
<ul>
<?php foreach ($articles as $article): ?>
<li>
<article>
<h2><?= $article['title']; ?></h2>
<p><?= $article['content']; ?></p>
</article>
</li>
<?php endforeach; ?>
</ul>
<?php endif; ?>
</main>
</body>
</html>

Add a New Page to Show a Single Article & Passing Data in the URL

You can start this by just copy/pasting the code from the index.php file (same as above).

Changes:

Save new file as article.php
Change $sql query by deleting ORDER BY, and replace with WHERE
We only want one article instead of all of them like on index page. We do this by referencing by index number
Change the $articles = mysqli_fetch_all($results, MYSQLI_ASSOC); within the if/else within PHP at top to:

$article = mysqli_fetch_assoc($results);
→ This will only fetch one result at a time

Now that there is only one result being fetched, we need to change the loop below in the HTML to reflect that
Remove:

<ul>
<?php foreach ($articles as $article): ?>
<li>

Also remove:

</li>
<?php endforeach; ?>
</ul>

Change if statement in HTML to: (to reflect if the answer is null)

<?php if ($article===null): ?>
<p>Article not found.</p>
<?php else: ?>

Concatenate the value from the query string to the end of the $sql string: WHERE id = " . $_GET['id'];, refresh screen, then type in ?id=1 at end of URL to get post

Create Links to Each Blog Post on Index Page

Create link to article.php from index.php passing through the id in the query string.
Make each article on index.php into a link to article.php by wrapping the title with an anchor tag.
Just add ?id= to make article.php?id in anchor tag.
<h2><a href="article.php?id=<?=$article['id']; ?>"><?= $article['title'];?></a></h2>

Passing Data in the URL

Query String

Pass in info using the URL http://example.com/page.php?page=2

Any text after the ? is the query string
We can access this text inside the script file - use Superglobal $_SERVER
Query strings are traditionally used to pass values to a script as key/value pairs (ie - id=1)

Create a new file: querystring.php

<?php
var_dump($_SERVER["QUERY_STRING"]);
If you run this in the web browser, you will get string(0) ""
If you type ?hello into the URL on the end of the querystring.php, you will get string(5) "hello"

$_GET

$_GET superglobal - this $_GET array returns an associative array of any variables sent in the query string

<?php
var_dump($_SERVER["QUERY_STRING"]);
var_dump($_GET);
Type ?id=1 into the URL on the end of the querystring.php

→ string(4) "id=1" array(1) {['id']=>string(1)"1"}

You can add more variables to the query string by separating them with an ampersand → &

http://example.com/page.php?id=1&name=Katie

Avoid SQL Injection

isset & is_numeric

Avoid SQL Injection: Validate the ID Passed in from the Query String

You can validate the ID to make sure it is a number, and not another SQL command
If someone removes the ? in the query string your page will show a detailed error message. That is bad because it could give details to an attacker that they can then exploit.
Need to add isset (determine if a variable is set and not NULL) and is_numeric with && - must be on article.php

Code to add to validate ID in Query String:

(Above $sql = "SELECT...)
if (isset($_GET['id']) && is_numeric($_GET['id'])) {
(End of PHP, add so it's last)
} else {
$article = null;
}

It is important to avoid detailed error messages on your website once your website is in production on a live server. Information like this could potentially give a hacker details that they could exploit.

Require Statement

Don't Repeat Yourself: Extract Repeated Code to a Separate File

Take repeated code and put it in its own file. Add a script on each page to run the repeated code file.

require Statement

require is identical to include except upon failure it will produce a fatal level error. (include will produce an error, but allow the script to continue.)

Steps Needed

Create an 'includes' folder to keep all of these files
For example, if you want to include a database.php file, you will need to use a require statement at the top of every page you want to require it.

<?php
require 'includes/database.php';
?>

database.php

Put all of your login credentials here so if you change your password in the future you only have to update it in one place.

header.php

Include all code from <!DOCTYPE html> through the main heading inside the body.

footer.php

Include any JavaScript files and other footer files here.

.htaccess Files

This allows us to configure the web server on a per-directory basis.

Create a .htaccess file in the includes file.
Type: Deny from all
Save

Now others cannot access your other 'includes' files in case there is a PHP malfunction on the server.

Inserting Data into the Database

INSERT INTO Statement

Tips for data entry

Under VALUES, numbers/NULL do not need quotes
Strings and Date/Times need single quotes

Multiple Options for Entering Data:

Add in data directly into all columns using SQL and Sequel Pro

INSERT INTO table_name
VALUES (value1, value2, value3);

You have to put a value in for each column in the table, and they have to be in the same exact order

Directions for Sequel Pro:

Click 'Query' tab at top
Type an entry

Example: INSERT INTO article
VALUES (200, 'Title here', 'Content here', '2020-09-21 12:12:12');

Click 'Content' tab at top to see entry in table
If not showing, refresh database from pull down menu (Database → Refresh Tables)

Only Insert Data into Specific Columns using SQL and Sequel Pro

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

Directions for Sequel Pro:

Click 'Query' tab at top
Type an entry

Example: INSERT INTO article
VALUES ('Another article', 'Some Content');

Click 'Content' tab at top to see entry in table

Add Multiple Records at Once With One INSERT Statement

INSERT INTO table_name (column1, column2)
VALUES (value1, value2),
(value3, value4),
(value5, value6);

Directions for Sequel Pro:

Click 'Query' tab at top
Type an entry

Example: INSERT INTO article (title, content)
VALUES ('Yet Another', 'More Content'),
('And another', 'Even More');

You can specifically use NULL in a column if it is set to allow NULL

SQL

SequelPro

Create a New Table

Add Columns to Table

Add Data to Columns

SQL - Data Manipulation Statements

SELECT

WHERE

INDEXES

ORDER BY

Connect to the Database from PHP

Using MYSQLI connect

Query the Database from PHP and Get Results

Combine PHP & HTML

Add a New Page to Show a Single Article & Passing Data in the URL

Create Links to Each Blog Post on Index Page

Passing Data in the URL

Query String

$_GET

Avoid SQL Injection

isset & is_numeric

Require Statement

require Statement

Steps Needed

database.php

header.php

footer.php

.htaccess Files

Inserting Data into the Database

INSERT INTO Statement

©2022 coderkatie.com All rights Reserved.